Issue with RefreshCurrentRow with no primary key.

[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

[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

[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?

[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

[marsupilami]

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.

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.

[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

[marsupilami]

Hello Mark,

I applied your changes to the SVN. They will be copied to git tonight.

Best regards,

Jan

[MJFShark]

Hi Jan,

Thanks!

-Mark