Subject Re: [IBO] TIB_DSQL1.RowsAffected returns 0 when using a MERGE statement
Author Jason Wharton
I'm not sure what the problem is since IBO gets this value directly from the
API.
It's likely the statement type is not being parsed property.
What is the StatementType?

Jason

----- Original Message -----
From: "Sean" <palmers@...>
To: <IBObjects@yahoogroups.com>
Sent: Wednesday, November 10, 2010 8:00 AM
Subject: [IBO] TIB_DSQL1.RowsAffected returns 0 when using a MERGE statement


> Hi,
>
> I'm using the following code to execute a MERGE statement, but
> RowsAffected always returns 0, if I execute the same statement in IBExpert
> I get 602 rows are affected.
>
> TIB_DSQL1.SQL.Clear;
> TIB_DSQL1.SQL.Add('MERGE INTO lcc_ForecastOperatingCosts fom ');
> TIB_DSQL1.SQL.Add('using (SELECT TheYear, AssetID, RunID, Element,
> NominalCost FROM F_OperationalMaintenance('+IntToStr(RunID)+')) om ');
> TIB_DSQL1.SQL.Add('on (fom.assetid = om.AssetID ');
> TIB_DSQL1.SQL.Add('and fom.RunID = om.RunID ');
> TIB_DSQL1.SQL.Add('and fom.Element = om.Element ');
> TIB_DSQL1.SQL.Add('and fom.TheYear = om.TheYear) ');
> TIB_DSQL1.SQL.Add('when matched then update set fom.nominalcost =
> om.nominalcost ');
> TIB_DSQL1.SQL.Add('when not matched then insert (TheYear, AssetID, RunID,
> Element, NominalCost) values (om.TheYear, om.AssetID, om.RunID,
> om.Element, om.NominalCost) ');
> TIB_DSQL1.Execute;
> RecordsIns := RecordsIns + TIB_DSQL1.RowsAffected;
>
> I'm using IBO 4.9.9, Delphi 2010 and Firebird 2.1.3
>
> Any help greatfully rreceived.
>
> Thanks in advance,
> Sean
>