[R] RODBC and sqlColumns

Wed Aug 17 07:50:43 CEST 2005

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