[R] prepared query with RODBC ?

[Laurent Gautier]

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