[R-sig-DB] Follow-up: prepared query with RODBC ?

[Laurent Gautier]

Dear all,

I have been experimenting with RODBC, and prepared queries.

My setting was as follows:
* server:
   - Linux desktop
   - latest stable version for Postgres DBM (version 8.1.3)
* client:
   - Windows XP notebook
   - 'psqlodbc' Postgres-ODBC drivers (latest stable version from
http://pgfoundry.org/projects/psqlodbc/), and use of the
"Postgres-unicode" driver
   - R-2.2.1
   - RODBC-1.1.5

This seemed a reasonable standard setting to me
(and felt at times enough like opening a Pandora's box to keep me from
venturing into devel versions for any of the components).

The outcome is 1) a bug (and a suggested patch), and 2) the belief
that it is not possible for pulling the result of 'SELECT' statements
without writting C code (which I had no for):

1- There is apparently a problem triggered when calling 'sqlSave' with
a data.frame that contains any uppercase letter in the identifiers. I
tracked the problem up to
a call to a C function in "odbcUpdate"

.Call("RODBCUpdate", attr(channel, "handle_ptr"), as.character(query),
    data, cnames, as.integer(nrow(data)), as.integer(ncol(data)),
    as.character(params), as.integer(vflag), PACKAGE = "RODBC")

where the column names in the 'query' were all turned to lower case
while the original names remain in 'data' (the data.frame). A trivial fix could
be to have the names in 'data' converted to lower.case before the .Call
(if this does not cause the copy of a potentially very large 'data'
object, naturally).
This is may be not the best possible fix, but it has the benefit of not touching
too much of what is a non-exported function.

The following demonstrates the problem
library(RODBC)
# assuming the existence of a database 'testdb' and the priviledges to
write to it
channel <- sqlConnect("testdb")
dataf <- data.frame(ID = 1:10, values = rnorm(10))
sqlSave(channel, dataf)
## error about a missing column name

The problem is probably not noticeable on systems that have problems
distinguishing upper-case from lower-case (e.g., MS-Windows).

2- My attempt at having prepared/batch queries ended with the conclusion that
one needs to extend the current package with C code to achieve for 'SELECT'
queries.
The C function 'RODBCUpdate' can be used for 'INSERT', 'UPDATE' or
'DELETE' statements.
In the meanwhile, a hack can be to write temporary tables in the database
(especially since the writting of tables can used prepared statements)
and perform join operation on these (although this can be hair-rising
in a multiple-users setting).


Laurent