[R] mysql retrive question

[David James]

simone gabbriellini wrote:
> hello R-Users,
> I have this simple but not for me question:
> 
> I do:
> 
>  > res<-dbSendQuery(con, "SELECT * FROM tabellaProva")
>  > myDataFrame<-fetch(res)
>  > myDataMatrix<-as.matrix(myDataFrame[,-1])
>  > namerows(myDataMatrix)<-as.character(myDataFrame[,1])
> 
> and I have:
> 
>        io  tu
> io  "0" "1"
> tu  "1" "0"
> 
> my problem is that the content of the matrix is interpreted by R as 
> strings, not as numbers.
> Is there a way to convert those characters to numbers like
> 
>      io  tu
> io  0 1
> tu  1 0
> 
> thanx in advance,
> simone
> 

Hi Simone,

If you use dbReadTable, as I mentioned in my previous email, you should 
be able to coerce myDataFrame to a numeric matrix.

A couple of extra observations: 
(1) If you really want to use fetch() to extract all the rows resulting
    from a SELECT statement in a single fetch, you may need to specify 
    n=-1, e.g.,

       > fetch(res, n = -1)

    otherwise you may only get the first 500 rows. (See ?fetch, ?MySQL, 
    and ?dbHasCompleted.) The reason there is this default is to prevent 
    crashing R with a very large and unexpected amount of data. By specifying 
    n=-1 you're effectively asserting that the output of SELECT can be 
    properly handled by R.

(2) Tables in a relational database are only superficially similar to
    data.frames (the SQL term "relation" for tables conveys semantics
    that do not exist in R), thus fetch() and dbReadTable() do not
    coerce their columns to factors.  Clearly, there is a need to allow
    users to specify their own converters, as other interfaces (e.g.,
    RSPython, RSPerl), and functions (e.g., read.table) actually provide.

Hope this helps,

--
David