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