Override Default Data Types In The SQL Descriptor

August 21, 2013 Hey, Mike

Note: The code accompanying this article is available for download here.

I’d like to use SQL Descriptors in an embedded SQL program to read column data into host variables. However, if my host variable type doesn’t match exactly the parameter or column data type then I receive an SQL0076 error: “DATA or INDICATOR item not correct.” How can I, for example, receive the data from a CHAR(10) column from the SQL descriptor into an RPG varying-length character 20 variable, without using an explicit CAST in the SQL statement?

–J.B.

AUTHOR’S NOTE: If you’re unfamiliar with SQL descriptors, please see the references at the end of this tip. This tip is somewhat technical and expects the reader to have knowledge of dynamic SQL, parameter markers and the SQL descriptor.

The descriptor area is primarily used to retrieve metadata information and exchange data for parameters (dynamic SQL, CALL) and columns (CURSOR) in an SQL statement. For example, when calling a stored procedure dynamically, the descriptor area can be used to retrieve information about the procedure’s parameters (name, data type, scale, precision, mode, etc). It can be used to set the parameter values for IN and INOUT parameters from host variables before calling the procedure. Likewise it can also retrieve into host variables the values of INOUT and OUT parameters after a procedure has been called. Similarly, when working with a cursor, the descriptor can return metadata about the columns in a statement (data type, column name, label text, column headings (where applicable), etc.) and it can be used to place post-fetch column values into host variables.

Dear J.B.:

When DB2 for i populates the SQL descriptor with parameter or column information, it does so based on the column or parameter attributes derived from the statement itself. For example, if you have this table definition:

CREATE TABLE TEST (ID INT PRIMARY KEY)

And you’re attempting to run this dynamic SQL statement:

SELECT MAX(ID) INTO ? FROM TEST

DB2 will automatically assign the data type of the parameter marker as an INT, based on the information it derived from the ID column definition. If you’re running this dynamic SQL in an RPG program and are expecting to fetch the result into a variable via an SQL Descriptor, then by default, SQL is expecting an INT (or 10I 0) RPG variable waiting to receive the result.

That’s fine in situations where you always know the data type you want to fetch beforehand. But what about if your program is expected to request a dynamic value from any number of columns having different data types and attributes? In this case, the ideal would be to fetch the result into a varying length character variable (defined as say 64A) that could handle the most common data types without having to do an explicit CAST. NOTE: Specialized large object or binary values will need separate/special logic.

It is possible to fetch any compatible data type from a descriptor into an RPG varying 64A host variable from an SQL descriptor without getting the SQL0076 error. The trick is to use the SET DESCRIPTOR statement to override the expected data type of the parameter or column before the prepared statement is executed. Doing this will set DB2’s expectation for how to prepare the result for use by the host program. If a column or parameter will normally return an INT to a descriptor, using SET DESCRIPTOR can be used to override the result to a BIGINT, CHAR(10) or other implicitly compatible data type.

So, the host variable will still have to match the descriptor’s column type definition, but fortunately, we can override the descriptor to have DB2 conform the parameter’s or column’s data to match the RPG variable type (instead of the other way around).

I’ll show a quick example of how to do this. The sample code is for RPG program DESC_EXT_R (Descriptor Example) can be found here. The dynamic SQL statement is:

SELECT SUM(BALDUE) 
  INTO ?
  FROM QIWS/QCUSTCDT

By default, the parameter data type will be DEC(31,2) and DB2 for i will expect an RPG host variable to match (31P 2). Instead, the RPG program will use a 20A varying length variable called ResultValue to store the result. The SET DESCRIPTOR statement is used to force DB2 to cast the result to VARCHAR(20) before giving it to the RPG variable.

Take note that since the above SELECT INTO statement can’t be handled by the PREPARE statement, I had to rewrite it as a VALUES INTO statement (because it can be dynamically prepared) as follows:

VALUES ( 
SELECT SUM(BALDUE)
  FROM QIWS/QCUSTCDT) INTO ?

This statement is held in an RPG character variable called SQL. The SQL statement is then prepared and information about the statement placed in an SQL descriptor called “Results.”

// Prepare SQL Statement
Exec SQL
    Prepare DynamicSQL
    Using Descriptor Local 'Results'
    From :SQL;

Here is where the override comes in. If you read the SQL descriptor information, it will indicate the data type for the parameter marker is DEC(31,2). So the program tells DB2 “for parameter marker number one, prepare it as a VARCHAR(20) CCSID 37.” Even though the result of the calculation is numeric, DB2 is more than happy to perform the service to CAST it to alpha. J.B., this step will solve the problem by telling DB2 to cast the data to the specific RPG data type.

Exec SQL
    Set Descriptor Local 'Results'
    Value 1                   // Parameter marker #
            Type = 12,        // VARCHAR
            DB2_CCSID = 37,
            Length = 20;      // MAXLEN=20

Specifying a data type of “12” tells DB2 to prepare the result as VARCHAR. Table 2 in the GET DESCRIPTOR SQL Reference entry shows how to map the “Type” value to a specific SQL data type. For instance, to have DB2 prepare the parameter data as a BIGINT (RPG 20I 0) you’d specify Type = 25. For FLOAT (RPG 8F), you’d use Type=6.

Once the descriptor has been overridden with this new information, it’s a matter of issuing the EXECUTE statement (against the prepared statement called DynamicSQL) while instructing the results of the execution to be placed in the SQL descriptor:

Exec SQL
    Execute DynamicSQL
    Into Sql Descriptor Local 'Results';

If everything ran as planned, the parameter marker’s value will be placed in the descriptor and can be placed in a compatible RPG variable (20A Varying):

Exec SQL
    Get SQL Descriptor 'Results'
        Value 1                  -- Parameter Marker #1
        :ResultValue=Data,       -- Copy in the data  20A Varying
        :ResultInd=Indicator;      -- Identify NULL if applicable

And that’s how to make DB2 change its data type to meet the needs of the RPG program and not the other way around. Of course it can be as versatile as you’d like. With a little extra programming, you could make all numeric data (INT, SMALLINT, DEC(8,0), NUM(5,0), etc) with a scale of zero be copied into an RPG 10I 0 variable. This way the program can easily handle various unknown data types and place them in a single compatible data type variable instead of making the RPG program have a variable defined for every possibility.

While this example only has one parameter marker, remember the parameter markers are processed within a SQL statement from left to right, top to bottom.

This SET DESCRIPTOR technique also works for fetching column data when working with a cursor. In this case, the DESCRIBE statement is used to dump information about the columns being retrieved in the cursor to an SQL descriptor. The descriptor can be overridden so that each column’s data type can be overridden to something compatible with the host program variables’ data types. This step to override the data type using SET DESCRIPTOR must be done before the first FETCH statement places information about the retrieved row in the descriptor.

The one drawback I witnessed to overriding the descriptor’s data types (when populated by a cursor) is that other information about the columns (column name, label text, etc.) is erased. If you need to process this metadata in the program, it’s best to save off the values in the descriptor before overriding the data type.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

Table of Contents

Recent Posts

Subscribe

Pages

Search