0

I am trying to retrieve data from a MySQL Database using RODBC with the commands odbcConnect and sqlQuery. There are different tables in the database and in some cases it works without any difficulties. Nevertheless I get an error with some of them and it seems to be related to date format. Since I'm not familiar with MySQL I hope to get some ideas here.

My code looks as follows:

library(RODBC)
mycon<-odbcConnect("nicer_64")
> data1<-sqlQuery(mycon,paste("select PID, NAME, FIRSTNAME1, SEXE from a_patient;"))
> head(data1)
    PID     NAME       FIRSTNAME1 SEXE
1 22145 PILLONEL   GASTON ANTONIN    1
2 22146  PORCHER  HENRIETTE MARIE    2
3 22147    PUGIN   AUGUSTE JOSEPH    1
4 21437     HEIZ MARIE ANTOINETTE    2
5 21439     KALT             OTTO    1
6  2144  BRUNNER            MARIE    2
> data2<-sqlQuery(mycon,paste("select PID, NAME, FIRSTNAME1, BIRTHDATE, SEXE from a_patient;"))
Error in as.POSIXlt.character(x, tz, ...) : 
  la chaîne de caractères n'est pas dans un format standard non ambigu
> head(data2)
Error in head(data2) : object 'data2' not found

Many thanks in advance!

Robisch
  • 3
  • 1
  • Try adding `as.is="BIRTHDATE"` (set to a vector of any date-formatted columns, in case you have more) to your call to `sqlQuery`. The column is most likely going to be string at this point, not a date or timestamp, but it will greatly inform where you go next. For instance, it might be that you cannot trust the date/time format in this database, must always query "as is", and then use `as.POSIXct` or `as.Date` manually post-query. Unless you have large amounts of data, this is unlikely to be very onerous, and will allow you to continue forward with your work. – r2evans Jun 12 '22 at 14:27
  • Many thanks for your reply. I have tried to add as.is but I still get an error: `data2<-sqlQuery(mycon,paste("select PID,NAME, FIRSTNAME1, BIRTHDATE, SEXE from a_patient;"),as.is="BIRTHDATE") Error in sqlGetResults(channel, errors = errors, ...) : 'as.is' has the wrong length 1 != cols = 5` – Robisch Jun 14 '22 at 07:54
  • Odd, but ... okay, try `as.is=c(F,F,F,T,F)` – r2evans Jun 14 '22 at 11:57
  • 1
    It works!! Thanks a lot! This was only a very small subset of the data...I do have a lot of tables and variables so won't be easy to implement globally but at least it works! – Robisch Jun 14 '22 at 12:15
  • If you run into other problems, one option is to switch from `RODBC` to `DBI`/`odbc`. In my experience, the latter combination is more robust/resilient than `RODBC`. If all you're doing is just reading or inserting (nothing complicated), then most of the switch will be changing from `odbcConnect` -> `dbConnect`, `sqlQuery` -> `dbGetQuery`, perhaps a few others. – r2evans Jun 14 '22 at 12:27

0 Answers0