[R] RODBC sqlSave with multiple schema in DB2

From: Aliza R Heching <ahechi_at_us.ibm.com>
Date: Tue, 06 May 2008 20:57:48 -0400


Hi,

I am trying to use sqlSave to write a dataframe to an existing table in a DB2 database. The database contains two schemas. My experience is the following: (1) in the case that tablename is left empty in sqlSave, R writes to the instance level schema (2) in the case that a tablename is specified and that table name exists only once in the database, R will write to that table irrespective of the schema in which it resides (3) in the case that a tablename is specified and the table name appears in more than one schema in the database, R seems to be concatenating the fields from both tables and thus the sqlSave does not work.

Searching the archives, the most recent discussion on this issue that I could find was: http://tolstoy.newcastle.edu.au/R/e3/help/07/11/3373.html where it was suggested to set schema prior to sqlSave. This does not work.

Below is the error as well as traceback():

>

sqlSave(myConnection,merged,tablename="PCCSLANOTMETBYSEVERITY",append=T,rownames=F,colnames=F,verbose=T,oldstyle=F, safer=TRUE,addPK=F,fast=F,test=FALSE,nastring="") Error in dimnames(x) <- dn :
  length of 'dimnames' [2] not equal to array extent
>
> traceback()

3: `colnames<-`(`*tmp*`, value = c("COC_ID", "ACCOUNT_ID", "COMPETENCY",

   "SERVICE_LINE", "WORKSTREAM_ID", "SPC_LEVEL", "SNAPSHOT_ID", 
   "SEVERITY_ID", "MR", "PERCENTSLA_NOTMET", "TIME_STAMP", "WID", 
   "ACCOUNT_NAME", "SNAPSHOT_ID", "SEVERITY_ID", "MR", 
"PERCENTSLA_NOTMET",
   "TIME_STAMP", "WID"))
2: sqlwrite(channel, tablename, dat, verbose = verbose, fast = fast,

       test = test, nastring = nastring) 1: sqlSave(myConnection, merged, tablename = "PCCSLANOTMETBYSEVERITY",

       append = T, rownames = F, colnames = F, verbose = T, oldstyle = F, 
       safer = TRUE, addPK = F, fast = F, test = FALSE, nastring = "")

>

The fields in italic in 3: are from table PCCSLANOTMETBYSEVERITY in one schema in the db and the remaining fields are from table PCCSLANOTMETBYSEVERITY in the other schema in the db. R appears to be concatenating the fields from the tables in both schemas, uncertain to which table I wish to write.

Does sqlSave allow one to specify table name and schema when writing out a dataframe?

Thanks.

Aliza

        [[alternative HTML version deleted]]



R-help_at_r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code. Received on Wed 07 May 2008 - 01:02:22 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Wed 07 May 2008 - 02:30:35 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.

list of date sections of archive