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