Elaine Jones
2009-Oct-16 23:33 UTC
[R] RODBC sqlSave does not append the records to a DB2 table
I am running R version 2.9.2 on Windows XP OS with RODBC version Version: 1.3-0. Has anyone out there in the R user community successfully appended records to a DB2 table on a remote database using the sqlSave function in the RODBC package? (or by any other means from R?) I posed a similar question a few months ago and unfortunately, did not receive a response. I was hoping recent upgrades to our DB2 on the database, and I installed the current version RODBC. Unfortunately, it did not bring any joy. I asked the database adminstrator try it, and she had a similar experience. No error message is returned, but the record is not inserted to the table. For testing purposes, I have a very simple one-row, three-column data.frame (se2) I want to insert into a DB2 table.> sqlSave(channel, se2, tablename = "STORAGE.TEST_APPEND2", append = TRUE,+ rownames = FALSE, colnames = FALSE, + verbose = TRUE, + safer = TRUE, addPK = FALSE, + fast = FALSE, test = FALSE, nastring = NULL) Query: INSERT INTO STORAGE.TEST_APPEND2 ( "MACRONAME", "MACROUSER", "MACRO_RT" ) VALUES ( 's_ej_mach_config_vz', 'jones2', 5 )>I don't get any error message, but when I check the table row count, the record has not been added to the table. Any suggestions for how to resolve are appreciated! Sincerely, **************** Elaine McGovern Jones ************************ ISC Tape and DASD Storage Products Characterization and Failure Analysis Engineering Phone: 408 705-9588 Internal tieline: 587-9588 jones2@us.ibm.com [[alternative HTML version deleted]]
Felipe Carrillo
2009-Oct-18 03:20 UTC
[R] RODBC sqlSave does not append the records to a DB2 table
Elaine:
Try this.
It works for me using my own database. Perhaps you don't have admin rights.
Good luck
library(RODBC)
myDB <-
odbcConnectAccess("DB2.mdb",uid="admin",pwd="")
sqlSave(myDB,se2,rownames=FALSE,append=TRUE)
close(myDB)
Felipe D. Carrillo
Supervisory Fishery Biologist
Department of the Interior
US Fish & Wildlife Service
California, USA
--- On Fri, 10/16/09, Elaine Jones <jones2 at us.ibm.com> wrote:
> From: Elaine Jones <jones2 at us.ibm.com>
> Subject: [R] RODBC sqlSave does not append the records to a DB2 table
> To: r-help at r-project.org
> Date: Friday, October 16, 2009, 4:33 PM
>
>
> I am running R version 2.9.2 on? Windows XP OS with
> RODBC version Version:
> 1.3-0.
>
> Has anyone out there in the R user community successfully
> appended records
> to a DB2 table on a remote database using the sqlSave
> function in the RODBC
> package? (or by any other means from R?)
>
> I posed a similar question a few months ago and
> unfortunately, did not
> receive a response.? I was hoping recent upgrades to
> our DB2 on the
> database, and I installed the current version RODBC.
> Unfortunately, it did
> not bring any joy. I asked the database adminstrator try
> it, and she had a
> similar experience.? No error message is returned, but
> the record is not
> inserted to the table.
>
> For testing purposes, I have a very simple one-row,
> three-column
> data.frame (se2) I want to insert into a DB2 table.
>
> > sqlSave(channel, se2, tablename > "STORAGE.TEST_APPEND2",
append = TRUE,
> +? ? ? ???rownames = FALSE,
> colnames = FALSE,
> +? ? ? ???verbose = TRUE,
> +? ? ? ???safer = TRUE, addPK
> = FALSE,
> +???fast = FALSE, test = FALSE, nastring > NULL)
>
> Query: INSERT INTO STORAGE.TEST_APPEND2 ( "MACRONAME",
> "MACROUSER",
> "MACRO_RT" ) VALUES ( 's_ej_mach_config_vz',
'jones2', 5 )
> >
>
> I don't get any error message, but when I check the table
> row count, the
> record has not been added to the table.
>
> Any suggestions for how to resolve are appreciated!
> Sincerely,
>
> **************** Elaine McGovern Jones
> ************************
>
> ISC Tape and DASD Storage Products
> ? ???Characterization and Failure
> Analysis Engineering
> ? ? ???Phone:? 408?
> 705-9588? Internal tieline: 587-9588
> ? ? ???jones2 at us.ibm.com
>
>
>
>
> ??? [[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.
>
Orvalho Augusto
2009-Oct-18 04:49 UTC
[R] RODBC sqlSave does not append the records to a DB2 table
Hello! Before you try from R. Can you tell us what happens when you use another DB2 client when you try with those credentials? One thing to note: If you are trying to access DB2 from ODBC on Linux on DB2 versions before 9.4 there are some particular issues and better check this http://holmwood.id.au/~lindsay//2007/10/26/setting-up-unixodbc-with-a-remote-db2/ Caveman On Sat, Oct 17, 2009 at 1:33 AM, Elaine Jones <jones2 at us.ibm.com> wrote:> > > I am running R version 2.9.2 on ?Windows XP OS with RODBC version Version: > 1.3-0. > > Has anyone out there in the R user community successfully appended records > to a DB2 table on a remote database using the sqlSave function in the RODBC > package? (or by any other means from R?) > > I posed a similar question a few months ago and unfortunately, did not > receive a response. ?I was hoping recent upgrades to our DB2 on the > database, and I installed the current version RODBC. Unfortunately, it did > not bring any joy. I asked the database adminstrator try it, and she had a > similar experience. ?No error message is returned, but the record is not > inserted to the table. > > For testing purposes, I have a very simple one-row, three-column > data.frame (se2) I want to insert into a DB2 table. > >> sqlSave(channel, se2, tablename = "STORAGE.TEST_APPEND2", append = TRUE, > + ? ? ? ? rownames = FALSE, colnames = FALSE, > + ? ? ? ? verbose = TRUE, > + ? ? ? ? safer = TRUE, addPK = FALSE, > + ? fast = FALSE, test = FALSE, nastring = NULL) > > Query: INSERT INTO STORAGE.TEST_APPEND2 ( "MACRONAME", "MACROUSER", > "MACRO_RT" ) VALUES ( 's_ej_mach_config_vz', 'jones2', 5 ) >> > > I don't get any error message, but when I check the table row count, the > record has not been added to the table. > > Any suggestions for how to resolve are appreciated! > Sincerely, > > **************** Elaine McGovern Jones ************************ > > ?ISC Tape and DASD Storage Products > ? ? Characterization and Failure Analysis Engineering > ? ? ? Phone: ?408 ?705-9588 ?Internal tieline: 587-9588 > ? ? ? jones2 at us.ibm.com > > > > > ? ? ? ?[[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. >