Multiple Delphi TZquery Error Statements (Zeos)

Question

im trying to query several statements as follows:

// without the second insert the query works fine. // i need 2 querys to work because later, i'll do inserts on different kind of tables. // that why i need 2 querys, not a single query which insert 2 records. with ZQuery1 do begin SQL.Clear; SQL.Add('insert into client (name,age) values ('+QuotedStr('john')+','+QuotedStr('20')+');'); SQL.Add('insert into client (name,age) values ('+QuotedStr('doe')+','+QuotedStr('21')+');'); ExecSQL; end; 

I received this error message: SQL error: you have an error in the SQL syntax; check the manual that matches the version of your MySQL server for the correct syntax to use next to the values ​​"paste into the client" (name, age) ("doe", "21") on line 2;

I am already checking the manual. The TZQuery and TZUpdateSql components (from zeos lib) provide the ability to execute multiple instructions internally.

EDIT [SOLVED]

Thanks, GregD, after a few tests, transactions work just fine for me! what I use to help others in the future.

 try ZConnection.AutoCommit := True; ZConnection.StartTransaction; With ZQuery Do begin SQL.Clear; SQL.Add('insert into clients (name,age) values ('+QuotedStr('john')+','+QuotedStr('20')+')'); ExecSQL; SQL.Clear; SQL.Add('insert into clients (name,age) values ('+QuotedStr('doe')+','+QuotedStr('21')+')'); ExecSQL; end; ZConnection.Commit; except ZConnection.Rollback end; 

This is how the AutoCommit property really works in Zeos:

when AutoCommit is True, transactions are made automatically after each executable SQL statement, but you can use the StartTransaction command explicitly to prevent automatic commits until you explicitly call Commit.

when AutoCommit is False, you should not call StartTransaction. Then the transaction starts automatically, but it will not be automatically recorded after each executed statement.

StartTransaction procedure . The StartTransaction procedure starts a new transaction in the connected database. It should be used only when the AutoCommit property is set to TRUE. Whenever you try to call it with AutoCommit set to false, SInvalidOpInNonAutoCommit will be activated. This behavior is expected since StartTransaction should be used as an exit to AutoCommit mode. When you call StartTransaction, AutoCommit is turned off, and then when you call Commit or Rollback, AutoCommit is turned on again. If you work with AutoCommit equal to false, new transactions are created automatically, and you choose how to close them (Commit or Rollback).

procedure Commit Commit the current statements in the database. It should be used only in non-automatic mode (where each operator is auto-updated, which makes this procedure useless) or when you are in AutoCommit mode and want to complete the transaction opened by the StartTransaction procedure. Commiting completes the current transaction, if any. If you do not want to save your settings in the database, you must use the rollback procedure.

Rollback procedure Rollback of all previous statements in the current transaction. It should be used only in non-automatic mode (where each operator is auto-updated, which makes this procedure useless) or when you are in AutoCommit mode and want to complete the transaction opened by the StartTransaction procedure. Rollback completes the current transaction, if any. If you do not want to lose your feelings, you should use the fixation procedure.

Answer 1

Try this code and let us know if the same problem occurs:

 with ZQuery1 do begin SQL.Clear; SQL.Add('insert into client (name,age) values ('+QuotedStr('john')+','+QuotedStr('20')+'),('+QuotedStr('doe')+','+QuotedStr('21')+');'); ExecSQL; end; 

Thus, you can also speed up MySQL processing of this INSERT query, as it happens in one batch, and not twice.

EDIT. # 1:

I'm not an expert at Zeos, but with other languages ​​you can try to execute the query one by one:

 with ZQuery1 do begin SQL.Clear; SQL.Add('insert into client (name,age) values ('+QuotedStr('john')+','+QuotedStr('20')+');'); ExecSQL; SQL.Clear; SQL.Add('insert into client (name,age) values ('+QuotedStr('doe')+','+QuotedStr('21')+');'); ExecSQL; end; 

EDIT # 2 : Transactions

fooobar.com/questions/26203 / ... there are many good examples of using transactions in MySQL. Although the examples are written for PHP, I'm sure you can find good pointers there. Make sure your tables are on the MySQL InnoDB not MyISAM server.

Answer 2

I have no idea about Zeos and a few statements, but that is not a problem. You caused a serious security problem with your query (SQL injection) and a slow way to execute them (concatenated strings that cannot be cached and reused).

If you correctly stop using string concatenation to form your queries and use parameterized instructions instead, you don’t need to worry about a few statements at all:

 with ZQuery1 do begin SQL.Clear; SQL.Add('insert into client (name,age)'); SQL.Add('values (:Name, :Age);' ParamByName('Name').AsString := 'John'; ParamByName('Age').AsInteger := 20; ExecSQL; ParamByName('Name').AsString := 'Doe'; ParamByName('Age').AsInteger :- 21; ExecSQL; end; 

Now the query will work faster (because the DBMS can compile it once and reuse it several times (the "caching" I mentioned), you no longer have the risk of SQL injection, and several statements no longer work necessary.

Answer 3

I'm also not an expert in ZEOS, but looking at the source , you set the MultiStatements TZUpdateSQL property to true?

Answer 4

Have you tried TZSQLProcessor? He said that the component was created for such needs (as in the ZSqlProcessor.pas block):

 {** Implements a unidatabase component which parses and executes SQL Scripts. }