MySQL Bugs: #7445: MyODBC still doesn't support batch statements

Bug #7445	MyODBC still doesn't support batch statements
Submitted:	20 Dec 2004 22:12	Modified:	3 Aug 2007 10:16
Reporter:	Nathan Jensen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.10	OS:	Windows (Windows 2000/XP)
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
It appears that MyODBC still does not support batched sql (multiple sql statements separated by semicolons). MySQL has supported batch for all versions of 4.x. When will MyODBC catch up? The fact that this isn't supported forces sql-related application code to be written very inefficiently, because if you have 10 INSERT's you want to run, you have to send each in a separate connection, which is very inefficient.

Or is there some flag or setting that I need to configure for MyODBC to support this?

How to repeat:
Try sending multiple INSERT's in one statement via MyODBC, like so:

INSERT Users ('Jim');
INSERT Users ('Bob');
INSERT Users ('Calvin');

This will throw an error in myodbc, but it works fine in straight mysql 4.x.

Suggested fix:
Please bring MyODBC up to speed with MySQL 4.x to support batch statements.

Hi,

Thank you for your bug report.   
It is correctly the driver did not support this functionality.

Best Regards

Here we are - nine months later - and MyODBC still doesn't support batch statements?!

Do you plan to fix this like ever? This is causing me massive headaches, having to re-write big portions of my code when porting to mysql from sql server.

Have you tried "INSERT INTO Users (username) VALUES ('Sally'), ('Sue'), ('Bob'), ('Birtha')"?  That works very well.

>> from Larry Adams: "Have you tried "INSERT INTO Users (username) VALUES ('Sally'), ('Sue'), ('Bob'), ('Birtha')"?  That works very well."

That's fine, but it doesn't even come close to true batch statements. A batch is a series of atomic sql statements that can be executed one after another. Like this:

INSERT INTO Users (username) VALUES ('Sally'), ('Sue'), ('Bob'), ('Birtha')
INSERT INTO Users (username) VALUES ('Sally2'), ('Sue2'), ('Bob2'), ('Birtha2')
INSERT INTO Users (username) VALUES ('Sally3'), ('Sue3'), ('Bob3'), ('Birtha3')

If you run this directly against mysql through a UI tool like Yog, it works fine, but if you run it through MyODBC, it chokes.

Support for batch commands

Attachment: bug7445.diff (text/x-patch), 1.64 KiB.

We have to make this an OPTIONS flag. Enabling batch statements creates a larger target for SQL injection attacks, so developers/admins should be aware they are making this choice. This is consistent with Connector/J, the C client library, DBD::mysql, and probably other drivers. (Connector/Net is an exception here. I'm not sure why.)

Add option for enabling multi statements, including GUI

Attachment: bug7445.patch (text/plain), 6.92 KiB.

The addition of this option was committed to our source tree, and will be in 3.51.18.

A note has been added to the 3.51.18 changelog: 

Connector/ODBC now supports batched statements. In order to enable catched statement support you must switch enable the batched statement option (FLAG_MULTI_STATEMENTS, 67108864, or Allow multiple statements within a GUI configuration). Be aware that batched statements create an increased chance of SQL injection attacks and you must ensure that your application protects against this scenario.

I've also added an entry to the FAQ to cover the dangers of batched statements and how to enable support, and the connection options table has been updated.