[R-sig-DB] Data type error with RpgSQL on Windows XP SP3 32bit

[Gabor Grothendieck]

On Sun, Oct 31, 2010 at 5:39 AM, Xiaobo Gu <guxiaobo1982 using gmail.com> wrote:
> Hi,
> Can you help with this
>
>> driver<-pgSQL(classPath="D:\\rtemp\\postgresql-9.0-801.jdbc4.zip")
>> con <- dbConnect(driver, user="postgres", password="postgres",
> dbname="demo")
>> con
> An object of class "pgSQLConnection"
> Slot "jc":
> [1] "Java-Object{org.postgresql.jdbc4.Jdbc4Connection using 1ce2dd 4}"
>
> Slot "identifier.quote":
> [1] "\""
>
>> data <- dbGetQuery(con, "select * from optest.colana")
> Error as.Date.default(.jcall(res using jr, "S", "getString", i)) :
> do not know how to convert '.jcall(res using jr, "S", "getString", i)' to class
> "Date"
>>
> the DDL is :
> CREATE TABLE optest.colana
> (
> a integer,
> b character varying(12),
> c date,
> d numeric
> )
>

Could you write up a reproducible example?  I created a reproducible
example below but it works for me.  Here we create a table with a Date
field and then insert some data and then read it back.  Note that the
new table that results from the dbGetQuery at the end has a Date field
as required.

> options(RpgSQL.password = "postgres", RpgSQL.dbname = "mydb")
> library(RpgSQL)
>
> con <- dbConnect(pgSQL())
>
> ct <- 'CREATE TABLE DF ("a" INTEGER, "b" VARCHAR(255), "c" DATE,"d" DOUBLE PRECISION)'
> dbSendUpdate(con, ct)
>
> DF <- data.frame(a = 1L, b = "Hello", c = Sys.Date(), d = 32)
> dbSendUpdate(con, "insert into DF values(1, 'Hello', to_date('2000-01-01', 'YYYY-MM-DD'), 32)")
>
> res <- dbGetUpdate(con, "select * from DF")
Error: could not find function "dbGetUpdate"
> str(res)
'data.frame':   1 obs. of  4 variables:
 $ a: num 1
 $ b: Factor w/ 1 level "Hello": 1
 $ c:Class 'Date'  num 10957
 $ d: num 32
>
> dbSendUpdate(con, "drop table DF")
> dbDisconnect(con)
[1] TRUE
> R.version.string
[1] "R version 2.11.1 Patched (2010-05-31 r52167)"
> win.version()
[1] "Windows Vista (build 6002) Service Pack 2"

-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com