Why do I get "Data Conversion or Data Conversion? SQLCODE = -802" in a simple DB2 select statement?

Question

I am accessing DB2 information about 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
Line 2 successfully
Line 3 successfully
Line 4 successfully
Data conversion or data mapping error. SQLCODE = -802

There must be more than 4 results. Why can this error occur?

More details:

• The same error occurs for any value that I look for in MYFIELD, although it may be after a different number of successful results.
• MYFIELD NUMERIC (7.0)
• I can search for other fields in the table (including numeric) and it works fine.

Answer 1

The table had invalid decimal data. Instead of zeros there were spaces. I moved the zeros to these spaces and fixed the problem

Answer 2

If this could help someone else, I had the same error and it turned out that I was making a connection between inappropriate data types : a DECIMAL(2,0) and the VARCHAR(5) field. While VARCHAR , which can be converted to DECIMAL(2,0) , it can work, otherwise it throws an error.

Answer 3

According to the documentation, SQL0802 is an arithmetic overflow. Most likely, one of the lines has a data value that is too large for the php variable you declared for it, although I don't see this in the code you posted. Or there is another funny data transformation that does not happen properly, for example, if the QS36F.MYTABLE object is actually a view that selects from another table and converts one of the columns.