Zeoslib with DataSnap

[lams]

Hi

I am using latest Zeoslib with D6 SP1 and Firebird 2.1. I am try to convert IBX to ZeosLib. But I find ZeosLib not working with DataSnap quite well.

I use ZQuery <-> DataProvider <-> ClientDataSet for update/insert etc. In the ZQuery, my query looks like this:

Code: Select all

SELECT MODULE_TASKS.*, P.THUMBNAIL
FROM MODULE_TASKS 
Left Join PageList P on  P.PAGEID = MODULE_TASKS.PAGEID 
WHERE MODULE_TASKS.COURSEID=:COURSEID 
AND MODULE_TASKS.MODULEID=:MODULEID

And on the ProviderGEtTableName event, I add follow line:

Code: Select all

TableName := 'MODULE_TASKS';

It tell how DataSnap to generate the correct sql base on the table name.
It works fine under IBX, but with ZeosLib, I get follow error:

And here is error trace:

ExceptHandler(EZSQLException: Cannot update a complex query with more then one table, 00519F11) [00519F11] ZDbcGenericResolver.TZGenericCachedResolver.DefineTableName (Line 289, "ZDbcGenericResolver.pas")
ExceptStackList-----------------------------------------------------------------
ZDbcGenericResolver.TZGenericCachedResolver.DefineTableName (Line 288, "ZDbcGenericResolver.pas")
ZDbcGenericResolver.TZGenericCachedResolver.FormInsertStatement (Line 630, "ZDbcGenericResolver.pas")
ZDbcGenericResolver.TZGenericCachedResolver.PostUpdates (Line 757, "ZDbcGenericResolver.pas")
ZDbcCachedResultSet.TZAbstractCachedResultSet.PostRowUpdates (Line 434, "ZDbcCachedResultSet.pas")
ZDbcCachedResultSet.TZAbstractCachedResultSet.PostUpdates (Line 540, "ZDbcCachedResultSet.pas")
ZDbcCachedResultSet.TZAbstractCachedResultSet.InsertRow (Line 1514, "ZDbcCachedResultSet.pas")
ZAbstractDataset.TZAbstractDataset.InternalAddRecord (Line 414, "ZAbstractDataset.pas")
ZAbstractDataset.TZAbstractDataset.InternalPost (Line 464, "ZAbstractDataset.pas")

It seems like ZeosLib just igore the TableName setting. How can I handle this under ZeosLib?

Cheers,

Tao

[mdaems]

Hi,

I'm not sure but normally you shouldn't handle this using the Provider events, I think.
If the query was easier Zeoslib should handle this by automatically. In case of a complex query like this one, you need a TZUpdateSQL component linked to your query. (works more or less the same way as the Delphi TUpdateSql component if you need some docs)

To be honest : this may also give trouble as there's bug #100 which is about updates using a provider.

[lams]

Hi Mdaems,

Thanks for your help again.

Yes, once I use a ZUpdateSQL with ZQuery, everything works fine.

But I just wondering, why ZQuery always need to check the TableName before PostUpdates, as DataSnap can create update query. Is it possible disable this feature by such as Disable/Enable property?

By the way, where is the bug #100, I cannot see the bug tracker in both forum and SourceForge website.


Cheers,

Tao

[mdaems]

We have a Mantis bug tracker at zeosbugs.firmos.at (or in the "links->zeoslib places" section on this forum). Registration required. Please use same username as on the forum.

Your question : I guess it's build in TDataset or TProvider bahaviour to check the name of the table containing a field that must be updated. Zeoslib ZQuery is just an extension of a TDataset.

Mark

[lams]

Yes, it's the DataProvider who suppose to generate the update/insert sql statement base on the TableName. But it seems like the TZGenericCachedResolver.DefineTableName is call to check the TableName before TDataProvider try to generate the SQL statement for the TZQuery. I believe this is good feature which IBX don't have. But could I disable this feature and let DataProvide totally control TZQuery without the TZUpdateSQL?

Cheers,

Tao

[mdaems]

That's not completely correct. The DataProvider is effectively setting the values in the ZQuery component instead of generating the update query himself. After setting the values in the dataset (=ZQuery), the ZQuery.post method is called by the provider. Which fires the Genericresolver coding. As zeoslib is not smart enough to generate update queries for complex joins, you get this error. This is what you remedy by adding the TZUpdateSQL component.
So, to disable this behaviour you should look how you can force the TDatasetprovider to write all the queries himself. Did you find this :

Delphi syntax:

property ResolveToDataSet: Boolean;

C++ syntax:

__property bool ResolveToDataSet = {read=FResolveToDataSet, write=SetResolveToDataset, default=false};

Description

Set ResolveToDataSet to specify how updates are applied. When ResolveToDataSet is true, the Resolver property is set to a TDataSetResolver component, which applies updates directly to the dataset specified by the DataSet property. This can be useful if the application uses the events on the dataset component or if the dataset does not represent the data from a database server (for example, a client dataset).

When ResolveToDataSet is false, the Resolver property is set to a TSQLResolver component, which applies updates directly to the database server associated with DataSet. This can be more efficient, because it skips the intermediate step of using the dataset. It is the only way to resolve updates if the source dataset is read-only (for example, a unidirectional dataset).

Mark

[lams]

Hi

