Page 1 of 1
Issue with RefreshCurrentRow with no primary key.
Posted: 03.06.2021, 18:31
by MJFShark
NOTE: The issues pointed out below all correspond to tables that don't have a primary key. So this is a bit of a special case, but it would be nice to fix it. See my note below on the RefreshResultSet statement caching.
I noticed when calling RefreshCurrentRow on a query with no primary key and some null values that I got an "number of bind variables doesn't match" type error from Oracle. It seemed that it was binding parameters in the where clause that had been changed to " is null" (and so were no longer meant to be bound.) I may have fixed it with the following change.
It appears that the following line:
Code: Select all
RowAccessor.FillStatement(Stmt, FWhereColumns, Metadata);
Should be:
Code: Select all
RowAccessor.FillStatement(Stmt, FCurrentWhereColumns, Metadata);
In the TZGenerateSQLCachedResolver.RefreshCurrentRow method.
-- Update.
The above does fix things initially, however currently this method caches the RefreshResultSet statement and this statement needs to change if there is no primary key and there are any nulls in the current record. Basically if there's no primary key than I think the refresh statement has to be initialized every time.
-Mark
Re: Issue with RefreshCurrentRow with no primary key.
Posted: 03.06.2021, 19:21
by MJFShark
To take care of the caching problem I ended up adding "or WhereAll" to the test for InitStmt.
Code: Select all
if (RefreshResultSet = nil) or (RefreshResultSet.GetStatement = nil) or RefreshResultSet.GetStatement.IsClosed or WhereAll
then InitStmt(Stmt)
else RefreshResultSet.GetStatement.QueryInterface(IZPreparedStatement, Stmt);
-Mark
Re: Issue with RefreshCurrentRow with no primary key.
Posted: 03.06.2021, 20:27
by aehimself
Completely offtopic. We have a method called "RefreshCurrentRow"? :O
I have a tool which checks and highlights changes in a record as time elapses. I called Dataset.Refresh but (especially at large resultsets) this can be time consuming.
Seems you used it already, does it worth for me to look into it? Is it a significant improvement?
Re: Issue with RefreshCurrentRow with no primary key.
Posted: 04.06.2021, 15:21
by MJFShark
In my case I use RefreshCurrentRow after a post to reread the values in case they changed (triggers and such.) In my testing it with Oracle I ran into a lot of errors that seem to be related to the refresh statement being cached. The only way that I was able to fix it completely (so far) was to remove the caching completely and initialize the stmt each time RefreshCurrentRow is called, which seems sub-optimal.
-Mark
Re: Issue with RefreshCurrentRow with no primary key.
Posted: 04.06.2021, 15:36
by marsupilami
aehimself wrote: ↑03.06.2021, 20:27
Completely offtopic. We have a method called "RefreshCurrentRow"? :O
We do. We have this method like forever. But the generic resolver never implemented it. The only way to use it was TZUpdateSQL. With Zeos 8 this changed.
aehimself wrote: ↑03.06.2021, 20:27
Seems you used it already, does it worth for me to look into it? Is it a significant improvement?
It really depends on your use case. It will refresh the current row and no other rows. As far as I know, Egonhugeist implemented it for all drivers.
Re: Issue with RefreshCurrentRow with no primary key.
Posted: 05.06.2021, 17:39
by MJFShark
Here's what I ended up with. This just adds two checks for "WhereAll" since if that's true the refresh statement could change each time if there are nulls involved. This retains the refresh statement caching if there's a primary key.
Body of TZGenerateSQLCachedResolver.RefreshCurrentRow
Code: Select all
if (RefreshResultSet = nil) or (RefreshResultSet.GetStatement = nil) or RefreshResultSet.GetStatement.IsClosed or WhereAll
then InitStmt(Stmt)
else RefreshResultSet.GetStatement.QueryInterface(IZPreparedStatement, Stmt);
if Stmt = nil then
raise EZSQLException.Create(SUpdateSQLNoResult)
else begin
if WhereAll then
RowAccessor.FillStatement(Stmt, FCurrentWhereColumns, Metadata)
else
RowAccessor.FillStatement(Stmt, FWhereColumns, Metadata);
RefreshResultSet := Stmt.ExecuteQueryPrepared;
if (RefreshResultSet = nil) or not RefreshResultSet.Next then
raise EZSQLException.Create(SUpdateSQLNoResult);
RowAccessor.FillFromFromResultSet(RefreshResultSet, FInsertColumns);
RefreshResultSet.ResetCursor; //unlock handles
end;
-Mark
Re: Issue with RefreshCurrentRow with no primary key.
Posted: 07.06.2021, 13:27
by marsupilami
Hello Mark,
I applied your changes to the SVN. They will be copied to git tonight.
Best regards,
Jan
Re: Issue with RefreshCurrentRow with no primary key.
Posted: 07.06.2021, 17:21
by MJFShark
Hi Jan,
Thanks!
-Mark