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

[Sean Davis]

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