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
Obrigado
Marcelo Camisa
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
Curtir tópico
+ 0
Responder
Clique aqui para fazer login e interagir na Comunidade :)