Page 1 of 1
ADO and MsAccess Post() problem
Posted: 21.03.2008, 11:45
by 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!
Posted: 21.03.2008, 13:13
by 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
Posted: 21.03.2008, 15:47
by 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!
Posted: 21.03.2008, 16:23
by 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
Posted: 23.03.2008, 09:48
by 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
Posted: 24.03.2008, 23:49
by 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
Posted: 25.03.2008, 11:32
by 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
Posted: 25.03.2008, 13:47
by 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
Posted: 25.03.2008, 15:25
by 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
Posted: 25.03.2008, 21:56
by 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
Posted: 26.03.2008, 11:59
by 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
Posted: 26.03.2008, 20:35
by 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