| Bug #7445 | MyODBC still doesn't support batch statements | ||
|---|---|---|---|
| Submitted: | 20 Dec 2004 23:12 | Modified: | 3 Aug 2007 12:16 |
| Reporter: | Nathan Jensen | ||
| Status: | Closed | ||
| Category: | Connector/ODBC | Severity: | S2 (Serious) |
| Version: | 3.51.10 | OS: | Microsoft Windows (Windows 2000/XP) |
| Assigned to: | Jim Winstead | Target Version: | |
[21 Dec 2004 14:15]
Harun Eren
Hi, Thank you for your bug report. It is correctly the driver did not support this functionality. Best Regards
[1 Sep 2005 14:09]
Tais Hansen
Here we are - nine months later - and MyODBC still doesn't support batch statements?!
[20 Oct 2005 1:27]
Dimitri Psaltopoulos
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.
[9 Nov 2006 21:42]
Larry Adams
Have you tried "INSERT INTO Users (username) VALUES ('Sally'), ('Sue'), ('Bob'),
('Birtha')"? That works very well.
[9 Nov 2006 21:50]
Nathan Jensen
>> 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.
[13 Jun 2007 9:04]
Georg Richter
Support for batch commands
Attachment: bug7445.diff (text/x-patch), 1.64 KiB.
[14 Jun 2007 2:26]
Jim Winstead
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.)
[28 Jul 2007 1:53]
Jim Winstead
Add option for enabling multi statements, including GUI
Attachment: bug7445.patch (text/plain), 6.92 KiB.
[30 Jul 2007 17:27]
Jim Winstead
The addition of this option was committed to our source tree, and will be in 3.51.18.
[3 Aug 2007 12:16]
MC Brown
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.
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.