RODBC sqlSave problem with bigint numbers

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

RODBC sqlSave problem with bigint numbers

PtitBleu
Hello,

After a (bad) first try some months ago, I'm trying again to use RODBC package instead of DBI+RMySQL packages to populate MySQL database.
The main command is

sqlSave(channel, data_df, tablename, append=T, rownames=FALSE, colnames=FALSE, fast=FALSE, varTypes=vartypes)

where data_df is the data.frame I want to save and vartypes is obtained following the tip given here
Useful Tips for RODBC

Two columns have a bigint unsigned type.
The first contains numbers with 11 figures (like 20015301001)
The second colums has numbers with 13 figures (like 2002000103651)

First column are well saved in the database but, even with the same type, numbers of the second one are truncated (this was not observed with RMySQL package)
2002000103651 become 2002000103000

The only solution I found at the moment, is to change the type of the second column in the data.frame and in the database, that is use a char(13) instead of bigint unsigned.
But it is not satisfactory.

Thanks in advance for any tip or solution,
Have a good day,
Ptit Bleu.



Reply | Threaded
Open this post in threaded view
|

Re: RODBC sqlSave problem with bigint numbers

PtitBleu
I've just tested with 12 figures instead of 13. And it works ...
But I'm not able to modify these numbers as they are codes given by other guys (and women as well) to distinguish equipments.

And I thought the limit with R was 15 digits and not 13.

Is it a (bad for me) special feature of the sqlSave command?
Thanks in advance for any suggestion,
Ptit Bleu.
Reply | Threaded
Open this post in threaded view
|

Re: RODBC sqlSave problem with bigint numbers

Renaud Lancelot
Why don't you read/write this field as a character string? Pretty common for
codes.

2011/7/27 PtitBleu <[hidden email]>

> I've just tested with 12 figures instead of 13. And it works ...
> But I'm not able to modify these numbers as they are codes given by other
> guys (and women as well) to distinguish equipments.
>
> And I thought the limit with R was 15 digits and not 13.
>
> Is it a (bad for me) special feature of the sqlSave command?
> Thanks in advance for any suggestion,
> Ptit Bleu.
>
>
> --
> View this message in context:
> http://r.789695.n4.nabble.com/RODBC-sqlSave-problem-with-bigint-numbers-tp3697738p3697907.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> [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.
>



--
Renaud Lancelot
EDENext Project, coordinator: http://www.edenext.eu/

CIRAD, UMR15, Campus International de Baillarguet TA A-DIR / B
F34398 Montpellier

Tel.  +33 4 67 59 37 17  -  Fax  +33 4 67 59 37 95
Secr. +33 4 67 59 37 37  - Cell. +33 6 77 52 08 69

        [[alternative HTML version deleted]]

______________________________________________
[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: RODBC sqlSave problem with bigint numbers

Jason Becker
In fact, I always use a string for ID variables because you can get
some funky matching errors due to mismatches in precision.

Bill Gould at Stata Corp said this well in a recent blog post
"1. Use theory to check IDs if they are numeric

One way the id variable can become corrupted is if it is not stored
properly or if it is read improperly. This can happen to both string
and numeric variables, but right now, we are going to emphasize the
numeric case.

Say the identification variable is Social Security number, an example
of which is 888-88-8888. Social Security numbers are invariably stored
in computers as 888888888, which is to say that they are run together
and look a lot like the number 888,888,888. Sometimes they are even
stored numerically. Say you have a raw data file containing perfectly
valid Social Security numbers recorded in just this manner. Say you
read the number as a float. Then 888888888 becomes 888888896, and so
does every Social Security number between 888888865 and 888888927,
some 63 in total. If Bob has Social Security number 888888869 and Mary
has 888888921, and Bob appears in dataset one and Mary in dataset two,
then Bob and Mary will be combined because they share the same rounded
Social Security number.

Always be suspicious of numeric ID variables stored numerically, not
just those stored as floats."

Source: http://blog.stata.com/2011/04/18/merging-data-part-1-merges-gone-bad/


On Wed, Jul 27, 2011 at 8:31 AM, Renaud Lancelot
<[hidden email]> wrote:

>
> Why don't you read/write this field as a character string? Pretty common for
> codes.
>
> 2011/7/27 PtitBleu <[hidden email]>
>
> > I've just tested with 12 figures instead of 13. And it works ...
> > But I'm not able to modify these numbers as they are codes given by other
> > guys (and women as well) to distinguish equipments.
> >
> > And I thought the limit with R was 15 digits and not 13.
> >
> > Is it a (bad for me) special feature of the sqlSave command?
> > Thanks in advance for any suggestion,
> > Ptit Bleu.
> >
> >
> > --
> > View this message in context:
> > http://r.789695.n4.nabble.com/RODBC-sqlSave-problem-with-bigint-numbers-tp3697738p3697907.html
> > Sent from the R help mailing list archive at Nabble.com.
> >
> > ______________________________________________
> > [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.
> >
>
>
>
> --
> Renaud Lancelot
> EDENext Project, coordinator: http://www.edenext.eu/
>
> CIRAD, UMR15, Campus International de Baillarguet TA A-DIR / B
> F34398 Montpellier
>
> Tel.  +33 4 67 59 37 17  -  Fax  +33 4 67 59 37 95
> Secr. +33 4 67 59 37 37  - Cell. +33 6 77 52 08 69
>
>        [[alternative HTML version deleted]]
>
> ______________________________________________
> [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.



--
______________________
Jason P. Becker

______________________________________________
[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: RODBC sqlSave problem with bigint numbers

PtitBleu
In reply to this post by Renaud Lancelot
I spent all the day on this problem and I've just finally found a solution:
with options(digits=13), it now works.

If I hadn't found this, I would have used the solution of Renaud (but not very convenient to me).

Nice end of day,
(Happy) Ptit Bleu.