[R-sig-DB] Incorrect Error Status RODBC 1.2-3

[Sebastian P. Luque]

On Mon, 9 Jun 2008 20:04:12 -0400,
"McGehee, Robert" <Robert.McGehee using geodecapital.com> wrote:

[...]

>> sqlQuery(.conn, "CREATE TABLE dbo.aaa (fun VARCHAR(255))",
> 	     errors = FALSE) [1] -1
>> odbcGetErrMsg(.conn)
> [1] "[RODBC] ERROR: Could not SQLExecDirect"

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.


-- 
Seb