Failure of odbc_num_rows sometimes

[Bob Stearns]

I am trying this query here because I got no useful reply on
alt.php.sql. If you can recommend a better venue for this question, I'd
be glad to hear it.

When I have an Sql statement of the form:

SELECT LOC_ID,REPEAT('--',LEVELX)||NAMEX
-- or SELECT LOC_ID,REPEAT('-',LEVELX*2)||NAMEX
FROM is3.locations
WHERE entity_id=2
AND (inactive='N' OR inactive IS NULL)
AND leftx BETWEEN 2 AND 983
ORDER BY LEFTX

odbc_num_rows returns -1. When I change the sql statement to:

SELECT LOC_ID,CHAR(LEVELX)||' '||NAMEX
FROM is3.locations
WHERE entity_id=2
AND (inactive='N' OR inactive IS NULL)
AND leftx BETWEEN 2 AND 983
ORDER BY LEFTX

odbc_num_rows returns the correct result.

Both statements work as expected in the ADS development environment.

The rdms is db2 udb v8.1.9 linux

php is 4.4.0 with db2 compiled in
'./configure' '--prefix=/usr/local/php-4.4.0'
'--with-apxs2=/usr/local/apache2.0.54/bin/apxs'
'--with-ibm-db2=/db2home/db2inst1/sqllib'
'--with-openssl=/usr/local/ssl-0.9.8'

[Knut Stolze]

Bob Stearns wrote:

I am trying this query here because I got no useful reply on
alt.php.sql. If you can recommend a better venue for this question, I'd
be glad to hear it.

When I have an Sql statement of the form:

SELECT LOC_ID,REPEAT('--',LEVELX)||NAMEX
-- or SELECT LOC_ID,REPEAT('-',LEVELX*2)||NAMEX
FROM is3.locations
WHERE entity_id=2
AND (inactive='N' OR inactive IS NULL)
AND leftx BETWEEN 2 AND 983
ORDER BY LEFTX

odbc_num_rows returns -1. When I change the sql statement to:

SELECT LOC_ID,CHAR(LEVELX)||' '||NAMEX
FROM is3.locations
WHERE entity_id=2
AND (inactive='N' OR inactive IS NULL)
AND leftx BETWEEN 2 AND 983
ORDER BY LEFTX

odbc_num_rows returns the correct result.

Both statements work as expected in the ADS development environment.

The rdms is db2 udb v8.1.9 linux

php is 4.4.0 with db2 compiled in
'./configure' '--prefix=/usr/local/php-4.4.0'
'--with-apxs2=/usr/local/apache2.0.54/bin/apxs'
'--with-ibm-db2=/db2home/db2inst1/sqllib'
'--with-openssl=/usr/local/ssl-0.9.8'

This is actually acceptable behavior for odbc_num_rows(). The PHP
documentation says as much:
http://de.php.net/manual/en/function.odbc-num-rows.php

I can't comment on the reasons why DB2 cannot return the number of rows
returned by the first statement.

There are some terrible suggestions in the comments to the function how to
get it to return some semi-reliable results (like running the query twice).
Maybe you could first explain what you need the row-count for. Then it
might be possible to give you a better advise.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Bob Stearns]

Knut Stolze wrote:

Bob Stearns wrote:

I am trying this query here because I got no useful reply on
alt.php.sql. If you can recommend a better venue for this question, I'd
be glad to hear it.

When I have an Sql statement of the form:

SELECT LOC_ID,REPEAT('--',LEVELX)||NAMEX
-- or SELECT LOC_ID,REPEAT('-',LEVELX*2)||NAMEX
FROM is3.locations
WHERE entity_id=2
AND (inactive='N' OR inactive IS NULL)
AND leftx BETWEEN 2 AND 983
ORDER BY LEFTX

odbc_num_rows returns -1. When I change the sql statement to:

SELECT LOC_ID,CHAR(LEVELX)||' '||NAMEX
FROM is3.locations
WHERE entity_id=2
AND (inactive='N' OR inactive IS NULL)
AND leftx BETWEEN 2 AND 983
ORDER BY LEFTX

