Monday, February 6, 2012

RODBC sqlSave() autonumber auto-increase problem

This is a quick note on R.

The RODBC package for R-project is a popular add-on to access ODBC database. The sqlSave() function is a common method used to save a data.frame to an ODBC table. For an ODBC table with autonumber or auto-increase fields, this function reports errors. Turning on the verbose option of that function, it is observed that sqlSave() constructed a SQL statement that ALWAYS intended to insert values to all table fields. With this SQL statement, user are forced to assign a value for the auto-increase field, which, by its own definition, should not be assigned a value from external source.

For some odd database implementation, you might be able to assign a null value to the auto-increase field and got it to work. But, as far as I know, the MS ACCESS would not take that.

I was trying to find the source code for the RODBC package but was unsuccessful. I do found that if I wrote a function so that it write the data.frame to database using the sqlQuery() function, this can be overcome. However, this may take longer to process the data. The idea solution is to modify the sqlSave() function so that it is more flexible.

I also run into the problem with sqlSave() when dealing with two MS ACCESS memo fields. Again, this can be solved with the sqlQuery() function.






No comments:

Post a Comment