[R] RODBC and sqlColumns

[Ben Stabler]

Ok, I understand that.  Then, how do I get the columns for a table that
is housed in a schema?  And, second, why does the following not work (or
at least partially work).  It creates the new table in the X schema but
then does not populate the table (and returns a sqlColumns() error.

sqlSave(db,x,"X.test",T,F)

Thanks.

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



-----Original Message-----
From: Prof Brian Ripley [mailto:ripley at stats.ox.ac.uk] 
Sent: Wednesday, August 17, 2005 12:59 AM
To: Ben Stabler
Cc: r-help at stat.math.ethz.ch
Subject: Re: [R] RODBC and sqlColumns


AFAIK "." is not a valid part of an SQL table name. I think the help
files 
are perfectly clear as to what is supported:

  sqtable: character: a database table name accessible from the
           connected dsn.

Why do you think "X.test" is a `database table name'?

On Tue, 16 Aug 2005, Ben Stabler wrote:

> 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.

-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595