[R-sig-DB] FW: RSQLite does not read very large values correctly

[Gabor Grothendieck]

I am able to create sqlite tables with large numbers and read them back,
e.g.

> Lines <- "OrderID Day             TimeToclose
+ 1               2009-11-25      29467907000
+ 2               2009-11-25      29467907000
+ 3               2009-11-25      29467907000"
> DF <- read.table(textConnection(Lines), header = TRUE)
> library(sqldf)
>
> # sqldf creates an sqlite database with table DF
> #   uploading DF to it and reads it back
>
> DF2 <- sqldf("select * from DF")
>
> all.equal(DF, DF2, check.attributes = FALSE)
[1] TRUE
> DF
  OrderID        Day TimeToclose
1       1 2009-11-25 29467907000
2       2 2009-11-25 29467907000
3       3 2009-11-25 29467907000
> DF2
  OrderID     Day__1 TimeToclose
1       1 2009-11-25 29467907000
2       2 2009-11-25 29467907000
3       3 2009-11-25 29467907000

> R.version.string # Windows Vista
[1] "R version 2.10.0 Patched (2009-11-21 r50532)"
> packageDescription("RSQLite")$Version
[1] "0.7-3"
> packageDescription("DBI")$Version
[1] "0.2-4"
> packageDescription("sqldf")$Version
[1] "0-1.7"


On Mon, Nov 30, 2009 at 11:28 AM, Ruecker, Sebastian <
Sebastian.Ruecker using commerzbank.com> wrote:

> Hello,
>
> I posted this in the regular r-help list, but it may be more appropriate
> here. Sorry about the double posting!
>
> I am trying to import data from an SQLite database to R.
> Unfortunately, I seem to get wrong data when I try to import very large
> numbers.
>
> For example:
> I look at the database via SQLiteStudio(v.1.1.3) and I see the following
> values:
>
> OrderID Day             TimeToclose
> 1               2009-11-25      29467907000
> 2               2009-11-25      29467907000
> 3               2009-11-25      29467907000
>
>
> Now I run this R Code:
>
> > library("DBI")
> > library("RSQLite")
> >
> > # DB Connection
> > con <- dbConnect(dbDriver("SQLite"), "C:/Temp/TickDB01.db")
> > raw_Data <- dbGetQuery(con, "SELECT OrderID, Day, TimeToClose FROM
> Tr_TickData WHERE OrderID in (1,2,3)")
> > raw_Data
>  OrderID        Day TimeToClose
> 1       1 2009-11-25  -596864072
> 2       2 2009-11-25  -596864072
> 3       3 2009-11-25  -596864072
>
>
> The values are totally wrong... Is it because RSQLite has a problem with
> big numbers?
> TimeToClose is microseconds till 17:00.
>
> When I make the numbers smaller, it works again:
>
> > raw_Data <- dbGetQuery(con, "SELECT TimeToClose/1000 as TTC FROM
> Tr_TickData WHERE OrderID in (1,2,3)")
> > raw_Data
>       TTC
> 1 29467907
> 2 29467907
> 3 29467907
>
>
> I would appreciate any help with this problem!
>
> Thanks and regards,
>
> Sebastian
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>

	[[alternative HTML version deleted]]