[R] Creating a data table (frame?) from a SQL Statement?

[Jeff Newmiller]

Please always cc the list... the group usually has better answers than any one person, and I don't do private consulting on the net. For future reference, there is also an r-sig-db mailing list where this question really belongs. 

Since I almost never use jdbc, I would need a reprex (hint you are ignoring my advice... a good way to get ignored), which this still isn't. There is probably enough here for someone more familiar than I to help you with. I for one find it hard to understand why read.table is successfully figuring out how to make sense of the presumably-valid data frame that dbGetQuery is returning. That is, you really don't need to send everything through read.table in order to make use of it. Maybe try

irisDF <- dbGetQuery(conn, "select * from iris")

str( irisDF )

-- 
Sent from my phone. Please excuse my brevity.

On October 24, 2017 10:11:28 AM PDT, Morkus <morkus at protonmail.com> wrote:
>Jeff,
>
>Excellent points, thank you!
>
>Yes, I have all the required packages installed.
>
>Below's the actual full script.
>
>require(Rserve)
>require(biotools)
>library(rJava)
>library(RJDBC)
>drv <- JDBC("com.mysql.jdbc.Driver","/Users/.../mysql.jar")
>conn <- dbConnect(drv, "jdbc:mysql://localhost:3306/morkus","root",
>"password")
>dbListTables(conn)                                                     
>           // works!
>count <- dbGetQuery(conn,"select count(*) from iris")            //
>works!
>irisQuery <- read.table(dbGetQuery(conn, "select * from iris")) works!
>irisQuery                                                              
>              // displays table from iris dataset I imported into MySQL
>boxM(irisQuery[,-5], irisQuery[,5])                                    
> / />>>  FAILS! <<< "Error: is.numeric(x) || is.logical(x) is not TRUE"
>dbDisconnect(conn)                                                     
>            // displays TRUE.
>
>----
>
>Few rows of displayed data in R Console from line above: "irisQuery"
>
>   Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
>1            5.1         3.5          1.4         0.2     setosa
>2            4.9           3          1.4         0.2     setosa
>3            4.7         3.2          1.3         0.2     setosa
>4            4.6         3.1          1.5         0.2     setosa
>5              5         3.6          1.4         0.2     setosa
>6            5.4         3.9          1.7         0.4     setosa
>7            4.6         3.4          1.4         0.3     setosa
>.
>.
>.
>---
>
>Now, if I do the boxM statistic reading a CSV file from disk, like
>this:
>
>irisQuery = read.table('/Users/.../iris.csv', sep=',', header=FALSE,
>stringsAsFactor=FALSE)
>
>Then everything works!  (But I'm not reading files from a CSV on the
>disk; rather, from SQL as above.)
>
>---
>
>So my problem is that I can't seem to package the SQL Result (shown
>above) so that it works like the read.table. I'm missing some R
>function to transform the SQL to a "table" R can work with.
>
>Does that help fill in the missing pieces?
>
>Sorry my first posting wasn't this complete.
>
>Thanks again in advance,
>
>- M
>
>Sent from [ProtonMail](https://protonmail.com), Swiss-based encrypted
>email.
>
>> -------- Original Message --------
>> Subject: Re: [R] Creating a data table (frame?) from a SQL Statement?
>> Local Time: October 24, 2017 12:40 PM
>> UTC Time: October 24, 2017 4:40 PM
>> From: jdnewmil at dcn.davis.ca.us
>> To: Morkus <morkus at protonmail.com>, r-help at r-project.org
><r-help at r-project.org>
>>
>> Your question desperately needs a reproducible example (a.k.a.
>"reprex"), because you have to be using contributed packages to do any
>of this. You also need to clarify whether you are intending to access
>data already in an external database, or are planning to load it using
>R and manipulate it with SQL.
>>
>> I suggest that you install the "reprex" and "sqldf" packages and read
>their documentation and try out their examples. Then when you ask a
>question use reprex to be sure we see all the steps needed to get to
>where you are stuck. In particular we need the library calls that
>preceeded your sample code, but the reprex package will verify that you
>have it all there before you send the question.
>>
>> FWIW, note that the data function has a specific purpose of pulling
>data sets by name out of packages into your workspace, and giving it
>the output of randomly selected functions is not likely to work. Start
>reading help files soon... they may seem dense at first but the only
>way to get past that is to get started soon. Type
>>
>> ?data
>>
>> at the console to start learning about that function.
>>
>> Note that csv files are outside the world of standard SQL, so some of
>what you are doing may be very tightly linked with the particular SQL
>engine you are using.
>>
>> Sent from my phone. Please excuse my brevity.
>>
>> On October 24, 2017 7:01:36 AM PDT, Morkus via R-help
>r-help at r-project.org wrote:
>>
>>> Hello,
>>> I'm new to R so this is probably a simple question for somebody.
>>> I have an RScript that reads a CSV on the disk using
>read.table(...).
>>> It then does a boxM test using that data.
>>> However, I'm now trying to load the same data via an SQL command,
>but I
>>> can't seem to get the data structure defined so R will like it --
>using
>>> the included "iris" dataset.
>>> I've tried these ways of loading the SQL statement into a compatible
>R
>>> Structure:
>>> irisQuery <- data(dbGetQuery(conn, "select * from iris"))
>>> irisQuery <- data.frame(dbGetQuery(conn, "select * from iris"))
>>> irisQuery <- table(dbGetQuery(conn, "select * from iris"))
>>> .
>>> .
>>> .
>>> Followed by:
>>> boxM(irisQuery[,-5], irisQuery[,5])
>>> Nothing works work.
>>> For example, if i use ...
>>> irisQuery <- data.frame(dbGetQuery(conn, "select * from iris"))
>>> I get this error from R on the boxM test: Error: is.numeric(x) ||
>>> is.logical(x) is not TRUE
>>>
>>> The SQL Statement is returning results, but their not CSV. Not sure
>if
>>> that matters.
>>>
>>> So, how do I read a SQL statement into an R structure like I read
>the
>>> CSV using "read.table" so the boxM test will work?
>>> Thanks very much in advance.
>>>
>>> - M
>>>
>>> Sent from [ProtonMail](https://protonmail.com), Swiss-based
>encrypted
>>> email.
>>> [[alternative HTML version deleted]]
>>> ---------------------------------------------------------------
>>>
>>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>>> https://stat.ethz.ch/mailman/listinfo/r-help
>>> PLEASE do read the posting guide
>>> http://www.R-project.org/posting-guide.html
>>> and provide commented, minimal, self-contained, reproducible code.