SQL type variables in RPG @ RPGPGM.COM

sqltype variables used when dcl-s in rpg

I have written about defining RPG variables defined as SQL types in past posts, which made me curious about all there are. Having searched in IBM's Knowledge Center I was disappointed to find that there was no one page with all of this information upon. Which gave me idea to write this, a list of all SQL types available.

If I am defining variables as SQL types my source needs to be compiled as a SQLRPGLE, as the RPG SQL precompiler converts these SQL data types into more familiar RPG equivalents.

The syntax for these variable definitions is simple, it is just:

dcl-s WorkVar sqltype(Valid SQL type) ;

There are only certain of the SQL data types that can be used. If I was define a variable with the SQL type of Variable length character, VARCHAR for example I get a "SQL precompile failure". When I look in the generated spool file I see the following:

03  dcl-s WorkVar sqltype(varchar) ;


                                           DIAGNOSTIC MESSAGES
MSG ID  SEV RECORD TEXT
SQL0104  30      3 Token VARCHAR was not valid. Valid tokens: CLOB
                   CLOB_LOCATOR CLOB_FILE BLOB BLOB_LOCATOR BLOB_FILE
                   DBCLOB_LOCATOR DBCLOB_FILE ROWID BINARY VARBINARY
                   RESULT_SET_LOCATOR XML_CLOB XML_BLOB XML_DBCLOB
                   XML_LOCATOR XML_CLOB_FILE XML_BLOB_FILE 
                   XML_DBCLOB_FILE.

The SQL type can be entered in either case, in the examples below I will be using upper case only just to make it easier to read.

Let me go through all of the SQL types and show how the RPG SQL precompiler converts them to RPG compatible variables.

Binary

I fail to understand why RPG would use different binary types than SQL, but it does. There are two types of binary variables:

Description	SQL type	Minimum length	Maximum length
Binary	`BINARY`	1	3,2766
Variable length binary	`VARBINARY`	1	3,2740

When defining SQL binary variables I have to give a length, or the SQL precompile will fail.

01  dcl-s Binary sqltype(BINARY:9) ;
02  dcl-s VarBinary sqltype(VARBINARY:32740) ;

When this is compiled, the compile listing shows these SQL types were translated to RPG compatible types:

//*DCL-S BINARY SQLTYPE(BINARY:9) ;
   DCL-S BINARY CHAR(9) CCSID(*HEX);

//*DCL-S VARBINARY SQLTYPE(VARBINARY:32740) ;
   DCL-S VARBINARY VARCHAR(32740) CCSID(*HEX);

IBM documentation

Large Objects

These large objects SQL types come in three flavors:

Binary Large OBjects (BLOB)
Character Large OBjects (CLOB)
Double Byte Character Large OBjects (DBCLOB)

And these are the SQL types you can define them as:

Description	SQL type	Minimum length	Maximum length
Binary LOB	`BLOB`	1	16,773,100
Binary LOB locator	`BLOB_LOCATOR`	Needs to be >16M
Character LOB	`CLOB`	1	16,773,100
Character LOB locator	`CLOB_LOCATOR`	Needs to be >16M
Double byte LOB	`DBCLOB`	1	8,386,550
Double byte LOB locator	`DBCLOB_LOCATOR`	Needs to be >8M

The BLOB, CLOB, and DBCLOB all have to be defined with a length, the locator types do not.

03  dcl-s Blob sqltype(BLOB:1000000) ;
04  dcl-s BlobLocator sqltype(BLOB_LOCATOR) ;

05  dcl-s Clob sqltype(CLOB:1000) ;
06  dcl-s ClobLocator sqltype(CLOB_LOCATOR) ;

07  dcl-s DbClob sqltype(DBCLOB:2000) ;
08  dcl-s DbClobLocator sqltype(DBCLOB_LOCATOR) ;

When compiled the program listing shows:

//*DCL-S BLOB SQLTYPE(BLOB:1000000) ;
   DCL-DS BLOB;
     BLOB_LEN UNS(10);
      BLOB_DATA CHAR(1000000) CCSID(*HEX);
    END-DS BLOB;

//*DCL-S BLOBLOCATOR SQLTYPE(BLOB_LOCATOR) ;
   DCL-S BLOBLOCATOR UNS(10);

//*DCL-S CLOB SQLTYPE(CLOB:1000) ;
   DCL-DS CLOB;
     CLOB_LEN UNS(10);
     CLOB_DATA CHAR(1000) CCSID(*JOBRUNMIX);
   END-DS CLOB;

//*DCL-S CLOBLOCATOR SQLTYPE(CLOB_LOCATOR) ;
   DCL-S CLOBLOCATOR UNS(10);

