Hi Doug,
I'm not sure how useful this will be (given that it's ASP code), but I wrote
this for extracting data types off of database tables (works on Access and
SQL Server without issue). You know we have to write all this functionality
that CF builds in when working with "other" languages :).
Dan
> I didn't feel like being productive this morning so I decided
> to try to create an adaptor for Access. I can get *almost*
> all the information I need. Unfortunately, I can't find a
> way to find out what columns are in an access table nor
> information such as if it's a PK, autonumbered, Nullable,
> it's datatype, size or default value. Anyone have any ideas
> on how I could get that? With that, we'd have one more
> adaptor, albeit a somewhat useless one.
-- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at
http://www.mail-archive.com/reactor%40doughughes.net/
Function cwQueryDump(rs,strLabel)
'---------------------------------------------------------------------
'Created: July 25, 2004
'Modified: July 25, 2004
'FUNCTION: cwQueryDump
'DESCRIPTION: This function is used to output the entire contents of
' a recordset for debugging purposes. The output includes the
' RecordCount and SQL for the recordset. If cwDebug in Application.asp
' is set to False then no action is taken to reduce load on live sites.
'ARGUMENTS
'rs: The recordset object to dump.
'strLabel: The label to use for the query dump table. This is used as
' the <caption> in the output table.
'RETURNS
'If the recordset is empty, a string stating such is returned,
' otherwise a table containing all of the records in the recordset is
' returned.
'EXAMPLES
'Dump a recordset named myRS with a caption of myRS.
'cwQueryDump(myRecordset, "myRecordset")
'---------------------------------------------------------------------
If NOT CBool(Session("debug")) Then Exit Function
If IsObject(rs) Then
If NOT rs.EOF Then
'Outputs a query, it's source, and all returned records.
Dim objField, intNumFields, str, x
intNumFields = rs.Fields.Count
str = str & "<table class=""tblcwQryDump""><caption>" &
strLabel & "</caption>" &_
"<tr><td colspan=""" & intNumFields &
"""><strong>SQL:</strong> " & rs.source & "<br /><strong>Records:</strong> " &
rs.RecordCount & "</td></tr>" &_
"<tr>"
For Each objField in rs.Fields
str = str & "<th>" & objField.Name & " - "
& cwGetFieldType(objField.Type,False) & "</th>"
Next 'objField in rs.Fields
str = str & "</tr>"
Do While NOT rs.EOF
str = str & "<tr>"
For Each objField in rs.Fields
str = str & "<td>"
If objField.Type = 136 Then
str = str &
cwQueryDump(objField.Value,"Child Recordset (datashaping)")
Else
str = str & objField.Value
End If
str = str & " </td>"
Next 'objField in rs.Fields
str = str & "</tr>"
rs.MoveNext()
Loop 'While NOT rs.EOF
str = str & "</table>"
rs.MoveFirst()
Else
str = "<strong>" & strLabel & "</strong> is empty:<br
/> " & rs.Source
End If 'IsObject(recordset)
cwQueryDump = str
Else
cwQueryDump = "You must pass a valid recordset object to the
cwQueryDump function."
End If
End Function 'cwQueryDump
Function cwGetFieldType(str,bolDescrip)
'---------------------------------------------------------------------
'Created: July 25, 2004
'Modified: July 25, 2004
'FUNCTION: cwGetFieldType
'DESCRIPTION: Returns a friendly type name based on the type value for
' a recordset field.
'ARGUMENTS
'str: This argument should contain a string with the numeric data type
' code from a recordset field. This can be obtained by using
' myRS("myField").Type.
'bolDescrip: True or False. Determines whether the description for the
' type is also returned.
'RETURNS
'A string containing the type name of the current field, and the
' description of the field if desired.
'EXAMPLES
'Display the type for the field myField in the recordset myRS.
'Response.Write(cwGetFieldType(myRS("myField").Type))
'---------------------------------------------------------------------
Dim arTypes
arTypes = "AdArray|0x2000|A flag value, always combined with another
data type constant, that indicates an array of that other data type." &_
"|adBigInt|20|Indicates an eight-byte signed integer
(DBTYPE_I8)." &_
"|adBinary|128|Indicates a binary value (DBTYPE_BYTES)." &_
"|adBoolean|11|Indicates a boolean value (DBTYPE_BOOL)." &_
"|adBSTR|8|Indicates a null-terminated character string
(Unicode) (DBTYPE_BSTR)." &_
"|adChapter|136|Indicates a four-byte chapter value that
identifies rows in a child rowset (DBTYPE_HCHAPTER)." &_
"|adChar|129|Indicates a string value (DBTYPE_STR)." &_
"|adCurrency|6|Indicates a currency value (DBTYPE_CY). Currency
is a fixed-point number with four digits to the right of the decimal point. It
is stored in an eight-byte signed integer scaled by 10,000." &_
"|adDate|7|Indicates a date value (DBTYPE_DATE). A date is
stored as a double, the whole part of which is the number of days since
December 30, 1899, and the fractional part of which is the fraction of a day."
&_
"|adDBDate|133|Indicates a date value (yyyymmdd)
(DBTYPE_DBDATE)." &_
"|adDBTime|134|Indicates a time value (hhmmss)
(DBTYPE_DBTIME)." &_
"|adDBTimeStamp|135|Indicates a date/time stamp (yyyymmddhhmmss
plus a fraction in billionths) (DBTYPE_DBTIMESTAMP)." &_
"|adDecimal|14|Indicates an exact numeric value with a fixed
precision and scale (DBTYPE_DECIMAL)." &_
"|adDouble|5|Indicates a double-precision floating-point value
(DBTYPE_R8)." &_
"|adEmpty|0|Specifies no value (DBTYPE_EMPTY)." &_
"|adError|10|Indicates a 32-bit error code (DBTYPE_ERROR)." &_
"|adFileTime|64|Indicates a 64-bit value representing the
number of 100-nanosecond intervals since January 1, 1601 (DBTYPE_FILETIME)." &_
"|adGUID|72|Indicates a globally unique identifier (GUID)
(DBTYPE_GUID)." &_
"|adIDispatch|9|Indicates a pointer to an IDispatch interface
on a COM object (DBTYPE_IDISPATCH). Note This data type is currently not
supported by ADO. Usage may cause unpredictable results." &_
"|adInteger|3|Indicates a four-byte signed integer
(DBTYPE_I4)." &_
"|adIUnknown|13|Indicates a pointer to an IUnknown interface on
a COM object (DBTYPE_IUNKNOWN). Note This data type is currently not
supported by ADO. Usage may cause unpredictable results." &_
"|adLongVarBinary|205|Indicates a long binary value." &_
"|adLongVarChar|201|Indicates a long string value." &_
"|adLongVarWChar|203|Indicates a long null-terminated Unicode
string value." &_
"|adNumeric|131|Indicates an exact numeric value with a fixed
precision and scale (DBTYPE_NUMERIC)." &_
"|adPropVariant|138|Indicates an Automation PROPVARIANT
(DBTYPE_PROP_VARIANT)." &_
"|adSingle|4|Indicates a single-precision floating-point value
(DBTYPE_R4)." &_
"|adSmallInt|2|Indicates a two-byte signed integer
(DBTYPE_I2)." &_
"|adTinyInt|16|Indicates a one-byte signed integer
(DBTYPE_I1)." &_
"|adUnsignedBigInt|21|Indicates an eight-byte unsigned integer
(DBTYPE_UI8)." &_
"|adUnsignedInt|19|Indicates a four-byte unsigned integer
(DBTYPE_UI4)." &_
"|adUnsignedSmallInt|18|Indicates a two-byte unsigned integer
(DBTYPE_UI2)." &_
"|adUnsignedTinyInt|17|Indicates a one-byte unsigned integer
(DBTYPE_UI1)." &_
"|adUserDefined|132|Indicates a user-defined variable
(DBTYPE_UDT)." &_
"|adVarBinary|204|Indicates a binary value." &_
"|adVarChar|200|Indicates a string value." &_
"|adVariant|12|Indicates an Automation Variant
(DBTYPE_VARIANT). Note This data type is currently not supported by ADO.
Usage may cause unpredictable results." &_
"|adVarNumeric|139|Indicates a numeric value." &_
"|adVarWChar|202|Indicates a null-terminated Unicode character
string." &_
"|adWChar|130|Indicates a null-terminated Unicode character
string (DBTYPE_WSTR)."
arTypes = Split(arTypes,"|")
Dim i
For i = 0 To UBound(arTypes) STEP 3
If CStr(str) = CStr(arTypes(i+1)) Then
If bolDescrip Then
cwGetFieldType = arTypes(i) & " (" &
arTypes(i+2) & ")"
Else
cwGetFieldType = arTypes(i)
End If
Exit Function
End If
Next
End Function
-- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at
http://www.mail-archive.com/reactor%40doughughes.net/
