HOWTO: Determine How ADO Will Bind ParametersID: Q181199
|
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.
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.
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
For additional information, please see the following article in the Microsoft Knowledge Base:
Q175018 HOWTO: Acquire and Install the Microsoft Oracle ODBC Driver(c) Microsoft Corporation 1998, All Rights Reserved. Contributions by Ron Nelson, Microsoft Corporation.
Additional query words: stored procedures ado binding parameters
Keywords : kbADO kbADO150 kbADO200 kbDatabase
Version : WINDOWS:1.0,1.5,2.0
Platform : WINDOWS
Issue type : kbhowto
Last Reviewed: May 3, 1999