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:
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.