TZquery dont works with Multiple Statements

[testebelo]

I use Delphi7 + ZeosLib 6.6.6-stable + MySql 3.23.55 + TZconnection.protocol = mysql-4.1

I need to use multiple statements in TZquery or TZReadOnlyQuery but it dont works.

This is the code:

Code: Select all

  with ZQuery1 do
   begin
      Close;
      Sql.Clear;
      Sql.Add('DROP TABLE IF EXISTS table1 ;   ');
      Sql.Add('CREATE TABLE table1 SELECT field1, field2 FROM table2 ; ');
      ExecSQL;
   end;

The error message is:

SQL Error: You have an error in your SQL syntax near ';
CREATE TABLE table1 SELECT field1, field2 FROM ' at line 1


If I execute in separate the first Sql (DROP ...) and then the second (Create table ...) and without the ";" it works fine.

It seems that I need to define the Delimiter for the Sql to ";" (like in TZSqlProcessor) but where can I define it?

Thanks in advance.

[aristideau]

Hi, I am having the same problem.
I need to import a 500k file and I need multiple statements.
Have you had any luck?.

[testebelo]

No. In your case you can use TZSqlProcessor and put the sql statements in Script property; the delimiter is the Delimiter property.

In my case I cant use TZSqlProcessor because I have to do a select in the end:

Code: Select all

with ZQuery1 do 
   begin 
      Close; 
      Sql.Clear; 
      Sql.Add('DROP TABLE IF EXISTS table1 ;   '); 
      Sql.Add('CREATE TABLE table1 SELECT field1, field2 FROM table2 ; '); 
      Sql.Add('SELECT * from table1 ; '); 
      Open; 
   end; 

And my Zquery1 is connect to a TDataSetProvider and a TClientDataSet.

Because "table1" is a temporary table I need to execute the Sql together, otherwise Zquery1 dont find the table. And I need to configurre the fields in ClientDataSet's fields editor.

[Wild_Pointer]

Hello, testebelo,

you could set AutoCommit to false, TransactionIsolation to tiNone to your Connection object, then start your own transaction by calling executedirect('START TRANSACTION ISOLATION LEVEL READ COMMITTED;').
Then call as many SQL statements as you need (creating and using temp tables) and finally call executedirect('COMMIT;')

I would suggest you take control of your transactions.

Good luck!

[testebelo]

Thanks Wild_Pointer.

In my TZconnection I set AutoCommit to false and TransactionIsolation to tiNone. I modifiy my Sql:

Code: Select all

with ZQuery1 do 
   begin 
      Close; 
      Sql.Clear; 
      Sql.Add('START TRANSACTION ISOLATION LEVEL READ COMMITTED; ');
      Sql.Add('DROP TABLE IF EXISTS table1 ;   '); 
      Sql.Add('CREATE TABLE table1 SELECT field1, field2 FROM table2 ; '); 
      Sql.Add('SELECT * from table1 ; '); 
      Sql.Add(''COMMIT ;);
      Open; 
   end; 

The kind of the error remains.

"You have an error in your SQL syntax near 'START TRANSACTION ISOLATION LEVEL READ COMMITTED;
DROP TABLE IF EXISTS table1' at line 1. "

My problem seems to be with the delimiter ";" in the end of each SQL line but I dont know where can I set this delimiter.

[Wild_Pointer]

testebelo,

instead do:

Code: Select all

   Connection.ExecuteDirect('START TRANSACTION ISOLATION LEVEL READ COMMITTED;');

   with ZQuery1 do 
   begin 
      Close;   
      Sql.Text := 'DROP TABLE IF EXISTS table1 ;'; 
      ExecSql;
      Sql.Text := 'CREATE TABLE table1 SELECT field1, field2 FROM table2 ; ';
      ExecSql;
      Sql.Text :='SELECT * from table1 ; '; 
      Open;
   end; 

   Connection.ExecuteDirect('COMMIT;');

[testebelo]

I cant use transaction because my tables are myisam but I think it is not the problem.

Really if I execute the SQLs separately it works but dont exact as I need.

Because "table1" dont exists in the Database (its temporary) and I need to fill the Fields Editor of the ClientDataSet at design time, I think that I have to drop, create and select (all together) inside the Zquery1.SQL

