RODBCType&NullDataExportProblems
On Tue, 31 Dec 2002, Robert Pates wrote:
Hello I am trying to move data between R and other systems (e.g. SAS and MS SQL Server) using RODBC. I think I have the most recent version of RODBC (0.9) and here is some other system info:
odbcGetInfo(channel)
[1] "Microsoft SQL Server version 08.00.0679. Driver ODBC version 03.52"
version
_ platform i386-pc-mingw32 arch i386 os mingw32 system i386, mingw32 status major 1 minor 6.1 year 2002 month 11 day 01 language R
I appear to be having 2 sets of problems when I execute an sqlSave to export an R dataframe to either SAS or to MS SQL: 1) RODBC seems to deal with chars and int types OK -- but seems to want to create "double" types in the receiving systems which don't support "double"s (instead -- for example -- MS SQL supports "float"). How can I specify more precisely the data types to be created in the receiving system? If this is documented on the R site, or in the RODBC docs, I have missed it.
Have you looked at SQLTypeInfo? My bet is that the RODBC driver is reporting that `double' is supported, as it jolly well should be.
2) So if I create a file with chars and ints in MS SQL it seems to work OK -- until I have missing data in the data frame (e.g. int NAs). I have not yet hit upon the way to write nulls to the receiving system. I have played with "nastring=" and each time I get: "[RODBC]Failed exec in Update22005 0 [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification "
Looks like a bug in the driver.
In my defence, I have spent many hours trying to sort these issues out. What am I missing? Any help would be much appreciated.
My guess is that you are looking in the wrong place: see if this works with a well-written ODBC driver. 1) certainly does.
Brian D. Ripley, ripley at stats.ox.ac.uk Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595