I have the following problem in getting the "sqlSave" function from the
example code in the R package RODBC to work with MySQL as ODBC server:
 - a new database is created, but no data is written to it
 - the example code works just fine when I use MS Access as ODBC server.
 
----------- offending code and output -------------------
> library(RODBC);
> channel <- odbcConnect("opus");

> data(USArrests) # R < 2.0.0 only

> sqlSave(channel, USArrests, rownames = "state", addPK=TRUE)
Error in sqlColumns(channel, tablename) : USArrests : table not found on
channel 
Check case parameter in odbcConnect

> sqlSave(channel, USArrests, rownames = "state", addPK=TRUE)
Error in sqlSave(channel, USArrests, rownames = "state", addPK = TRUE) :

        [RODBC] ERROR: Could not SQLExecDirect

> odbcGetErrMsg(channel)
character(0)
 
> sqlTables(channel)
  TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE     REMARKS
1      opus              usarrests      TABLE MySQL table
---------------------------------------------------------------------
 
 
The next thing I did was to check from the MySQL commandline what the
status of the database was.
 
 
------------------------ MySQL commandline query
-----------------------------------------------------------
mysql> use opus;
Database changed
mysql> show tables;
+----------------+
| Tables_in_opus |
+----------------+
| usarrests      |
+----------------+
1 row in set (0.00 sec)
 
mysql> describe usarrests;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| state    | varchar(255) |      | PRI |         |       |
| Murder   | double       | YES  |     | NULL    |       |
| Assault  | int(11)      | YES  |     | NULL    |       |
| UrbanPop | int(11)      | YES  |     | NULL    |       |
| Rape     | double       | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
 
mysql> select * from usarrests;
Empty set (0.00 sec)
 
------------------------------------------------------------------------
-------------------------------------------------------------
 
 
So: the table is created, but not filled. As noted, the example code
works OK with MS access.
 
The software versions I use are: 
- OS: Windows XP SP2
- R: 2.0.1; installed as binary
- RODBC: latest version from CRAN
- MySQL: 4.0.21-nt
 
P.S. The problem has low priority for me bacause I can simply use
Microsoft Access as RODBC server, but in future I would like to revert
back to MySQL. 
 
 

	[[alternative HTML version deleted]]


