Re: SQLSTATE 01004 now appears with v7.1 ?

On 24-Oct-2011 11:43 , Koester, Michael wrote:

A program that has been functioning nicely for several months is now
having run-time issues after our weekend upgrade from v6.1 to v7.1, and
it appears to be related to my test for "if %subst(SQLSTATE : 1 : 2) =
'00';" Prior to today, that test was apparently satisfied after:

fetch from [cursor] into [host variable]

even though the variable from the cursor was longer than the host
variable target.

Today, the sqlstate gets a "01004" to inform me that "The value of a
string was truncated when assigned to another string data type with a
shorter length." So my if-test fails, and bad things happen. It's true
that the lengths are not matched, but if that had shown up in my testing
last March, I wouldn't be fixing production stuff today.

In the code below <<SNIPped>>

Because of SQLSTATE 01004, my EquipmentTypes array is no longer
getting loaded.
Did I reach a wrong conclusion that v7.1 is handling that
differently? <<SNIP>>

Got a lot of code out there that tests for
"if %subst(SQLSTATE : 1 : 2) = '00';"
Maybe not so much that involves
cursor-variable-to-host-variable-length-mismatch, but I sure got
blindsided here. Since I didn't find any references to "01004" in the
archives, I thought it might help someone else, unless I've totally
missed what's really causing my trouble.

Perhaps the v7r1 MTU [pdf link to latest updates] under topic "Information center updates since IBM i 7.1 announcement"?:
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/rzahg/rzah8updates.htm
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/rzaq9/rzaq9.pdf


IBM i Memo to Users 7.1 (October 2011 update)

. Chapter 2. IBM i operating system . . . 9

... SQL warnings now reported to applications . . 17


_SQL warnings now reported to applications_

In earlier releases of IBM i, warning conditions which were encountered
during the processing of query result sets were not always reported back to the application as they should have been. These warnings, which include but are not limited to, SQLSTATE 01003 - ’Null values were eliminated from an argument of an aggregate function’, and SQLSTATE 01506 -’An adjustment was made to a DATE or TIMESTAMP value to correct an invalid date resulting from an arithmetic operation’, are now returned to the application. As a result of this change, ODBC and CLI
applications encountering these warnings receive a SQL_SUCCESS_WITH_INFO return code on the fetch API request rather than a SQL_SUCCESS return code.

Regards, Chuck