Array
(
)

Information_schema - Porque Não Funciona ? ? ?

Camisa
   - 08 set 2007

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

ele simplesmente retorna uma página em branco



#Código



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