Skip to content

Incorrect Error Status RODBC 1.2-3

2 messages · McGehee, Robert, Sébastien Bihorel

Original
McGehee, Robert
# 
Hello all,
I'm in the process of upgrading from RODBC 1.1-9 to 1.2-3 and have
discovered a problem in which RODBC will correctly run queries
containing CREATE and DROP but will incorrectly report an error status
of -1 resulting in an error in R (despite no actual error occurring). I
wanted to see if anyone has come across this before. Note that when I
run this script using RODBC 1.1-9 the queries run successfully without
returning a -1 error code.
errors = FALSE)
[1] -1
[1] "[RODBC] ERROR: Could not SQLExecDirect"

However, the table is created!
character(0)
fun
1   1
[1] -1
odbcGetErrMsg(.conn)
[1] "[RODBC] ERROR: Could not SQLExecDirect"

Yet, the table has, in fact, been dropped!

Setup:
My database is Microsoft SQL Server 8.0 (SQL Server 2000). I'm running
RODBC on a Linux cluster tested on both 32-bit and 64-bit computers
running R 2.7.0. I'm using unixODBC 2.2.12 and FreeTDS 0.64 to interface
with the SQL server.

Any thoughts are appreciated!
Cheers, 
Robert

Robert McGehee, CFA
Geode Capital Management, LLC
One Post Office Square, 28th Floor | Boston, MA | 02109
Tel: 617/392-8396    Fax:617/476-6389
mailto:robert.mcgehee at geodecapital.com



This e-mail, and any attachments hereto, are intended fo...{{dropped:12}}
Sébastien Bihorel
# 
On Mon, 9 Jun 2008 20:04:12 -0400,
"McGehee, Robert" <Robert.McGehee at geodecapital.com> wrote:
[...]
I have a similar problem in the following (with a PostgreSQL db):

---<---------------cut here---------------start-------------->---
R> sessionInfo()
R version 2.7.0 (2008-04-22) 
x86_64-pc-linux-gnu 

locale:
LC_CTYPE=en_CA.UTF-8;LC_NUMERIC=C;LC_TIME=en_CA.UTF-8;LC_COLLATE=en_CA.UTF-8;LC_MONETARY=C;LC_MESSAGES=en_CA.UTF-8;LC_PAPER=en_CA.UTF-8;LC_NAME=C;LC_ADDRESS=C;LC_TELEPHONE=C;LC_MEASUREMENT=en_CA.UTF-8;LC_IDENTIFICATION=C

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] RODBC_1.2-3    lattice_0.17-8

loaded via a namespace (and not attached):
[1] grid_2.7.0
R> sqlQuery(.conn, "CREATE TABLE zzz (aaa VARCHAR(255))", errors=FALSE)
[1] -1
R> odbcGetErrMsg(.conn)
[1] "[RODBC] ERROR: Could not SQLExecDirect"                                          
[2] "42P07 7 ERROR: relation \"zzz\" already exists;\nError while executing the query"
---<---------------cut here---------------end---------------->---

In my case though, I have an extra error message *and* the table is not
created, even though the alleged table does not really exist.  From psql
directly, after quitting R above:

---<---------------cut here---------------start-------------->---
test=> \d
          List of relations
 Schema |   Name    | Type  | Owner  
--------+-----------+-------+--------
 public | cities    | table | sluque
 public | test_city | view  | sluque
 public | weather   | table | sluque
(3 rows)
---<---------------cut here---------------end---------------->---

So I'm also baffled at this.  I'm using unixodbc 2.2.11 to connect to
the PostgreSQL 8.3.1 db, in a Debian sid 64 bit system.  This didn't
happen in a previous RODBC version, although I can't recall which one it
was.