An embedded and charset-unspecified text was scrubbed... Name: not available URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20110807/4bac6978/attachment.pl>
RODBC: sqlUpdate doesn't handle properly POSIXct field?
4 messages · Mikhail Titov, WanderingWizard
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
I've tried to upgrade to latest (?) http://cran.mtu.edu/bin/windows/contrib/2.12/RODBC_1.3-3.zip , but no luck. It looks like nothing is being escaped as I tried to use character class, but I got same error message. 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:27 PM To: r-help at r-project.org Subject: Re: [R] RODBC: sqlUpdate doesn't handle properly POSIXct field? 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
______________________________________________ R-help at r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting- guide.html and provide commented, minimal, self-contained, reproducible code.
20 days later
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.