[R-sig-DB] RODBC and MySQL: problem with sqlSave function

[Boris Leroy]

2010/5/10 Prof Brian Ripley <ripley using stats.ox.ac.uk>:
> Please do study the posting guide and post again with the information
> requested there.
>
> The list moderator

I put the additional informations below, as well as a much simpler example.
Please note that I also tested my code on the last R version (2.11.0)
but I got exactly the same error.

>
> On Mon, 10 May 2010, Boris Leroy wrote:
>
>> Hello,
>>
>> I have trouble using RODBC with MySQL. (R version : 2.9.2 ; MySQL version
>> :
>> 5.1.37 (embedded with xampp on a remote server))

OS: Windows XP Professional SP3 on both client and server

>>
>> The main problem is that I can't use the function sqlSave, since it seems
>> to
>> send "?" to the server instead of the correct values in tables.
>>
>> Here is my code :
>> note: I extract from the database a table, and then I try to write it into
>> the database.
>> This is because I want to be sure that the table is in a correct format,
>> and
>> I can therefore try to understand why the function sqlSave doesn't work.
>>
>>> require(RODBC)
>>
>> Le chargement a n?cessit? le package : RODBC
>>
>>> channel<-odbcDriverConnect() #I do not specify the database name here

> odbcGetInfo(channel)
                 DBMS_Name                   DBMS_Ver
                   "MySQL"                   "5.1.37"
           Driver_ODBC_Ver           Data_Source_Name
                   "03.51"               "URU420serv"
               Driver_Name                 Driver_Ver
             "myodbc5.dll"               "05.01.0006"
                  ODBC_Ver                Server_Name
              "03.52.0000" "129.20.94.228 via TCP/IP"


>>
>>> stationsData<-sqlFetch(channel, "baseimport.importstations")
>>
>> Erreur dans fromchar(x) :
>>  character string is not in a standard unambiguous format
>>
>> This does not work because I have null dates in my table : "00-00-0000"
>> and
>> R does not manage to convert them
>> The solution is:
>>
>>> stationsData<-sqlFetch(channel, "baseimport.importstations", as.is=T)
>>
>> Now, I would like to write the table "stationsData" in my database:
>>
>>> sqlSave(channel, stationsData, "baseimport.test1")
>>
>> Erreur dans odbcUpdate(channel, query, mydata, coldata[m, ], test = test,
>>  :
>>
>>  no parameters, so nothing to update
>>
>> Didn't work..
>>
>>> sqlSave(channel, stationsData, "baseimport.test2", verbose=T)
>>
>> Query: CREATE TABLE baseimport.test2  (`rownames` varchar(255),
>> `station_id`
>> integer, `dept` varchar(255), `commune` varchar(255), `corine`
>> varchar(255),
>> `c1` varchar(255), `c2` varchar(255), `c3` varchar(255), `c4`
>> varchar(255),
>> `c5` varchar(255), `c6` varchar(255), `c7` varchar(255), `c8`
>> varchar(255),
>> `lieudit` varchar(255), `reservelabel` varchar(255), `milieu`
>> varchar(255),
>> `dateech` varchar(255), `datedebutech` varchar(255), `datefinech`
>> varchar(255), `anneeech` integer, `anneedebutech` integer, `anneefinech`
>> integer, `anneepub` integer, `typeech` varchar(255), `xreleve` integer,
>> `yreleve` integer)
>> Query: INSERT INTO baseimport.test2 ( `` ) VALUES (
>> ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )
>> Erreur dans odbcUpdate(channel, query, mydata, coldata[m, ], test = test,
>>  :
>>
>>  no parameters, so nothing to update

A simpler example:

> nameData<-sqlFetch(channel, "dbmassifarmoricain.collaborateurs", as.is=T)[1,]

> nameData
  collaborateur_id   nom prenom
1                1 Leroy  Boris

> sqlSave(channel, nameData, "baseimport.test", verbose=T )
Query: CREATE TABLE baseimport.test  (`rownames` varchar(255),
`collaborateur_id` integer, `nom` varchar(255), `prenom` varchar(255))
Query: INSERT INTO baseimport.test ( `` ) VALUES ( ?,?,?,? )
Erreur dans odbcUpdate(channel, query, mydata, coldata[m, ], test = test,  :
  no parameters, so nothing to update

Additional information:

> sessionInfo()
R version 2.9.2 (2009-08-24)
i386-pc-mingw32

locale:
LC_COLLATE=French_France.1252;LC_CTYPE=French_France.1252;LC_MONETARY=French_France.1252;LC_NUMERIC=C;LC_TIME=French_France.1252

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base

other attached packages:
[1] RODBC_1.3-1

>>
>> So, the function seems to be able to create the table, but the INSERT INTO
>> query does contain any correct value.
>> Do you have any idea about this problem, or where did I fail ?
>> I tried to add columns in an existing table with append=T, I had the same
>> error.
>>
>> note: Another function of RODBC did not want to work with MySQL:
>> sqlColumns,
>> which never gave any results, but this is not my major concern.
>>
>> Many thanks in advance,
>>
>> Boris
>>
>> --
>> Boris Leroy,
>> PhD Student
>> URU 420 Biodiversity and Territory Management
>> University of Rennes 1 / Mus?um National d'Histoire Naturelle
>>
>>        [[alternative HTML version deleted]]
>>
>>
>
> --
> Brian D. Ripley,                  ripley using stats.ox.ac.uk
> Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
> University of Oxford,             Tel:  +44 1865 272861 (self)
> 1 South Parks Road,                     +44 1865 272866 (PA)
> Oxford OX1 3TG, UK                Fax:  +44 1865 272595
>