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

Question

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 results. 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.

Solution 1

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

Solution 2

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.

Comments

[Mike]

MYTABLE is not a view. How could a data value be to big for the php variable? I have tried doing a select that just return one column. So I think that would eliminate the possibility that $row is being overloaded.

[Eric Belair]

How did you find this? I have the same issue, but I can't find the bad data.

[user2338816]

It might be easy to overflow a PHP variable. But because you're using SELECT * FROM... and there are no column definitions shown to us, we can't tell why it's happening here. In production code, SELECT * FROM... should essentially never be used; specify an explicit column-list or simply accept that various answers will never be found.