Why am I getting "Data conversion or data mapping error. SQLCODE=-802" on a simple DB2 select statement?

I am accessing DB2 information on an IBM i (AS400) with PHP.

With this code:

$query = "SELECT * FROM QS36F.MYTABLE WHERE MYFIELD=120006";
$result = db2_prepare($conn, $query);
db2_execute($result);

$i=0;
while($row = db2_fetch_assoc($result) or die(db2_stmt_errormsg())){
    $i++;
    print "Row " . $i . " successful<br />";
}

I get:

SELECT * FROM QS36F.MYTABLE WHERE MYFIELD=120006

Row 1 successful

Row 2 successful

Row 3 successful

Row 4 successful

Data conversion or data mapping error. SQLCODE=-802

There should be more than 4 re开发者_如何学Csults. Why might this error be occurring?

More details:

• This same error seems to happen on any value I search for in MYFIELD although it may be after a different number of successful results
• MYFIELD is NUMERIC(7,0)
• I can search other fields in the table(including numeric ones) and it works fine.

There was invalid decimal data in the table. There were blanks instead of zeros. I moved zeros into those blanks and that fixed the problem

If it can help someone else, I had the same error and found out I was making a join between mismatching data types: a DECIMAL(2,0) field and a VARCHAR(5) field. Till we have VARCHARs that can be converted into DECIMAL(2,0) it can work, otherwise it throws the error.

According to the documentation, SQL0802 is arithmetic overflow. More than likely one of the rows has a data value that is too big for the php variable that you have declared for it, although I can't see that in the code you've posted. Or else there is some other funny data conversion not happening properly, for example if the QS36F.MYTABLE object is actually a view that is selecting from another table, and is converting one of the columns.