Try append = F, that works for me. A
----- Original Message ----- From: "Ben Stabler" <[EMAIL PROTECTED]> To: <[email protected]> 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:[EMAIL PROTECTED] > Sent: Wednesday, August 17, 2005 12:59 AM > To: Ben Stabler > Cc: [email protected] > 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, [EMAIL PROTECTED] > 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 > > ______________________________________________ > [email protected] mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide! > http://www.R-project.org/posting-guide.html > ______________________________________________ [email protected] mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
