• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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.

    RELATED STORIES

    Retrieve Column Descriptions in your ADO Client/Server Applications

    SQL Goodies in DB2 for i5/OS V5R4, Part 2

    Use SQL Descriptors To Extend DB2 For i Database Applications



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    PERFSCAN

    Revolutionary Performance Management Software

    At Greymine, we recognize there is a void in the IT world for a dedicated performance management company and also for a performance management tool that’s modern, easy to use, and doesn’t cost an arm and a leg. That’s why we created PERFSCAN.

    PERFSCAN is designed to make your job easier. With revolutionary technology, an easy-to-read report and graphics engine, and real time monitoring, tasks that used to take days can now take minutes. This means you will know your system better and will be able to provide better service to your customers.

    OUR FEATURES

    PERFSCAN is full of robust features that don’t require you to take a three-day class in order to use the product effectively.

    Customizable Performance Reporting

    Whether you are troubleshooting a major system problem or simply creating a monthly report, PERFSCAN lets you select any combination of desired performance metrics (CPU, Disk, and Memory).

    User Defined Performance Guidelines

    No matter if you are a managed service provider managing complex systems in the cloud or a customer analyzing your on-premises solution, PERFSCAN gives you the flexibility to define all mission critical guidelines how they need to be.

    Understanding The Impact Of Change

    Tired of all the finger pointing when performance is suffering? PERFSCAN’s innovative What’s Changed and Period vs. Period analysis creates a culture of proof by correlating known environmental changes with system performance metrics.

    Comprehensive Executive Summary

    Creating performance graphs is easy. Understanding what they mean is another thing. With one mouse click, PERFSCAN includes an easy-to-understand executive summary for each core metric analyzed.

    Combined Real-Time Monitor And Performance Analysis Tool

    With PERFSCAN’s combined built in enterprise real-time monitor and historical performance analysis capability, you will always know how your mission-critical systems are performing.

    Cloud Performance Reporting Is Easy

    Managing performance for production systems in the cloud can be a black hole to many system administrators. The good news is PERFSCAN analyzes all core metrics regardless of the location. That’s why MSPs and customers love PERFSCAN.

    Detailed Job Analysis

    PERFSCAN shows detailed top job analysis for any desired period. All metrics are displayed in two ways: Traditional Report and Percentage Breakdown Pie Chart. This toggle capability instantly shows the jobs using the most system resources.

    Save Report Capability

    Your boss lost the report you gave to him on Friday. Now what do you do? With PERFSCAN’s save report capability, any report can be retrieved in a matter of seconds.

    Professional PDF Reporting With Branding

    Creating professional looking reports for your customers has never been easier with PERFSCAN. Branding for our partners and service provider customers is easy with PERFSCAN.

    Check it out at perfscan.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Maxava:  FREE Webinar: Test your DR without Downtime. September 12
    HiT Software:  Ritmo/i leverages IBM i ODBS protocol. Download FREE Trial!
    Townsend Security:  Download eBook "Encryption Key Management Simplified" now!

    More IT Jungle Resources:

    System i PTF Guide: Weekly PTF Updates
    IBM i Events Calendar: National Conferences, Local Events, and Webinars
    Breaking News: News Hot Off The Press
    TPM @ The Reg: More News From ITJ EIC Timothy Prickett Morgan

    Capitalware Updates MQ Series Tools IBM To Divulge Power8 Processor Secrets At Hot Chips

    Leave a Reply Cancel reply

Volume 13, Number 16 -- August 21, 2013
THIS ISSUE SPONSORED BY:

CCSS
WorksRight Software
Shield Advanced Solutions

Table of Contents

  • Override Default Data Types In The SQL Descriptor
  • Join The Queue With Open Access
  • Which Job Is Filling Up My System Storage?

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Fortra Issues 20th State of IBM i Security Report
  • FNTS Launches Managed Services for Power Servers in IBM Cloud
  • Total LTO Shipped Capacity Up Slightly in 2022
  • Four Hundred Monitor, May 24
  • Update On Critical Security Vulnerability In PowerVM
  • Critical Security Vulnerability In PowerVM Hypervisor
  • IBM Power: Hosted On-Premises Or In The Cloud?
  • Guru: Watch Out For This Pitfall When Working With Integer Columns
  • As I See It: Bob-the-Bot
  • IBM i PTF Guide, Volume 25, Number 21

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2023 IT Jungle