prepared query with RODBC ?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

prepared query with RODBC ?

lgautier
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

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: prepared query with RODBC ?

Uwe Ligges


On Mon, 27 Feb 2006, Laurent Gautier wrote:

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


People might be able to help if you are more specific. See the posting
guide.

Uwe Ligges
 

> Thanks,
>
>
>
> Laurent
>
> ______________________________________________
> [hidden email] mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
>

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: prepared query with RODBC ?

McGehee, Robert
In reply to this post by lgautier
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: [hidden email]
[mailto:[hidden email]] On Behalf Of Laurent Gautier
Sent: Monday, February 27, 2006 9:38 AM
To: [hidden email]
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

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide!
http://www.R-project.org/posting-guide.html

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: prepared query with RODBC ?

lgautier
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 <[hidden email]> 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: [hidden email]
> [mailto:[hidden email]] On Behalf Of Laurent Gautier
> Sent: Monday, February 27, 2006 9:38 AM
> To: [hidden email]
> 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
>
> ______________________________________________
> [hidden email] mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide!
> http://www.R-project.org/posting-guide.html
>
>

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: prepared query with RODBC ?

McGehee, Robert
In reply to this post by lgautier
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:[hidden email]]
Sent: Thursday, March 02, 2006 3:20 AM
To: McGehee, Robert
Cc: [hidden email]
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 <[hidden email]> 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: [hidden email]
> [mailto:[hidden email]] On Behalf Of Laurent Gautier
> Sent: Monday, February 27, 2006 9:38 AM
> To: [hidden email]
> 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
>
> ______________________________________________
> [hidden email] mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide!
> http://www.R-project.org/posting-guide.html
>
>

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: prepared query with RODBC ?

David James-3
In reply to this post by lgautier
Perhaps this thread should be continued in the r-sig-db list?

Laurent Gautier wrote:

> 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
>
> ______________________________________________
> [hidden email] 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 applications that require a tighter interface to DBMSs could
greatly benefit from such a facility, but prepared statements
have not been used much from R, AFAIK.  

In a nutshell, a prepared statement is an SQL statement that is
parsed, optimized, cached in the server, and then repeatedly executed
with new data (using what is called "data binding").  Some of its
benefits are significant improved performance, breaking big tasks
into smaller, more manageable tasks, etc.

A trivial example:

  ## prepare an SQL statement for repeated insertions, and bind
  ## output data.frame columns 1, 2, 3, and 4 to the SQL statement

  ps <- dbPrepareStatement(conn,
           "INSERT into SCORING (id, x1, x2, score) VALUES (:1,:2,:3,:4)",
           bind = c("char", "char", "numeric", "numeric"))
 
  ## compute new scores....
  while(condition){
     ...
     new_scores <- predict(model, newdata)
     dbExecStatement(ps, data = new_scores)
 
  }
  dbCommit(con)
  dbClearResult(ps)

I believe most DBMSs provide means to do this (PostgreSQL, MySQL,
Sybase, Oracle, SQLite, ODBC 3.0, ...), but I think only the
R-Oracle interface currently implements them (and only in an
experimental basis).

Regards,

--
David

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
Loading...