Hello everyone and welcome to this new article, this time about SQL Data Types that don’t have ILE RPG matching type. As you can see in the picture that illustrates the article, there are many SQL data types that can be defined (there are more SQL types like XML but I think they deserve their own article). I’m going to explain all of them and, as always, there will be an example program that you can copy (remember that in the upper right corner of the source code you have a button to automatically copy the source to the clipboard) and paste in you preferred editor, compile it and then test it in your own environment.
Let’s go!
SQL binary data type can be fixed o varying length (you must always type a length or the precompile will fail), can be declared as standalone host variables or in host structures.
For BINARY host variables, the length must be in the range 1 to 32766.
The declaration of:
generates this code:
dcl-s db2Binary sqltype(binary:30);
DCL-S DB2BINARY CHAR(30) CCSID(*HEX);
For VARBINARY host variables, the length must be in the range 1 to 32740.
The declaration of:
dcl-s db2VarBinary sqltype(varbinary:200);
generates this code:
DCL-S DB2VARBINARY VARCHAR(200) CCSID(*HEX);
SQL LOB host variables can be:
This time the precompiler will generate a data structure with two fields, one will contain the length of the data and the other will contain the data itself. The rules to create the data structure are very simple:
The length for CLOB and BLOB host variables must be in the range 1 to 16773100. The length for DBCLOB host variables must be in the range 1 to 8386550. LOB variables can be declared as standalone host variables or in host structures but are now allowed in host structure arrays. You can use LOB locators instead. The last thing to keep in mind is that LOB variables cannot be initialized.
For CLOB host variables, the length must be in the range 1 to 16773100.
The declaration of:
dcl-s db2Clob sqltype(clob:5000);
generates this code:
DCL-DS DB2CLOB;
DB2CLOB_LEN UNS(10);
DB2CLOB_DATA CHAR(5000) CCSID(*JOBRUNMIX);
END-DS DB2CLOB
For DBCLOB host variables, the length must be in the range 1 to 8386550.
The declaration of:
dcl-s db2DBClob sqltype(dbclob:1000);
generates this code:
DCL-DS DB2DBCLOB;
DB2DBCLOB_LEN UNS(10);
DB2DBCLOB_DATA GRAPH(1000);
END-DS DB2DBCLOB;
For BLOB host variables, the length must be in the range 1 to 16773100.
The declaration of:
dcl-s db2Blob sqltype(blob:1000000);
generates this code:
DCL-DS DB2BLOB;
DB2BLOB_LEN UNS(10);
DB2BLOB_DATA CHAR(1000000) CCSID(*HEX);
END-DS DB2BLOB
As you have seen in the previous section, the maximum length for a LOB host variable is 16MB for CLOB and BLOB and 8MB for DBCLOB. What happens if you need to hold more than this? Or maybe you don’t want to process the whole LOB but rather a piece at a time. For these cases LOB locators can be used.
A LOB locator is a host variable that holds a pointer or a reference to where the LOB value is physically stored in the database server. With LOB locators you can manipulate very large objects in programs without having to store the entire LOB value in a very large host variable. You can use the LOB locator in the program to issue database operations such CONCAT, SUBSTR, doing assignments or searching the LOB supplying the LOB locator as input.
It is important to remark that a LOB locator represents a value not a row or location in the database.
The declaration of:
dcl-s db2ClobLocator sqltype(clob_locator);
generates this code:
DCL-S DB2CLOBLOCATOR UNS(10);
The declaration of:
dcl-s db2DBClobLocator sqltype(dbclob_locator);
generates this code:
DCL-S DB2DBCLOBLOCATOR UNS(10);
The declaration of:
dcl-s db2BlobLocator sqltype(blob_locator);
generates this code:
DCL-S DB2BLOBLOCATOR UNS(10);
LOB files are used to transfer data from and to IFS files.
They represent a file, they do not contain it. You can use database queries, updates and inserts to store or to retrieve LOB values without the need of routines to read and write files. LOB files are allowed in host data structures and cannot be initialized.
LOB Files variables can be CLOB, DBCLOB or BLOB data type. The precompiler will generate a data structure with subfileds relative to the file using suffixes as follow:
The precompiler will also generate four constants:
The declaration of:
dcl-s db2ClobFile sqltype(clob_file);
generates this code:
DCL-DS DB2CLOBFILE;
DB2CLOBFILE_NL UNS(10);
DB2CLOBFILE_DL UNS(10);
DB2CLOBFILE_FO UNS(10);
DB2CLOBFILE_NAME CHAR(255) CCSID(*JOBRUNMIX);
END-DS DB2CLOBFILE;
The declaration of:
dcl-s db2DBClobFile sqltype(dbclob_file);
generates this code:
DCL-DS DB2DBCLOBFILE;
DB2DBCLOBFILE_NL UNS(10);
DB2DBCLOBFILE_DL UNS(10);
DB2CLOBFILE_FO UNS(10);
DB2DBCLOBFILE_NAME CHAR(255) CCSID(*JOBRUNMIX);
END-DS DB2DBCLOBFILE;
The declaration of:
dcl-s db2BlobFile sqltype(blob_file);
generates this code:
DCL-DS DB2BLOBFILE;
DB2BLOBFILE_NL UNS(10);
DB2BLOBFILE_DL UNS(10);
DB2BLOBFILE_FO UNS(10);
DB2BLOBFILE_NAME CHAR(255) CCSID(*JOBRUNMIX);
END-DS DB2BLOBFILE;
It’s time to test some of the sql data types that has been showed. Here you are a program that will work with a clob variable, two clob files and two blob files.
First of all it will use a clob file to save a very simple html code in the IFS that you can open with a browser.
Next it will use another clob file to read a json file and then assign its content to a clob variable. After that the clob variable will be processed to show some information on the screen.
Finally it will use a blob file to read a jpg file and copy it to another blob file in the IFS.
LOBTEST:
**free
ctl-opt option(*nodebugio:*srcstmt:*nounref);
dcl-c CRLF x'0d25'; //Carriage Return + Line Feed
//JSON and HTML variables
dcl-s db2Clob1 sqltype(clob:10000);
// The compiler will generate this:
// dcl-ds db2Clob1
// db2Clob1_len uns(10);
// db2Clob1_data char(10000);
// end-ds;
dcl-s json_name varchar(50);
dcl-s json_job varchar(100);
dcl-s json_city varchar(25);
dcl-s json_pos1 uns(5);
dcl-s json_pos2 uns(5);
dcl-s htmlVar varchar(10000);
dcl-s ifsFile1 sqltype(clob_file);
// The compiler will generate this:
// dcl-ds ifsFile inz;
// ifsFile1_nl uns(10);
// ifsFile1_dl uns(10);
// ifsFile1_fo uns(10);
// ifsFile1_name char(255);
// end-ds;
//
// _name = Name of the file (full path)
// _nl = Length of the name of the file
// _fo = Operation to be done in the file (see constants below)
// _dl = Not used
//
// The compiler adds this constants too:
// SQFRD = 2 = Read file
// SQFCRT = 4 = Create file
// SQFOVR = 8 = Overwrite file
// SQFAPP = 16 = Append to file
dcl-s ifsFile2 sqltype(clob_file) ccsid(1208);
//Binary files variables
dcl-s blobFile1 sqltype(blob_file);
dcl-s blobFile2 sqltype(blob_file);
exec sql set option commit = *none, closqlcsr = *endmod;
ifsFile1_name = '/home/asalcedo/whoami2.html';
ifsFile1_nl = %len(%trim(ifsFile1_name));
ifsFile1_fo = SQFOVR;
htmlVar = '<html>' + CRLF +
'<h1>Who am i?</h1>' + CRLF +
'<p><strong>Name:</strong> Antonio Salcedo</p>' + CRLF +
'<p><strong>Job:</strong> IBM i Teacher / Senior ILE RPG Programmer</p>' + CRLF +
'<p><strong>City:</strong> Madrid</p>' + CRLF +
'</html>';
exec sql set :ifsFile1 = :htmlVar;
ifsFile2_name = '/home/asalcedo/whoami.json';
ifsFile2_nl = %len(%trim(ifsFile2_name));
ifsFile2_fo = SQFRD;
exec sql set :db2Clob1 = :ifsFile2;
//This is only for educational purposes only. It's about how to read a clob file into a clob
// variable. It is not intended to process a json file. Use data-into or yajl from Scott Klement to
// do it.
//
// As the structure of the json is known we are going to process it with
// %scan and %subst searching for '"' and ':'.
json_pos1 = %scan(':':db2Clob1_data:*natural);
json_pos2 = %scan('"':db2Clob1_data:json_pos1+2:*natural);
json_name = %subst(db2Clob1_data:json_pos1+2:(json_pos2) - (json_pos1 + 2):*natural);
json_pos1 = %scan(':':db2Clob1_data:json_pos2:*natural);
json_pos2 = %scan('"':db2Clob1_data:json_pos1+2:*natural);
json_job = %subst(db2Clob1_data:json_pos1+2:(json_pos2) - (json_pos1 + 2):*natural);
json_pos1 = %scan(':':db2Clob1_data:json_pos2:*natural);
json_pos2 = %scan('"':db2Clob1_data:json_pos1+2:*natural);
json_city = %subst(db2Clob1_data:json_pos1+2:(json_pos2) - (json_pos1 + 2):*natural);
snd-msg 'Employee ' + json_name %target(*self:2);
snd-msg 'that works as ' + json_job %target(*self:2);
snd-msg 'lives in ' + json_city %target(*self:2);
//Binary files copy
blobFile1_name = '/home/asalcedo/Iron Maiden.jpg';
blobFile1_nl = %len(%trim(blobFile1_Name));
blobFile1_fo = SQFRD;
blobFile2_name = '/home/asalcedo/Iron Maiden2.jpg';
blobFile2_nl = %len(%trim(blobFile2_Name));
blobFile2_fo = SQFOVR;
exec sql set :blobFile2 = :blobFile1;
*inlr = *on;
Explanation:
Line 1: Code will be fully free.
Line 2: My standard control options keywords.
Line 4: Constant to add Carriage Return and Line Feed to a text file.
Lines 7: Declaration of a clob host variable of 10000 bytes.
Lines 13-15: Variables to hold the values of the json file.
Lines 16-17: Variables to be used with %scan and %subst.
Line 19: Variable to contain the html code that will be written to the file.
Line 21: Declaration of the first clob file to hold the html code.
Line 41: Declaration of the second clob file that will hold the json file so ccsid is set to utf-8.
Lines 44-45: Declaration of the blob files to copy the jpg.
Line 47: Change of the sql compiler options in code. No commit cycle will be used and sql cursors will be automatically closed when the module ends.
Lines 49-51: ifsFile1 is set using full path using the subfields generated by the precompiler. The file will be opened in overwrite mode.
Lines 53-58: Html code is assigned to a variable. Although it is not necessary, each line is finished with a carriage return and a line feed so you can see how to write a new line in any ifs text file.
Line 60: This is the line where DB2 writes the content of the variable htmlVar to the file ifsFile1 that points to ‘/home/asalcedo/whoami2.html’.
Lines 62-64: ifsFile2 is set to an existing file in the ifs using full path. The file will be opened to be read.
Line 66: DB2 assigns the content of the file pointed in variable ifsFile2 in the clob variable db2Clob1.
Lines 68-88: As is in the comments of the code, this is not the proper method to process a json file or json string. It is here just for educational purposes as it was another string held in the clob variable. You can see the structure of the json file below and it only has 3 fields called name, job and city. As fields are separated from values with a colon (:) the program search first for this position. Then as all the values are characters and in json are between double quotes the program search for the last double quote that finish the value. Finally using these positions (and with some addition and subtraction to calculate the starting position and exact length) and a substring the value is extracted to a variable.
Lines 86-88: A message is composed with the values of the json file and it will be show in the screen message line and in the job log using snd-msg operation code.
Lines 91-93: The first jpg file is assigned to blobFile1 and it is opened to be read. The jpg file must exist in the ifs.
Lines 95-97: The second jpg file is assigned to blobFile2 and it is opened as overwrite, that is, if the file does not exists it will be created and otherwise will be overwritten.
Line 99: DB2 makes the copy assigning the contents of blobFile1 to blobFile2.
whoami.json
{"name":"Antonio Salcedo","job":"IBM i Teacher / Senior ILE RPG Programmer","city":"Madrid"}
Running the program and looking at the job log…
If you go to the ifs and double click whoami2.html you will see something like this:
You can view the html code of the file just right clicking in the browser and selecting “Show source code” but I’m gonna use edtf command to show you the code from inside the IBM i.
I hope you liked this article and found it entertaining. For me it was a lot while I was writing it.
Do not hesitate to comment if you have questions or simply want to leave your opinion.
Thank you and see you next time.
One Response