[R] sqlSave [in RODBC]

[Prof Brian Ripley]

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 at 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 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