varType usage in RODBC

Question

I am writing to from an ODBC to a SQL Server table via the RODBC package, specifically the function sqlSave. It seems that the default var types is charvar(255) for this function. I tried to use the argument of varTypes that is listed within the documentation but it fails.

Here is the table called spikes20 with the Class structure, this in turn is what I am trying to save via sqlSave

sapply(spikes20, class)
Date       Day       EWW       PBR       BAC      CHTP        FB       SPY 
"Date"  "factor" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric" 

Here is the code which attempts to write to the SQL Server

require(RODBC)
varTypes = c(as.Date="Date") 
channel <-odbcConnect("OptionsAnalytics", uid="me", pwd="you")

sqlSave (channel, spikes20, tablename = NULL, append=TRUE, rownames = FALSE, colnames = TRUE, safer = FALSE, addPK = FALSE, varTypes=varTypes )

The error message that I get says:

Warning messages:

In sqlSave(channel, spikes20, tablename = NULL, append = TRUE, rownames = FALSE, : column(s) as.Date 'dat' are not in the names of 'varTypes'

I tried to change the varType to:

varTypes=c(Date="Date")

then the error message becomes:

Error in sqlSave(channel, spikes20, tablename = NULL, append = TRUE, rownames = FALSE,  : 
  [RODBC] Failed exec in Update
22007 241 [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.

Any help will be appreciated. It seems I cannot decipher to use varTypes correctly...

Carvel

Answer 1

First, are you really trying to append to a table named NULL?

As far as issues with varTypes goes, in my experience I have had to provide a mapping for all of the variables in the data frame even though the documentation for the varTypes argurment says:

"an optional named character vector giving the DBMSs datatypes to be used for some (or all) of the columns if a table is to be created"

You need to make sure that the names of your varTypes vector are the column names and the values are the data types as recommended here. So following their example you would have:

tmp <- sqlColumns(channel, correctTableName)
varTypes = as.character(tmp$TYPE_NAME)
names(varTypes) = as.character(tmp$COLUMN_NAME)

Expenditure

Thank you Pete, actually in the definition of the ODBC (from the control panel), I specified where to write to, hence the tablename being NULL is no issue. I finally got varTypes to work on a subset of the columns. – Carvel

The key was to put the varTypes within the sqlSave command as follow: sqlSave (channel, spikes20, tablename = NULL, append=TRUE, rownames = FALSE, colnames = FALSE, safer = FALSE, addPK = FALSE, varTypes=c(Date="Date")) – Carvel

Answer 2

varTypes = c(somecolumn="datetime") works for me.

Jenks