odbc_num_rows returns the correct result.

Both statements work as expected in the ADS development environment.

The rdms is db2 udb v8.1.9 linux

php is 4.4.0 with db2 compiled in
'./configure' '--prefix=/usr/local/php-4.4.0'
'--with-apxs2=/usr/local/apache2.0.54/bin/apxs'
'--with-ibm-db2=/db2home/db2inst1/sqllib'
'--with-openssl=/usr/local/ssl-0.9.8'

This is actually acceptable behavior for odbc_num_rows(). The PHP
documentation says as much:
http://de.php.net/manual/en/function.odbc-num-rows.php

I can't comment on the reasons why DB2 cannot return the number of rows
returned by the first statement.

There are some terrible suggestions in the comments to the function how to
get it to return some semi-reliable results (like running the query twice).
Maybe you could first explain what you need the row-count for. Then it
might be possible to give you a better advise.

This is the first time I have had odbc_num_rows fail.

Just laziness: I use the count to judge the correctness of the result,
in particular I test (at various times) for n==0, n==1, n>0, n==0 ||
n==1. Furthermore, I use the construct

for($i=0; $i<$n; $i++) {
$arow = odbc_fetch_array($res, $i+1);

[Larry Menard]

In one of Dan Scott's developerWorks articles on PHP and DB2
(http://www-128.ibm.com/developerwork...tt/index.html),
he mentions some cases where odbc_row_count() might not work.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Bob Stearns" <rs**********@charter.net> wrote in message
news:M9*****************@fe06.lga...

Knut Stolze wrote:

Bob Stearns wrote:

I am trying this query here because I got no useful reply on
alt.php.sql. If you can recommend a better venue for this question, I'd
be glad to hear it.

When I have an Sql statement of the form:

SELECT LOC_ID,REPEAT('--',LEVELX)||NAMEX
-- or SELECT LOC_ID,REPEAT('-',LEVELX*2)||NAMEX
FROM is3.locations
WHERE entity_id=2
AND (inactive='N' OR inactive IS NULL)
AND leftx BETWEEN 2 AND 983
ORDER BY LEFTX

odbc_num_rows returns -1. When I change the sql statement to:

SELECT LOC_ID,CHAR(LEVELX)||' '||NAMEX
FROM is3.locations
WHERE entity_id=2
AND (inactive='N' OR inactive IS NULL)
AND leftx BETWEEN 2 AND 983
ORDER BY LEFTX

odbc_num_rows returns the correct result.

Both statements work as expected in the ADS development environment.

The rdms is db2 udb v8.1.9 linux

php is 4.4.0 with db2 compiled in
'./configure' '--prefix=/usr/local/php-4.4.0'
'--with-apxs2=/usr/local/apache2.0.54/bin/apxs'
'--with-ibm-db2=/db2home/db2inst1/sqllib'
'--with-openssl=/usr/local/ssl-0.9.8'

This is actually acceptable behavior for odbc_num_rows(). The PHP
documentation says as much:
http://de.php.net/manual/en/function.odbc-num-rows.php

I can't comment on the reasons why DB2 cannot return the number of rows
returned by the first statement.

There are some terrible suggestions in the comments to the function how
to
get it to return some semi-reliable results (like running the query
twice). Maybe you could first explain what you need the row-count for.
Then it
might be possible to give you a better advise.

This is the first time I have had odbc_num_rows fail.

Just laziness: I use the count to judge the correctness of the result,
in particular I test (at various times) for n==0, n==1, n>0, n==0 ||
n==1. Furthermore, I use the construct

for($i=0; $i<$n; $i++) {
$arow = odbc_fetch_array($res, $i+1);
.
.
.
}

[Knut Stolze]

Bob Stearns wrote:

Just laziness: I use the count to judge the correctness of the result,
in particular I test (at various times) for n==0, n==1, n>0, n==0 ||
n==1.
What do you use that for? You could also count the records you already got
and check those predicates that way, can't you?
Furthermore, I use the construct

for($i=0; $i<$n; $i++) {
$arow = odbc_fetch_array($res, $i+1);
.
.
.
}

Now there I'd use a while loop or something like:

for ($i = 0; $arow = odbc_fetch_array($res), $i++) {
...
}

Omitting the row number in the fetch is also a good idea because you could
then move a way from scrollable cursors and use forward-only ones (better
performance) without running into any suprises, like if $i changes inside
the loop.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Bob Stearns]

Knut Stolze wrote:

Bob Stearns wrote:

Just laziness: I use the count to judge the correctness of the result,
in particular I test (at various times) for n==0, n==1, n>0, n==0 ||
n==1.

What do you use that for? You could also count the records you already got
and check those predicates that way, can't you?

I haven't fetched any rows yet. I check n before I do any fetching.
Seems better if the sql statement is being run exactly for the value of n.

Furthermore, I use the construct

for($i=0; $i<$n; $i++) {
$arow = odbc_fetch_array($res, $i+1);
.
.
.
}

Now there I'd use a while loop or something like:

for ($i = 0; $arow = odbc_fetch_array($res), $i++) {
...
}

Omitting the row number in the fetch is also a good idea because you could
then move a way from scrollable cursors and use forward-only ones (better
performance) without running into any suprises, like if $i changes inside
the loop.

Hopefully, I don't ever change $i except in the for statement itself.
Will the loop run 0 times when the result set is empty?

[Knut Stolze]

Bob Stearns wrote:

Knut Stolze wrote:

Bob Stearns wrote:

Just laziness: I use the count to judge the correctness of the result,
in particular I test (at various times) for n==0, n==1, n>0, n==0 ||
n==1.

What do you use that for? You could also count the records you already
got and check those predicates that way, can't you?

I haven't fetched any rows yet. I check n before I do any fetching.
Seems better if the sql statement is being run exactly for the value of n.

I don't quite follow you there. I could do a fetch and if nothing comes
back, i.e. SQLCODE +100, then n == 0. Otherwise, there is something to do
anyways.

Now there I'd use a while loop or something like:

for ($i = 0; $arow = odbc_fetch_array($res), $i++) {
...
}

Omitting the row number in the fetch is also a good idea because you
could then move a way from scrollable cursors and use forward-only ones
(better performance) without running into any suprises, like if $i
changes inside the loop.

Hopefully, I don't ever change $i except in the for statement itself.
Will the loop run 0 times when the result set is empty?

The above loop won't do anything, i.e. run 0 times, if the fetch doesn't
return anything. It's the same is such a loop (imagine variables in the
proper places so that it makes sense):

