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

[Gabor Grothendieck]

On Sun, Oct 31, 2010 at 1:01 PM, Gabor Grothendieck
<ggrothendieck using gmail.com> wrote:
> 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.
>

Sorry I pasted the wrong code in. Here is the correct reproducible
code.  (The prior one was correct except dbGetQuery was mistyped as
dbGetUpdate.)

> 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 <- dbGetQuery(con, "select * from DF")
> 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


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