ADO Stored Procedure Calls with MSDASQL

Microsoft ActiveX Data Object (ADO) programming model offers multiple methods of calling stored procedures. Depending on the method used, programmers might encounter one of the issues listed below. Note that regardless of the method used, the procedure must be defined to the database using a CREATE PROCEDURE statement. Common issues encountered include the following:

1.	Errors or incorrect output data when using input/output and output parameters.
2.	Errors involving parameters with data types of numeric and decimal.
3.	Incorrect resolution of unqualified procedure names.
4.	Command type adCmdStoredProc is slower than adCmdText.

1 Incorrect Values for Input/Output and Output Parameters

When using command type adCmdText or manually adding parameters to the parameter collection, ADO might not correctly describe the direction for input-output and output parameters of stored procedures. This can result in one or more of the following errors:

o	The parameter direction property is adParamUnknown (0).
o	V5R1 and later of IBM iSeries Access for Windows ODBC with ODBC 3.x application: DIAG[07S01][IBM][iSeries Access for Windows ODBC Driver (32-bit)]Invalid use of default parameter. V5R1 and later of iSeries Access for Windows ODBC with ODBC 2.x application: DIAG[S1000][IBM][Client Access Express ODBC Driver (32-bit)]Invalid use of default parameter. V4R5 and earlier of Client Access ODBC: DIAG [S1090] [IBM][Client Access Express ODBC Driver (32-bit)][IBM DB2/400 SQL]Invalid value stored in pcbValue. (10002).
o	No output value is returned (no error message).

The problem occurs because MSDASQL is not always querying Client Access for the parameter direction. Programmers must manually specify parameter direction or force ADO and MSDASQL to check for parameter direction. The sample at the bottom of this document demonstrates how to manually specify parameter direction. To force ADO to check direction, use the following syntax:

o	Command type of adCmdStoredProc (This is the ADO documented method).
o	Note: Using command type of adCmdText and ODBC Stored procedure syntax ("{ CALL MYPROC(...) }") may correctly describe parameters in some versions of ADO. .This second method appears to work correctly only with ADO 2.5, ADO 2.6, and Client Access Express V4R5 and earlier (ODBC 2.0). With ADO 2.6 and V5R1 Client Access Express ODBC (ODBC 3.x), ADO does not query the parameter direction.

Another consideration in calling stored procedures is that DB2 UDB for iSeries does not define default values for Input and InputOutput parameters. Input and InputOutput parameters must be initialized by setting their value before the Execute method is called. If this is not done, MSDASQL sets the pcbValue of SQLBindParameter to SQL_DEFAULT_PARAM. This is an invalid value (because no default is defined), and one of the error messages listed above is reported by iSeries Access for Windows.


2 Errors Involving Parameters with Data Types of Numeric and Decimal

When manually adding numeric and decimal parameters to the parameter collection, programmers may forget to specify precision and or scale. This results in one of the following errors:

o

If the "NumericScale" property is not set, then ADO defaults to a scale of 0. Any decimal digit is truncated. No error is returned.

o

If the "Precision" property is not set, ADO returns the error: "-2147217887 Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. (Microsoft OLE DB Provider for ODBC Drivers)" Note: This error is not encountered when using V4R5 and earlier of Client Access Express ODBC. Client Access Express ODBC is a 2.0 compliant driver. ADO passes the invalid precision of 0 to 2.0 drivers. Client Access Express ODBC suppress the error and uses the actual precision. With 3.x compliant drivers such as iSeries Access for Windows, ADO verifies the precision and returns an error before passing it to the underlying ODBC driver. Applications upgrading to iSeries Access for Windows must verify that precision is set correctly.

The best solution to this problem is to use the parameters refresh method and allow the driver to describe the parameter type. There is no additional overhead involved. The programmer must still specify the direction after calling refresh.


3 Incorrect Resolution of Unqualified Procedure Names

When the ADO command type is set to adCmdStoredProc, unqualified procedures may resolve to the incorrect procedure. This can result in several errors such as: Procedure not found errors; The incorrect procedure being called; Data mapping errors or application faults in the procedure.

The problems occur because ADO resolves the procedure parameters differently than specified by the SQL specification. ADO uses a catalog call to query the database for parameter information. ADO specifies the procedure name but requests that all schemas (libraries in OS/400 or i5/OS terms) be searched. The catalog call returns data sorted by Catalog, Schema (library), Name, and Ordinal. ADO uses the parameter description for the first procedure returned to build the underlying SQL statement. If a procedure with the same name but different signature (number and/or type of parameters) exists anywhere on the system, the parameter description may not match the procedure that the database resolves to.

As an example consider the following:
SQL Path: LIB1, LIB2
The following procedures exist:
LIB1.PROC1(IN INT, INOUT CHAR(10))
LIB0.PROC1(IN CHAR(10), IN INT, IN INT)
Application use ADO CommandType of adCmdStoredProc and CommandText of "PROC1".
ADO resolves the parameters to LIB0/PROC1. It builds the SQL Statement "{ call PROC1(?, ?, ?) }". DB2 UDB for iSeries resolves this call to LIB1.PROC1. Because the number of parameters is incorrect, an error is returned on the prepare.

