[R] RODBC and sqlColumns

From: Ben Stabler <bstabler_at_ptvamerica.com>
Date: Wed 17 Aug 2005 - 15:50:43 EST


I have a Postgres database that I am connecting to with the Postgres ODBC driver on Windows XP in R 2.1.0. In the database is a database with two schemas (public and X). With RODBC (1.1-4) , I can connect to the database and get the tables with sqlTables(db). I can query tables in the schema with sqlQuery("SELECT * FROM X.test"). However, I can't get the columns in table X.test with sqlColumns(db,"X.test") //it returns  

Error in sqlColumns(db, "X.test") : 'X.test': table not found on channel

If I do  

sqlColumns(db, "test") it returns

 [1] TABLE_QUALIFIER   TABLE_OWNER       TABLE_NAME        COLUMN_NAME
DATA_TYPE        
 [6] TYPE_NAME         PRECISION         LENGTH            SCALE
RADIX            

[11] NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE

SQL_DATETIME_SUB
[16] CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE DISPLAY_SIZE
FIELD_TYPE

<0 rows> (or 0-length row.names)

But there is no test table defined anywhere else but the X schema. If I do sqlSave(db,aDataFrame,"X.test",T,F), it says test already defined. If I change the aDataFrame to be different than the fields actually in the data, then R starts to create a new table but returns  

Error in sqlColumns(db, "X.test") : 'X.test': table not found on channel

It seems to be having problems with what is returned by the columns.....since  

Error in sqlSave(db, aDataFrame, "X.test", T, F) :

        [RODBC] ERROR: Could not SQLExecDirectS1000 7 ERROR: relation "test" already exists

but if I change the input table to be different....then R can create the table, but fails to populate it. I checked the db in PgAdmin and the table is created by the sqlSave call. All this stuff works if I don't use a schema "schema.table". So it appears there is something wrong in some place dealing with understanding the columns for tables in schemas.  

Any ideas? Any help would be much appreciated. Thank you.

Ben Stabler
Project Manager
PTV America, Inc.
1128 NE 2nd St, Suite 204
Corvallis, OR 97330
541-754-6836 x205
541-754-6837 fax
www.ptvamerica.com

Ben Stabler
Project Manager
PTV America, Inc.
1128 NE 2nd St, Suite 204
Corvallis, OR 97330
541-754-6836 x205
541-754-6837 fax
www.ptvamerica.com



R-help@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 Received on Wed Aug 17 15:56:19 2005

This archive was generated by hypermail 2.1.8 : Fri 03 Mar 2006 - 03:39:50 EST