Information_schema - Porque Não Funciona ? ? ?

08/09/2007

0

alguém saberia me dizer o porque que o código abaixo não funciona . . .

ele simplesmente retorna uma página em branco



<¬
Set Conexao = Server.CreateObject("Adodb.Connection")
Conexao.ConnectionString = "DBQ=" & Server.MapPath("../../databases/123/123db.mdb") & ";"
Conexao.ConnectionString = Conexao.ConnectionString & "Driver={Microsoft Access Driver (*.mdb)};uid=Admin; password=XXXXXXX"
Conexao.Open


Sub RenderTableConstraints(strTableName, conexao)

  Dim strQuery
  Dim strConstraintName, strConstraintType, strConstraintDetails
  Dim oRsConstraintList
  
  ´--- Get the list of constraints on the table.
  strQuery = "SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME=´" + strTableName + "´"
  Set oRsConstraintList = conexao.Execute(strQuery)

  Response.Write("<BR><BR><B>Constraints</B>")
    
  ´--- Start a table for the list of columns.

  Response.Write("<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=2 WIDTH=100¬ Class=Normal bordercolor=Navy>")

  Response.Write("<TR>")
  Response.Write("<TD WIDTH=´25¬´>Constraint Type</TD>")
  Response.Write("<TD WIDTH=´35¬´>Constraint Name</TD>")
  Response.Write("<TD WIDTH=´40¬´>Fields(PK,FK) / Clause(CHECK)</TD>")
  Response.Write("</TR>")
  
  ´--- Loop to parse to the list of columns.
  While (Not oRsConstraintList.EOF)
  
    ´--- Store the constraint name and type.
    strConstraintName = oRsConstraintList("CONSTRAINT_NAME")
    strConstraintType = oRsConstraintList("CONSTRAINT_TYPE")
    
    ´--- Get constraint details based on the constraint type.
    If (strConstraintType = "CHECK") Then
      strConstraintDetails = GetClauseForConstraint(strConstraintName, conexao)
    Else
      strConstraintDetails = GetColumnsForConstraint(strConstraintName, strTableName, conexao)
    End If
  
    ´--- Output the details of each column.
    Response.Write("<TR>")
    Response.Write("<TD>" + strConstraintType + "</TD>")
    Response.Write("<TD>" + strConstraintName + "</TD>")
    Response.Write("<TD>" + strConstraintDetails + "</TD>")
    Response.Write("</TR>")    
     
    ´--- Move to the next column.
    oRsConstraintList.MoveNext
  Wend
    
  ´--- End the table for columns.
  Response.Write("</TABLE><br><br>")
  
  ´--- Clean up.
  Set oRsConstraintList = Nothing
End Sub

Function GetClauseForConstraint(strConstraintName, conexao)

  Dim strQuery
  Dim oRs
  
  ´--- Build query to get the column list for constraint.
  strQuery = "SELECT c.CHECK_CLAUSE " + _
             "FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS c " + _
             "WHERE ( c.constraint_name = ´" + strConstraintName + "´)"
    
  ´--- Use connection and get the recordset.
  Set oRs = conexao.Execute(strQuery)
  
  ´--- Return the string.
  GetClauseForConstraint = oRs("CHECK_CLAUSE")
  
End Function


´------------------------------------------------------------------
´--- To get the list of cols associated with constraint as , delimited str.
´------------------------------------------------------------------
Function GetColumnsForConstraint(strConstraintName, strTableName, conexao)

  Dim iCount
  Dim strQuery, strFldNames
  Dim oRs
  

  ´--- Build query to get the column list for constraint.
  strQuery = "SELECT d.column_name " + _
             "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE d " + _
             "WHERE (d.constraint_name = ´" + strConstraintName + " ´) " + _
             "ORDER BY d.ordinal_position"
    
  ´--- Use connection and get the recordset.
  Set oRs = conexao.Execute(strQuery)
  
  ´--- Initialize the field names string.
  strFldNames = "": iCount = 0
  
  ´--- Loop to build a comma delimited string.
  While (Not oRs.EOF)
    ´--- Add a comma separator between 2 column names.
    If (iCount > 0) Then strFldNames = strFldNames + ", "
    
    ´--- Add the next field name.
    strFldNames = strFldNames + oRs("column_name")
  
    ´--- Move to next row and increment counter.
    oRs.MoveNext: iCount = iCount + 1
  Wend
  
  ´--- Return the column names as a comma delimited string.
  GetColumnsForConstraint = strFldNames
  
End Function

conexao.Close
Set conexao = Nothing

¬>


Obrigado

Marcelo Camisa


Camisa

Camisa

Responder

Que tal ter acesso a um e-book gratuito que vai te ajudar muito nesse momento decisivo?

Ver ebook

Recomendado pra quem ainda não iniciou o estudos.

Eu quero
Ver ebook

Recomendado para quem está passando por dificuldades nessa etapa inicial

Eu quero

Utilizamos cookies para fornecer uma melhor experiência para nossos usuários, consulte nossa política de privacidade.

Aceitar