Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / November 2005

Tip: Looking for answers? Try searching our database.

Failure of odbc_num_rows sometimes

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Bob Stearns - 29 Nov 2005 17:50 GMT
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 - 29 Nov 2005 18:00 GMT
> 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
[quoted text clipped - 30 lines]
> '--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.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Bob Stearns - 29 Nov 2005 21:56 GMT
>>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
[quoted text clipped - 42 lines]
> 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 - 29 Nov 2005 22:12 GMT
 In one of Dan Scott's developerWorks articles on PHP and DB2
(http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0502scott/index.html),
he mentions some cases where odbc_row_count() might not work.

Signature

--------------------
Larry Menard
"Defender of Geese and of All Things Natural"

>>>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
[quoted text clipped - 57 lines]
> .
> }
Knut Stolze - 29 Nov 2005 22:20 GMT
> 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
>
[quoted text clipped - 4 lines]
> .
> }

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.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Bob Stearns - 29 Nov 2005 23:34 GMT
>>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
>>
[quoted text clipped - 15 lines]
> 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 - 30 Nov 2005 09:08 GMT
>>>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 ||
[quoted text clipped - 5 lines]
> 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:
>>
[quoted text clipped - 9 lines]
> 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.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Dan Scott - 30 Nov 2005 19:57 GMT
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 - 30 Nov 2005 21:34 GMT
> The only reason mysql_num_rows() returns the number of rows from a
> SELECT statement is because MySQL immediately returns ALL of the rows
[quoted text clipped - 17 lines]
> 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
[quoted text clipped - 4 lines]
> 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 - 30 Nov 2005 21:51 GMT
> 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/developerworks/db2/library/techarticle/scott/0614_scott.html

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.