Re: SQLSTATE 01004 now appears with v7.1 ?

[CRPence <CRPbottle@xxxxxxxxx>]

  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.