rothen ecotronics Bern: ASP-Skript "ASP fortgeschrittene DB-Techniken: Datenbank-Schema auslesen"

<%@ Language=VBScript %>
<%
Option Explicit
Response.Expires = 0
'On Error Resume Next

'****************************************************************************333
'Konstanten und Variablendeklaration und -initialisierung
'Schema
Const adSchemaProviderSpecific = -1
Const adSchemaAsserts = 0
Const adSchemaCatalogs = 1
Const adSchemaCharacterSets = 2
Const adSchemaCollations = 3
Const adSchemaColumns = 4
Const adSchemaCheckConstraints = 5
Const adSchemaConstraintColumnUsage = 6
Const adSchemaConstraintTableUsage = 7
Const adSchemaKeyColumnUsage = 8
Const adSchemaReferentialContraints = 9
Const adSchemaTableConstraints = 10
Const adSchemaColumnsDomainUsage = 11
Const adSchemaIndexes = 12
Const adSchemaColumnPrivileges = 13
Const adSchemaTablePrivileges = 14
Const adSchemaUsagePrivileges = 15
Const adSchemaProcedures = 16
Const adSchemaSchemata = 17
Const adSchemaSQLLanguages = 18
Const adSchemaStatistics = 19
Const adSchemaTables = 20
Const adSchemaTranslations = 21
Const adSchemaProviderTypes = 22
Const adSchemaViews = 23
Const adSchemaViewColumnUsage = 24
Const adSchemaViewTableUsage = 25
Const adSchemaProcedureParameters = 26
Const adSchemaForeignKeys = 27
Const adSchemaPrimaryKeys = 28
Const adSchemaProcedureColumns = 29

'Datentypen Recordset
Const adEmpty =              0
Const adSmallInt =           2
Const adInteger =            3
Const adSingle =             4
Const adDouble =             5
Const adCurrency =           6
Const adDate =               7
Const adBSTR =               8
Const adIDispatch =          9
Const adError =             10
Const adBoolean =           11
Const adVariant =           12
Const adIUnknown =          13
Const adDecimal =           14
Const adTinyInt =           16
Const adUnsignedTinyInt =   17
Const adUnsignedSmallInt =  18
Const adUnsignedInt =       19
Const adBigInt =            20
Const adUnsignedBigInt =    21
Const adGUID =              72
Const adBinary =           128
Const adChar =             129
Const adWChar =            130
Const adNumeric =          131
Const adUserDefined =      132
Const adDBDate =           133
Const adDBTime =           134
Const adDBTimeStamp =      135
Const adVarChar =          200
Const adLongVarChar =      201
Const adVarWChar =         202
Const adLongVarWChar =     203
Const adVarBinary =        204
Const adLongVarBinary =    205

'Feldattribute
Const adFldMayDefer = &H00000002
Const adFldUpdatable = &H00000004
Const adFldUnknownUpdatable = &H00000008
Const adFldFixed = &H00000010
Const adFldIsNullable = &H00000020
Const adFldMayBeNull = &H00000040
Const adFldLong = &H00000080
Const adFldRowID = &H00000100
Const adFldRowVersion = &H00000200

Dim arrschema(30)
Dim intcounter
Dim intSchema
Dim strcon

strcon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=" & Server.MapPath("/asppages/silvi/db/dbfieldtest.mdb")

arrschema(0) = adSchemaProviderSpecific
arrschema(1) = adSchemaAsserts
arrschema(2) = adSchemaCatalogs
arrschema(3) = adSchemaCharacterSets
arrschema(4) = adSchemaCollations
arrschema(5) = adSchemaColumns
arrschema(6) = adSchemaCheckConstraints
arrschema(7) = adSchemaConstraintColumnUsage
arrschema(8) = adSchemaConstraintTableUsage
arrschema(9) = adSchemaKeyColumnUsage
arrschema(10) = adSchemaReferentialContraints
arrschema(11) = adSchemaTableConstraints
arrschema(12) = adSchemaColumnsDomainUsage
arrschema(13) =  adSchemaIndexes
arrschema(14) = adSchemaColumnPrivileges
arrschema(15) = adSchemaTablePrivileges
arrschema(16) = adSchemaUsagePrivileges
arrschema(17) = adSchemaProcedures
arrschema(18) = adSchemaSchemata
arrschema(19) = adSchemaSQLLanguages
arrschema(20) = adSchemaStatistics
arrschema(21) = adSchemaTables
arrschema(22) = adSchemaTranslations
arrschema(23) = adSchemaProviderTypes
arrschema(24) = adSchemaViews
arrschema(25) = adSchemaViewColumnUsage
arrschema(26) = adSchemaViewTableUsage
arrschema(27) = adSchemaProcedureParameters
arrschema(28) = adSchemaForeignKeys
arrschema(29) = adSchemaPrimaryKeys
arrschema(30) = adSchemaProcedureColumns

'****************************************************************************333
'lokale Prozeduren und Funktionen

