search for: sqlupdate

Hello all! Can someone confirm whether there is a bug or not? I was trying to use sqlUpdate in place of sqlSave as data set I import has duplications. However I get errors while using fast=FALSE argument to safely update/ignore duplicates: Error while executing the query[RODBC] ERROR: Could not SQLExecDirect 'UPDATE "data" SET "logger"=1, "value"=0.03...

Dear all, I using R version 2.9.0 on Windows XP and want to insert data from a data frame in R into a oracle database via sqlUpdate with the package RODBC. My example is the following: TEST0<-data.frame(NR=c(1,2,600),NAME=c("JK","TR","AR"), DATUM=c("2009-01-01","2010-01-02","2010-06-09")) If I create the table in the database with sqlSave all is okay. sqlUpdate w...

...3,] 1 0.46566719 1.313711 1.000000 0.7233312 1.4097987 [4,] 1 0.38866371 2.453226 0.976024 0.6377314 1.3493957 [5,] 1 0.08894066 1.036830 1.000000 0.5270633 1.0901834 into a named are in Excel of the correct size, but I do not want to export the rownames as well. I've tried using RODBC sqlUpdate: sqlUpdate(channel=channel, dat=Duration_summary, index = NULL,verbose = FALSE, test = FALSE,nastring = NULL, fast = TRUE) Error in sqlUpdate(channel = channel, dat = Duration_summary, index = NULL, : cannot update 'Duration_summary' without unique column Please help... h...

..., 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. > foo state Assault Alabama Alabama 9999 Alaska Alaska 9999 Arizona Arizona 9999 > sqlUpdate(accesChannel , foo, "USArrests") Error in sqlUpdate(accesChannel,...

...ey2 character varying(1) NOT NULL, somevalue double precision, CONSTRAINT pktbl PRIMARY KEY (key1, key2) ) # Continue in R pg <- odbcConnect("testdb") sqlSave(pg, tbl[1:2, ], append = TRUE, rownames = FALSE) sqlSave(pg, tbl[3, ], append = TRUE, rownames = FALSE) tbl[1, 3] <- 1 sqlUpdate(pg, tbl[1:4, ], index = c("key1", "key2")) # Fails # Can replace the above sqlUpdate with: sqlUpdate(pg, tbl[1:3, ], index = c("key1", "key2")) sqlSave(pg, tbl[4, ], append = TRUE, rownames = FALSE) # Proposed solution: tbl[1, 3] <- 0 tmp <- tbl yes...

