Re: [R] sqlSave [in RODBC]

From: Prof Brian Ripley <ripley_at_stats.ox.ac.uk>
Date: Mon 20 Feb 2006 - 20:57:09 EST

If you look in the file RODBC/tests.R you will see an example of using dates with Access. As you will see from that file the incantations required are highly DBMS-dependent.

On Mon, 20 Feb 2006, Prof Brian Ripley wrote:

> 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 21:02:28 2006

This archive was generated by hypermail 2.1.8 : Tue 21 Feb 2006 - 14:38:21 EST