'weist einer Schema-Konstanten einen Namen zu
Function funSchemaName(ByVal intSchema)
   Select Case intSchema
   Case adSchemaProviderSpecific
      funSchemaName = "adSchemaProviderSpecific"
   Case adSchemaAsserts
      funSchemaName = "adSchemaAsserts"
   Case adSchemaCatalogs
      funSchemaName = "adSchemaCatalogs"
   Case adSchemaCharacterSets
      funSchemaName = "adSchemaCharacterSets"
   Case adSchemaCollations
      funSchemaName = "adSchemaCollations"
   Case adSchemaColumns
      funSchemaName = "adSchemaColumns"
   Case adSchemaCheckConstraints
      funSchemaName = "adSchemaCheckConstraints"
   Case adSchemaConstraintColumnUsage
      funSchemaName = "adSchemaConstraintColumnUsage"
   Case adSchemaConstraintTableUsage
      funSchemaName = "adSchemaConstraintTableUsage"
   Case adSchemaKeyColumnUsage
      funSchemaName = "adSchemaKeyColumnUsage"
   Case adSchemaReferentialContraints
      funSchemaName = "adSchemaReferentialContraints"
   Case adSchemaTableConstraints
      funSchemaName = "adSchemaTableConstraints"
   Case adSchemaColumnsDomainUsage
      funSchemaName = "adSchemaColumnsDomainUsage"
   Case adSchemaIndexes
      funSchemaName = "adSchemaIndexes"
   Case adSchemaColumnPrivileges
      funSchemaName = "adSchemaColumnPrivileges"
   Case adSchemaTablePrivileges
      funSchemaName = "adSchemaTablePrivileges"
   Case adSchemaUsagePrivileges
      funSchemaName = "adSchemaUsagePrivileges"
   Case adSchemaProcedures
      funSchemaName = "adSchemaProcedures"
   Case adSchemaSchemata
      funSchemaName = "adSchemaSchemata"
   Case adSchemaSQLLanguages
      funSchemaName = "adSchemaSQLLanguages"
   Case adSchemaStatistics
      funSchemaName = "adSchemaStatistics"
   Case adSchemaTables
      funSchemaName = "adSchemaTables"
   Case adSchemaTranslations
      funSchemaName = "adSchemaTranslations"
   Case adSchemaProviderTypes
      funSchemaName = "adSchemaProviderTypes"
   Case adSchemaViews
      funSchemaName = "adSchemaViews"
   Case adSchemaViewColumnUsage
      funSchemaName = "adSchemaViewColumnUsage"
   Case adSchemaViewTableUsage
      funSchemaName = "adSchemaViewTableUsage"
   Case adSchemaProcedureParameters
      funSchemaName = "adSchemaProcedureParameters"
   Case adSchemaForeignKeys
      funSchemaName = "adSchemaForeignKeys"
   Case adSchemaPrimaryKeys
      funSchemaName = "adSchemaPrimaryKeys"
   Case adSchemaProcedureColumns
      funSchemaName = "adSchemaProcedureColumns"
   Case Else
      funSchemaName = "unbekannt"
   End Select
End Function

'Zeigt für die verschiedenen Schema-Konstanten Tabellen an
Sub procSchemaToTable(strcon, intSchema)
  Dim objcon
  Dim objfield
  Dim rsSchema

  Set objcon = Server.CreateObject("ADODB.Connection")

  Response.Write intSchema
  objcon.Open strcon

  on error resume next
  Set rsSchema = objcon.OpenSchema(intSchema)

  If err.number = 3251 Then
     response.flush
     response.write " <b>" & funSchemaName(intSchema)
     response.write "</b><br /> is not supported<br />"
     err.clear
  Else
     response.write " <b>" & funSchemaName(intSchema) & "</b><br />"
     response.write "<table border=""1""><tr>"
     'Beschriftungen
     For each objfield in rsSchema.fields
      response.write "<td><b>" & objfield.name & "</b></td>"
     next
     response.write "</tr>"
     Do Until rsSchema.eof
       response.write "<tr>"
       for each objField in rsSchema.fields
         response.write "<td valign=""top"">" & Trim(objField.value) & " </td>"
       next
       response.write "</tr>"
       rsSchema.MoveNext
     Loop
     response.write "</table><br />"
     response.flush
  End If

  rsSchema.Close
  set rsSchema = nothing

  objcon.close
  set objcon = nothing
End Sub
%>

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1252" />
    <title>ASP fortgeschrittene DB-Techniken: Datenbank-Schema auslesen</title>

  </head>
<body>
<h3>ASP Datenbankanbindung: Datenbank-Schema auslesen</h3>
<p>Beispiel einer Access-DB</p>
<%
For intcounter = 1 to 30
  If intcounter <> 2 then
    intSchema = arrschema(intcounter)
    Call procSchemaToTable(strcon, intSchema)
    response.write "<p>"
  End If
Next
%>

</body>
</html>

Demo: beispiele/065dbschemaauslesen.asp

Letzter Update: 26.12.2021 16:48

Zurück zur Liste mit ASP-Beispielen auf www.ecotronics.ch