R help - Aug 2011 - RODBC: sqlUpdate doesn't handle properly POSIXct field?

Hello all!

 

Can someone confirm whether there is a bug or not?

 

I was trying to use sqlUpdate in place of sqlSave as data set I import has
duplications. However I get errors while using fast=FALSE argument to safely
update/ignore duplicates:

 

Error while executing the query[RODBC] ERROR: Could not SQLExecDirect
'UPDATE "data" SET "logger"=1,
"value"=0.0321584 WHERE "time"=2008-09-22 13:15:00'

Error in sqlUpdate(con2, na.omit(dat), "data", fast = FALSE) : 

  42601 7 ERROR: syntax error at or near "13";

 

It looks like POSIXct class is not escaped properly.

 

I have R 2.12.2 running on Windows XP 32 bit, and I’m using PostgreSQL database.
Column time is supposedly of ‘timestamp without time zone’.

 

Here is what I have in the data frame I’m pushing to DB:

 
> class(dat$time)
[1] "POSIXct" "POSIXt"

 

Mikhail

 


	[[alternative HTML version deleted]]

I apologize for the first e-mail as I didn't use plain text. Here is the
full message.
--8<------------------------------------------------------------------------
------>8--

Hello all!

Can someone confirm whether there is a bug or not?

I was trying to use sqlUpdate in place of sqlSave as data set I import has
duplications. However I get errors while using fast=FALSE argument to safely
update/ignore duplicates:

Error while executing the query[RODBC] ERROR: Could not SQLExecDirect
'UPDATE "data" SET "logger"=1,
"value"=0.0321584 WHERE "time"=2008-09-22
13:15:00'
Error in sqlUpdate(con2, na.omit(dat), "data", fast = FALSE) : 
  42601 7 ERROR: syntax error at or near "13";

It looks like POSIXct class is not escaped properly.

I have R 2.12.2 running on Windows XP 32 bit, and I?m using PostgreSQL
database. Column time is supposedly of ?timestamp without time zone?.

Here is what I have in the data frame I?m pushing to DB:
> class(dat$time)
[1] "POSIXct" "POSIXt"

Mikhail
> -----Original Message-----
> From: r-help-bounces at r-project.org [mailto:r-help-bounces at
r-project.org]
On
> Behalf Of Mikhail Titov
> Sent: Sunday, August 07, 2011 7:22 PM
> To: r-help at r-project.org
> Subject: [R] RODBC: sqlUpdate doesn't handle properly POSIXct field?
> 
> Hello all!
> 
> 
> 
> Can someone confirm whether there is a bug or not?
> 
> 
> 
> I was trying to use sqlUpdate in place of sqlSave as data set I import has
> duplications. However I get errors while using fast=FALSE argument to
> safely update/ignore duplicates:
> 
> 
> 
> Error while executing the query[RODBC] ERROR: Could not SQLExecDirect
> 'UPDATE "data" SET "logger"=1,
"value"=0.0321584 WHERE "time"=2008-09-22
> 13:15:00'
> 
> Error in sqlUpdate(con2, na.omit(dat), "data", fast = FALSE) :
> 
>   42601 7 ERROR: syntax error at or near "13";
> 
> 
> 
> It looks like POSIXct class is not escaped properly.
> 
> 
> 
> I have R 2.12.2 running on Windows XP 32 bit, and Ib

Thanks a bunch, it was driving me nuts.  My experience would seem to confirm
yours.  Thanks to your post, I fixed a database update that was crashing my
R session.  I changed the data column that I was posting from a POSIXct to a
Date, and now the data goes in.  That works fine for me since my dataset is
daily.


--
View this message in context:
http://r.789695.n4.nabble.com/RODBC-sqlUpdate-doesn-t-handle-properly-POSIXct-field-tp3725857p3775164.html
Sent from the R help mailing list archive at Nabble.com.