IBM Support

SQL Precompiler SQL0312 and SQL5011 message enhancement

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.

[{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB08","label":"Cognitive Systems"}}]

Document Information

Modified date:
13 January 2020

UID

ibm11118547