for ($i = 1; $i < 1; $i++) { ... }

This loop won't do anything either.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Dan Scott]

The only reason mysql_num_rows() returns the number of rows from a
SELECT statement is because MySQL immediately returns ALL of the rows
to the client for a mysql_query() call. Yes, this means that it's a bad
idea to do a SELECT that returns a million rows against MySQL. (Note
that the MySQL interface in PHP recently introduced
mysql_unbuffered_query() which returns only one row at a time -- and
surprise surprise, mysql_num_rows() will return 0 for a SELECT
statement when you call mysql_unbuffered_query().)

You have several options for getting the number of rows from a SELECT
statement in DB2:
1. Return all of the rows from the SELECT statement yourself, basically
explicitly doing the same thing that MySQL does under the covers:

while ($result_set[] = db2_fetch_array($stmt)) {}
$num_rows = count($result_set);

2. Very often, mysql_num_rows() is used only to determine whether any
results were returned from the SELECT statement. In this case, just go
ahead and issue your first db2_fetch_*() call -- if it returns FALSE,
then you know that there are no rows in the result set.

3. Use a scrollable cursor for your SELECT statement -- this uses more
resources on the database server but will allow db2_num_rows() to
return the right number of rows.

$stmt = db2_prepare($conn, 'SELECT firstnme FROM employee",
array("cursor" => DB2_SCROLLABLE));
db2_execute($stmt);
$num_rows = db2_num_rows($stmt);

