MySQL Bugs: #3745: Error while executing multiple operations in one query (ASP/MyODBC)

Bug #3745	Error while executing multiple operations in one query (ASP/MyODBC)
Submitted:	13 May 2004 16:12	Modified:	13 May 2004 18:22
Reporter:	TEHEK tehek	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51.6, 3.51.7	OS:	Windows (Windows XP, Windows 2000)
Assigned to:	Dean Ellis	CPU Architecture:	Any

Description:
Hello! Respect to MySQL developers!

I use:
   MySQL 4.0.17 for Windows
   MySQLCC v0.9.3beta
   MyODBC v3.51.6 and 3.51.7
   Windows XP (and 2000)
   ASP on IIS
   ADODB objects (tested with ADODB.Connection, ADODB.Command, ~.Recordset)

I have such a problem (I find it critical): When I attempt to make several operations in one query string (using semicolon ";" separator), example:

     INSERT INTO table1 (Field1, Field2) VALUES (1,1); INSERT INTO table1 (Field1) VALUES (2)

I got this:

[MySQL][ODBC 3.51 Driver][mysqld-4.0.17-nt]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '; INSERT INTO table1 (Field1) VALUES (2)' at line 1

Practically the same happens, when i try to execute several "SELECT queries" 
     SELECT * FROM table1; SELECT * FORM table1

Also it happens when I execute nested (cascaded or how to call it...hmm...) 
     SELECT * FROM table1 WHERE Field1 NOT IN (SELECT Field1in2 FROM table2);
	
When I tried to execute the same script in MySQL Control Center and even from console (mysql.exe), everything worked perfectly!

Please fix it or make it a feature :)
                                             With all my respect, TEHEK.

P.S.: Possible solutions for those who also have this problem is to break the query into several queries and execute them one by one. But if you need them to work in one transaction (everyone or noone!) you'll experience heavy problems with transaction rollback.

How to repeat:
1) I used MySQLCC to create tables in default ('test') database. And it generated such script:

CREATE TABLE `table1` (
  `Index` int(11) NOT NULL auto_increment,
  `Field1` varchar(100) NOT NULL default '',
  `Field2` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`Index`)
) TYPE=MyISAM; 

# Host: localhost
# Database: test
# Table: 'table2'
# 
CREATE TABLE `table2` (
  `Index` int(11) NOT NULL auto_increment,
  `Field1in2` varchar(100) NOT NULL default '',
  `Field2in2` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`Index`)
) TYPE=MyISAM; 

So the structure is like this (if the script doesn't work):

Database "test":

  table `table1`:
     Index (key)
     Field1
     Field2

  table `table2`:
     Index (key)
     Field1in2
     Field2in2

2) Create a user with access rights to this database. I've created:
     USER: odbctest
     PASSWORD: odbctest

Create a `System DSN` from Control panel -> Administration -> ODBC DataSources
   Driver: MySQL ODBC 3.51 Driver
   Datasource name: odbctest
   Database name: test
   User: odbctest
   Password: odbctest

3) I suggest, you have a server, that can run ASP scripts with ADO objects (in my case it was IIS).
Create file (e.g. `1.asp`) with such content:

<%@LANGUAGE="JAVASCRIPT"%>

<%
	var DBC = Server.CreateObject("ADODB.Connection");
	
	DBC.Open ("dsn=odbctest;UID=odbctest;PWD=odbctest");
	

        // Uncomment needed lines to test
	DBC.Execute("INSERT INTO table1 (Field1, Field2) VALUES (1,1); INSERT INTO table1 (Field1) VALUES (2)");
	//DBC.Execute("SELECT * FROM table1; SELECT * FORM table1");
	//DBC.Execute("SELECT * FROM table1 WHERE Field1 NOT IN (SELECT Field1in2 FROM table2)");
	
	
%>

4) Run it

Subqueries and multiple statements within the same batch are implemented within MySQL 4.1.  There is no support for them in 4.0; some clients appear to support multiple statements with 4.0 but they are simply breaking your batch into multiple queries.

I have the same issue with MYSQL 5.  I'm converting an ASP page from acessing a MSSQL database to MYSQL, and having a problem where a SQL statement inserts some data and then returns the newly created identity key (eg. Insert into table values(null,1,2); select @@identity as 'identity'). This works fine through MYSQL query analyser, but fails on the ASP page. It doesn't like having multiple selects (tried simple test of two selects and it fails also).

Wonder is this a limitation - or is there some sort of other syntax ot use?

I too am having the problem on a MySQL 4.1.10-MAX server.  Ive tried everything from using InnoDB tables to changing syntax.  And I stll have the same error...

 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
 INSERT IGNORE INTO table (column) VALUES (

Figured it might have something to do with the IGNORE for some odd reason, but that turned up nothing.

This is on a Gentoo Linux 2004.2 system using the php API for the connector.

You must be using the newer server versions, _and_, you must explicitly enable support for multiple statements via the API you are using.  For PHP, this requires the mysqli extension available in PHP5.