num_rows() always return a -1 value after query in db2 database

[eluser]mhel_dc[/eluser]
I have this code just to check whether an entry is existing in my database,
However, the num_rows() function always returns a -1 value even if the data I queried
exists.
What would have caused this?
I did check if the database parameters in database.php file in codeigniter
I found no possible errors.


function retrieve_aecode($socode)
{
$qry = "Select MSOSAG from itlib.WARFILE00 where WARCOD# ='".$socode."'";
$dbresult = $this->db->query($qry);

if($dbresult->num_rows() <= 0){
$dboutput = '';
}
else{
foreach($dbresult->result() as $row)
{
$dboutput = $row->MSOSAG;
}
}
return $dboutput;
}

By the way... im using codeigniter 1.7.1 and im connecting in AS/400 database using odbc.
any help will do. Thanks in advance.

[eluser]designfellow[/eluser]
Hi,

just try to echo the result you got from db & post back the result

So, we can find a solution


Happy Coding,
DesignFellow

[eluser]mhel_dc[/eluser]
Hi,
Thanks for the suggestion but I already tried that by doing this:

function retrieve_aecode($socode)
{
$qry = “Select MSOSAG from itlib.WARFILE00 where WARCOD# =’”.$socode.”’”;
$dbresult = $this->db->query($qry);

echo $dbresult->num_rows();
if($dbresult->num_rows() <= 0){
$dboutput = ‘’;
}
else{
foreach($dbresult->result() as $row)
{
$dboutput = $row->MSOSAG;
}
}
return $dboutput;
}

but still, I manage to get a -1 result, I tried to revise the query and hopefully solve the problem..

function retrieve_aecode($socode)
{
$qry = “Select Count(MSOSAG) as fldCount from itlib.WARFILE00 where WARCOD# =’”.$socode.”’”;
$dbresult = $this->db->query($qry);

echo $dbresult->num_rows();
if($dbresult->num_rows() <= 0){
$dboutput = ‘’;
}
else{
foreach($dbresult->result() as $row)
{
$dboutput = $row->fldCount;
}
}
return $dboutput;

Amazingly it produced an error stating that $dboutput is not likely registered as
a variable or $dboutput has never been declared.

for me to check whether I'm really not getting any row results I executed the
same query (the 2nd code) using SQL Developer, luckily, I manage to get 1 row
with 0 value.... how come that num_rows() function cannot see or determine that
the query returned a row with 0 as a result of the query?

[eluser]kurucu[/eluser]
It would be worth echoing the generated sql (including the variable, to confirm that what is being run is the same as you assume) and then popping that into PHPmySQL or your equivalent to see if the query runs and what it returns.

In fact, turn on profiling temporarly and see what queries are being run without any further intervention!

[eluser]mhel_dc[/eluser]
[quote author="kurucu" date="1253298323"]It would be worth echoing the generated sql (including the variable, to confirm that what is being run is the same as you assume) and then popping that into PHPmySQL or your equivalent to see if the query runs and what it returns.

In fact, turn on profiling temporarly and see what queries are being run without any further intervention![/quote]

Hi,
Thanks for the suggestion... I already tried echoing both the result and the query... but the question is this...

If you put a query like this:
"Select Count(*) from myTable Where conditionA = '" . $condition_var ."'";

Will or will it not return a single row (even if the result is zero(0)?

I did check the query as you have mentioned... in fact I double checked it by executing the same query directly to the as/400 command line and it produces 1 row as a result and that's zero(0) ... the function I just created was only to check whether my query result will return 0 or greater the value based on my expectation ... but still it keeps popping a -1 value...

I'm just curious...

Thanks,
Mhel_dc

[eluser]kurucu[/eluser]
Well... that's quite clearly true. How I missed the second part of your function is beyond me, but I only saw the following... whoops!

Code:

Select MSOSAG from itlib.WARFILE00 where WARCOD# =’”.$socode.”’

I've triple-read your function (the one I missed before) and can't fault it. I guess it's worth checking the value of $socode, for sanity, and perhaps temporarily using

Code:

function retrieve_aecode($socode)
{
    return 5;
}

just to be sure the error isn't somewhere else in your app.

[eluser]kgill[/eluser]
Since db2 isn't one of the included drivers, have you looked at the driver to see what code it's executing when you call num_rows? What's in there, if it's using db2_num_rows you're going to run into problems.

See PHP's documentation for the db2_num_rows function:

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

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.

[eluser]mhel_dc[/eluser]
Hi guys,
Thanks for all your help... I manage to solve my problem with regards to this issue...

SPECS :

AS/400 IBM DB2 database
CodeIgniter 1.7.1
ODBC Driver provided by CodeIgniter

Facts :

1. We used ODBC driver provied by codeigniter to connect to db2 database.
2. num_rows() function is not supported in ODBC (allegedly, based on this exprience).

example:

function get_aecode($socode){

/*
I found no errors on this query.
Based on this query... it must return 1 valid row even if the result value
is less than,equal or greater than zero(0).
*/
$qry = "Select Count(MSOSAG) as rowCnt Where Warcod# ='".$socode ."'";
$dbresult = $this->db->query($qry);

/* this where I validate using num_rows function in order to
proceed to another process.
But I wonder why it keeps returning a -1 value.
*/

if($dbresult->num_rows() <= 0) {
$dboutput = '';
}
else {
$dboutput = 'This is a valid output'
}
}

[eluser]InsiteFX[/eluser]
Did you try it like this in the user guide?

Code:

$query = $this->db->query("Select Count(MSOSAG) as rowCnt Where Warcod# ='".$socode ."'");

if ($query->num_rows() > 0)
{

}

Notice your quotes on the end!
You should be using the code tags.

Enjoy
InsiteFX

[eluser]mhel_dc[/eluser]
[quote author="InsiteFX" date="1253786763"]Did you try it like this in the user guide?

Code:

$query = $this->db->query("Select Count(MSOSAG) as rowCnt Where Warcod# ='".$socode ."'");

if ($query->num_rows() > 0)
{

}

Notice your quotes on the end!
You should be using the code tags.

Enjoy
InsiteFX[/quote]

Hi,
I already tried that... but still the num_rows() function keeps returning a -1 value... I don't know the reason why.

Thanks,
Mhel_dc