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_DATETIME_SUB
[16] CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE
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