mysql odbc driver issue with ; and multi lined sql.

I am working on a project using MySQL for the first time. I have a valid connection to the DB and have been developing ok for a few weeks now. But I just encountered an oddity that I can only seem to attribute to the MySQL ODBC driver.

Using MySQLControl Center I can enter this as SQL:

insert into table1 values(1, 1);
insert into table1 values(2, 2);

Execute and it's all good.

If I try to do this from a connection with the MySQL ODBC driver I get an error that says "you have an error in your sql syntax near ';' in Line 1."

I am using Delphi 7.0 with ADO components connecting to my datasource using the MySQL ODBC driver version 3.51.09. If I connect to my datasource via a database explorer I get the same results... an error.

Does MySQL or the ODBC driver for MySQL not allow the ; and multi-lined SQL to be executed?

If so the MySQL ControlCenter must parse the SQL and remove the ;'s and excute them one at a time behind the scenes. I just need to know if thats the case.

I have an application server that runs on Informix and Access and MSSQL and they all work using multi-lined sql with ;'s seperating the statements. I just need to know so that I can make a parser on the server to do this if there is not just some setting or something that I am missing.

Any info would be greatly appreciated.

Fred

From version 4.1, MySQL supports the execution of multiple statements specified in a single query string. To use this capability with a given connection, you must specify the CLIENT_MULTI_STATEMENTS option in the flags parameter of mysql_real_connect() when opening the connection. You can also set this for an existing connection by calling mysql_set_server_option(MYSQL_OPTION_MULTI_STATEME NTS_ON)

By default, mysql_query() and mysql_real_query() return only the first query status and the subsequent queries status can be processed using mysql_more_results() and mysql_next_result().

/* Connect to server with option CLIENT_MULTI_STATEMENTS */mysql_real_connect(..., CLIENT_MULTI_STATEMENTS);/* Now execute multiple queries */mysql_query(mysql,"DROP TABLE IF EXISTS test_table;\ CREATE TABLE test_table(id INT);\ INSERT INTO test_table VALUES(10);\ UPDATE test_table SET id=20 WHERE id=10;\ SELECT * FROM test_table;\ DROP TABLE test_table");do{ /* Process all results */ ... printf("total affected rows: %lld", mysql_affected_rows(mysql)); ... if (!(result= mysql_store_result(mysql))) { printf(stderr, "Got fatal error processing query\n"); exit(1); } process_result_set(result); /* client function */ mysql_free_result(result);} while (!mysql_next_result(mysql));