[R] prepared query with RODBC ?

[McGehee, Robert]

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
>
> ______________________________________________
> R-help at 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
>
>