importing numeric types via sqlQuery

x <- 54469517.307692307692
x

options(digits=20)
x

Hi everyone,
I am using the sqlQuery function (in RODBC library) to import data from a database into R. My table (called temp) in the database looks like this:
category ? ? ? ?numabc ?54469517.307692307692def ? ? ? ?36428860.230769230769
I used the following R code to pull data into R:data <-sqlQuery(channel, "select category, num from temp;")
However, the result is that "num" gets all its decimal places chopped off, so "data" looks like this instead in R:category ? ? ?numabc ?54469517def ? ? 36428860

I've tried various alternative approaches, but none have fixed the problem. When I cast the variable to a numeric type like this (data <-sqlQuery(channel, "select category, num::numeric from temp;"), it still gave me the same result. Casting to a real type like this (data <-sqlQuery(channel, "select category, num::real from temp;") resulted in scientific notation that also rounded the numbers.
Any suggestions? Much appreciated!
? ? ? ?[[alternative HTML version deleted]]

require(sqldf)
# don't have MySql, but will use sqlite as example
myData <- data.frame(cat = c('abc', 'def'), num=c(123.456, 7890.1234))
myData

sqldf('select cat, num from myData')  # now make sql request

Thanks for the quick reply! Hmm, I did not know about the options default.
However, after I set options, it seems like it's still not displaying
correctly. I've tried an even simpler example table with only 6 digits (much
fewer than 20):
category num\n
abc 123.456\n
def 456.789\n
Then in R:
options(digits = 20)
data<-sqlQuery(channel,?"select category, num from temp;")
But "data" looks like this:
category num\n
abc 123\n
def 456\n
I suspect it's something with sqlQuery that chops off the digits and
wondering if there's a way of turning it off. Thanks!

Date: Mon, 11 Oct 2010 16:28:25 -0400
Subject: Re: [R] importing numeric types via sqlQuery
From: jholtman at gmail.com
To: mmmraspberries at hotmail.com
CC: r-help at r-project.org

I would assume that the digitis are not being chopped off. It is just
that R will typically print data to 7 significant digits:

x <- 54469517.307692307692
x

[1] 54469517

options(digits=20)
x

[1] 54469517.3076923

Your data it there and you can set 'options' to show it if you want
to. Also with floating point, you will only get about 15 digits of
accuracy (see FAQ 7.31).


On Mon, Oct 11, 2010 at 4:19 PM, E C <mmmraspberries at hotmail.com> wrote:

Hi everyone,
I am using the sqlQuery function (in RODBC library) to import data from
a database into R. My table (called temp) in the database looks like this:
category ? ? ? ?numabc ?54469517.307692307692def
?36428860.230769230769
I used the following R code to pull data into R:data <-sqlQuery(channel,
"select category, num from temp;")
However, the result is that "num" gets all its decimal places chopped
off, so "data" looks like this instead in R:category ? ? ?numabc
?54469517def ? ? 36428860

I've tried various alternative approaches, but none have fixed the
problem. When I cast the variable to a numeric type like this (data
<-sqlQuery(channel, "select category, num::numeric from temp;"), it still
gave me the same result. Casting to a real type like this (data
<-sqlQuery(channel, "select category, num::real from temp;") resulted in
scientific notation that also rounded the numbers.
Any suggestions? Much appreciated!
? ? ? ?[[alternative HTML version deleted]]

--
Jim Holtman
Cincinnati, OH
+1 513 646 9390

What is the problem that you are trying to solve?