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
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
RODBC and sqlColumns
4 messages · Brian Ripley, Ben Stabler, Andreas Hary
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
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
Try append = F, that works for me. A ----- Original Message ----- From: "Ben Stabler" <bstabler at ptvamerica.com> To: <r-help at stat.math.ethz.ch> Sent: Wednesday, August 17, 2005 3:52 PM Subject: Re: [R] RODBC and sqlColumns
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
______________________________________________ R-help at stat.math.ethz.ch mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html