prepared query with RODBC ?

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 at 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 at 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 at gmail.com]
Sent: Thursday, March 02, 2006 3:20 AM
To: McGehee, Robert
Cc: r-help at 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 at 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 at stat.math.ethz.ch
[mailto:r-help-bounces at stat.math.ethz.ch] On Behalf Of Laurent Gautier
Sent: Monday, February 27, 2006 9:38 AM
To: r-help at 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