//*DCL-S DBCLOB SQLTYPE(DBCLOB:2000) ;
   DCL-DS DBCLOB;
     DBCLOB_LEN UNS(10);
     DBCLOB_DATA GRAPH(2000);
   END-DS DBCLOB;

//*DCL-S DBCLOBLOCATOR SQLTYPE(DBCLOB_LOCATOR) ;
   DCL-S DBCLOBLOCATOR UNS(10);

Notice how the BLOB, CLOB, and DBCLOB variables have been converted into data structures consisting of two subfields:

_LENGTH Length of the string within the data subfield
_DATA The data itself

Large Objects Files

I used one of these types, Character LOB file, with SQL to copy data to a text file in the IFS.

It should come as no surprise that the three flavors of LOBs are represented here.

Description	SQL type	Minimum length	Maximum length
Binary LOB file	`BLOB_FILE`	N/A
Character LOB file	`CLOB_FILE`	N/A
Double Byte Character LOB file	`DBCLOB_FILE`	N/A

When defining these types I do not have to give a length as these represent, rather than contain, the file.

09  dcl-s BlobFile sqltype(BLOB_FILE) ;
10  dcl-s ClobFile sqltype(CLOB_FILE) ;
11  dcl-s DbClobFile sqltype(DBCLOB_FILE) ;

RPG's SQL precompiler translate these variables into data structures:

//*DCL-S BLOBFILE SQLTYPE(BLOB_FILE) ;
   DCL-DS BLOBFILE;
     BLOBFILE_NL UNS(10);
     BLOBFILE_DL UNS(10);
     BLOBFILE_FO UNS(10);
     BLOBFILE_NAME CHAR(255) CCSID(*JOBRUNMIX);
   END-DS BLOBFILE;

//*DCL-S CLOBFILE SQLTYPE(CLOB_FILE) ;
   DCL-DS CLOBFILE;
     CLOBFILE_NL UNS(10);
     CLOBFILE_DL UNS(10);
     CLOBFILE_FO UNS(10);
     CLOBFILE_NAME CHAR(255) CCSID(*JOBRUNMIX);
   END-DS CLOBFILE;

//*DCL-S DBCLOBFILE SQLTYPE(DBCLOB_FILE) ;
   DCL-DS DBCLOBFILE;
     DBCLOBFILE_NL UNS(10);
     DBCLOBFILE_DL UNS(10);
     DBCLOBFILE_FO UNS(10);
     DBCLOBFILE_NAME CHAR(255) CCSID(*JOBRUNMIX);
   END-DS DBCLOBFILE;

The subfields of these data structures are all used the same way:

_NL Length of the IFS path
_DL Not used
_FO File operation
_NAME Path of the file in the IFS

For more information about how to use this type read the post Copying any data to and from a file in the IFS.

IBM documentation: CLOB_FILE, same rules apply for BLOB_FILER and DBCLOB_FILE

Row Identifier

Row id is an alternative to the identity column when you want to ensure uniqueness in my files. As with the identity column the value placed in the Row id is generated by the Db2 for i engine automatically. I do not want to say much more as I will write about this in a future post.

When defining a variable I do not give a length as the length of a Row id is fixed.

Description	SQL type	Minimum length	Maximum length
Row identifier	`ROWID`	Generated by Db2 for i

The definition is simple:

12  dcl-s RowId sqltype(ROWID) ;

The precompiler translate this to:

//*DCL-S ROWID SQLTYPE(ROWID) ;
   DCL-S ROWID VARCHAR(40) CCSID(*HEX);

IBM documentation

Result Set Locator

This one is an odd one. A Result Set Locator is a variable that contains the locator that identifies a stored procedure result set. Not much more I can say about that.

I do not define a length when using this type as it is system generated.

Description	SQL type	Minimum length	Maximum length
Result set locator	`RESULT_SET_LOCATOR`	Generated by Db2 for i

So what does this look like:

13  dcl-s ResultSetLocator sqltype(RESULT_SET_LOCATOR) ;

And the precompiler translate that to:

//*DCL-S RESULTSETLOCATOR SQLTYPE(RESULT_SET_LOCATOR) ;
   DCL-S RESULTSETLOCATOR INT(20);

IBM documentation

XML Host Variables

All "host variables" mean is that the XML data is hosted on my IBM i environment.

Description	SQL type	Minimum length	Maximum length
XML BLOB	`XML_BLOB`	1	16,773,100
XML CLOB	`XML_CLOB`	1	16,773,100
XML Double Byte CLOB	`XML_DBCLOB`	1	8,386,550
XML locator	`XML_LOCATOR`	> 16M

I do need to give a length with the first three, but not with the locator.

14  dcl-s XmlBlob sqltype(XML_BLOB:500) ;
15  dcl-s XmlClob sqltype(XML_CLOB:5000) ;
16  dcl-s XmlDbClob sqltype(XML_DBCLOB:50000) ;
17  dcl-s XMLLocator sqltype(XML_LOCATOR) ;

