[solved] PostgreSQL : Can not update this query type

[WorldWalker]

Hello,

Sorry for writing here as the old one is locked!

At last I found the reason, as my code was executed at run time I spent several hours to extract the SQL code and I discovered that something maybe strange: if I execute

Code: Select all

SELECT * FROM test 

the query is executed normally but if the table name is capitalized

Code: Select all

SELECT * FROM Test 

the query is opened as read only and forcing the edit will raise the exception plz find attached two projects runtime and designtime.
After executing the script creates on psql window, you will see how the code in runtime behaves with table name Test.
compile/run the project after specifying username, password and setting connected to true.

Tested with ZeosLib Stable 7.0.3
Best Regards.

[EgonHugeist]

WorldWalker,

Sorry for writing here as the old one is locked!

My fault ((: Sometimes we've to refresh the threads...

I'll check your demo later, because now i slightly understand what's going wrong!

PostgrSQL is casesensitive. Zeos uses MetaInformations to determine which fields are readonly, have default values etc. If you've a wrong typo like 'Test' instead of 'test' then the table could not be found in the MetaInformations (because Zeos quotes the name now) and zeos uses the ResultSet-MetaInformations instead. But these do not contain informations about ReadOnly etc. Just the types, lenghts and names not more.

The generic-resolver which assambles the statments starts from the premisse we can't update any column or the "QueryType" is invalid and raises this exception.

If you would quote the the table name too, then you won't get any result or you got an exception from Postgre.

Try:

Code: Select all

select * from "test";

This should open the table successfully.

Code: Select all

select * from "Test";

This should raise an exception.

Am i right?

[WorldWalker]

Am i right?

Yes, that's true.

Best regards.

[EgonHugeist]

WorldWalker,

yep, and this is the reason why you got the exception and all fields are readonly.

Case closed?

[WorldWalker]

EgonHugeist,
I recieve the exception Table "Test" not found, that's normal, so there is no fields within the query as it was not opened.
In the example I sent you I can open the query in designtime sample as read-only and in the runtime I can open it for edit, but I recieve the exception "Cannot update this query type"

Edit:
And if I set the proprety UpdateSQL of the query to a TZUpdateSQL component the exception "Cannot update this query type" disappears and the edited record is saved properly.
MFG.

[EgonHugeist]

WorldWalker,

ok, ill have a look tonight..

[EgonHugeist]

WorldWalker,

Me again. I've tested both attached demos. The designtime and runtime differences a clear for me. It happens because off the additional functionality of our property editors.

What happpens:

The PropertyEditors do extract the Table-Name from your statement, checks if quoting is required (wich is if your're using mixed cases) and quotes the table-name. Now we've the exact name and Zeos trys to read the metainformations, which is not successfully. If nothing is retrieved then the ResultSet-MetaInformations where used. All field-names & types are available but zeos can't determine whether the field is readonly or not so all fields are readonly.

I made some little tests to get an equal behavior but this breaks the current way we go to much.

The whole behavior chages if you assign the TZUpdateSQL component because now you as developer have to take care everything is right. Zeos only splits the statements and checks for parameters starting with the ParamChar. Now Zeos knows how to update the table, which fields are writable and your task is to tell zeos "how to".

Well i know this is a little bit confusing, but changeing this codebase would broke a loads of apps for others. Do you agree?

[WorldWalker]

Hello,
Thank you for this detailed explanation, anyway working with lowercase statements in the runtime executed queries is simple and could be easily applied as solution in end-user application, thus it's a fix without a need to change a whole codebase behavior in ZeosLib,
Thank you for your time,
Best Regards.