Using ‘New’ SQL Data Types in RPG

March 17, 2004 Raymond Everhart

As RPG programmers, we have become very comfortable with character, zoned, and packed decimal data types. Occasionally, when dealing with APIs or subprocedures, we’ve been forced to dabble with binary numbers or variable length strings. More recently some of us have started taking advantage of the strengths of the date, time, and timestamp data types supported in RPG IV. And just when we think we’ve got it all covered, we find out that DB2 supports even more data types.

DB2 supports data types that are seldom used by iSeries programmers. However, since C or Java programmers may create tables (or files) on the iSeries, we need to know how to interact with these data types.

The following tables list the data types that can be used in DB2 on the iSeries.

Now that we know just how much we don’t know, let’s look at how to define these data types in RPG and how to convert to and from these data types using SQL scalar functions.

DEFINING SQL DATA TYPES IN RPG

Let me start by saying that the SQL precompiler does a lot of the hard work for you. When you use embedded SQL to put data into your application, you must define “host variables” to contain the values selected in your SQL statement. When using native I/O, coding an F-spec adds the fields and their definitions into the program, during the compile process. Embedded SQL does not require an F-spec. Instead, host variables are defined in D-specs, like any other variable in RPG. Host variables can be defined as stand-alone fields, data structures, externally described data structures or multiple occurrence data structures.

In the example below, a single host variable has been defined. When the SQL statement runs, the number of records in the QADBXREF file will be placed into the variable RecordCount. In this case, RecordCount is defined as a packed decimal value with a precision of 9,0.

.....DName+++++++++++ETDsFrom+++To/L+++IDc.Keywords
     D RecordCount     S              9  0

     C/Exec SQL
     C+ Select Count(*) into :RecordCount from QADBXREF
     C/End-Exec

Now let’s make a change. Notice that the RecordCount variable is now defined as a zoned decimal value with a precision of 7,0. This code will also work just fine.

.....DName+++++++++++ETDsFrom+++To/L+++IDc.Keywords
     D RecordCount     S              7S 0

     C/Exec SQL
     C+ Select Count(*) into :RecordCount from QADBXREF
     C/End-Exec

Now the RecordCount variable is defined as a four-byte Integer. This code will also work.

.....DName+++++++++++ETDsFrom+++To/L+++IDc.Keywords
     D RecordCount     S              5I 0

     C/Exec SQL
     C+ Select Count(*) into :RecordCount from QADBXREF
     C/End-Exec

The point that I am trying to make is that if the data types are not the same, the program will work to convert the value correctly, if possible. The data types do not have to be the same, but they do need to be compatible. If the value of one data type can be assigned to another without the use of a scalar function, the data types are compatible. For example, a small integer value can be assigned to a decimal variable, a character value can be assigned to a character large object, and a correctly formatted character value can be assigned to a date variable. Just like in RPG, you can add a zoned decimal to an integer and store the results in a packed decimal field. You don’t have to worry about the differences; RPG will handle all of the data type conversions necessary. So if a column in your table is defined as numeric, but you define it as an integer in RPG, the conversion will be handled automatically. The most important thing to watch for is that you keep data from being truncated as it moves from one data type to another.

The source code listed below shows a sample RPG definition for each SQL data type. Care should be taken not to exceed the maximum length, as shown in the table above, or change the lengths of the integer or float data types. Large objects require the SQLTYPE keyword, which is processed by the SQL precompiler.

.....DName+++++++++++ETDsFrom+++To/L+++IDc.Keywords

      * Numeric Definitions
     D SmallInteger    S              5I 0
     D SmallInteger    S              4B 0
     D Integer         S             10I 0 
     D Integer         S              9B 0
     D BigInteger      S             20I 0
     D Decimal         S             31P 9
     D Numeric         S             31S 9
     D Float_Single    S              4F 
     D Float_Double    S              8F

      * Character Definitions
     D Char            S          32766A
     D VarChar         S          32740A   Varying

      * Graphic Data Definitions
     D Graphic         S          16383G
     D VarGraphic      S          16370G   Varying

      * Large Object Definitions
     D CLOB            S                   SQLTYPE(CLOB:500)
     D DBCLOB          S                   SQLTYPE(DBCLOB:500)
     D BLOB            S                   SQLTYPE(BLOB:500)

      * Date/Time Definitions
     D Date            S               D
     D Time            S               T
     D TimeStamp       S               Z

For performance reasons, it makes sense to avoid unnecessary data type conversions, but the system will convert compatible data types nonetheless.

CHANGING BETWEEN DATA TYPES IN SQL

It seems as if programmers spend a lot of their time changing data from one type to another. A value stored as a number is converted to a date, an invoice amount is converted to a character string, so it can be inserted into a body of text, and on and on. SQL has built-in functions to accomplish these tasks as well. These functions allow you to control the length, precision, and data type of your results. The rest of this article examines the use of scalar functions to convert between data types. However, another option is available within SQL. The CAST expression lets you perform the same data type conversions as individual scalar functions. Once you master the scalar functions, you can decide which you prefer.

