Re: [R] sqlSave [in RODBC]

From: Prof Brian Ripley <ripley_at_stats.ox.ac.uk>
Date: Mon 20 Feb 2006 - 18:49:44 EST

You can make use of the 'varTypes' argument of sqlSave() to specify the field types to be used. You will have to do so if you are creating a table, as RODBC does not know what Access uses for dates (and POSIXct is appropriate for timestamps, not dates).

It is often easier to create the table in the DBMS (here Access) and then save to it, and you just haven't told us enough to know if that is what you are doing.

On Sun, 19 Feb 2006, Yi-Xiong Zhou wrote:

> Hi,
>
> I am having trouble to write/create a table, which has
> a date field. I want to create a stock price table,
> which has fields of ticker, date, price. First, I
> created such a table in Microsoft Access with a few
> rows inputs. Using sqlQuery, I found that the date
> field was retrieved as POSIXct value. Then I made a
> data.frame with POSIXct as the data type for dates.
> However, I received the following errors when I was
> executing the sqlSave:
>
>> price = data.frame(ticker=rep("FMDEX",5))
>> price$date=c(as.POSIXct("2003-1-1"),
> as.POSIXct("2003-1-2"), as.POSIXct("2003-1-3"),
> as.POSIXct("2003-1-4"), as.POSIXct("2003-1-5"))
>> price$price=1:5
>> price
> ticker date price
> 1 FMDEX 2003-01-01 1
> 2 FMDEX 2003-01-02 2
> 3 FMDEX 2003-01-03 3
> 4 FMDEX 2003-01-04 4
> 5 FMDEX 2003-01-05 5
>> sqlSave(h, price, rownames=F)
> Error in sqlSave(h, price, rownames = F) :
> [RODBC] ERROR: Could not SQLExecDirect
> 37000 -3553 [Microsoft][ODBC Microsoft Access Driver]
> Syntax error in field definition.
>
>
> I am using R2.2.1 with RODBC library, on a Dell P5
> computer with winXP Pro and office 2003.
>
> Thanks for your helps.
>
> Sean
>
> ______________________________________________
> R-help@stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
>

-- 
Brian D. Ripley,                  ripley@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

______________________________________________
R-help@stat.math.ethz.ch mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
Received on Mon Feb 20 18:53:42 2006

This archive was generated by hypermail 2.1.8 : Fri 03 Mar 2006 - 03:42:36 EST