If you have a look the error trace again:

2008-08-29-10:10:38 2008-08-29-10:10:38 ExceptHandler(EZSQLException: Cannot update a complex query with more then one table, 00521CAD) [00521CAD] ZDbcGenericResolver.TZGenericCachedResolver.DefineTableName (Line 289, "ZDbcGenericResolver.pas")
2008-08-29-10:10:38 2008-08-29-10:10:38 ExceptStackList-----------------------------------------------------------------
ZDbcGenericResolver.TZGenericCachedResolver.DefineTableName (Line 288, "ZDbcGenericResolver.pas")
ZDbcGenericResolver.TZGenericCachedResolver.FormInsertStatement (Line 630, "ZDbcGenericResolver.pas")
ZDbcGenericResolver.TZGenericCachedResolver.PostUpdates (Line 757, "ZDbcGenericResolver.pas")
ZDbcCachedResultSet.TZAbstractCachedResultSet.PostRowUpdates (Line 434, "ZDbcCachedResultSet.pas")
ZDbcCachedResultSet.TZAbstractCachedResultSet.PostUpdates (Line 540, "ZDbcCachedResultSet.pas")
ZDbcCachedResultSet.TZAbstractCachedResultSet.InsertRow (Line 1514, "ZDbcCachedResultSet.pas")
ZAbstractDataset.TZAbstractDataset.InternalAddRecord (Line 414, "ZAbstractDataset.pas")
ZAbstractDataset.TZAbstractDataset.InternalPost (Line 464, "ZAbstractDataset.pas")
DB.TDataSet.CheckOperation (Line 10371, "db.pas")
DB.TDataSet.Post (Line 10228, "db.pas")
ZAbstractDataset.TZAbstractDataset.PSUpdateRecord (Line 788, "ZAbstractDataset.pas")
Provider.TSQLResolver.InternalDoUpdate (Line 3648, "Provider.pas")
Provider.TSQLResolver.DoInsert (Line 3676, "Provider.pas")
Provider.TCustomResolver.InternalUpdateRecord (Line 3241, "Provider.pas")
Provider.TUpdateTree.DoUpdates (Line 3071, "Provider.pas")
Provider.TCustomResolver.ApplyUpdates (Line 3299, "Provider.pas")
Provider.TBaseProvider.InternalApplyUpdates (Line 2250, "Provider.pas")
Provider.TDataSetProvider.InternalApplyUpdates (Line 2656, "Provider.pas")
Provider.TCustomProvider.ApplyUpdates (Line 2092, "Provider.pas")

The except occur when PSUpdateRecord been called in side Provider.TSQLResolver.InternalDoUpdate function:

Code: Select all

procedure TSQLResolver.InternalDoUpdate(Tree: TUpdateTree; UpdateKind: TUpdateKind);
var
  Alias: string;
begin
  if not IProviderSupport(Tree.Source).PSUpdateRecord(UpdateKind, Tree.Delta) then
  begin
    if (PSQLInfo(Tree.Data)^.QuotedTable = '') and not Tree.IsNested then
      DatabaseError(SNoTableName);
    if PSQLInfo(Tree.Data)^.HasObjects then Alias := DefAlias else Alias := '';
    FSQL.Clear;
    FParams.Clear;
    case UpdateKind of
      ukModify: GenUpdateSQL(Tree, FSQL, FParams, Alias);
      ukInsert: GenInsertSQL(Tree, FSQL, FParams);
      ukDelete: GenDeleteSQL(Tree, FSQL, FParams, Alias);
    end;
    DoExecSQL(FSQL, FParams);
  end;
end;

In ZeosLib, TZAbstractDataset.PSUpdateRecord try to solve the tablename and form the insert Statement for the ZQuery - as I said, this is a good thing. In IBX, it just do nothing if there has no FOnUpdateReord event and FUpdateObject:

Code: Select all

function TIBCustomDataSet.PSUpdateRecord(UpdateKind: TUpdateKind; Delta: TDataSet): Boolean;
...
begin
  Result := False;
  if Assigned(OnUpdateRecord) then
  begin
    UpdateAction := uaFail;
    if Assigned(FOnUpdateRecord) then
    begin
      FOnUpdateRecord(Delta, UpdateKind, UpdateAction);
      Result := UpdateAction = uaApplied;
    end;
  end
  else if Assigned(FUpdateObject) then
  begin
    SQL := FUpdateObject.GetSQL(UpdateKind).Text;
    if SQL <> '' then
    begin
      Params := TParams.Create;
      try
        Params.ParseSQL(SQL, True);
        AssignParams(Delta, Params);
        if PSExecuteStatement(SQL, Params) = 0 then
          IBError(ibxeNoRecordsAffected, [nil]);
        Result := True;
      finally
        Params.Free;
      end;
    end;
  end;
end;

Then let procedure TSQLResolver.GenInsertSQL generate the Sql statement.

I don't regard this as a ZeosLib's bug. As you suggest, adding a TZUpdateSQL can solve this problem. I just wondering how to make ZQuery working with TProvider better.

Cheers,

Tao

[mdaems]

Could you find a way to let TZAbstractDataset.PSUpdateRecord return false when the resolving doesn't succeed? Like 'If error resolving then undo all and return false'. That might be the ideal solution to the problem.

Mark