Re: SQLSTATE 01004 now appears with v7.1 ?

I had composed this message but failed to send earlier. The OP has already resolved by making the receiving field large enough to hold all of the data, but for completeness...

Specifically in response to the question if there are "better ways I should have coded this to prevent eventual upgrade trauma?":

If the assumption could be made that the "warnings" for a SQLSTATE could be tolerated, then a better test than "if %subst(SQLSTATE : 1 : 2) = '00';" would have been the test "if %subst(SQLSTATE : 1 : 2) = '00' OR %subst(SQLSTATE : 1 : 2) = '01';". The latter because according to the "SQLCODE and SQLSTATE concepts" at the following link, the "class code of an SQLSTATE value indicates whether the SQL statement was executed successfully (class codes 00 and 01) or unsuccessfully (all other class codes)."
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/rzala/rzalaco.htm

Note: Because the class code may be non-digit characters, the simple test [of one non-weighted less-than-or-equal comparison] of just "if %subst(SQLSTATE : 1 : 2) <= '01'" is undesirable. For example, the class 'A0' is less than the class '01' [because x'C1F0' is less than x'F0F1'], but the 'A0' is *not* an indication that the SQL statement was executed successfully.

Another option would have been to test SQLCODE for a zero as return code instead of testing the SQLSTATE which is more granular; i.e. the SQLSTATE having the additional ability to diagnose "specific conditions or classes of conditions."

Regards, Chuck

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

<<SNIP>>

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,
"equipment" in the EPstatus file is 16 A
and local variable "type" is 10 A

clear EquipmentTypes;

exec sql
declare EPstatusCursor cursor for
select DISTINCT equipment
from EPstatus
where order = :ServiceOrder
For Read only;

exec sql
open EPstatusCursor;

for x = 1 to 10;
exec sql
fetch next
from EPstatusCursor
into :Type;

if %subst(SQLSTATE : 1 : 2) = '00';
// SQL executed successfully
EquipmentTypes(x) = Type;
else;
NextElement = x;
LEAVE;
endif;
endfor;

Because of SQLSTATE 01004, my EquipmentTypes array is no longer
getting loaded.
<<SNIP>>
Anyone else have similar fun with this? Are there better ways I
should have coded this to prevent eventual upgrade trauma?

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.