I am trying to update a SQL table using sqlSave function of RODBC package in R. Data is present in a data frame. When I try to run the command:
sqlSave(DBConn, dat=df, verbose=T, tablename='table', append=T)
I get the following error:
Query: INSERT INTO "table" ( "col1", "col2", "col3", "col4" ) VALUES ( ?,?,?,?,? )
sqlwrite returned
42000 -131 [Sybase][ODBC Driver][Sybase IQ]Syntax error near 'table' on line 1
[RODBC] ERROR: Could not SQLPrepare 'INSERT INTO "table" ( "col1", "col2", "col3", "col4" ) VALUES ( ?,?,?,?,? )'
What am I doing wrong here so that I am not getting the values in SQLQuery?
Thanks for any help in advance
EDIT 1 after @Gordon comment:
Error shows 5 placeholders but my data.frame has only 4 columns. I did dim(df) and got 4. Is it somehow related to row index of df?
EDIT 2
On doing the following:
sqlSave(DBConn, dat=df, verbose=T, tablename='table', append=T)
The error now I get is still the same with 4 placeholders instead but all values are still (?,?,?,?)
EDIT 3
I tried using sqlUpdate also
sqlUpdate(DBConn, dat=df, verbose=T, tablename='table')
Error that I now got is:
Query: UPDATE "table" SET "col2"=?, "col3"=?, "col4"=? WHERE "col1"=?
Error in sqlUpdate(DBConn, t, tablename = "table", verbose = T) :
42000 -131 [Sybase][ODBC Driver][Sybase IQ]Syntax error near 'table' on line 1[RODBC] ERROR: Could not SQLPrepare 'UPDATE "table" SET "col2"=?, "col3"=?, "col4"=? WHERE "col1"=?'
There is a possibility of data types and Column names being a problem. So It's best to obtain the datatypes and column names of the table and assign them to the data frame.
ColumnsOfTable <- sqlColumns(conn, tablename)
varTypes <- as.character(ColumnsOfTable$TYPE_NAME)
names(varTypes) <- as.character(ColumnsOfTable$COLUMN_NAME)
colnames(dataObject) <- as.character(ColumnsOfTable$COLUMN_NAME)
sqlSave(conn, dataObject, tableNames, fast=TRUE,append=TRUE, rownames=FALSE, varTypes=varTypes )