Devart Forums

Hi,

Delphi RAD 2010, UniDAC 3.00.0.10, PostgreSQL.

Objective: updating one of the tables in a multi table query (unfortunately PostgreSQL doesn't support updatable views without going through the trouble of creating the view first and defining rules):

Methods: using the UpdateSQL and UpdatingTable properties of TUniQuery.

I have the following TUniQuery:

SELECT DISTINCT
"tTable1"."iTable1ID" AS "iTable1ID",
"tTable1"."bCompleted" AS "bCompleted",
"tTable1"."dDate" AS "dDate",
"tTable2"."sDescription" AS sTable2,
"tTable3"."sDescription" AS sTable3,
"tTable4"."sDescription" AS sTable4
FROM ((("tTable1" INNER JOIN "tTable5" ON "tTable1"."iTable5ID"="tTable5"."iTable5ID") LEFT JOIN "tTable3" ON "tTable1"."iTable3ID"="tTable3"."iTable3ID") INNER JOIN "tTable2" ON "tTable1"."iTable2ID"="tTable2"."iTable2ID") INNER JOIN "tTable4" ON "tTable1"."iTable4ID"="tTable4"."iTable4ID"
WHERE (("bCompleted"=False) AND ("tTable1"."dDate">='20071001') AND ("tTable1"."dDate"<='20101031') AND ("tTable3"."sDescription" Is Null) AND "tTable2"."sDescription" IN ('Debit Request'))
ORDER BY "tTable5"."iTable5ID"

when I set UpdatingTable to "tTable1" I get the error message:

Wrong UpdatingTable - table "tTable1" is unknown. I've tried this with QuoteNames True and False, either way I'm getting the error.


I've also tried using the SQLUpdate property of the TUniQuery to:

UPDATE "tTable1"
SET "dDate" = :dDate, "bCompleted" = :bCompleted
WHERE "iTable1ID" = :iTable1ID

The following code:

with Table1.FieldByName ('bCompleted') do Value := not Value;

doesn't trigger the UpdateSQL statement (I set Debug to True and it doesn't show a debug pop up dialoge) but generates the error:

Field "bCompleted" cannot be modified.

kind regards,
Jan

hello,

If you set the UpdatingTable property in the code check that you've specified the table name correctly (case character, quotes etc), also try to set the UpdatingTable property in Object Inspector.

You should add the UniDACVcl unit to the uses clause of any unit in your project to make the Debug property work.

>Field "bCompleted" cannot be modified.
I couldn't reproduce this problem.
Please send me the script to create the tables and I'll try to reproduce the problem again.

Hi,

Table name is correct. When setting the UpdatingTable in the Object Inspector, the Delphi IDE throws the same error as soon as the dataset is made active.

Debug works OK because the first time the query is executed, it shows the SQL statement. It doesn't show the UpdateSQL statement though when I try to update the field with query.FieldByName so I assume it's not running that UpdateSQL statement but rather tries to update the full query (which results in a PostgreSQL error).

It might be relevant to know that the pull down for UpdatingTable doesn't show anything in the Object Inspector.

Helo,

Please set the TUniQuery.Active property to true and after that check the values in the TUniQuery.UpdatingTable drop down list.

>Field "bCompleted" cannot be modified.
This error may occur when the ReadOnly field property is set to true.

If it doesn't help, please, send me the script to create the tables and a sample project to demonstrate the problem.

Hi,

The drop down list remains empty after setting TUniQuery.Active to true.
However when I remove the brackets ( & ) from the query (see my original post), the drop down list gets properly populated and everything works as expected.

The SQLUpdate statement is now also correctly executed when I update a field.

UniDACs SQL statement parser apparently gets confused by the brackets?

kind regards,
Jan

Hello,

If you create a table without brackets then you should call it without brackets as well.

Hey Alex,

You're funny

Please have a look at the SQL statement in this post.
It is valid (ie. it executes). The tablename does not contain a bracket.
The SQL statement does.

I really think there's an issue with the statement parser in UniDAC.
It might even be that ("tTable1" does not work but ( "tTable1" would.

kind regards,
Jan

Hello,

Thank you for the information.
We have reproduced the problem.
We will notify you as soon as we have any results.

We have fixed this bug. This fix will be included in the next PgDAC build.