Hi Jason

StatementType is stUnknown before calling execute and stInsert after, RowsAffected is still 0.

Problem solved if I add a call to Prepare before execute, StatementType is stInsert and I get the correct number returned in RowsAffected.

Could calling TIB_DSQL1.Execute with different statements, prior to this call, be causing this? Do I need to call Prepare on before each call to Execute?

Thanks,
Sean.

--- In IBObjects@yahoogroups.com, "Jason Wharton" <supportlist@...> wrote:
>
> 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
> >
>