No Automatic Casting for Char

Published: June 28, 2006

by Howard Arner

Recently, I did a Web services project to integrate a legacy iSeries system with a new GUI and a new Web-based interface. When programming the system, I needed to write a stored procedure to take incoming data and store it in the both the legacy program's data tables and our "new" database. This new database was still on the iSeries, and was just new in the sense that it has more and varied fields and capabilities that the previous system. I found an interesting iSeries feature and would like to take the opportunity to present the potential problem with it and give you a work around.

Part of the incoming data from our Web service was text that described a problem being reported. In the previous system, users were allowed to enter lines of text that were stored in a file associated with the problem record. The file contained a problem ID, text line number and then a 66 character field to hold that line of text. In the new system, we opted to store the text data in a CLOB object so as not to limit the amount of text information. This system was going to serve several thousand potential users and they would be rolled out department by department, therefore in order to perform the rollout we had to keep the green screen application updated and in sync with the new version of the system. I elected to write a stored procedure that would serve two purposes: Store the data in the "new" target tables and then update the legacy system. I won't show you the entire procedure, but let's recreate the situation so I can walk you through the problem.

Here are the create table statements required to make the files referred to in these examples:

/*create NTEST2 to represent new problem file*/

CREATE TABLE SQLBOOK.NTEST2
	(ID INTEGER NOT NULL, TDATA CLOB);

/*create NTEST3 to represent legacy text file*/

CREATE TABLE SQLBOOK.NTEST3
	(ID INTEGER NOT NULL,
	 LINENUM INTEGER NOT NULL,
	 LINETEXT CHAR(66) NOT NULL,
	 PRIMARY KEY(ID,LINENUM));

Here is my first attempt at programming the procedure:

/*vID and vText represent the ID of the problem and the 
problem text only*/

CREATE PROCEDURE sqlbook.clobtest1
(vID  IN INTEGER ,
 vText  IN CLOB)
 LANGUAGE SQL 
 Modifies SQL DATA
BEGIN
DECLARE CL INTEGER;
DECLARE T INTEGER DEFAULT 1;
DECLARE i INTEGER DEFAULT 1;
/*Write data to new file*/

INSERT INTO sqlbook.ntest2 VALUES (vid,vtext);

/*Get string length and update legacy file*/

SET CL=LENGTH(vText);
WHILE T<=CL DO
	INSERT INTO sqlbook.ntest3 VALUES
		(vid,i,SUBSTRING(vText,T,66));
	SET T=T+66, i=i+1;
END WHILE;
END

Now, I go to SQLThing or Operations Navigator and execute the following statement:

CALL SQLBOOK.CLOBTEST1 (2,'this is some really long test that I need to pass you what do you think of it I do not know nor kare so that is all good men must come to the aid.');

I know the sample text is a lot of blather, but it will become clear in a moment. What is supposed to happen is that while the variable T, initialized to 1, is less than the variable CL, set to the length of the CLOB object, the program should substring the CLOB starting at position 1 with a length of 66 and write that substring to the file NEST3. Next, the program increments the variable I to the next line number and the position variable T to the value of 67, T+66. The program loops and then writes the second line which is the substring from position 67 for the next 66 characters. Finally, the program attempts to write the last line by making a substring from position 133 and then next 66 characters.

However, there are only 14 characters left in the string and we are specifying to read 66; one would assume that this would return a string of the first 14 characters and then pad the result with blank characters. However, this is not the case as the substring function returns just the fist 14 characters and does not pad the string because the substring function returns the data type VARCHAR not CHAR. Now, it doesn't seem like this should present a problem, as we are inserting into a CHAR 66 field, but it does. See, the internally generated program does not clear the string variable that it uses to hold the result of the substring and write it to the NTEST3 file; and by not clearing the internal variable the results of the last operation, a full 66 character string are still there. The program uses a memory move to put just the 14 characters of the resultant varchar from the substring operation into the filled 66 character variable. Your variable now contains the characters you desire along with garbage left over from the previous operation.

Here is a screen shot of the data written to the legacy table NTEST3, which demonstrates the problem. You can recreate this by using the statement:

SELECT * FROM SQLBOOK.NTEST3

Fixing the Problem

In order to fix this, you need to cast the result of the substring operation to the appropriate data type. A side effect of the cast operation is that it will append blank characters to the end of the third substring operation and thereby overwrite any data left in the buffer. Here is the revised procedure that correctly writes the data:

CREATE PROCEDURE sqlbook.clobtest2
(vID  IN INTEGER ,
 vText  IN CLOB)
 LANGUAGE SQL 
 Modifies SQL DATA
BEGIN
DECLARE CL INTEGER;
DECLARE T INTEGER DEFAULT 1;
DECLARE i INTEGER DEFAULT 1;
/*Write data to new file*/

INSERT INTO sqlbook.ntest2 VALUES (vid,vtext);

/*Get string length and update legacy file*/

SET CL=LENGTH(vText);
WHILE T<=CL DO
	INSERT INTO sqlbook.ntest3 VALUES
		(vid,i,CAST(SUBSTRING(vText,T,66) AS CHAR(66)));
	SET T=T+66, i=i+1;
END WHILE;
END

Once this version of the procedure is created, simply re-execute the call to the stored procedure with the following code:

CALL SQLBOOK.CLOBTEST2 (3,'this is some really long test that I need to pass you what do you think of it I do not know nor kare so that is all good men must come to the aid.');

Now execute the following SQL statement and view the correctly written text:

SELECT * FROM SQLBOOK.NTEST3;

Notice that in the figure showing the output of the select statement, the text is correctly written for the last line and no longer contains the data from the substring operation.

The moral of this story: never assume. No automatic casting is performed when attempting to write from a VARCHAR to a CHAR column, so you need to ensure that you perform the CAST operation in order to clear any byte droppings from the table.

Howard Arner is a consultant that primarily writes .NET programs that keep all of their data on the industrial strength System i platform. You can contact him at hfarner@sqlthing.com or visit his site at www.SQLThing.com to learn more about SQL, purchase his books, or download free products and code.