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

[Laurent Gautier]

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
> >
> >
>
>