4

I'm using RODBC to interface R with a MySQL database and have encountered a problem. I need to join two tables based on unique ID numbers (IDNUM below). The issue is that the ID numbers are 20 digit integers and R wants to round them. OK, no problem, I'll just pull these IDs as character strings instead of numeric using CAST(blah AS CHAR).

But R sees the incoming character strings as numbers and thinks "hey, I know these are character strings... but these character strings are just numbers, so I'm pretty sure this guy wants me to store this as numeric, let me fix that for him" then converts them back into numeric and rounds them. I need to force R to take the input as given and can't figure out how to make this happen.

Here's the code I'm using (Interval is a vector that contains a beginning and an ending timestamp, so this code is meant to only pull data from a chosen timeperiod):

test = sqlQuery(channel, paste("SELECT CAST(table1.IDNUM AS CHAR),PartyA,PartyB FROM 
table1, table2 WHERE table1.IDNUM=table2.IDNUM AND table1.Timestamp>=",Interval[1]," 
AND table2.Timestamp<",Interval[2],sep=""))
joran
  • 169,992
  • 32
  • 429
  • 468
Johnathan Maddon
  • 105
  • 5
  • "The issue is that the ID numbers are 20 digit integers" 0_0 ... WHY?! Why would you try to store ONE QUINTILLION rows in a MySQL database?! –  Aug 26 '13 at 21:33
  • Ha, not my database! I'm doing contract work, gotta take what's given to me :) – Johnathan Maddon Aug 26 '13 at 21:52
  • 2
    I'm not an R user, but I just saw this and it looks like a clear and reasonable question to me. I can't see why it's been downvoted, let alone why it just got flagged as Very Low Quality. Anyone care to explain their reasoning? – Mark Amery Aug 26 '13 at 21:55
  • 1
    @Mark : Please see [here](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example): reasons – Metrics Aug 26 '13 at 22:03
  • 5
    @Metrics Some questions are well-formed and answerable without reproducible code. – joran Aug 26 '13 at 22:19

1 Answers1

1

You will most likely want to read the documentation for the function you are using at ?sqlQuery, which includes notes about the following two relevant arguments:

as.is which (if any) columns returned as character should be converted to another type? Allowed values are as for read.table. See ‘Details’.

and

stringsAsFactors logical: should columns returned as character and not excluded by as.is and not converted to anything else be converted to factors?

In all likelihood you want to specify the columns in questions in as.is.

joran
  • 169,992
  • 32
  • 429
  • 468