Kelly Keller-Heikkila Kelly Keller-Heikkila - 4 months ago 11
PHP Question

db2_num_rows() returns -1

I have a query that uses pagination that worked earlier today, but now is returning a fetch failure. I checked the

db2_num_rows()
and it is giving me -1. When I check the PHP documentation,
db2_num_rows()
should never return a negative value; it should always return a positive value or
false
.

Does anyone have an idea what condition would cause it to return -1? Since
db2_stmt_error()
and
db2_stmt_errorMsg()
both return empty-string, I'm hoping this would help me troubleshoot the root cause of my problem.

Here is a shortened version of my script. The query itself works correctly when I test with the AS400 console using
strsql
.

$minRow = 1;
$maxRow = 14;
$sql = 'SELECT * '
. 'FROM ('
. 'SELECT row_number() OVER (ORDER BY FETYPE) AS ID, '
. 'FETYPE, FECNO, FELC, FEID, FEDESC, FEBLEND, FECALC, '
. 'CAST(FECOST AS VARCHAR(12)) AS FECOST, '
. 'CAST(FEMARK AS VARCHAR(12)) AS FEMARK, '
. 'FEMKTP, '
. 'CAST(FESRNK AS VARCHAR(12)) AS FESRNK, '
. 'FEBSIZ, FEUDT, FEUTM, '
. 'FEMDT, FEUSID, FEINS1, FEMIXINS1, FEMIXINS2, FEMIXINS3, '
. 'FEVER, FEIMDT, FEANIMAL, '
. 'CAST(FERATE AS VARCHAR(7)) AS FERATE, '
. 'FETLA1, FETLA2, FETLA3, '
. 'FETLA4, FETLA5, FETLA6, FETLA7, FETLA8, FETLA9, FETLA10, '
. 'FESLNO, FESORV, FECMCD, FEMULTI, FEBRILLDT, FEFLUSH, '
. 'FEFLUSHQTY, '
. 'CAST(FECFEE AS VARCHAR(12)) AS FECFEE, '
. 'FECFTP, FEFILL, FEMIXTIME, FEPURPOSE, FEMEDS, '
. 'FEMEDTXT, FEDIRECT, FEMORTXT '
. 'FROM UFFRATH ' . $this->whereClause
. ') AS P '
. 'WHERE P.ID BETWEEN ? AND ?';
db2_bind_param($stmt, 1, 'minRow', DB2_PARAM_IN);
db2_bind_param($stmt, 2, 'maxRow', DB2_PARAM_IN);
$exec = db2_execute($stmt);

if ($exec) {
$this->log('num_rows = '. db2_num_rows($stmt), true); // writes 'num_rows = -1' to the log file
while ($row = db2_fetch_object($stmt)) { // throws db2_fetch_object(): Fetch Failure
array_push($data, $this->buildRation($row));
}
}

Answer

You are trying to use db2_num_rows to determine number of rows found. You run this function after executing select.

PHP documentation says:

Returns the number of rows deleted, inserted, or updated by an SQL statement.

It also says:

To determine the number of rows that will be returned by a SELECT statement, issue SELECT COUNT(*) with the same predicates as your intended SELECT statement and retrieve the value.

Summary

I agree, it is weird that PHP documentation says nothing about negative value. But it says that you can not use this function to determine count of rows found by your select query. You mistake is a typical usage error.

Comments