search for: addpk

Dear Prof. Ripley et al., I have a data frame with some variable names that contain the underscore character ('_'). It seems that RODBC is not converting them to a suitable variable name for MySQL. > sqlSave(channel, zz, addPK = TRUE, verbose = TRUE) Query: CREATE TABLE zz (rownames varchar(255) PRIMARY KEY, NAME varchar(255), ID varchar(255), GROUP varchar(255), OAC_RT double, OUC_RT double, OAT_RT double, OUT_RT double, OFN double, OFP double, OFP_RT double, TAC_RT double, TUC_RT double, TAT_RT double, TUT_RT double,...

..., "se", "df"), row.names = c(NA, -15L), class = "data.frame") > xlsFile <- odbcConnectExcel("output.xls", readOnly=F) ##the file is created here, it didn't exist before here > sqlSave(xlsFile, test, tablename="test1", rownames=F, addPK=T, append=T) Error in sqlSave(xlsFile, test, tablename = "test1", rownames = F, addPK = T, : HYS21 -1508 [Microsoft][Pilote ODBC Excel] Le champ 'Group' existe d?j? dans la table 'test1'. [RODBC] ERROR: Could not SQLExecDirect 'CREATE TABLE [test1] ("Group&...

...mple code works just fine when I use MS Access as ODBC server. ----------- offending code and output ------------------- > library(RODBC); > channel <- odbcConnect("opus"); > data(USArrests) # R < 2.0.0 only > sqlSave(channel, USArrests, rownames = "state", addPK=TRUE) Error in sqlColumns(channel, tablename) : USArrests : table not found on channel Check case parameter in odbcConnect > sqlSave(channel, USArrests, rownames = "state", addPK=TRUE) Error in sqlSave(channel, USArrests, rownames = "state", addPK = TRUE) : [RODBC]...

...so it would be great if I could just import the 1st sheet into the dataframe. - to export I do this (from Tal Galili's solution earlier today): xlsFile <- odbcConnectExcel("file", readOnly=F) save2excel <- function(x, t.name) sqlSave(xlsFile, x, tablename=t.name, rownames=F, addPK=T) odbcCloseAll() It also works great, but how can I append lines? This function allows me to append new sheets in the file, but I would also like to append new lines of data onto one of the sheets. How can I do that? - The last problem: I would like to export a dataframe, but I get this error m...

...FALSE, append=T) close(chan) ### With rownames = T and safer=F, it works, but I loose the autoincrementing PK in MySQL chan <- odbcConnect("MySQL51", uid="root", pwd="momie") #default database=fbn sqlSave(chan, dframe, tablename="example", rownames = T, addPK=T,append=T,safer=F) close(chan) Any idea? I'm on win2K, MySQL version 5.0.21-community-nt > version _ platform i386-pc-mingw32 arch i386 os ming...

...r 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...

...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", &qu...

