ZQUERY sent sql statement

[k1attila12]

Hi !


Before ZQUERY send a delete, update , insert SQL statement to the server (with .POST, .DELETE etc)
How can i get, read the composed SQL query (as a string) before ZQUERY send to the server ?

Thank you Attila

OS : Win10
SQL server : Firebird 2.5

[marsupilami]

Hmmm - did you register with anoter userame laready? The anwer is to use a TZUpdateSQL object. There you can specify your own SQL for updates, deletes and what not. There is a chapter about this in the Zeos Documentation collection

[k1attila12]

Sorry, YES
Because i have forgotten my password, and i can't find any way to come back to the forum again.
Thnak you for your help.

But i have an another question.

I have a Firebird 2.5 table with some fileds.
Some fileds nullanle , some not.

Firstly i delete only one record with ZQuery.Delete ---> everything is OK
Right after this i delete an anther row --> 0 record(s) updated. Only on record should have benn updated.

The difference between the 2 row :

1. row : every field was filled
2. row : nullable filed weren't filled

First delete make a SQL statement
The second delete want to use the first statement with different values, but not works because of the nullable field s are missing. -->> we need another statement.

The solution was : ZQueryAfterdelete()
begin
dataSet.refresh; <----force to make a new SQL statement
end;
You can see it well with ZSQLMonitor.

Thank you Attila
And sorry again for new username

[Fr0sT]

I'd say it is bug.

Code: Select all

TZGenericCachedResolver.PostUpdates
...
    utDeleted:
      begin
        if DeleteStatement = nil then
        begin
          SQL := FormDeleteStatement(FDeleteParams, OldRowAccessor);
          If Assigned(DeleteStatement) and (SQL <> DeleteStatement.GetSQL) then // <=== this NEVER happens
            DeleteStatement := nil;
          If not Assigned(DeleteStatement) then
            DeleteStatement := CreateResolverStatement(SQL);
          Statement := DeleteStatement;
        end
        else
        begin
          Statement := DeleteStatement;
          SQL := DeleteStatement.GetSQL;
        end;
        SQLParams := FDeleteParams;
      end;

The whole logic of unconditional caching statements for INSERT and DELETE seems quite dangerous for me. UPDATE at least always generates SQL and only then searches for cached statement.

[EgonHugeist]

@Attila

welcome back
Seems you miss a primarykey ... is it the case?

@Fr0sT

the issue with the unreachable code is true. This needs to be fixed.
Also caching the stmts in a "whereall" mode makes no sense or we need a list of delete stmts like i did it for the update-sttmt. The caching is made by me. So i'm sorry for this inconsitency. I would never ever update/delete something without a primary key. In a autocommit mode you can't rollback a update/delete which accidential affected more than one row. The more zeos can't handle this case. IMHO It's not a good design.

So caching the stmts is NOT dangerous with a primary key but without .. i've to agree. In contrarary it would be nice having a more effective way for the update cached stmts instead of precomposing the sql and compare the hashes.

Can you fix it Fr0sT?
What are your objections according the cached insert?

Cheers Michael

[k1attila12]

Hi

No i have primary key, but i use

WhereMode = wmWhereAll (with all fields ) (with wmWhereKeyOnly everything work fine)

because i would like to detect all changes in a row

for example :

there is a table : country

id -- int not null primarykey
name -- varchar(30) not null
state -- char(10) ----------- but nullable

record 1 : 1,'Germany','XXX'
record 2 : 2,'Hungary',NULL


first statement, first delete (ZQUERY made it):

delete from country where (id = 12) and (name = 'Germany') and (state = 'XXX')

Next delete where error occured :

delete from country where (id = 12) and (name = 'Germany') and (state = NULL) <--- this doesn't work because of NULL field

we would need this statement :

delete from country where (id = 12) and (name = 'Germany') and (state IS NULL)

so we have to force the ZQUERY to make a new statement after every delete :

Afterdelete(....)
dataSet.Refresh;
End;


I hope you can understand from it.
It is only problem for DELETE.

Thank you Attila

[Fr0sT]

Michael, you're right in that the issue won't happen in WherePK mode, but as long as other fields are involved, we get problems as we see in Attila's case. WhereAll mode even if PK available could be used for, say, controlling whether somebody has already modified the record - with WhereAll our client only touches a record that has unchanged values or it gets error about 0 records touched. This case is quite common IMHO.
So I think the scheme "generate SQL - compare it with cached stmt - use cached if equal" is absolutely obligatory.
But there are questions:
1) should we clear params list first?
2) should we use hash table or just one cached stmt?
3) and if hash table is used, what limit is has? In big software it could contain hundreds objects...

@k1attila12, to fix the issue for now you can modify sources:

Code: Select all

    utDeleted:
      begin
DeleteStatement := nil;
//        if DeleteStatement = nil then
//        begin
          SQL := FormDeleteStatement(FDeleteParams, OldRowAccessor);
//          If Assigned(DeleteStatement) and (SQL <> DeleteStatement.GetSQL) then
//            DeleteStatement := nil;
          If not Assigned(DeleteStatement) then
            DeleteStatement := CreateResolverStatement(SQL);
          Statement := DeleteStatement;
//        end
//        else
//        begin
//          Statement := DeleteStatement;
//          SQL := DeleteStatement.GetSQL;
//        end;
        SQLParams := FDeleteParams;
      end;

this should do the trick

[k1attila12]

Thank you FrOst, i will try it.