Hi

I am running R2.15.2 64-bit on Windows 7, using RODBC 1.3-6, MySQL5.5.20,
MySQL Connector 5.5.2 - these are the latest 64-bit versions AFAIK.

sqlQuery and sqlSave work fin as expected, but in a long session with a few
sqlSave() calls, I get an error, for example:

Error in sqlSave(channel = channel, dat = USArrests[, 1, drop = FALSE],  :
  HY000 1461 [MySQL][ODBC 5.2(w) Driver][mysqld-5.5.20]Can't create more
than max_prepared_stmt_count statements (current value: 16384)
[RODBC] ERROR: Could not SQLPrepare 'INSERT INTO `usarrests` ( `murder` )
VALUES ( ? )'

In my setup the MySQL global variable max_prepared_stmt_count has the
default setting of 16K.  If I reset the variable higher, I can run a while
longer, but this is not a permanent solution.

Digging around for a solution, I see that the following may cast some
light:

show global status like 'com_stmt%';

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Com_stmt_close          | 0     |
| Com_stmt_execute        | 49931 |
| Com_stmt_fetch          | 0     |
| Com_stmt_prepare        | 36    |
| Com_stmt_reprepare      | 0     |
| Com_stmt_reset          | 36    |
| Com_stmt_send_long_data | 0     |
+-------------------------+-------+

If I understand right, the number of Com_stmt_close should be 'close to or
equal to' Com_stmt_execute, but is not.  Rolling back to all 32-bit R2.13.2
etc does work, all entries in the table above remaining at zero,

The number Com_stmt_execute increases with each row written using
sqlSave(), but does not increase if I use sqlQuery()

#This causes Com_stmt_execute to increase 50:
sqlQuery(channel=channel,query="DROP TABLE IF EXISTS USArrests")
sqlSave(channel=channel, dat=USArrests[,1,drop=FALSE],rownames=FALSE)

#This causes no change in Com_stmt_execute :
sqlQuery(channel=channel,query="INSERT INTO USArrests (murder) values (1)")

This behaviour did not occur with R2.13.2 & RODBC 1.3-3 32-bit.

I could just revert one thing at a time to narrow it down but if anyone can
offer a shortcut I'd be delighted.

Thanks

Giles Heywood

	[[alternative HTML version deleted]]

