sqlSave

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlSave

Yi-Xiong Zhou
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

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
Reply | Threaded
Open this post in threaded view
|

Re: sqlSave [in RODBC]

Prof Brian Ripley
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
>
> ______________________________________________
> [hidden email] 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,                  [hidden email]
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

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
Reply | Threaded
Open this post in threaded view
|

Re: 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
>>
>> ______________________________________________
>> [hidden email] 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,                  [hidden email]
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

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html