Execute multiple SQL queries in one call

[Sergey Kolbunov]

I'm creating utility tool to add/update data in MySQL database. I'm using the Database Connectivity toolkit in LabVIEW. I never had problems with it, but I always used just single SQL queries. Now I tried to send multiple semicolumn delimited queries to the DB Tools Execute Query.vi and got the error -2147217900 :"You have an error in your SQL syntax" while the queries run perfectly in phpmyadmin. Then I tried to change Cursor Type for DB Tools Execute Query.vi from the default "forward-only" to "dynamic" or other kind of bidirectional cursors. In this case I got the error -2147217887 : "Exception occured in Microsoft OLE DB Provider for ODBC Drivers: ODBC driver does not support the requested properties." Both errors are gone in the case of single query.

Here is my query: SET @NewTestID = (SELECT TestID+1 FROM `TestSteps` WHERE TestID BETWEEN (100000) AND (199999) ORDER BY TestID DESC LIMIT 1);

INSERT INTO `TestSteps` (TestID , Description , Test_Name ) VALUES ((@NewTestID), 'test', 'test');

Select @NewTestID;

Is that a principial limitation of the Database Connectivity toolkit or limitation of my drivers? I'm using MySQL 5 and ODBC 3.51 drivers.

[jzoller]

If you're using MyODBC, you may need to update your driver, or possibly recompile with an optional switch.

From http://bugs.mysql.com/bug.php?id=7445:

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.

As far as cursors, it appears there may be a mismatch between MySQL cursors (http://dev.mysql.com/doc/refman/5.0/en/cursors.html) and the DBCT cursors (http://www.ni.com/pdf/manuals/321525c.pdf, page 62)... not sure how well they'll work together, but I haven't had the toolkit for a while.

Joe Z.

[MikaelH]

Don't you need to: DECLARE @NewTestID int

I would suggest to write a Stored Procedure that does it.

Cheers,

Mikael

[Sergey Kolbunov]

Zoe, Mikael,

Thank you, I'll try to update the ODBC driver tomorrow. I thought about stored procedures. My queries getting bigger, so I'll try them.

Sergey