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
______________________________________________
R-help@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
