Re: [R] RODBC and sqlColumns

From: Ben Stabler <>
Date: Thu 18 Aug 2005 - 00:52:42 EST

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.



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

-----Original Message-----
From: Prof Brian Ripley [] Sent: Wednesday, August 17, 2005 12:59 AM To: Ben Stabler
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
> <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,        
Professor of Applied Statistics,
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595

______________________________________________ mailing list
PLEASE do read the posting guide!
Received on Thu Aug 18 00:59:43 2005

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