Complete newbie question!

GVP · Post by **GVP** » 10.02.2009, 15:45

Hello,

A complete newbie, so be gentle!
Using Delphi 6, Zeoslib 6.6.4 & MySQL 5.1.31 under Windows.

When I try a multi-row update like this:

Code: Select all

    QuotesZQuery.SQL.Clear;
    QuotesZQuery.SQL.Add('UPDATE LineItemTable SET DesignTime = ' + IntToStr(55) + ' WHERE Projectnumber=504 AND LineNumber=1;');
    QuotesZQuery.SQL.Add('UPDATE LineItemTable SET DesignTime = ' + IntToStr(55) + ' WHERE Projectnumber=505 AND LineNumber=1');
    QuotesZQuery.ExecSQL;

I get this error message:

"Project quoteit.exe raised exception class EZSQLException with message 'SQL Error: 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 'UPDATE LineItemTable SET DesignTime = 55 WHERE Projectnumber=505 AND LineNumber=' at Line 2. Process stopped. Use Step or Run to continue."

The query executes fine if it is only one line.

I have indeed looked in the MySQL manual, but can't find the answer - though I suspect it will turn out to be obvious!

Can anyone help me, please?

Thanks.

humantool · Post by **humantool** » 10.02.2009, 17:43

hi,
a coma is missing at the end of line 2, maybe ?

DarylC · Post by **DarylC** » 10.02.2009, 18:55

I don't think you can execute multiple update statements at once.

Try this
QuotesZQuery.SQL.Clear;
QuotesZQuery.SQL.Add('UPDATE LineItemTable SET DesignTime = ' + IntToStr(55) + ' WHERE Projectnumber=504 AND LineNumber=1;');
QuotesZQuery.ExecSQL;

QuotesZQuery.SQL.Clear;
QuotesZQuery.SQL.Add('UPDATE LineItemTable SET DesignTime = ' + IntToStr(55) + ' WHERE Projectnumber=505 AND LineNumber=1');
QuotesZQuery.ExecSQL;

This way you are executing each statement separately.

Post by **mdaems** » 10.02.2009, 22:57

Also : you could use the TZSQLProcessor component. That one is made for multiple statements.

Something else : you could put ZConnection1.Properties.Add('CLIENT_MULTI_STATEMENTS=1'); in your code (or add the property line at design time.
Normally that should work as well.

Mark

GVP · Post by **GVP** » 11.02.2009, 15:20

Thank you all.

I'm looking into the TZSQLProcessor component, and it seems to be the solution. Thanks again.