Query.RowsAffected is always -1

[matf]

Hi.

I'm using ZEOS to connect to Firebird 1.5.
I'm Executing a Query and want to get the Affected Rows using Query.RowsAffected.
But the result is always -1

Why ?

[gto]

Hello there

The RowsAffected property only have a value if you are inserting, deleting or updating rows. The select statement always resullts in -1 value.

By the way, I've only tested with ExecSQL method, hope the .open (and active := true) works too, but always with a non select statement.

[matf]

No that is wrong.

Before I switched my Project to Firebird I used MySQL. I just changed the protocol string in the Zeos Connection Component.
When I was using MySQL the RowsAffected returned the right count of Rows of the SELECT Statement.
The -1 only appears in Firebird.

[btrewern]

What value do you expect RowsAffected to return for a SELECT statement? Arn't you looking for RowCount?

Ben

[matf]

My Query Component doesn't have a property named RowCount.
I just can find RowsAffected.

And this worked using MySQL.

[gto]

hmm, I think btrewern mean the RecordCount property

about the RowsAffected, testing here with firebird it's exactly what happens.
into the deep of zeos, the RowsAffected property comes from an interface, reading the value of FRowsAffected which is set by:

FRowsAffected := Statement.ExecuteUpdatePrepared;

into the ExecSQL procedure (ZAbstractRODataset.pas). I think beyond that, everything becomes database dependant.

In ZDbcInterbase6Statement is:
Result := GetAffectedRows(GetPlainDriver, StmtHandle, StatementType);

the GetAffectedRows comes directly from driver (ZDbcInterbase6Utils.pas) and the total transcription is here:

Code: Select all

function GetAffectedRows(PlainDriver: IZInterbasePlainDriver;
  StmtHandle: TISC_STMT_HANDLE; StatementType: TZIbSqlStatementType): Integer;
var
  ReqInfo: Char;
  OutBuffer: array[0..255] of Char;
  StatusVector: TARRAY_ISC_STATUS;
begin
  Result := -1;
  ReqInfo := Char(isc_info_sql_records);

  if PlainDriver.isc_dsql_sql_info(@StatusVector, @StmtHandle, 1,
    @ReqInfo, SizeOf(OutBuffer), OutBuffer) > 0 then
    Exit;
  CheckInterbase6Error(PlainDriver, StatusVector);
  if OutBuffer[0] = Char(isc_info_sql_records) then
  begin
    case StatementType of
      stUpdate: Result := PlainDriver.isc_vax_integer(@OutBuffer[6], 4);
      stDelete: Result := PlainDriver.isc_vax_integer(@OutBuffer[13], 4);
      stInsert: Result := PlainDriver.isc_vax_integer(@OutBuffer[27], 4);
      else Result := -1;
    end;
  end;
end;

As you may see, there's a case at the final of function, which returns values from driver is the StatementType if an update, delete or insertion one. otherwise, the result is always -1

[btrewern]

Yea sorry, RecordCount.

Ben

[matf]

But why did it work wiht MySQL ?

And how do I get the number of resultsets of an SELECT Statement ?

[zippo]

Query.RecNo = current row
Query.RecordCount = all rows.

But you can also do it by SQL: Select Count(*) From TABLE Where ...

[matf]

Yes. That works. Thank you !

[anse123]

DBMS: MySQL
Zeos: 6.5.1 Alpha

The RowsAffected property seems only to be updated when an application sends a query with ExecSQL(). It is not updated when updates, deletes or inserts are done by using a dbgrid. In this case i find that RowsAffected is always 0 in an AfterPost-event from a TZQuery.

Hmm..

It's kind of hard to find out the difference between a ExecSQL() call and a Post from within a dbgrid. The Post-events should also call ExecSQL shouldn't they? However, they do not and therfore we have a different behaviour in both methods.

I thought I found a workaround for this problem: In an AfterPost-event, I called a second query "SELECT ROW_COUNT()". That returns the same as the API-method mysql_affected_rows() would return. Works like a charm.... but only in MySQL 5.0.1 and up! In order to keep the compatibility, this turned to be a bad solution.

So, the question: How can I retrieve the count of affected rows after posting an update (or delete or insert) through a data aware component like a DBGrid?

[LuisRodrigues]

i think this is a firebird issue.


read Firebird documentation, i think that is impossible to Firebird arquitecture returns RowsAffected..

[anse123]

no, sorry this is a MySQL issue...

We fixed it by adding a new method: GetAffectedRowsFromLastPost
http://www.heidisql.com/forum/viewtopic ... 8dabbc2768