ADO and MsAccess Post() problem

[FrankSL]

While trying to update a TZQuery with Post() method the rows don't get updated.
I'm using ado on a MsAccess .mdb database
A Simple test case would be:

Code: Select all

qr := TZQuery.Create(nil);
qr.Connection := Conn;
qr.ReadOnly := false;
qr.SQL.Add('SELECT fldname FROM tbl');
qr.Open();
qr.First();
qr.Edit();
qr.FieldByName('fldname').Value := 'AAA';
qr.Post();

Looking and debugging code (delphi 7) it seems that TZGenericCachedResolver.DefineUpdateColumns doesn't add anything because Metadata.IsWritable(I) returns false...
Thanks!

[mdaems]

Can you find out WHY this Metatdata.IsWritable(i) returns false? Have a look at TZAdoResultSet.Open. I think that's where the flag is set.

Mark

[FrankSL]

At first in TZAdoResultSet.Open() the property Writable is true, then it is already false in TZCachedResultSet.Open, in fact adding this row in TZCachedResultSet.Open:

Code: Select all

Writable := FResultSet.GetMetadata.IsWritable(I);

Writable is already false.
If you can give me some other hints I can inspect again,
Thanks!

[mdaems]

Hi,

Is TZAbstractCachedResultSet.CreateWithColumns used to create the Cached Resultset? As there it seems like there's some incoming resultset being copied over the retrieved resultset. Using the call stack here it may be possible to find out where the information comes from. I think it may be comming from TZAdoDatabaseMetadata where some code is commented out...

Mark

[FrankSL]

I think I've found where the problem could be: the method is TZAdoDatabaseMetadata.GetColumns where there are some commented lines, including the ones that should set the Writable flag.
I don't have the necessary knowledge of the library (and the time to study it) , but if i put this line:

Code: Select all

Result.UpdateBooleanByName('WRITABLE', True);

inside the while loop, Post() starts to work correctly.
If someone may post a corrected patch that would be very appreciated,
Many thanks,
Frank

[mdaems]

Have you tried just removing the comments from these lines?

Code: Select all

  //during resultset open the writable property will be set to false when basecolumnname is null, in this case it is a computed column
  //it is required because in the flags it is shown as writable.
  //        if (Flags and (DBCOLUMNFLAGS_WRITE or DBCOLUMNFLAGS_WRITEUNKNOWN)) = 0 then
  //          Result.UpdateBooleanByName('WRITABLE', False)
  //        else
  //          Result.UpdateNullByName('WRITABLE');

  //        Result.UpdateBooleanByName('DEFINITELYWRITABLE',
  //          (Flags and (DBCOLUMNFLAGS_WRITE) <> 0));
  //        Result.UpdateBooleanByName('READONLY',
  //          (Flags and (DBCOLUMNFLAGS_WRITE or DBCOLUMNFLAGS_WRITEUNKNOWN) = 0));
  //        Result.UpdateBooleanByName('SEARCHABLE',
  //          (Flags and (DBCOLUMNFLAGS_ISLONG) = 0));

When that works it seems to provide a better result than the current one. It also is the way it works for other databases. So if you can confirm uncommenting works that seems the safest solution to me.

Mark

[FrankSL]

Uncommenting the code, along with definition of Flags local variable does not work. In this code:

Code: Select all

if (Flags and (DBCOLUMNFLAGS_WRITE or DBCOLUMNFLAGS_WRITEUNKNOWN)) = 0 then
            Result.UpdateBooleanByName('WRITABLE', False)
          else
            Result.UpdateNullByName('WRITABLE');

it always executes the 'else' part, leaving Writable undefined...
Thanks,
Frank

[mdaems]

So instead of 'Result.UpdateNullByName('WRITABLE');' it should be 'Result.UpdateBooleanByName('WRITABLE', True);' ?
Does this leave the writable status of a field true as long as the 'flags' stuff doesn't become true?

Mark

[FrankSL]

I tried this instead of the if/else block:

Code: Select all

Result.UpdateBooleanByName('WRITABLE',
            (Flags and (DBCOLUMNFLAGS_WRITE or DBCOLUMNFLAGS_WRITEUNKNOWN) <> 0));

The row gets update correctly, but the sql generated is wrong, because it doesn't contain primary key in the WHERE clause.
For example, given this table:
CRATE TABLE test (id INT, txtval VARCHAR(50), PRIMARY KEY(id))
with these records:
INSERT INTO test (id, txtval) VALUES (1, 'aaa')
INSERT INTO test (id, txtval) VALUES (2, 'bbb')
INSERT INTO test (id, txtval) VALUES (3, 'aaa')

And filling a TZQuery object with this statement:
SELECT txtval FROM test

If I try to modify the first row the generated sql is:
UPDATE test SET txtval = 'aaaMOD' WHERE txtval = 'aaa'
that updates two rows instead of one...

Thanks,
Frank

[mdaems]

Committed this change to SVN (Rev. 352). If you use testing branch : please test!

Concerning your 'little' problem : this isn't (considered as) a bug. It's just impossible for zeoslib to get enough information without changing the query. You can solve this by just adding the primary key column to your select clause.
Concerning this subject another thread is active at the moment. Have a look here : http://zeos.firmos.at/viewtopic.php?t=1757

Mark

[FrankSL]

Thanks, I'll check out the testing branch and use it during my conversions from delphi components to zeos.
About the key problems, having some experience with data access layers ut no experience with zeoslib I can't say much, but as there is a metadata access layer I would use it to always recover primary key data, to allow updates, etc. I think this is what delphi components do.
Thanks,
Frank

[mdaems]

I'm afraid it isn't that simple as we would have to change the query send to the server to include the PK. Even if this is possible (maybe even easy), we would have to hide it from the user on display and in the dataset exposed to code but then use it again to do the updates. I suppose such things can be done, but this is a complete new feature we need a dedicated volunteer for.

Waiting until somebody shows up, I propose we'll live with the current situation and maybe the patch Sandeep might provide.

Mark