Buenas tardes, Estoy tratando de realizar un update en SQL desde R: sqlUpdate(conexion1, data.frame(AUXILIAR), tablename = "AUXILIAR") y me devuelve el siguiente error: *Error in sqlUpdate(conexion1, data.frame(AUXILIAR), tablename = "AUXILIAR", : * * data frame column(s) Corteoptimo Cortediario not in database table* El problema es que sí existen esa...

Is there a way to get a table in a certain schema? The Oracle database I am using has a table by the same name in two different schemas. This creates problems in sqlUpdate because to sqlUpdate there are duplicate columns. The following is part of the output of sqlColumns: sqlColumns(eids, "TEST_ARTCL_INST")[,1:4] TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME 1 EIDS TEST_ARTCL_INST CHANNEL_ID 2 EIDS...

Buenos días, al querer actualizar los datos de una tabla en SQL SERVER sale el siguiente error: Error in sqlUpdate(canal_conexion, Datos, tablename = "Tabla_SQL_SERVER_Actualizar", : [RODBC] Failed exec in Update22018 0 [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification Gracias desde ya por la ayuda. -- [[alternative HTML version deleted]]

...I thought tryCatch might be the way to do it, but I honestly do not understand the help file for tryCatch at all. I thought something like this might work: for (i in seq(along = tbl$key)) { tryCatch(sqlSave(pg, tbl[i, ], "tbl", append = TRUE, rownames = FALSE), if (fails) do {sqlUpdate(pg, tbl[i, ], index = "key")}) } This obviously isn't the correct syntax, but could tryCatch do this if I got the syntax right? And any tips on what the right syntax would be? Mikkel

...cast_Result', rownames = FALSE); But I got an error Error in sqlSave(db, a, "Forecast_Result", rownames = FALSE) : table 'Forecast_Result' already exists Yes, that table has been created before. But I need to overwrite the content in that table now. Obviously, I should use sqlUpdate. But sqlUpdate is to update existing table. So it seems to me I should use sqlSave first time when the table is not there and then use sqlUpdate for further update. How to test if the table is already there or not? Or, if there is a simpler logic to solve my problem, please advise me. Many thanks...

...tedValues, rownames = FALSE) : table ?PredictedValues? already exists and my new records don't get updated. I was under the impression that 'sqlSave' would copy new data on top of the existing one or if the table didn't exist it would create one with the new values. I tried 'sqlUpdate' but my existing 'PredictedValues' didn't update. What am I doing wrong. ? Felipe D. Carrillo Supervisory Fishery Biologist Department of the Interior US Fish & Wildlife Service California, USA

We have a PostgreSQL table with about 400000 records in it. Using either RODBC or RdbiPgSQL, what is the fastest way to update one (or a few) column(s) in a large collection of records? Currently we're sending sql like BEGIN UPDATE table SET col1=value WHERE id=id (repeated thousands of times for different ids) COMMIT and this takes hours to complete. Surely there must be a quicker

...and the typeInfo argument to sqlSave allow users to specify the mapping from R types to DBMS datatypes. sqlSave also allows the specification of DBMS datatypes by column. * It is now possible to write more than 255 chars to a field with sqlSave and sqlUpdate. * Dates and timestamps are now read as 'Date' and 'POSIXct' columns by sqlGetResults (unless as.is = TRUE for the column). I have been able to test SQL Server reasonably extensively this time around, as well as MySQL, PostgreSQL, Access, Excel and SQLite. --...

...and the typeInfo argument to sqlSave allow users to specify the mapping from R types to DBMS datatypes. sqlSave also allows the specification of DBMS datatypes by column. * It is now possible to write more than 255 chars to a field with sqlSave and sqlUpdate. * Dates and timestamps are now read as 'Date' and 'POSIXct' columns by sqlGetResults (unless as.is = TRUE for the column). I have been able to test SQL Server reasonably extensively this time around, as well as MySQL, PostgreSQL, Access, Excel and SQLite. --...

Hi, I am creating some graphs which I want to update into a database table. The procedure I am following is: 1. create the graphs as a png/jpeg file. 2. Read that file as a binary vector 3. sqlUpdate My code: pngfile <- file(<filename>, "rb") N <- 1e6 repeat{ pngfilecontents <- readBin(pngfile, what="raw", n=N) if(length(pngfilecontents) == N) N <- 5 * N else break } close(pngfile) #There is a table df_DemandPatternMaster in the da...

I want to update a column of a database table (in MS SQL) using sqlUpdate( ) or sqlSave( ) function in "RODBC" package. E.g. SQLchannel is the database connection; SQLtable is the destination table in the database, including at least two columns, ID and Value; Xdata is a data frame in R environment, including two columns: ID and Value. What I wanna do is to upd...

Hi all, I was quite succesfully working with the RODB package on a 32 bit linux box to connect to a MSSQL Server via the freeTSL driver. After changing to a 64 bit environment I ran into some segmentation faults using function sqlUpdate on large database operations (the actual seg faults occured in the call to the C function ODBCUpdate). I just briefly looked into the code and found some extensive type casting going on so I figured this might be a 64 bit problem with the 'SQL_C_SLONG' and 'SQL_C_DOUBLE' data types....

...) Error in odbcTableExists(channel, sqtable) : ?meso.trees?: table not found on channel Apparently the test if (!odbcValidChannel(channel)) is returning true. The inclusion of any non alphanumeric character leads a table name to be considered invalid. This also means that the useful sqlSave and sqlUpdate functions cannot be used in the usual way for tables that are not in the public schema. This has come up several times previously e.g. http://finzi.psych.upenn.edu/R/Rhelp02a/archive/113715.html, but no thread that I found led to an obvious solution. Brian Ripley pointed out the issue with the...

exportData(MU.Cost,paste("C:/RAUDSL/S",as.character(MU.Cost$Run.Id[1])," .",as.character(MU.Cost$MU.Id[1]),".MU.PRICE.OUTPUT.txt",sep=""),append = FALSE,type="ASCII",quote=FALSE) [[alternative HTML version deleted]]

Dear R-user, I have to feed my database using some SQL commands. I have already read a data frame with the data I need but after that these data should be write in a file wtih SQL commands. 1) My dataframe: dput(Alldados) structure(list(Station_NO = c(836490, 836920, 836950, 836980, 837380, 837460), TMAX_2M = c("NULL", "NULL", "NULL", "NULL",