MySQL and RODBC - limitations

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

MySQL and RODBC - limitations

jorgusch
Hello,

By accident I fell over a problem, which just did not expected...

Via Windows ODBC (package RODBC) I download data from a MySQL 5.1 database. The amount is so large that the working set memory is going up to around 400MB. Not too large, I think.

Anyhow, one column includes ten digit numbers. Somehow, not all of these numbers are displayed correctly. At some point all numbers turn to "2147483647". I am aware of the meaning of the number (2GB). However, where is the problem? Is it the connector or R?

I set the flag in the ODBC Connector "Allow big results flag", but I see no difference. Any ideas what I can do?

Thank you!
 jorgusch
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and RODBC - limitations

jorgusch
This post was updated on .
I found the solution.
The problem was indeed R.

There is a simple way to solve the problem, but it just needs a bit more time.
If you download large integers from a database, convert it "on the fly" with

SELECT CONVERT(yourcolumn,char)

That is it. This is no problem, as long you do NO comparisons within this columns. If you want to find something like entry10>entry11 ('13'>'2') than the result will be wrong, if both values do not have the same number of characters. Hence, if you have numbers, you must fill up the empty slots with zeros. So it would look like:  '13'>'02'.
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and RODBC - limitations

Duncan Murdoch
On 30/03/2010 1:35 PM, jorgusch wrote:

> I found the solution.
> The problem was indeed R.
>
> Their is a simple way to solve the problem, but it just needs a bit more
> time.
> If you download large integers from a database, convert it "on the fly" with
>
> SELECT CONVERT(yourcolumn,char)
>
> That is it. This is nor problem, as long you do NO comparisons within this
> columns. If you want to find something like entry10>entry11 ('13'>'2') than
> the result will be wrong, if both values do not have the same number of
> characters. Hence, if you have numbers, you must fill up the empty slotes
> with zeros. So it would look like:  '13'>'02'.
>  

If your longest integer is 10 digits (as mentioned earlier), you might
do better to convert them to doubles rather than char.  I don't know how
to say "double" in mySQL, but if you can figure that out, you should be
good to about 15 digits.

Duncan Murdoch

______________________________________________
[hidden email] mailing list
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.
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and RODBC - limitations

Uwe Ligges-3


On 30.03.2010 19:43, Duncan Murdoch wrote:

> On 30/03/2010 1:35 PM, jorgusch wrote:
>> I found the solution.
>> The problem was indeed R.
>> Their is a simple way to solve the problem, but it just needs a bit more
>> time. If you download large integers from a database, convert it "on
>> the fly" with
>>
>> SELECT CONVERT(yourcolumn,char)
>>
>> That is it. This is nor problem, as long you do NO comparisons within
>> this
>> columns. If you want to find something like entry10>entry11 ('13'>'2')
>> than
>> the result will be wrong, if both values do not have the same number of
>> characters. Hence, if you have numbers, you must fill up the empty slotes
>> with zeros. So it would look like: '13'>'02'.
>
> If your longest integer is 10 digits (as mentioned earlier), you might
> do better to convert them to doubles rather than char. I don't know how
> to say "double" in mySQL, but if you can figure that out, you should be
> good to about 15 digits.


FLOAT

Uwe

> Duncan Murdoch
>
> ______________________________________________
> [hidden email] mailing list
> 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.

______________________________________________
[hidden email] mailing list
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.
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and RODBC - limitations

jorgusch
Thanks guys - that is better.
You all rock!