News
Abstract
Description of the SQL0312 and SQL5011 reason code support
Content
You are in: IBM i Technology Updates > Db2 for i - Technology Updates > Db2 for i Functional Enhancements > SQL Precompiler SQL0312 and SQL5011 message enhancement
One of the pain points for embedded SQL development within source languages has been the fact that when the precompiler gives an error message indicating that a host variable is not valid for use within SQL, it does not give any indication why. There are numerous reasons why a host variable or host structure may not be usable within SQL and the reason that the precompiler is generating the message is often not obvious.
Support was added to indicate a reason code on the SQL0312 message and the SQL5011 message. This support is available for all ILE precompilers.
In IBM i 7.2 and 7.3, the precompiler listing shows the English text “ Reason: <reason code description>” at the end of the message text. In IBM i 7.4 and later releases, the message text is updated to include the reason code. The message second level text includes a description of the reason code. The SQL precompiler parameter OPTION(*SECLVL) can be used to show the message second level text within the precompiler listing.
To see the complete message text which includes the reason code descriptions outside of the precompiler listing, use the following CL commands:
DSPMSGD SQL0312 QSQLMSG
DSPMSG SQL5011 QSQLMSG
SQL0312 Example:
**FREE
DCL-S var5 INT(5) DIM(10);
DCL-DS struct1 QUALIFIED DIM(10);
sub1 CHAR(4);
END-DS;
DCL-DS struct4 QUALIFIED;
sub1 SQLTYPE(RESULT_SET_LOCATOR);
END-DS;
DCL-DS struct6 QUALIFIED;
sub1 CHAR(4) DIM(2);
END-DS;
DCL-DS struct7 QUALIFIED;
sub1 CHAR(40000);
END-DS;
EXEC SQL insert into DUMMYTAB values(:var5);
EXEC SQL insert into DUMMYTAB values(:struct1);
EXEC SQL insert into DUMMYTAB values(:struct2);
EXEC SQL insert into DUMMYTAB values(:struct4);
EXEC SQL insert into DUMMYTAB values(:struct6);
EXEC SQL insert into DUMMYTAB values(:struct7);
*INLR = *ON;
Here are the messages prior to this enhancement:
SQL0312 30 17 Position 40 Variable VAR5 not defined or not usable.
SQL0312 30 18 Position 40 Variable STRUCT1 not defined or not usable.
SQL0312 30 19 Position 40 Variable STRUCT2 not defined or not usable.
SQL0312 30 20 Position 40 Variable STRUCT4 not defined or not usable.
SQL0312 30 21 Position 40 Variable STRUCT6 not defined or not usable.
SQL0312 30 22 Position 40 Variable STRUCT7 not defined or not usable.
Here are the messages after this enhancement for IBM i 7.4 and higher:
SQL0312 30 17 Position 40 Variable VAR5 not defined or not usable for
reason code 2.
SQL0312 30 18 Position 40 Variable STRUCT1 not defined or not usable
for reason code 22.
SQL0312 30 19 Position 40 Variable STRUCT2 not defined or not usable
for reason code 1.
SQL0312 30 20 Position 40 Variable STRUCT4 not defined or not usable
for reason code 45.
SQL0312 30 21 Position 40 Variable STRUCT6 not defined or not usable
for reason code 41.
SQL0312 30 22 Position 40 Variable STRUCT7 not defined or not usable
for reason code 44.
SQL5011 Example:
**FREE
DCL-DS struct3 QUALIFIED DIM(10);
sub1 CHAR(4);
*N CHAR(1);
END-DS;
DCL-DS struct4 QUALIFIED;
sub1 CHAR(4);
END-DS;
DCL-DS struct8 QUALIFIED DIM(10);
DCL-DS struct8sub;
sub1b CHAR(1);
END-DS;
END-DS;
DCL-DS struct14 QUALIFIED DIM(10);
sub1 UNS(5);
END-DS;
EXEC SQL declare c1 cursor for select * from DUMMYTAB;
EXEC SQL open c1;
EXEC SQL fetch c1 for 4 rows into :struct0;
EXEC SQL fetch c1 for 4 rows into :struct3;
EXEC SQL fetch c1 for 4 rows into :struct4;
EXEC SQL fetch c1 for 4 rows into :struct8;
EXEC SQL fetch c1 for 4 rows into :struct14;
EXEC SQL close c1;
*INLR = *ON;
Here are the messages prior to this enhancement:
SQL5011 30 22 Position 37 Host structure array STRUCT0 not defined or
not usable.
SQL5011 30 23 Position 37 Host structure array STRUCT3 not defined or
not usable.
SQL5011 30 24 Position 37 Host structure array STRUCT4 not defined or
not usable.
SQL0221 30 24 Position 20 Number of rows 4 not valid.
SQL5011 30 25 Position 37 Host structure array STRUCT8 not defined or
not usable.
SQL5011 30 26 Position 37 Host structure array STRUCT14 not defined or
not usable.
Here are the messages after this enhancement for IBM i 7.4 and higher:
SQL5011 30 22 Position 37 Host structure array STRUCT0 not defined ornot usable for reason code 1.
SQL5011 30 23 Position 37 Host structure array STRUCT3 not defined or
not usable for reason code 26.
SQL5011 30 24 Position 37 Host structure array STRUCT4 not defined or
not usable for reason code 21.
SQL0221 30 24 Position 20 Number of rows 4 not valid.
SQL5011 30 25 Position 37 Host structure array STRUCT8 not defined or
not usable for reason code 46.
SQL5011 30 26 Position 37 Host structure array STRUCT14 not defined or
not usable for reason code 44.Was this topic helpful?
Document Information
More support for:
IBM i
Software version:
All Versions
Operating system(s):
IBM i
Document number:
1118547
Modified date:
11 February 2025
UID
ibm11118547