• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • The ADO Client Side Of Default Parameters And Named Arguments In DB2 For i

    February 26, 2014 Michael Sansoterra

    In Stored Procedure Parameter Defaults And Named Arguments In DB2 For i, I covered two new related features in DB2 for i 7.1 that were implemented in Technology Refresh 5 (DB2 for i Group PTF Level 18 or higher). In this tip, I’m going to cover a surprising limitation of this feature when coding in an ActiveX Data Objects (ADO) environment, whether using an IBM DB2 OLE DB provider or the iSeries Access ODBC provider.

    Recall that stored procedure parameters now support a DEFAULT keyword that can be a literal, subquery, or expression (with limitations):

    CREATE PROCEDURE DEV.CREATE_ORDER (
    IN @CUSTOMER_ID INT,
    IN @ORDER_DATE DATE DEFAULT CURRENT_DATE,
    IN @ORDER_ID INTEGER DEFAULT (NEXT VALUE for DATALIB.ORDER_ID_SEQUENCE),
    IN @ORDER_SOURCE VARCHAR(10) DEFAULT 'WEB')
    LANGUAGE SQL
    BEGIN
    . . .
    END
    

    When a default is defined for a parameter, the parameter no longer needs to be explicitly passed so that both are valid calls as shown here:

    CALL DEV.CREATE_ORDER (1,'2013-03-12',100,'ERP');
    CALL DEV.CREATE_ORDER (1);
    

    Further, you can explicitly request the parameter’s default definition be used.

    CALL DEV.CREATE_ORDER (1,default,default,default);
    CALL DEV.CREATE_ORDER (1,default,default,'DATA ENTRY');
    

    Finally, parameters can now be named on the CALL statement using a special syntax:

    CALL DEV.CREATE_ORDER (@Customer_Id=>1,@OrderSource=>'DATA ENTRY')
    

    In a CALL statement, parameters only need to be named if there is no default or if the default should be overridden to a different value.

    All of this functionality carries over into the world of client-side technologies. However, there are a few things to beware with ADO:

    1. Be careful when choosing whether to specify a stored procedure name vs. command text.
    2. There is not currently a way to set a parameter value to its “default” value.

    Consider the following procedure definition that will perform an order search depending on what parameters are passed in:

    CREATE OR REPLACE PROCEDURE ORDER_SEARCH (
    IN  ORDER_NUMBER  INT   DEFAULT NULL,
    IN  START_DATE	DATE  DEFAULT NULL,
    IN  END_DATE  	DATE  DEFAULT NULL,
    IN  CUSTOMER_NAME VARCHAR(50) DEFAULT NULL,
    IN  ORDER_TYPE	SMALLINT  DEFAULT NULL,
    IN  ORDER_CLASS   SMALLINT  DEFAULT NULL,
    IN  ORDER_VALUE   DEC(19,4) DEFAULT NULL,
    IN  ITEM_NUMBER   CHAR(15)  DEFAULT NULL,
    IN  ITEM_NUMBER_2 CHAR(15)  DEFAULT NULL,
    IN  ITEM_NUMBER_3 CHAR(15)  DEFAULT NULL,
    IN  CITY          VARCHAR(50)   DEFAULT NULL,
    IN  STATE_CODE	CHAR(2)  DEFAULT NULL,
    IN  POSTAL_CODE   VARCHAR(10)   DEFAULT NULL
    )
    LANGUAGE SQL
    DYNAMIC RESULT SETS 1
    BEGIN
    	/* Dynamic SQL Code Goes Here */
    END
    

    Commonly to access this procedure, client-side ADO programmers will use a command object with a command type of stored procedure. Further, executing the command object’s .Parameters.Refresh method dynamically populates all of the parameter definitions such that the developer doesn’t have to define each parameter individually. This makes coding much easier, especially if a procedure has a large number of parameters with the tradeoff of a performance hit to query the database for the procedure’s parameter definitions.

    Because of the large number of parameters, it would be fantastic to use .Parameters.Refresh to bring all of the parameter definitions into the client without having to define them individually. Further, when calling the procedure, it would be ideal to only populate the parameter values that a user enters and just leave the remaining parameters to their defined default.

    But I’m here to tell you that this is not possible in ADO code. The .Parameters.Refresh method will auto-populate all of the parameter definitions, but each parameter must be populated with a value or the call to the procedure will fail. I confirmed this behavior with IBM‘s Lorie DuBois.

    For example, I issued a cmd.Parameters.Refresh and supplied just the one parameter the user entered while expecting the defined defaults to be used for the remaining parameters. The VBA code (using OLE DB or ODBC) is very simple:

    With cmd
    	.ActiveConnection = conn
    	.CommandText = "ORDER_SEARCH"
    	.CommandType = adCmdStoredProc
    	.Parameters.Refresh
    	.Parameters("ITEM_NUMBER").Value="BIKE_26IN"
    	Set rs = .Execute
    ...
    

    Disappointingly, an SQL0365 error for both ODBC and IBMDASQL is returned:

    SQL0365 - Use of extended indicator variable value not valid.
    (OS/400 )
    
    Cause . . . . . : The value of DEFAULT or UNASSIGNED was used
    for an extended indicator variable in a context in which it
    is not allowed. These values are only allowed in INSERT and
    UPDATE statements.
    Recovery . . . : Change the extended indicator variable to an
    allowable value for the context in which it was used. Try the
    request again.
    

    As Lorie told me, the OLE DB/ODBC providers are passing unassigned extended indicator values instead of a default value. Further, in ADO 2.6 and above, the ADO command object has a NamedParameters property that, when set to true, is supposed to allow parameter name matching, instead of ordinal numbers. However, even setting this property to true didn’t help with IBMDASQL or the ODBC driver. I’m guessing it works with SQL Server named parameters though!

    So the lesson is you will have to do full coding as stored procedure parameter defaults do not benefit in the client side code. The coding options are:

    Either, when coding using a stored proc command type, assign each parameter a value. However, if a parameter default is an expression, there is no way to access it with this method.

    Or, when coding using the command text command type, build the complete CALL statement text manually supplying only the parameters you need, and the remaining defaults will take effect. For instance:

    CALL ORDER_SEARCH (ITEM_NUMBER=>'BIKE_26IN')
    

    Author’s Note: Special thanks to Lorie DuBois for helping me understand this behavior.

    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 STORY

    Stored Procedure Parameter Defaults And Named Arguments In DB2 For i



                         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
    Midrange Dynamics North America

    Git up to speed with MDChange!

    Git can be lightning-fast when dealing with just a few hundred items in a repository. But when dealing with tens of thousands of items, transaction wait times can take minutes.

    MDChange offers an elegant solution that enables you to work efficiently any size Git repository while making your Git experience seamless and highly responsive.

    Learn more.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    LANSA:  Webinar: Preserving your IBM i investment. February 27, 11 am CT / 12 pm ET
    System i Developer:  Upgrade your skills at the RPG & DB2 Summit in Dallas, March 18-20.
    Northeast User Groups Conference:  24th Annual Conference, April 7 - 9, Framingham, MA

    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 @ EnterpriseTech: High Performance Computing Industry News From ITJ EIC Timothy Prickett Morgan

    Judge Gives Oracle Partial Victory in Rimini Case Power Systems Coming To The SoftLayer Cloud

    Leave a Reply Cancel reply

Volume 14, Number 4 -- February 26, 2014
THIS ISSUE SPONSORED BY:

CCSS
WorksRight Software
Northeast User Groups Conference

Table of Contents

  • The Case Of The IBM Systems Director And RBAC
  • The ADO Client Side Of Default Parameters And Named Arguments In DB2 For i
  • Auto-Answering Record Lock Errors And More On Returning An IBM i Box

Content archive

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

Recent Posts

  • Public Preview For Watson Code Assistant for i Available Soon
  • COMMON Youth Movement Continues at POWERUp 2025
  • IBM Preserves Memory Investments Across Power10 And Power11
  • Eradani Uses AI For New EDI And API Service
  • Picking Apart IBM’s $150 Billion In US Manufacturing And R&D
  • FAX/400 And CICS For i Are Dead. What Will IBM Kill Next?
  • Fresche Overhauls X-Analysis With Web UI, AI Smarts
  • Is It Time To Add The Rust Programming Language To IBM i?
  • Is IBM Going To Raise Prices On Power10 Expert Care?
  • IBM i PTF Guide, Volume 27, Number 20

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 © 2025 IT Jungle