Oh yeah, and please stay away from Unified ODBC for all of the reasons
mentioned in my article (the --with-ibm-db2 compile option) and use the
ibm_db2 extension instead (http://php.net/ibm_db2 or install the free
Zend Core for IBM from http://zend.com/core/ibm/ for precompiled PHP +
ibm_db2 extension). You'll have much more functionality and better
performance.

[Bob Stearns]

Dan Scott wrote:

The only reason mysql_num_rows() returns the number of rows from a
SELECT statement is because MySQL immediately returns ALL of the rows
to the client for a mysql_query() call. Yes, this means that it's a bad
idea to do a SELECT that returns a million rows against MySQL. (Note
that the MySQL interface in PHP recently introduced
mysql_unbuffered_query() which returns only one row at a time -- and
surprise surprise, mysql_num_rows() will return 0 for a SELECT
statement when you call mysql_unbuffered_query().)

You have several options for getting the number of rows from a SELECT
statement in DB2:
1. Return all of the rows from the SELECT statement yourself, basically
explicitly doing the same thing that MySQL does under the covers:

while ($result_set[] = db2_fetch_array($stmt)) {}
$num_rows = count($result_set);

2. Very often, mysql_num_rows() is used only to determine whether any
results were returned from the SELECT statement. In this case, just go
ahead and issue your first db2_fetch_*() call -- if it returns FALSE,
then you know that there are no rows in the result set.

I have written a general sql executor function which tests odbc_num_rows
for the conditions exactly 0, exactly 1, exactly 0 or 1, at least 1
returned result and fails/succeeds on the comparison. How do I do all of
these without impacting later fetches, especially if I'm trying to use
other advice I've gotten here: do not pass the row number to
odbc_fetch_array and use odbc_fetch_array for a loop predicate?
3. Use a scrollable cursor for your SELECT statement -- this uses more
resources on the database server but will allow db2_num_rows() to
return the right number of rows.

$stmt = db2_prepare($conn, 'SELECT firstnme FROM employee",
array("cursor" => DB2_SCROLLABLE));
db2_execute($stmt);
$num_rows = db2_num_rows($stmt);

Would a scrollable cursor be required if I wanted rows 125-149 of the
result? I make use of odbc_num_rows for driving such fetch operations as
well.
Oh yeah, and please stay away from Unified ODBC for all of the reasons
mentioned in my article (the --with-ibm-db2 compile option) and use the
A reference to the article would be appreciated.
ibm_db2 extension instead (http://php.net/ibm_db2 or install the free
Is either of these 1-1 substitutable with the corresponding odbc
library, by simply changing the odbc_* names to db2_* names?
Zend Core for IBM from http://zend.com/core/ibm/ for precompiled PHP +
ibm_db2 extension). You'll have much more functionality and better
performance.

[Knut Stolze]

Bob Stearns wrote:

Dan Scott wrote: I have written a general sql executor function which tests odbc_num_rows
for the conditions exactly 0, exactly 1, exactly 0 or 1, at least 1
returned result and fails/succeeds on the comparison. How do I do all of
these without impacting later fetches,
The you would need scrollable cursors to be able to scroll back after the
fetch in your counting function.
especially if I'm trying to use
other advice I've gotten here: do not pass the row number to
odbc_fetch_array and use odbc_fetch_array for a loop predicate?
I didn't say to avoid it at all costs. I just meant that you get often a
better performance if you stick to forward-only cursors.

Oh yeah, and please stay away from Unified ODBC for all of the reasons
mentioned in my article (the --with-ibm-db2 compile option) and use the

I'll second that. Especially if you try to use LOBs, you're in a lot of
trouble because the unified ODBC code screws up quite badly - no proper
error handling and a few other issues that Dan does not explain in his
article.
A reference to the article would be appreciated.

http://www-128.ibm.com/developerwork...614_scott.html

--
Knut Stolze
DB2 Information Integration Development
IBM Germany