To avoid this error, use fully qualified stored procedure names or use ADO CommandType adCmdText.


4 Command type adCmdStoredProc is slower then adCmdText

As mentioned above, command type adCmdStoredProc causes ADO to issue additional catalog calls to determine the number of parameters and their direction. This additional overhead can more than double the time needed to make a stored procedure call. To avoid the additional overhead use command type adCmdText and "native" call syntax. Call the parameters refresh method to describe the parameters, then set the parameter direction. See the sample below for details.

Microsoft Visual Basic Sample

'--Procedure used in sample.
'Create Procedure SQLINOUTRS2 (INOUT parm1 CHAR(10), OUT parm2 CHAR(10), parm3 INOUT DEC(8,2) )
'RESULT SET 2 LANGUAGE SQL BEGIN
'DECLARE C1 CURSOR FOR SELECT * FROM QIWS.QCUSTCDT;
'DECLARE C2 CURSOR FOR SELECT cusnum, current_time FROM QIWS.QCUSTCDT;
'SET parm2 = parm1;
'SET parm1 = '1234567890';
'SET parm3 = parm3 + 1;
'OPEN C1;
'OPEN C2;
'SET RESULT SETS CURSOR C1, CURSOR C2;
'End
Dim cn As New ADODB.Connection
Dim cm As New ADODB.Command
Dim rs As ADODB.Recordset

On Error GoTo dsperr
cn.Open "Provider=MSDASQL;DSN=RCHASSLH;"
Set cm.ActiveConnection = cn

'Method 1 - adCmdStoredProc
'Both parameter direction and type are described correctly.
'Slower then method 2 due to additional catalog calls.
'Unqualifed procedure names may not be resolved correctly.
cm.CommandType = adCmdStoredProc
cm.CommandText = "QGPL.SQLINOUTRS2"     'Use qualified names (Lib.proc)
cm.Prepared = True
cm.Parameters.Refresh

'Method 2 - ODBC Escape syntax
'This method peforms better than method 1
'Correctly resolves unqualified procedure names
'Programmer needs to set parameter direction for input/output and output params
''cm.CommandType = adCmdText
''cm.CommandText = "call SQLINOUTRS2(?, ?, ?)"     'ODBC Escape syntax or native
''cm.Prepared = True
''cm.Parameters.Refresh
'''Programmer must set direction for inputoutput and output parameters
''cm.Parameters(0).Direction = adParamInputOutput
''cm.Parameters(1).Direction = adParamOutput
''cm.Parameters(2).Direction = adParamInputOutput

'Method 3 - Manually adding parameters
'Not recommended.  There is no performance advantage to this method since
'the ODBC driver always prepares and describes the statement.
'Programmer must set precision for numeric and decimal data types.
''cm.CommandType = adCmdText
''cm.CommandText = "{call SQLINOUTRS2(?, ?, ?)}"     'ODBC Escape syntax or native
''cm.Prepared = True
''cm.Parameters.Append cm.CreateParameter("p1", adChar, adParamInputOutput, 10)
''cm.Parameters.Append cm.CreateParameter("p2", adChar, adParamOutput, 10)
''cm.Parameters.Append cm.CreateParameter("p3", adDecimal, adParamInputOutput, 8)
''cm.Parameters(2).Precision = 8
''cm.Parameters(2).NumericScale = 2

'For best performance prepare once (above), execute multiple times
For J = 1 To 2
Debug.Print "*** Execute " & J & " *************************************"
'Programmer must set input and inputoutput parameters.  DB2 UDB for iSeries
'does not define default values for procedure parameters.
cm.Parameters(0).Value = "MYINPUT000"
cm.Parameters(2).Value = 1.1

Set rs = cm.Execute
   
'Process any results first
Dim I As Long
I = 0
Dim NumRows As Integer
Do While rs.State = adStateOpen
   NumRows = 0
   I = I + 1
   Debug.Print "*** Result " & I & "*************************************"
   Do While Not rs.EOF And I < 20
      NumRows = NumRows + 1
      Debug.Print rs(0).Name & ": " & rs(0).Value
      rs.MoveNext
   Loop
   Debug.Print "*** Fetched " & NumRows & " on resultset " & I & " *******************************"
   Set rs = rs.NextRecordset
Loop
If rs.State = adStateOpen Then
    rs.Close
End If
Set rs = Nothing     'This must be called to get output values if there are zero results returned

'Retrieve any output parameters
Debug.Print "* Output parameters *************************************"
Dim parm As ADODB.Parameter
For Each parm In cm.Parameters
   Debug.Print "Parm " & parm.Name & ": " & 'GetDirectionEnum(parm.Direction) & " " &
               'GetDataTypeEnum(parm.Type) &
               "  Size: " & parm.Size & "   Value: " & parm.Value
Next parm
Debug.Print "*** End execute **************************************"
Next J
cn.Close
Exit Sub

dsperr:
Dim adErr As ADODB.Error
If cn.Errors.Count = 0 Then
    MsgBox Err.Number & ": " & Err.Description & vbCrLf & Err.Source
Else
    For Each adErr In cn.Errors
       MsgBox adErr.Number & ": " & adErr.SQLState & adErr.Description & vbCrLf & adErr.Source
    Next adErr
End If