Re: [R] RODBC and sqlColumns

From: Andreas Hary <u08adh_at_hotmail.com>
Date: Thu 18 Aug 2005 - 04:39:57 EST

Try append = F, that works for me.

A

> 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@stats.ox.ac.uk]
> Sent: Wednesday, August 17, 2005 12:59 AM
> To: Ben Stabler
> Cc: r-help@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@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@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
>



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 Received on Thu Aug 18 04:50:47 2005

This archive was generated by hypermail 2.1.8 : Sun 23 Oct 2005 - 15:27:41 EST