[R-sig-DB] RODBC and MySQL: problem with sqlSave function
Prof Brian Ripley
r|p|ey @end|ng |rom @t@t@@ox@@c@uk
Mon May 10 14:53:00 CEST 2010
Please do study the posting guide and post again with the information
requested there.
The list moderator
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))
>
> 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]]
>
>
--
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
More information about the R-sig-DB
mailing list