Updatable Query

waheed · Post by **waheed** » 09.03.2007, 06:09

I know that joins in a select makes a query not-Updatable. Does ZEOS knows that and how could I check if it is updatable or not?

btrewern · Post by **btrewern** » 09.03.2007, 10:04

Try the TZQuery.CanModify property.

Regards,

Ben

waheed · Post by **waheed** » 09.03.2007, 13:13

Didn't help. The property was true but one of the fields didn't update. The field is retrieved like this:

cast(author as char) as author

other fields did update.
Come to think about it. My question was wrong. After Monitoring the statements with ZMonitor component I noticed that that only field didn't update.
So...

How could I know if the field is updatable?

Post by **mdaems** » 10.03.2007, 18:27

Hi Waheed,

I think it's not possible the way it's written now. Normally you could use FieldByName('author').CanModify
I've been checking and it may be solved using this patch.

Code: Select all

Index: D:/Data/Delphi/ZEOSTesting/src/dbc/ZDbcResultSetMetadata.pas
===================================================================
--- D:/Data/Delphi/ZEOSTesting/src/dbc/ZDbcResultSetMetadata.pas	(revision 227)
+++ D:/Data/Delphi/ZEOSTesting/src/dbc/ZDbcResultSetMetadata.pas	(working copy)
@@ -543,9 +543,9 @@
 }
 procedure TZAbstractResultSetMetadata.ClearColumn(ColumnInfo: TZColumnInfo);
 begin
-//  ColumnInfo.ReadOnly := True;
-//  ColumnInfo.Writable := False;
-//  ColumnInfo.DefinitelyWritable := False;
+  ColumnInfo.ReadOnly := True;
+  ColumnInfo.Writable := False;
+  ColumnInfo.DefinitelyWritable := False;
   ColumnInfo.CatalogName := '';
   ColumnInfo.SchemaName := '';
   ColumnInfo.TableName := '';

I'm afraid to commit this one... It might break existing programs a I don't know yet how this behaves in combination with updatesql components.

So more testing will be needed. But maybe you are helped for now.

Mark

waheed · Post by **waheed** » 10.03.2007, 18:30

Thanks, I used UpdateSQL which is a better programming method (IMHO).

Post by **mdaems** » 10.03.2007, 18:46

Yes, you would have to use updateSQL anyhow. This patch would only make it possible to check if the field is updatable without updatesql. In a grid these fields become blocked for update. I'm not sure if that can be undone if you want to update anyhow.

I have a question for you. Could you try to apply my patch to your program and test if the updatesql solution still works? I don't know how you modify the fields. I suppose using a grid or some database component? Can you check if you can update this field after applying my patch? Maybe you have to explicitly tell the program they can be changed anyhow because you use an updatesql component. (removing the ReadOnly Attribute from the FieldDef)

Mark

waheed · Post by **waheed** » 10.03.2007, 22:11

I didn't completely understand the last sentence. But I have used on a seperate new application. I used the query with the field as mentioned above "cast(author as char) as author" and the update didn't work. Then I connected the query to UpdateSQL and it worked, as if the changes didn't have any affect.
I used the CanModify on that field to check and Zeos said it is TRUE!

P.S. I didn't know how to use the patch in your message. Could you tell me how? I applied the patch manually by removing the comments from the source and recompiling everything.

Post by **mdaems** » 13.03.2007, 01:52

Hi Waheed,

I just experimented a little with my patch. After applying it I can't update a calculated field in a datagrid anymore. If I add all fields to the query using the IDE and change 'Readonly' to false (default = true because of the patch) i can update again. If I don't add the fields at designtime removing the readonly attribute doesn't change anything and I can't update the field. Changing the field content using coding still works and the updatesql component updates.
So I'm afraid the data grid field locking by my patch breaks compatibility with existing applications that use updatesql to update changed calculated fields to the database. So I don't know what I should do...

The patch I included was intended to be manually applied. It was a SVN diff, but I didn't find out yet how I can cut these from a forum post to apply them. It was a short change so I just left it up to you to do the editing manually instead of generating an attachment file.

Mark