CONVERTING TO CHARACTER DATA

The first method of converting data to the character data type is the CHAR() function. The results of the CHAR() function depend on the data type being converted.

The CHAR() function will convert the following:

The table below shows the resulting length of the Char() function based on the source data type.

The table below shows some samples of the CHAR() function.

Another function for converting to character data is the DIGITS() function. The DIGITS() function works with integer and decimal data types only. The value returned is an absolute value that is right-justified with leading zeros and no decimal separator. This function works well when you are formatting a “flat file” for export or when you want to append a sequence number to a character value. There is no equivalent CAST expression for the DIGITS() function. The following table shows the length and sample uses of the DIGITS() function.

CONVERTING TO DATE/TIME DATA TYPES

The DATE() function returns a date from a value. The value supplied to the DATE() function can be represented in a number of ways. The value can be a timestamp, a date, a positive number, or a string. If the value supplied is a timestamp, the returned value is the date portion of the timestamp. If the supplied value is a date, then that date is returned. If the value supplied is a number, it must represent the number of days after January 1, 0001. If the value supplied is a string, it must be a valid date string or a seven-character numeric string in the form yyyynnn; where yyyy is the year and nnn is the number of days (1-366) since the beginning of the year.

DATE() EXAMPLES

The TIME() function returns a Time value from a value. The value supplied to the TIME() function can also be represented in a number of ways. The value can be a TimeStamp, Time, or string. If the value supplied is TimeStamp, the returned value is the time portion of TimeStamp. If the supplied value is a time, that time is returned. If the value supplied is a character string, that string must be a valid representation of a time value.

TIME() Examples

The TIMESTAMP() function returns a timestamp from a value. The TIMESTAMP function can accept one or two arguments. If only one argument is specified, it must be a timestamp or a 14-character representation of a timestamp. The format for the 14-character string is yyyyMMddhhmmss, where yyyy is for the year, MM is for the month, dd is for the day, hh is for the hour, mm is for the minute, and ss is for the number of seconds. If two arguments are specified, the first value must be a date or a character representation of a date value and the second value must be a date or a character representation of a time value.

TIMESTAMP() Examples

CONVERTING TO INTEGER DATA TYPES

To convert to an Integer data type use one of the following expressions:

• SmallInt()

• Integer() or Int()

• BigInt()

The syntax for these functions is the same. There is only one argument for these functions. The argument can be a number, a numeric expression, or a character-string representation of an integer, decimal, or float data type. The table below shows some examples of these functions.

CONVERTING TO NUMERIC DATA TYPES

To convert data to a numeric format, use the following functions:

• Decimal() or Dec()

• Zoned()

For these functions, the data type of the first argument determines the rest of the syntax. The first argument can be a number, a numeric expression, or a character-string representation of an integer, decimal, or float data type.

If the first argument is numeric:

• The second argument is an integer value for the desired precision for the result.

• The third argument is an integer value for the desired scale for the result.

If the first argument is a character string:

• The second argument is an integer value for the desired precision for the result.

• The third argument is an integer value for the desired scale for the result.

• The fourth argument is the character that is to be used as the decimal separator in the first argument.

The value of the precision argument is from 1 to 31. If no precision is specified, the precision is defaulted to the values listed below, based on the data type of the first argument.

The value of the scale argument is from zero to the precision specified in the second argument.

The value of the separator argument is either a comma (,) or a period (.).

The table below shows some examples of the ZONED() and DECIMAL() functions.

CONVERTING TO FLOAT DATA TYPES

To convert data to a float data type, use the following functions:

• Double() or Double_Precision()

• Float()

• Real()

The syntax for these functions is the same. There is only one argument for these functions. The argument can be a number, a numeric expression, or a character-string representation of an integer, decimal, or float data type. The table below shows some examples of the functions.

YOU ARE IN CONTROL

These functions allow you to have complete control over your data and data types. These functions can also be nested within other functions. When this approach is combined with the creation of a view, your data conversions can be defined one time for all programs or users. Another use for these functions is when you need to duplicate an existing formula coded within another language. By specifying the precision and scale of your operands, you can control the resulting value of a formula. These functions require frequent use before they become second nature to you. In the meantime, refer to your reference manuals frequently and don’t be afraid to experiment with new functions.

Raymond Everhart has over 18 years of IT experience with IBM midrange computers. He was a consultant, programmer, and educator for an IBM premier business partner in Upstate New York for 10 years, before relocating to the Dallas/Ft. Worth area in 1998. Since then, Raymond has been helping local companies implement new technologies with his consulting, programming and mentoring services. E-mail: reverhart@itjungle.com