Why dont work execute 3 SQLs together ?

[Wild_Pointer]

testebelo,

from what I know about MySql Temporary tables they exist until the session (connection ) is active.
If you need to define fields in design time, you can do it by filling them manually (new field from pop up menu).

[testebelo]

Really I can have a problem trying to use a "temporary" table in Mysql 3.23.55 because it dont have "temporary table", so I use a normal table.
This program is for one user only, so its a workaround.

But I dont know why dont work execute 3 SQLs together? Its a bug?

To manage my Database I use "SQLYog Community" for MySql and in it I can execute as many SQLs as I want, together, each one ended with ";"

[mdaems]

Hi,

The problem is you can't use multiple statements and do a select in the end to fill a dataset. What you can do:
Use the same query object for the three queries separately. Set sql1, execsql,set sql2, execsql, set sql3, Open.
It is possible to group the first 2 sql statements by adding a line to Connection.properties (CLIENT_MULTI_STATEMENTS=TRUE).
However, I seriously doubt this would open a usefull dataset. And you should verify in mysql documentation if MYSQL_CLIENT_OPTIONS already supports this option in mysql 3.23.

Concerning SQLYog : I personaly think it uses a parser to split all statements and sends them one by one to the server. Which is why it can handle this situation. In Delphi you'll have to handle it the same way.

[Stevie]

I think this is actually a bug in ZEOS as it cannot handle what the DBMS supports.
See https://dev.mysql.com/doc/refman/5.7/en ... eries.html

Here is a patch to enable this functionality (based on 7.1.4-stable)

Code: Select all

--- ZDbcMySqlStatement.pas
+++ ZDbcMySqlStatement.pas
@@ -324,8 +324,21 @@
   begin
     DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, LogSQL);
     if not FPlainDriver.ResultSetExists(FHandle) then
-      raise EZSQLException.Create(SCanNotOpenResultSet);
-    Result := CreateResultSet(LogSQL);
+    begin
+      while GetMoreResults do
+      begin
+        if LastResultSet <> nil then
+        begin
+          Result := LastResultSet;
+          Break;
+        end;
+      end;
+
+      if Result = nil then
+        raise EZSQLException.Create(SCanNotOpenResultSet);
+    end
+    else
+      Result := CreateResultSet(LogSQL);
   end
   else
     CheckMySQLError(FPlainDriver, FHandle, lcExecute, LogSQL);

It also needs a small fix to prevent a memory leak:

Code: Select all

--- ZDbcStatement.pas
+++ ZDbcStatement.pas
@@ -2521,6 +2521,8 @@
 begin
   if FCachedQuery <> nil then
     FCachedQuery.Free;
+  if FExecStatement <> nil then
+    FExecStatement.Close;
   inherited Destroy;
 end;

[marsupilami]

Hello Stevie,

I created a ticket for your proposal on Sourceforge. See https://sourceforge.net/p/zeoslib/tickets/186/
I think Michael wants to think about a solution that allows to cycle through the possible resultsets.

With best regards,

Jan

[Stevie]

Sure that would be better - however until now even if you did multiple select statements it returned the results from the first.
It only raised an exception if the first statement did not return a result set but discarded the following anyway. So I just fixed that by making it possible to return the first result set.

Without that fix the common pattern:

insert ...
select last_insert_id

in MySQL was not possible in one step.

[marsupilami]

Hello Stevie,

first of all I am no expert for MySQL so please forgive me if I am wrong at some point. I tried understanding your patches and have some questions.

My first problem is that your modification to TZMySQLStatement.GetMoreResults seems to change the semantics of that function. The comment above that function says:

This method also implicitly closes any current ResultSet object obtained with the method getResultSet.

If I understand your modification corretly, this doesn't happen anymore if there are no more resultsets? Also, if I understand your modifications to TZMySQLStatement.ExecuteQuery correctly, you don't need that modification because ExecuteQuery will stop asking for more results as soon as it has the first result because in that case it breaks the while loop? Or am I wrong?

With best regards,

Jan

[Stevie]

I think you are absolutely right.

My first version of the change did take the last resultset and it was always closed because of the following call to GetMoreResults which returned False.
Later I decided to take the first and leave the loop. So it could be the change in GetMoreResults is not needed anymore. I will verify tomorrow and report.