Home » Solve DBI returning integer64 in R

# Solve DBI returning integer64 in R

Recently I bumped into an issue where a query, ran using the DBI library with the dbGetQuery function returned an integer64. Even without me noticing it. When converting my data frame (or data.table) to a matrix for clustering purposes, I ran into the following error:

Error in if (changes == 0) break : missing value where TRUE/FALSE needed

After inspection of the table, I found that the data.matrix() function was unable to automatically convert integer64 values.

You can solve this issue retroactively by converting the integer64 into a double, as follows (data.table syntax):

dt[,COLUMN_NAME := as.double(COLUMN_NAME)]

However, you can solve the issue proactively by setting a specific parameter when connecting to the database.

dbConnect(db, bigint = 'integer', ...)

You can find more about it in the DBI documentation:

By the way, if you’re having trouble understanding some of the code and concepts, I can highly recommend “An Introduction to Statistical Learning: with Applications in R”, which is the must-have data science bible. If you simply need an introduction into R, and less into the Data Science part, I can absolutely recommend this book by Richard Cotton. Hope it helps!

Great success!

### Say thanks, ask questions or give feedback

Technologies get updated, syntax changes and honestly… I make mistakes too. If something is incorrect, incomplete or doesn’t work, let me know in the comments below and help thousands of visitors.