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

[Gabor Grothendieck]

On Mon, Nov 1, 2010 at 1:42 AM, Tomoaki NISHIYAMA
<tomoakin using kenroku.kanazawa-u.ac.jp> wrote:
> Hi,
>
> While the code presented by Gabor Grothendieck works fine.
>
>> options(RpgSQL.password = "", RpgSQL.dbname = "testdb",
>> RpgSLQ.user="testuser")
>> 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")
>> res
>  a     b          c  d
> 1 1 Hello 2000-01-01 32
>
>
> I found that when the database contain NULL value for the date field:
> (This is prepared with psql)
>
> testdb=# insert into df values ('2', 'bye', NULL,'48');
> INSERT 0 1
> testdb=# select * from df;
>  a |   b   |     c      | d
> ---+-------+------------+----
>  1 | Hello | 2000-01-01 | 32
>  2 | bye   |            | 48
> (2 rows)
>
> Then, the original error (by Xiaobo Gu) could be reproduced.
>
>> res <- dbGetQuery(con, "select * from DF")
> Error in 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"
>
> So, I guess there is some record with the c field having NULL value.

Thanks.  I was able to reproduce the problem using your observation:

> library(RpgSQL)
> con <- dbConnect(pgSQL())
> ct <- 'CREATE TABLE DF ("a" INTEGER, "b" VARCHAR(255), "c" DATE,"d" DOUBLE PRECISION)'
> dbSendUpdate(con, ct)
> dbSendUpdate(con, "insert into DF values(1, 'Hello', to_date('2000-01-01', 'YYYY-MM-DD'), 32)")
> dbSendUpdate(con, "insert into DF values(2, 'World', Null, 33)")
> res <- dbGetQuery(con, "select * from DF")
Error in 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"

I will look into this.

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