I am retrieving a column containing big integers from a data base into R (using RJDBCs dbGetQuery method). For a test case, one can consider the following numbers
1000522010609612
1000522010609613
1000522010609614
1000522010609615
1000522010609616
1000522010609617
**971000522010609612
1501000522010819466
971000522010943717
1501000522010733490**
R seems to be reading the contents wrongly. The way in which it is available to me in R (after I read from the database using RJDBC) is:
1000522010609612
1000522010609613
1000522010609614
1000522010609615
1000522010609616
1000522010609617
**971000522010609664
1501000522010819584
971000522010943744
1501000522010733568**
See the last 4 numbers. They are wrong! It seems to be automatically converting the data into a dataframe (which is fine - but) with the corrupted numbers (for bigints). Any suggestion on how we can solve the above problem particularly when we are using dbGetQuery using RJDBC package?
Your data is read in as floating point numbers:
DF <- read.table(text="1000522010609612
1000522010609613
1000522010609614
1000522010609615
1000522010609616
1000522010609617
971000522010609612
1501000522010819466
971000522010943717
1501000522010733490")
class(DF[,1])
#[1] "numeric"
sprintf("%20f", DF[10, 1])
#[1] "1501000522010733568.000000"
You could read it as strings and convert to big integers or read in as big integers directly:
library(bit64)
DF <- read.table(text="1000522010609612
1000522010609613
1000522010609614
1000522010609615
1000522010609616
1000522010609617
971000522010609612
1501000522010819466
971000522010943717
1501000522010733490", colClasses = "integer64")
# V1
#1 1000522010609612
#2 1000522010609613
#3 1000522010609614
#4 1000522010609615
#5 1000522010609616
#6 1000522010609617
#7 971000522010609612
#8 1501000522010819466
#9 971000522010943717
#10 1501000522010733490
I can't help you with your database application, but this should provide a starting point for solving your problem.
