MySQL driver : more than 1 statement in 1 query ?

[bangkok]

I try to make a revExecuteSQL on a query like :

"LOCK TABLES mytable ; INSERT...... ; UNLOCK TABLES"

The RunRev driver gives an error message.

If I paste the same query in the query editor of HeidiSQL... I can run it (successfully) (with or without a ";" a the end).

So I'm wondering, is it possible to have multiple statements in one query with the MySQL driver of RunRev ?

[Mark]

Hi bangkok,

This bug has been reported more than 5 years ago but hasn't had nay reponse from RunRev yet.

Best regards,

Mark

[bangkok]

This bug has been reported more than 5 years ago but hasn't had nay reponse from RunRev yet.

Fair enough ! It's clear. Thanks.

I guess we have to wait.

A little bit more.

[malbert]

I have also encountered this issue in the latest LC 4.6.4 version (Build 1451) and wondering if this is going to be addressed.

I have a MySQL DB and have written quite a few SQL statements of approx 100+ lines each and would prefer not to have to parse thru each each statement and execute sequentially (setting local variables etc).

All of the MySQL clients I have been using (Navicat, Aqua Data Studio etc) happily support the combining of these statements and execute but it appears LC does not.

From what I can tell, MySQL needs to have the client driver set the "allowMultiQueries" to be true (default is false) which will allow all of the SQL statements, delimited by a ";'" to work.
The link to the MySQL docs (on the MySQL web site) is:

/doc/refman/5.0/en/connector-j-reference-configuration-properties.html

A simple example of what I am trying to do:

Code: Select all

put  "SELECT @ShipTo:='123456'; SELECT * FROM Assessments WHERE Ship_to_key=@ShipTo;"  into vSQL
put revDataFromQuery(tab,return,tDB,vSQL) into vQueryData -- Call the query

The Error result is:
revdberr,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 'SELECT * FROM Assessments WHERE Ship_to_key=@ShipTo' at line 1

Would appreciate any input from folks on this issue.

Regards
Mike