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, the message text is updated to include the reason code.
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 (IBM i 7.2 and higher):
SQL0312 30 16 Position 40 Variable VAR5 not defined or not usable.
Reason: The variable is a dimensioned array.
SQL0312 30 17 Position 40 Variable STRUCT1 not defined or not usable.
Reason: The host structure is a dimensioned array.
SQL0312 30 18 Position 40 Variable STRUCT2 not defined or not usable.
Reason: No declaration for the variable exists, the
declaration is not within the current scope, or the
variable does not have an equivalent SQL data type.
SQL0312 30 19 Position 40 Variable STRUCT4 not defined or not usable.
Reason: A member of the host structure is a result set
locator.
SQL0312 30 20 Position 40 Variable STRUCT6 not defined or not usable.
Reason: A member of the host structure is dimensioned.
SQL0312 30 21 Position 40 Variable STRUCT7 not defined or not usable.
Reason: A member of the host structure does not have an
equivalent SQL data type.
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 (IBM i 7.2 and higher):
SQL5011 30 22 Position 37 Host structure array STRUCT0 not defined or
not usable. Reason: The host variable is not defined or
is not a valid structure. A valid host structure must
have at least one valid member.
SQL5011 30 23 Position 37 Host structure array STRUCT3 not defined or
not usable. Reason: Elements of the host structure array
are not contiguous.
SQL5011 30 24 Position 37 Host structure array STRUCT4 not defined or
not usable. Reason: The host structure is not a
dimensioned array.
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. Reason: A member of the host structure array
is a structure.
SQL5011 30 26 Position 37 Host structure array STRUCT14 not defined or
not usable. Reason: A member of the host structure array
does not have an equivalent SQL data type.
Was this topic helpful?
Document Information
Modified date:
13 January 2020
UID
ibm11118547