|
Displaying Source Code(s)
|
|
GetProcedureDef Function
--------------------------------------------------------------------------------
Description : GetProcedureDef returns information about the
arguments of a given stored procedure. It returns a reference to
a Scripting.Dictionary object whose keys and items collections
will be filled about data gathered from the stored procedure.
GetProcedureDef is primarily useful for MS Access users only as
SQL Server users can just call sp_helptext 'procname' for the
complete stored procedure definition of an unencrypted stored
procedure. GetProcedureDef has two arguments: connstring and
procname.
Arguments
==============
connstring OLEDB Connection String to MS Access database as
string. procname stored procedure name as string to lookup for
argument information.
Return Values
==============
GetProcedureDef returns a Scripting.Dictionary object, fully
loaded. There will be at least one key/item pair in the
dictionary at runtime if the stored procedure exists in the
target database. Each key/item pair follows this format: key -
represents the argument variable name minus the @ character. In
TSQL, local variables must be prefaced with the @ character.
These will be stripped from the key value. item - represents the
data type of the argument variable specified in key. Returns a
long. The following table identifies what each data type
constant means: constant meaning
20 Eight-byte signed integer (DBTYPE_I8). 128 binary value (DBTYPE_BYTES).
11 boolean value (DBTYPE_BOOL). 8 null-terminated character
string (Unicode) (DBTYPE_BSTR). 136 four-byte chapter value that
identifies rows in a child rowset (DBTYPE_HCHAPTER). 129 string
value (DBTYPE_STR). 6 currency value (DBTYPE_CY). Currency is a
fixed-point number with four digits to the right of the decimal
point. 7 date value (DBTYPE_DATE). 133 date value (yyyymmdd) (DBTYPE_DBDATE).
134 time value (hhmmss) (DBTYPE_DBTIME). 135 date/time stamp (yyyymmddhhmmss
plus a fraction in billionths) (DBTYPE_DBTIMESTAMP). 14 exact
numeric value with a fixed precision and scale (DBTYPE_DECIMAL).
5 double-precision floating-point value (DBTYPE_R8). 0 No value
(DBTYPE_EMPTY). 10 32-bit error code (DBTYPE_ERROR). 64 64-bit
value representing the number of 100-nanosecond intervals since
January 1, 1601 (DBTYPE_FILETIME). 72 globally unique identifier
(GUID) (DBTYPE_GUID). 9 pointer to an IDispatch interface on a
COM object (DBTYPE_IDISPATCH). 3 four-byte signed integer
(DBTYPE_I4). 13 pointer to an IUnknown interface on a COM object
(DBTYPE_IUNKNOWN). 205 long binary value (Parameter object
only). 201 long string value (Parameter object only). 203 long
null-terminated Unicode string value (Parameter object only).
131 Indicates an exact numeric value with a fixed precision and
scale (DBTYPE_NUMERIC). 138 Indicates an Automation PROPVARIANT
(DBTYPE_PROP_VARIANT). 4 single-precision floating-point value
(DBTYPE_R4). 2 two-byte signed integer (DBTYPE_I2). 16 one-byte
signed integer (DBTYPE_I1). 21 eight-byte unsigned integer
(DBTYPE_UI8). 19 four-byte unsigned integer (DBTYPE_UI4). 18
two-byte unsigned integer (DBTYPE_UI2). 17 one-byte unsigned
integer (DBTYPE_UI1). 132 user-defined variable (DBTYPE_UDT).
204 binary value (Parameter object only). 200 string value
(Parameter object only). 12 Automation Variant (DBTYPE_VARIANT).
139 numeric value (Parameter object only). 202 null-terminated
Unicode character string (Parameter object only). 130
null-terminated Unicode character string (DBTYPE_WSTR).
Function GetProcedureDef(ByVal connstring, ByVal procname)
Dim cnn, cmd, prm, cat, d
Set d = CreateObject("Scripting.Dictionary")
Set cnn = CreateObject("ADODB.Connection")
' Open the Connection
cnn.Open connstring
' Open the catalog
Set cat = CreateObject("ADOX.Catalog")
cat.ActiveConnection = cnn
' Get the command object
Set cmd = cat.Procedures(procname).Command
' Retrieve Parameter information
cmd.Parameters.Refresh
For Each prm In cmd.Parameters
d.Add prm.Name, prm.Type
Next
Set cmd = Nothing
Set cat = Nothing
Set cnn = Nothing
Set GetProcedureDef = d
End Function
|
|
|
123
Eng