odbc_num_rows returns -1 every time

workarounds

[coopster]

odbc_num_rows() [php.net] is a function that is supposed to return the number of rows in a result. However, I have indeed run across a driver (

DRIVER={iSeries Access ODBC Driver};

) as described in the notes on the PHP manual page.

Note: Using odbc_num_rows() to determine the number of rows available after a SELECT will return -1 with many drivers.

What have others in the community here found to be the fastest/best workaround?

[coopster]

I found the answer to my particular issue with the IBM DB2 database. Use odbc_pconnect() [php.net] as opposed to odbc_connect() [php.net].

This took me awhile to find so I'll leave this post out here for future readers. I found my answer on the IBM site. An IBM developer had a PHP code example and the connection being used is what caught my eye. I tested it and sure enough, problem solved. Now, if I only understood why... ;)

[jatar_k]

I have heard that using one of these 2 functions will fix the problem

oci_connect
mysql_connect

;)

[coopster]

hehe. Yeah, I'm sure changing databases is not an option for these folks though.

Actually, I like IBM DB2. It's ODBC that I don't care for much ;)

Always a smart-alec in the bunch...

[coopster]

Oh-oh. An update: odbc_pconnect() did not resolve my issue. It was the logic in my control loop that had me fooled. All right, I'm off to call IBM...

[coopster]

Not much help there either :(

Here is what I did find...

The PHP unified ODBC functions are translated into DB2 Call Level Interface (CLI) functions through the PHP-ODBC driver. This means that PHP applications access DB2 via the CLI interface.

The DB2 CLI translates the function odbc.num_rows() to the DB2 CLI function SQLRowCount(). SQLRowCount returns the number of rows affected by the last executed INSERT, UPDATE or DELETE statement. It does not return the number of rows results from a SELECT statement. You need to loop through the result set to find out the number of rows retrieved.

But you know what is whacky about the whole thing? Check this out...I will run the function prior to processing the result set, then again right afterward...

if ($link = odbc_connect(DSN, USERID, PASSWORD, SQL_CUR_USE_ODBC)) { 
 $stmt = 'my_statement_here'; 
 $rows = odbc_exec($link, $stmt); 
 print '<pre>Rows in result set: ' . odbc_num_rows($rows) . '</pre>'; 
 while ($row = odbc_fetch_array($rows)) { 
  print '<pre>'; print_r($row); print '</pre>'; 
 } 
 print '<pre>Rows in result set: ' . odbc_num_rows($rows) . '</pre>'; 
 odbc_close($link); 
} 
// Prints: 
Rows in result set: -1 
Array 
( 
  [COL#1] => Column1 
  [COL#2] => Column2 
  [COL#3] => Column3 
) 
Rows in result set: 1 

Once I have looped through the result set the correct value is returned from the function! Go figure!

My understanding is that it has something to do with the cursor sensitivity, but haven't nailed it down yet...

In the meantime, I use a workaround:

if ($link = odbc_connect(DSN, USERID, PASSWORD, SQL_CUR_USE_ODBC)) { 
 $stmt = 'my_statement_here'; 
 $rows = odbc_exec($link, $stmt); 
 $content = ''; 
 while ($row = @odbc_fetch_array($rows)) { 
  $content .= "<tr><td>{$row['COL#1']}</td></tr>"; 
 } 
 odbc_close($link); 
}

Now I know that if my $content variable is set but has no value I didn't have any rows returned (no data). I have to use the error suppression or PHP is going to blast out WARNINGS when you try to process an empty result set (and this is really what I wanted to do in the first place!). Lastly, if I truly did need the number of rows I could use a counter in my loop (or use the odbc_num_rows function because, as I showed earlier, once I loop through the stinking result set I can get the number of rows).

Frustrating, to say the least. Anyway, this is what I know to date on the subject.

[ergophobe]

Or make a second query with COUNT(*) no?

In that case, wouldn't you be pretty much assured that you would get the right count regardless of driver?

I assume that you've seen the page on SQLRowCount behavior [www-1.ibm.com] and cursors from IBM.

[coopster]

Actually no, I can't. I am not running a SELECT query in this implementation. I am actually running a "CALL" command, which is a stored procedure.

The code being execute is also running against DB2/400 as opposed to the DB2 Universal Database for Linux, UNIX and Windows. Yes, as of today they are two different animals and have different internals. hehe, I often joke about them calling it UDB, the "U" stands for "Universal" -- don't think so folks.

Thanks for the link though. I had not seen this one yet, but everything else I am finding points to the same issue, cursor sensitivity. I am trying to figure out if I can change it in my DSN definition -- no dice on all my attempts there so far either.

[ergophobe]

I have a shelf of books on DB2 in various flavors, mostly a bit older (DB2 7.1 or 7.2 for OS/390 and for Unix, Linux and Windows), from "Dummies" to "Ultimate" but none of them have anything to say about ODBC and CLI...

[jatar_k]

can you use something like odbc_num_fields as a work around? An empty set should have no fields.

I was also looking at odbc_next_result as a way to check for no rows but the only comment says it always returns -1 as well.

Everything I read tells about forward-only cursors being limited this way, along with erg's sweet link.

man coop, good thing erg and I are here or you'd be in serious jeopardy of talking to yourself. ;)

[coopster]

hehe, no kidding.

odbc_num_fields(). I read through all the other odbc_* functions when I was looking for a better workaround and didn't try that idea yet. Will have to give it a whirl when I get connected again.

Thanks for the suggestions fellas.