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

[Boris Leroy]

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))

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

> 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

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]]