...274541966673, numsig = 0L, pc = NA), .Names = c("m", "crit", "numsig", "pc")) > xlsFile <- odbcConnectExcel("file.xls", readOnly=F) > for (i in 1:4){ sqlSave(xlsFile, as.data.frame(test[[i]]), tablename="test", rownames=F, addPK=T, append=T) } Error in odbcUpdate(channel, query, mydata, coldata[m, ], test = test, : missing columns in 'data' > odbcCloseAll() I've tried different combination and the 1st element is always exported but the next ones never. Elements 2 and 3 have the same number of columns;...

...ion using RODBC 1.3-2 on Win 7 and R 2.12.0. But I'm getting the following error message when I try to save a data frame into the debase as shown below. library(RODBC) bbdb <- odbcConnect("bbdb") odbcGetInfo(bbdb) # returns ok sqlSave(bbdb, USArrests, rownames = "state", addPK=TRUE) # example from the RODBC manual Error in sqlSave(bbdb, USArrests, rownames = "state", addPK = TRUE) : [RODBC] ERROR: Could not SQLExecDirect 'CREATE TABLE "USArrests" ("state" varchar(255) NOT NULL PRIMARY KEY, "Murder" double, "Assault&qu...

...s advertised. ----------------------------------------------------------------------------------------------- #Then we get here.  I have the USArrests data and checked it but tried to save as it produced an error. sqlSave(channel, USArrests,"test.USArrests",rownames = "state", addPK = TRUE) "Error in odbcUpdate(channel, query, mydata, coldata[m, ], test = test,  :   no parameters, so nothing to update" The database table exists and upon edit of the table fields, all table info created correctly. Attempted again adding "verbose=TRUE": > sqlSave(channe...

...C) library(termstrc) path = getwd() setwd(getwd()) dbName = "data.mdb" pathdbname = paste(path,"/",dbName,sep="") accesChannel = odbcConnectAccess(pathdbname, uid = "", pwd = "") sqlSave(accesChannel, USArrests, rownames = "state", addPK=TRUE) sqlFetch(accesChannel , "USArrests", rownames = "state") # get the lot foo <- cbind(state=row.names(USArrests), USArrests)[1:3, c(1,3)] foo[1:3,2] <- 9999 sqlUpdate(accesChannel , foo, "USArrests") The sqlSave and sqlFetch command seem to work fine. &...

...nel,sql) But when I try to append records (from data.frame newdta) to the same DB2 table. I get an error: sqlSave(channel, newdta, tablename = storage.testappend_slt, append = TRUE, + rownames = FALSE, colnames = FALSE, + verbose = FALSE, oldstyle = FALSE,, + safer = TRUE, addPK = FALSE, typeInfo, varTypes, + fast = TRUE, test = FALSE, nastring = NULL) Error in sqlSave(channel, dta, tablename = storage.testappend_slt, append = TRUE, : object 'storage.testappend_slt' not found I've tried variations on the table name including dropping the schema, us...

...of R-2.6.2 when using RODBC-1.2_3 and a remote PostgreSQL-8.2.6 database, unixodbc-2.2.11-16, odbc-postgresql-1:08.01.0200-2.1 on Ubuntu Linux 7.10. The exact messages can be seen below. It seems that there is some memory corruption going on. But it seems random to me and can neither be related to addPK=T/F or fast=T/F. It happens for all possible combinations. Besides I do not really understand what these messages mean. Any idea to what it might be related exactly? Thanks in advance, Ulrich Leopold Session details: >sessionInfo() R version 2.6.2 (2008-02-08) i486-pc-linux-gnu locale: LC_C...

....8.1 on Windows XP OS. recs is a one-row, eighteen-column data.frame I want to insert into a DB2 table. sqlSave(channel, recs, tablename = "testappend_slt", append = TRUE, + rownames = FALSE, colnames = FALSE, + verbose = TRUE, oldstyle = FALSE, + safer = TRUE, addPK = FALSE, + fast = FALSE, test = FALSE, nastring = NULL) Query: INSERT INTO "testappend_slt" ( "RECORDNO", "HEADERID", "SITE", "TESTER", "TESTCELL", "PRODUCT", "OPERATION", "OPNUM", "PRODMODE", &qu...

...(2,4,7) > > dat <- data.frame(cosa, cantidad) > > > > colnames(dat) <- c("A", "B") > > sqlSave(cn, dat, tablename = "cosa", > > rownames=FALSE, > > colnames=TRUE, > > append=TRUE, > > addPK=TRUE, > > fast=TRUE) > > > > # http://stackoverflow.com/questions/36202414/r-download-image-using-rvest > > # https://www.r-bloggers.com/parameterized-sql-queries/ > > # https://cran.r-project.org/web/packages/RODBCext/ > vignettes/Parameterized_SQL_queries....

Buen dia compañerxs de R, les traigo una pregunta bastante especifica. En el trabajo me estan requiriendo el uso de SQL, asi q estoy aprendiendo a usarlo desde R, con el paquete RODBC. Mi problema puntual es que al crear una tabla en el servidor SQL a partir de un data frame en R (usando la funcion sqlSave) no puedo definir el tipo de variables que quiero usar en el data frame. Segun entiendo

...) } #Cambiamos los rownames del df modelo para que nos sirvan de clave primaria de la tabla rownames(modelo) <- paste(modelo$Tipologia, modelo$Provincia, modelo$AnoModelo) #Subimos el df modelo a la tabla FE_MODELO del servidor SQL sqlSave(channel,modelo,tablename ="FE_MODELOS", addPK =TRUE, append = TRUE ) #Cambiamos los rownames del df predicciones para que nos sirvan de clave primaria de la tabla rownames(predicciones) <- paste(predicciones$Tipologia, predicciones$Provincia, predicciones$AnoModelo, predicciones$Anio_Trimestre) #Subimos el df modelo a la tabla FE_PRED...

...(2,4,7) > > dat <- data.frame(cosa, cantidad) > > > > colnames(dat) <- c("A", "B") > > sqlSave(cn, dat, tablename = "cosa", > > rownames=FALSE, > > colnames=TRUE, > > append=TRUE, > > addPK=TRUE, > > fast=TRUE) > > > > # http://stackoverflow.com/questions/36202414/r-download-image-using-rvest > > # https://www.r-bloggers.com/parameterized-sql-queries/ > > # https://cran.r-project.org/web/packages/RODBCext/ > vignettes/Parameterized_SQL_queries....

Hi, We are working on a project on forecast modeling and would like to know if there are any examples on how to READ/WRITE to a database (e.g. PostgreSQL) using R-Project. I do have a sample R Script which takes input as files from a directory and writes back output files to a directory. I would like to convert this script to read from a database and write back the output to a database.