HOWTO: Determine How ADO Will Bind Parameters

• ActiveX Data Objects (ADO), versions 1.0, 1.5

SUMMARY

Sometimes it is desirable to determine how ADO will bind stored procedure parameters. This article provides a Visual Basic application that returns the parameter name, type, size and direction from a chosen stored procedure.

MORE INFORMATION

The following project has a list box and a command button on the start up form. All results display in the Debug window. Note that code is supplied to use either Microsoft SQL Server or Oracle databases. You must modify the connection string and comment or uncomment the lines that create the SQL string.

NOTE: You need to acquire and install the Microsoft Data Access Components (MDAC) version 1.5 stack for the sample in this article. Please refer to the article listed in REFERENCES section for information on installing MDAC 1.5. MDAC 1.5 contains ADO 1.5 and the Microsoft ODBC Driver for Oracle version 2.0.

1. Open a new project in Visual Basic 5.0. Form1 is created by default.

2. Place a list box and a command button control on the new form.

3. On the Project menu, select References and then select Microsoft ActiveX Data Objects 1.5. (This was installed from the MDAC 1.5 stack.)

4. Place the following code in the General Declarations section of Form1:

         Dim Conn As New ADODB.Connection
         Dim Rs As ADODB.Recordset
   

         Private Sub Form_Load()
            Command1.Caption = "Start"
         End Sub
   
         Private Sub Command1_Click()
            Dim strSQL As String
            Form1.MousePointer = vbHourglass
   
            ' Adjust connection parameters for your server.
            'SQL Server Connection
            Conn.Open "MySQLServer", "sa", ""  'SQL Server Connection
            StrSQL = "SELECT Name FROM sysobjects WHERE Type = 'p'"
   
            'Oracle Connection
            'Conn.Open "MyOracle", "MyUid", "MyPassword"
            'strSQL = "SELECT Distinct Name FROM user_source"
   
            Set Rs = New ADODB.Recordset
            Rs.Open strSQL, Conn, adOpenStatic, adLockReadOnly
   
            Do While Not Rs.EOF
                List1.AddItem Rs(0)
                Rs.MoveNext
            Loop
   
            Form1.MousePointer = vbNormal
   
         End Sub
   
         Private Sub List1_Click()
   
            ListADOParameters (Trim(List1.Text))
   
         End Sub
   
         Sub ListADOParameters(strStoredProcedureName As String)
   
         Dim Cmd As New ADODB.Command
         Dim P As ADODB.Parameter
         Dim i As Integer
   
            Debug.Print "-------------Starting---------------"
            Set Cmd.ActiveConnection = Conn
            Cmd.CommandText = strStoredProcedureName
            Cmd.CommandType = adCmdStoredProc
            Cmd.Parameters.Refresh
   
            For i = 0 To Cmd.Parameters.Count - 1
               Set P = Cmd.Parameters(i)
   
               'Returns a string like this:
            'Parameter[0] is [INID] with ADO DataType adNumeric,
            'Size is 0, Direction is Input.
   
               Debug.Print "Parameter[" & i & "] is [" & P.Name _
                 & "] with ADO DataType " _
                 & GetDataTypeEnum(P.Type) & ", Size is " _
                 & P.Size & ", Direction is " & GetDirectionEnum(P.Direction)
   
            Next i
   
         End Sub
   
         Function GetDirectionEnum(lngDirectionEnum As Long) As String
         Dim strReturn As String
            Select Case lngDirectionEnum
               Case 1: strReturn = "Input"
               Case 2: strReturn = "Output"
               Case 3: strReturn = "Input Output"
               Case 4: strReturn = "Return Value"
            Case Else:
               strReturn = "Unknown DirectionEnum of " & lngDirectionEnum _
                 & " found."
            End Select
            GetDirectionEnum = strReturn
   
         End Function
   
         Function GetDataTypeEnum(lngDataTypeEnum As Long) As String
         'Given ADO data-type constant, returns readable constant name.
         Dim strReturn As String
            Select Case lngDataTypeEnum
               Case 0: strReturn = "adEmpty"
               Case 16: strReturn = "adTinyInt"
               Case 2: strReturn = "adSmallInt"
               Case 3: strReturn = "adInteger"
               Case 20: strReturn = "adBigInt"
               Case 17: strReturn = "adUnsignedTinyInt"
               Case 18: strReturn = "adUnsignedSmallInt"
               Case 19: strReturn = "adUnsignedInt"
               Case 21: strReturn = "adUnsignedBigInt"
               Case 4: strReturn = "adSingle"
               Case 5: strReturn = "adDouble"
               Case 6: strReturn = "adCurrency"
               Case 14: strReturn = "adDecimal"
               Case 131: strReturn = "adNumeric"
               Case 11: strReturn = "adBoolean"
               Case 10: strReturn = "adError"
               Case 132: strReturn = "adUserDefined"
               Case 12: strReturn = "adVariant"
               Case 9: strReturn = "adIDispatch"
               Case 13: strReturn = "adIUnknown"
               Case 72: strReturn = "adGUID"
               Case 7: strReturn = "adDate"
               Case 133: strReturn = "adDBDate"
               Case 134: strReturn = "adDBTime"
               Case 135: strReturn = "adDBTimeStamp"
               Case 8: strReturn = "adBSTR"
               Case 129: strReturn = "adChar"
               Case 200: strReturn = "adVarChar"
               Case 201: strReturn = "adLongVarChar"
               Case 130: strReturn = "adWChar"
               Case 202: strReturn = "adVarWChar"
               Case 203: strReturn = "adLongVarWChar"
               Case 128: strReturn = "adBinary"
               Case 204: strReturn = "adVarBinary"
               Case 205: strReturn = "adLongVarBinary"
            Case Else:
               strReturn = "Unknown DataTypeEnum of " & lngDataTypeEnum _
                & " found."
            End Select
            GetDataTypeEnum = strReturn
         End Function
   
   

REFERENCES

For additional information, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q175018
   TITLE     : HOWTO: Acquire and Install the Microsoft Oracle ODBC Driver
               v2.0