0

I usually work with flat files but this time I'm trying to connect to a relational database but with no success. First time for me, I therefore apologize in advance if the question is silly.

Here are the details of the ODBC connection enter image description here

And here is the code I am using to try to connect

install.packages("RMySQL")
library(DBI)
dbConnect(RmySQL::MySQL(),dbname="nicer_med", host="WRGTNICER1", port=3306, user="root", password="xxxxx")

and the error message I get

> dbConnect(RmySQL::MySQL(),dbname="nicer_med", host="WRGTNICER1", port=3306, user="root", password="Rgt_9098")
Error in (function (cond)  : 
  erreur d'�valuation de l'argument 'drv' lors de la s�lection d'une m�thode pour la fonction 'dbConnect' : aucun package nommé ‘RmySQL’ n'est trouvé

I have tried another strategy using another package

install.packages("RODBC")
library(RODBC)
mycon<-odbcConnect("nicer_64")

Here the connection seems to work as I am able to describe the tables:

sqlTables(mycon)
sqlColumns(mycon,"a_patient")

Both are working but my queries actually don't:

> sqlQuery(mycon,paste("SELECT PID,NAME, FIRSTNAME1, SEXE, FROM a_patient;"))
[1] "42000 1064 [MySQL][ODBC 5.1 Driver][mysqld-5.6.22-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM a_patient' at line 1"
[2] "[RODBC] ERROR: Could not SQLExecDirect 'SELECT PID,NAME, FIRSTNAME1, SEXE, FROM a_patient;'"

I have tried several Sql dialects : uppercase, lowercase, with or without ";" and """, nothing is working for me.

I am still trying to find a way to have access to the data...I would be very happy to find some help. Thanks.

Robisch
  • 3
  • 1
  • 2
    After SEXE is a comma too much remove it – nbk Jun 10 '22 at 11:38
  • Thanks a lot. I finally found the issue. It is related to variables having dates as format. I get this error message: Error in as.POSIXlt.character(x, tz, ...) – Robisch Jun 10 '22 at 15:40
  • Your error *here* has nothing to do with date/timestamp formats, it is that your SQL statement has a syntax/parsing error. If you need further help here (and if this is different from your [other question](https://stackoverflow.com/questions/72577073/odbc-sqlquery-and-date-format), then I strongly suggest you [edit] your question, fix the syntax error, and add context to justify what is wrong and how this differs from the other. Thank! – r2evans Jun 12 '22 at 14:24
  • Thanks for you reply. Your are right this is redundant with my other qiestion. I'll delete this one – Robisch Jun 14 '22 at 07:59

0 Answers0