Number of rows in an Informix query?

[Anon]

I'm trying to get the actual number of rows returned in a query and not getting it.

The number should be two, since I'm doing a wildcard select on a table that only has two rows.

ifx_num_rows reports 0, and ifx_affected_rows gives me 10.

here's a snippet I'm toying with:

$cn = ifx_connect($db, $uid, $pwd) or die("$svr won't talk to me!");

perform the query and then get # of rows and columns returned.

$rp = ifx_prepare($SQL, $cn);
$rs = ifx_do($rp);
$nRows = ifx_num_rows($rp);
$nCols = ifx_num_fields($rp);
$types = ifx_fieldtypes ($rp);
if(! $rs) die ("<br>No data found!<br>\n");
echo "<b>Total of ".$nRows." rows and ".$nCols." columns returned.</b>\n";

This snippet was taken from a script written for MySQL, but modified to Informix routines. Just seems like the ifx_num_rows isn't working for me.

Any ideas?

[Anon]

I'm looking at the manual page for ifx_num_rows(), and it reads (emphasis added):

"Gives the number of rows <b>fetched so far</b> for a query"

That sure makes it sound like it has no intention of telling you in advance the total count of rows. Furthermore, under ifx_affected_rows() it says:

"For selects it is an estimate (sqlerrd[0]). Don't rely on it. The database server can never return the actual number of rows that will be returned by a SELECT because it has not even begun fetching them at this stage (just after the 'PREPARE' when the optimizer has determined the query plan)."

Informix probably considers this a performance-enhancing feature.

Sorry these ideas aren't cheeful ones...

[Anon]

Yes, I read the manual as well. That's why I'm using ifx_num_rows instead of ifx_affected_rows.

The way I am reading the code I posted, I prepare a query, I DO the query, and then want to see the number of rows returned.

Doesn't work. I can do a call to ifx_num_rows even after processing the results of my original ifx_do statement in a ifx_htmltbl_result, and ifx_num_rows is still 0. Can't figure out how it all knows how many rows to process in the ifx_htmltbl_result if it can't figure out ifx_num_rows. Seems fairly silly to me.

I've never gotten anything other than 0 with ifx_num_rows. I think it's broken.

[Anon]

OK. Maybe not exactly broken, but obviously ifx_num_rows works in a strange enough manner that I consider it broken.

If I twist this a little bit I can actually get ifx_num_rows to report back what it should do.

Compare this code to my first post:

    $afRows = ifx_affected_rows($rp);
    for ($i = 0; $i <= $afRows; $i++) {
        $k = ifx_fetch_row($rp);
    }
    $nRows = ifx_num_rows($rp);

NOW I get the correct number of rows returned. I have no idea why I need to all sorts of silly hoop-jumping to get this number.

Depending on performance, I may just have to use ifx_affected_rows and let the page display empty fields for the extra rows returned.

[Anon]

Why do you need the number up front? You can always just loop on ifx_fetch_row() until it returns false. I am used to using the rowcount with PostgreSQL, but that's because in the PosgreSQL model, the fetch function requires the row number within the result set as a parameter, so you have to use the num_rows function. It's no problem because it works as expected.

But with Informix you do have the option of just retrieveiving rows one at a time until there are no more.

[Anon]

Good point. That's what I get for trying to code at midnight.

I was trying to display a line that stated 'X rows found' and then display the rows in a table, but there is no reason why I can't show the row count at the end of the table.

I've only worked with Informix SQL, and then only in creating forms and ace reports, never the embedded C part. This morning as I am thinking of how I grab the number of rows returned on a query, I'm also realizing the count of rows returned is the last thing Informix tells me..

Thanks for the replies