R help - Dec 2002 - RODBCType&NullDataExportProblems

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.
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 "

In my defence, I have spent many hours trying to sort these issues out.
What am I missing? Any help would be much appreciated.

Thanks

Rob Pates
UVA, Charlottesville, VA, USA

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