Re: [R] RODBC sqlSave does not append the records to a DB2 table
Hello Caveman! I am able to append records using other applications. My credentials have the authority to append. I am able to query to select records, and even delete records from the remote DB2 table using RODBC. I'm just stuck when it comes to APPEND. The DB2 database is on an AIX server, and I am on a Windows XP laptop. Elaine McGovern Jones ISC Tape and DASD Storage Products Characterization and Failure Analysis Engineering Phone: 408 705-9588 Internal tieline: 587-9588 jon...@us.ibm.com [[alternative HTML version deleted]] __ R-help@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.
Re: [R] RODBC sqlSave does not append the records to a DB2 table
Hi Felipe, Thanks for your message. Your approach works for an Access database. I am trying to insert records into a table in a DB2 database on a remote AIX server. My userid has the authority to insert records, and I am able to do that using another application (DB2 Client command line). Sincerely, Elaine McGovern Jones ISC Tape and DASD Storage Products Characterization and Failure Analysis Engineering Phone: 408 705-9588 Internal tieline: 587-9588 jon...@us.ibm.com [[alternative HTML version deleted]] __ R-help@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.
Re: [R] RODBC sqlSave does not append the records to a DB2 table
Now it becomes strange. One thing I note from this generated query: INSERT INTO STORAGE.TEST_APPEND2 ( MACRONAME, MACROUSER, MACRO_RT ) VALUES ( 's_ej_mach_config_vz', 'jones2', 5 ) The names of the variables are in double quotes; That is a problem. Can you try to run this query on another application and see what you get. Caveman On Tue, Oct 20, 2009 at 7:05 PM, Elaine Jones jon...@us.ibm.com wrote: Hi Felipe, Thanks for your message. Your approach works for an Access database. I am trying to insert records into a table in a DB2 database on a remote AIX server. My userid has the authority to insert records, and I am able to do that using another application (DB2 Client command line). Sincerely, Elaine McGovern Jones ISC Tape and DASD Storage Products Characterization and Failure Analysis Engineering Phone: 408 705-9588 Internal tieline: 587-9588 jon...@us.ibm.com [[alternative HTML version deleted]] __ R-help@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. __ R-help@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.
Re: [R] RODBC sqlSave does not append the records to a DB2 table
The query generated by R looks ok to me. I went ahead and pasted it into my DB2 Client command editor. And I got the message confirmation message. INSERT INTO STORAGE.TEST_APPEND2 ( MACRONAME, MACROUSER, MACRO_RT ) VALUES ( 's_ej_mach_config_vz', 'jones2', 5 ) DB2I The SQL command completed successfully. And I confirmed the record was added to the table. Elaine McGovern Jones ISC Tape and DASD Storage Products Characterization and Failure Analysis Engineering Phone: 408 705-9588 Internal tieline: 587-9588 jon...@us.ibm.com [[alternative HTML version deleted]] __ R-help@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.
Re: [R] RODBC sqlSave does not append the records to a DB2 table
Well I do not know what could be happening. I tried to append records to a table on DB2 (community Edition 9.5 on a Linux machine). And it works greatly. The only odd thing (may be because of my low skills on DB2) is I could not create neither append to a table on a different schema than dbuser (actually my account was dbuser). My R version is 2.7.1 under another Linux PC and RODBC is 1.2-3. Caveman On Tue, Oct 20, 2009 at 8:45 PM, Elaine Jones jon...@us.ibm.com wrote: The query generated by R looks ok to me. I went ahead and pasted it into my DB2 Client command editor. And I got the message confirmation message. INSERT INTO STORAGE.TEST_APPEND2 ( MACRONAME, MACROUSER, MACRO_RT ) VALUES ( 's_ej_mach_config_vz', 'jones2', 5 ) DB2I The SQL command completed successfully. And I confirmed the record was added to the table. Elaine McGovern Jones ISC Tape and DASD Storage Products Characterization and Failure Analysis Engineering Phone: 408 705-9588 Internal tieline: 587-9588 jon...@us.ibm.com __ R-help@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.
Re: [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 jon...@us.ibm.com wrote: From: Elaine Jones jon...@us.ibm.com Subject: [R] RODBC sqlSave does not append the records to a DB2 table To: r-help@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 jon...@us.ibm.com [[alternative HTML version deleted]] __ R-help@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. __ R-help@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.
Re: [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 jon...@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 jon...@us.ibm.com [[alternative HTML version deleted]] __ R-help@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. __ R-help@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.
