[R-sig-DB] [R] prepared query with RODBC ?

[David James]

Hi Sean,

MySQL also provides server-side or SQL-level PREPARE statements
as of version 4.1.3, and behave similarly to what you describe;
however, in general server-side prepared statements are not
as efficient as "binary" or C-level prepared statements when
it comes to transferring large amounts of data, which is what
one may want/need to do from R.

--
David

Sean Davis wrote:
> Laurent,
> 
> What database are you using?  Some, like postgresql (just because I use it)
> support server-side prepared statements, something like:
> 
> PREPARE my_prepped_stmt(text,text) as
>  SELECT * 
>    FROM mytable
>   WHERE col1 = $1 AND
>         col2 = $2;
> 
> Followed by:
> 
> EXECUTE my_prepped_stmt('test1','test2');
> 
> The PREPAREd statement can be sent to the server as a SQL command and will
> remain on the server for the life of the connection.  You can then use
> successive RODBC calls to EXECUTE that query as often as you like and get
> the benefit of a PREPAREd statement.  Doing this two-step PREPARE/EXECUTE on
> the server allows you to overcome the lack of prepared statements via the
> client interface.  I don't know which other database engines offer such
> functionality, but it would probably be worth a quick question on the
> RDMS-specific mailing list or a glance at the documentation for a PREPARE
> syntax.
> 
> Hope that helps.
> 
> Sean
> 
> 
> On 3/2/06 11:06 PM, "Laurent Gautier" <lgautier using gmail.com> wrote:
> 
> > As said, my experience with (R)ODBC is limited.
> > I am trying to decompose the steps to be clear (preparation of the
> > query with placeholders
> > marked with ? or :1, :2... then run of the prepared query with values for
> > the placeholders). From some of the database connectivity kits I also
> > know of (non-R ones), both steps can be bundled.
> > According to your first paragraph, we may be talking about the same
> > thing (and I do mean "preparing" in what you call a traditional sense).
> > 
> > The answer from David James (in this thread), matches what I am after.
> > 
> > 
> > Thanks.
> > 
> > 
> > Laurent
> > 
> > On 3/2/06, McGehee, Robert <Robert.McGehee using geodecapital.com> wrote:
> >> Well, I'm still not sure what you're trying to do, specifically because
> >> I don't understand your distinction here between "preparing" and
> >> "running" a query, especially if you do not mean preparing in the
> >> traditional sense, i.e. pre-process a dynamic SQL query so that it can
> >> be run multiple times without re-processing.
> >> 
> >> Certainly, however, you can run a query and fetch its results in two
> >> different steps using RODBC, and this can actually be quite useful for
> >> fine-tuning performance and inserting error control in complex queries.
> >> One does this by first using the odbcQuery() function to run the query
> >> and then the sqlGetResults() function to fetch the rows (if as you said,
> >> there are rows to be fetched).
> >> 
> >> If you are more interested in batch processing multiple SQL queries,
> >> which is not the same as preparing a query, an easy way to do this is to
> >> just define a stored procedure and then run the stored procedure using
> >> sqlQuery or odbcQuery, or alternatively use transaction control. You can
> >> send an entire batch statement as a single string to sqlQuery.
> >> 
> >> Lastly, a previous version of RODBC had a function odbcDirectQuery which
> >> I made use of in the past to toggle between direct execution and batch
> >> execution for multiple queries. This was most useful for me if I wanted
> >> to make use of temporary tables. However, the odbcDirectQuery function
> >> is no longer supported in the current version of RODBC, probably because
> >> it was not stable across SQL platforms. That said, you might take a look
> >> at the C code for ideas.
> >> 
> >> HTH,
> >> Robert
> >> 
> >> 
> >> -----Original Message-----
> >> From: Laurent Gautier [mailto:lgautier using gmail.com]
> >> Sent: Thursday, March 02, 2006 3:20 AM
> >> To: McGehee, Robert
> >> Cc: r-help using stat.math.ethz.ch
> >> Subject: Re: [R] prepared query with RODBC ?
> >> 
> >> Well, I may not have been clear enough. My experience with database
> >> drivers
> >> is so far mostly limited to JDBC, Perl's DBI, and some other things with
> >> Python.
> >> I am rather new to (R)ODBC.
> >> 
> >> What I am after is something like:
> >> ## -- dummy R code
> >> pq <- prepareQuery("SELECT * FROM foo WHERE bar = ?", dbHandle)
> >> res <- runQuery(pq, allMyBars, dbHandle)
> >> ## then fetch the query if needed (may be not the case if 'pq'
> >> ## is about updating tables).
> >> 
> >> (as I am just told, this is may be more something like a BATCH query
> >> than
> >> a prepared query stricto senso).
> >> 
> >> I have tracked down things to the C level, with the function
> >> RODBCUpdate, that appear
> >> to do something related (
> >>     res = SQLPrepare( thisHandle->hStmt, (SQLCHAR *) cquery,
> >>                       strlen(cquery) );
> >> can be spotted around line 960)
> >> but the documentation is rare down there, so I was asking if anyone
> >> had experience
> >> on the topic.
> >> 
> >> If I understand correctly your suggestion, the idea would be to build a
> >> complete
> >> set of (Visual Basic ?) instructions into a (potentially very long)
> >> string and send them to the SQL server ?
> >> 
> >> 
> >> Thanks.
> >> 
> >> 
> >> Laurent
> >> 
> >> 
> >> 
> >> On 3/1/06, McGehee, Robert <Robert.McGehee using geodecapital.com> wrote:
> >>> I may be misunderstanding you, but why can't you execute a prepared
> >>> query the same in RODBC as you would directly on your SQL server? In
> >>> Microsoft SQL server, for instance, I would just set up an ADO
> >>> application and set the Prepared and CommandText properties before
> >>> running the query.
> >>> 
> >>> Here is an example from the Microsoft SQL help page. In this example,
> >> I
> >>> would try storing all of the below as a string in R, and simply pass
> >>> this into the odbcQuery or sqlQuery.  However, see the help for your
> >>> specific SQL application. Note that (for at least SQL server) one can
> >>> disable the prepare/execute model, so you might have to check your
> >> ODBC
> >>> settings before running.
> >>> 
> >>> --Robert
> >>> 
> >>> Dim cn As New ADODB.Connection
> >>> Dim cmdPrep1 As New ADODB.Command
> >>> Dim prm1 As New ADODB.Parameter
> >>> Dim prm2 As New ADODB.Parameter
> >>> Dim strCn As String
> >>> 
> >>> strCn = "Server=MyServerName;Database=pubs;Trusted_Connection=yes"
> >>> cn.Provider = "sqloledb"
> >>> cn.Open strCn
> >>> Set cmdPrep1.ActiveConnection = cn
> >>> cmdPrep1.CommandText = "UPDATE titles SET type=? WHERE title_id =?"
> >>> cmdPrep1.CommandType = adCmdText
> >>> cmdPrep1.Prepared = True
> >>> 
> >>> Set prm1 = cmdPrep1.CreateParameter("Type", adChar, adParamInput, 12,
> >>> "New Bus")
> >>> cmdPrep1.Parameters.Append prm1
> >>> 
> >>> Set prm2 = cmdPrep1.CreateParameter("ProductID", adInteger,
> >>> adParamInput, 4, 3)
> >>> cmdPrep1.Parameters.Append prm2
> >>> 
> >>> cmdPrep1.Execute
> >>> 
> >>> cmdPrep1("Type") = "New Cook"
> >>> cmdPrep1("title_id") = "TC7777"
> >>> cmdPrep1.Execute
> >>> 
> >>> cn.Close
> >>> 
> >>> 
> >>> -----Original Message-----
> >>> From: r-help-bounces using stat.math.ethz.ch
> >>> [mailto:r-help-bounces using stat.math.ethz.ch] On Behalf Of Laurent Gautier
> >>> Sent: Monday, February 27, 2006 9:38 AM
> >>> To: r-help using stat.math.ethz.ch
> >>> Subject: [R] prepared query with RODBC ?
> >>> 
> >>> Dear List,
> >>> 
> >>> Would anyone know how to perform prepared queries with ROBC ?
> >>> I had a shot with some of the internal (non-exported) functions of the
> >>> package
> >>> but ended up with a segfault, so I prefer asking around before
> >>> experimenting further...
> >>> 
> >>> Thanks,
> >>> 
> >>> 
> >>> 
> >>> Laurent
> >>> 
> >>> ______________________________________________
> >>> R-help using stat.math.ethz.ch mailing list
> >>> https://stat.ethz.ch/mailman/listinfo/r-help
> >>> PLEASE do read the posting guide!
> >>> http://www.R-project.org/posting-guide.html
> >>> 
> >>> 
> >> 
> >> 
> > 
> > _______________________________________________
> > R-sig-DB mailing list -- R Special Interest Group
> > R-sig-DB using stat.math.ethz.ch
> > https://stat.ethz.ch/mailman/listinfo/r-sig-db
> 
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db