Need help with SQLProcessor

jendrula · Post by **jendrula** » 10.01.2007, 09:36

Hello,
Delphi 2005, ZEOSDBO-6.6.0-beta, PostgreSQL 8

I'm until now use zeoslib 615 and often use SQLProcessor, after upgrade to 6.6 SQLProcessor stop working.

e.g.

SQLProcessor.script.text:='update tbl_test set nazwa=now() where number=1;
SQLProcessor.execute;

and no working ...

What is wrong?

I need fast help or rollback too 615

Jendrula

btrewern · Post by **btrewern** » 10.01.2007, 10:15

Just use a TZQuery as:

Code: Select all

With ZQuery1 do
begin
  SQL.Text := 'update tbl_test set nazwa=now() where number=1';
  ExecSQL;
end;

Regards,

Ben

jendrula · Post by **jendrula** » 10.01.2007, 10:27

Yes, its solution but i use SQLProcessor in many programs .... hard work to change ... time to rollback.

Thx for answer.

Jendrula

Post by **mdaems** » 10.01.2007, 13:19

OK, we understand that, but exactly what is going wrong? Error messages? No errors but not committed, ...?

Mark

jendrula · Post by **jendrula** » 10.01.2007, 13:37

After execute nothing happen. No erros, no events after and before execute (i set) also in SQLMonitor i can see only commands form other components .

(Operations with other components works well: select, update, delete. Only SQLProcessor looks as dummy )

Jendrula

Post by **mdaems** » 10.01.2007, 14:29

Oh...

When looking at your query I see the statement may not end in a ';'. Have you tried this :

Code: Select all

SQLProcessor.script.text:='update tbl_test set nazwa=now() where number=1;';
SQLProcessor.execute;

I'm not very sure, but I think the component looks for finished statements, and the default delimiter is a ';'.
If you can take some time to debug TZSQLScriptParser.ParseText() function you may find out if this is the reason.

Mark

jendrula · Post by **jendrula** » 11.01.2007, 07:35

You have right , with delimiter works fine. Thx

Jendrula

ste_ba · Post by **ste_ba** » 15.06.2007, 22:28

I experienced the same phenomenon. It works just as well if you have specified a different delimiter type, e.g. if you chose dtEmptyLine, just add an empty line to your script. However, it is understandable if one worries about their Zeos 6.5 apps not working properly anymore, since this behaviour obviously started with the 6.6.1 Beta release - before, it would just execute the script anyway ...

ste_ba

Post by **mdaems** » 16.06.2007, 09:53

@ste_ba,

Are you sure it was between 6.6.0 and 6.6.1? In that case it should be easy to find the 'guilty' change. Fixing 1 problem may have broken the other one. (SVN Trunk commits 154->222) I'm afraid it's longer ago, however.

As I can read the parser logic well, it's written to use only complete, delimited statements. Now it does. It did not before... I'm not sure this effect was desired or not. Probably 'end-of-strings' was seen as the final delimiter.

Can you please post a bug report (eventually with a very small test case) on the bug tracker when you think 'end-of-strings' should be considered as a valid delimiter anyhow?

Mark

ste_ba · Post by **ste_ba** » 18.06.2007, 09:49

mdaems wrote:Are you sure it was between 6.6.0 and 6.6.1?

That might have been a bit forejudged.

mdaems wrote:As I can read the parser logic well, it's written to use only complete, delimited statements. Now it does. It did not before... I'm not sure this effect was desired or not. Probably 'end-of-strings' was seen as the final delimiter.

I think so. Anyhow, it cannot be desired behaviour for a parser to just twiddle its thumbs without any error message when confronted with a non-delimited statement, can it? The only case I could think of would be executing an incomplete statement and planning to send the rest of it later on. But why should one execute then, why not wait and add the rest of the statement to the stringlist once it's ready?

mdaems wrote:Can you please post a bug report (eventually with a very small test case) on the bug tracker when you think 'end-of-strings' should be considered as a valid delimiter anyhow?

OK.

ste_ba

Post by **mdaems** » 31.07.2007, 00:41

Hi ste_ba,

I finally came to looking into this.
Actually : the parser itself does split into different statements and an 'uncomplete' part. That's the way it was designed.
However the SqlProcessor is clearly written to allow The uncomplete part to be executed. Please check and tell me what you think. Was the code intended to be patched like this?

Code: Select all

Index: D:/Data/Delphi/ZEOSTesting/src/component/ZSqlProcessor.pas
===================================================================
--- D:/Data/Delphi/ZEOSTesting/src/component/ZSqlProcessor.pas	(revision 269)
+++ D:/Data/Delphi/ZEOSTesting/src/component/ZSqlProcessor.pas	(working copy)
@@ -370,13 +370,13 @@
     SQL.MultiStatements := False;
     Parse;
 
-    for I := 0 to Pred(FScriptParser.StatementCount) do
+    for I := 0 to Pred(StatementCount) do
     begin
       Action := eaSkip;
       DoBeforeExecute(I);
       repeat
         try
-          SQL.Text := FScriptParser.Statements[I];
+          SQL.Text := GetStatement(I);
           Statement := CreateStatement(SQL.Statements[0].SQL, nil);
           SetStatementParams(Statement, SQL.Statements[0].ParamNamesArray,
             FParams);

Look at the interpretation given to the StatementCount and GetStatement functions of TZSqlProcessor.
It fixes the problem, I think.