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:

Be careful when choosing whether to specify a stored procedure name vs. command text.
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.

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