[R] prepared query with RODBC ?

[McGehee, Robert]

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