The precompiler generates:

//*DCL-S XMLBLOB SQLTYPE(XML_BLOB:500) ;
   DCL-DS XMLBLOB;
     XMLBLOB_LEN UNS(10);
     XMLBLOB_DATA CHAR(500) CCSID(*HEX);
   END-DS XMLBLOB;

//*DCL-S XMLCLOB SQLTYPE(XML_CLOB:5000) ;
   DCL-DS XMLCLOB;
     XMLCLOB_LEN UNS(10);
     XMLCLOB_DATA CHAR(5000) CCSID(*JOBRUNMIX);
   END-DS XMLCLOB;

//*DCL-S XMLDBCLOB SQLTYPE(XML_DBCLOB:50000) ;
   DCL-DS XMLDBCLOB;
     XMLDBCLOB_LEN UNS(10);
     XMLDBCLOB_DATA UCS2(50000) CCSID(13488);
   END-DS XMLDBCLOB;

Data structures are generated each with two subfields (like the LOB variables):

_LENGTH Length of the string within the data subfield
_DATA The data itself

If you are interested in more detail see Getting data from a XML file using SQL.

IBM documentation (included XML file types too)

XML File References

as with the LOB file reference types these types represent, rather than contain, the file. Therefore, when I define these types of variables I do not give them a length.

When defining these types I do not have to give a length as these represent, rather than contain, the file.

Description	SQL type	Minimum length	Maximum length
XML Binary LOB file	`XML_BLOB_FILE`	N/A
XML Character LOB file	`XML_CLOB_FILE`	N/A
XML Double Byte Character LOB file	`XML_DBCLOB_FILE`	N/A

There definitions would look like:

18  dcl-s XMLBlobFile sqltype(XML_BLOB_FILE) ;
19  dcl-s XMLClobFile sqltype(XML_CLOB_FILE) ;
20  dcl-s XMLDbClobFile sqltype(XML_DBCLOB_FILE) ;

Which is translated to:

//*DCL-S XMLBLOBFILE SQLTYPE(XML_BLOB_FILE) ;
   DCL-DS XMLBLOBFILE;
     XMLBLOBFILE_NL UNS(10);
     XMLBLOBFILE_DL UNS(10);
     XMLBLOBFILE_FO UNS(10);
     XMLBLOBFILE_NAME CHAR(255) CCSID(*JOBRUNMIX);
   END-DS XMLBLOBFILE;

//*DCL-S XMLCLOBFILE SQLTYPE(XML_CLOB_FILE) ;
   DCL-DS XMLCLOBFILE;
     XMLCLOBFILE_NL UNS(10);
     XMLCLOBFILE_DL UNS(10);
     XMLCLOBFILE_FO UNS(10);
     XMLCLOBFILE_NAME CHAR(255) CCSID(*JOBRUNMIX);
   END-DS XMLCLOBFILE;

//*DCL-S XMLDBCLOBFILE SQLTYPE(XML_DBCLOB_FILE) ;
   DCL-DS XMLDBCLOBFILE;
     XMLDBCLOBFILE_NL UNS(10);
     XMLDBCLOBFILE_DL UNS(10);
     XMLDBCLOBFILE_FO UNS(10);
     XMLDBCLOBFILE_NAME CHAR(255) CCSID(*JOBRUNMIX);
   END-DS XMLDBCLOBFILE;

The subfields of these data structures are all used the same way:

_NL Length of the IFS path
_DL Not used
_FO File operation
_NAME Path of the file in the IFS

These data structures are all used the same way. If you want to learn more see Creating a XML file in just one step using SQL.

IBM documentation (included XML host types too)

This article was written for IBM i 7.3, and should work for some earlier releases too.

10 comments:

ScottSeptember 26, 2018 at 12:31 PM
much needed with modern data set processing
Jon ParisSeptember 27, 2018 at 10:40 AM
"I fail to understand why RPG would use different binary types than SQL, but it does."

The word "binary" in SQL means something completely different from RPG. RPG used the type "binary" long before SQL so if there's any blame to be handed out ... . RPG's type B is a legacy data type from the S/38 days that mapped to the database's binary data. Mostly it was used for integers but RPG permitted implied decimal places. With the advent of RPG IV we introduced true integers (signed - type I and unsigned - type U).

SQL binary is not for numbers. It is for the storage of bit sensitive information that is not to be subject to code page conversion. For images etc. you would normally use a BLOB for the same purpose but binaries could I guess be used for small jpegs or anything really that is bit sensitive and not a character representation.
sabarishSeptember 30, 2018 at 8:36 AM
Please can you give me sql equivalent of RPG variable, as I am not able to create a sql procedure for a rpgle sub procedure in service program which returns an indicator

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.