ASP教程:OpenSchema方法技术探讨

作者:网络 来源:佚名 更新时间:2008-07-07 01:43:53 点击:

最大的网站源码资源下载站,

语法
set connection=server.createobject(“adodb.connection“)
set recordset = connection.openschema (querytype, criteria, schemaid)

返回值
返回包含模式信息的 recordset 对象。recordset 将以只读、静态游标打开。

参数
querytype 所要运行的模式查询类型,可以为下列任意常量。
criteria 可选。每个 querytype 选项的查询限制条件数组,

参数枚举(querytype )
'---- schemaenum values ----
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


利用openschema可以获得所有表的主键,当然这只是它的很多功能中的一个。
connstr = "provider=msdaora.1;user id=liujincai;password=ljc1001;data source=hp1"
set mm=server.createobject("adodb.connection")
mm.open connstr
const adschemaprimarykeys = 28
set rs=mm.openschema(adschemaprimarykeys)
response.write "<table>"
response.write "<tr>"

for i=0 to rs.fields.count-1
response.write "<td>" & rs(i).name & "</td>"
next
response.write "</tr> "
do while not rs.eof
response.write "<tr>"
for i=0 to rs.fields.count-1
response.write "<td>" & rs(i) & "</td>"
next
response.write "</tr> "
rs.movenext
loop
response.write “</table>“

'下面的语句获得表'tb_house_main'的主键所在的字段
rs.filter="table_name='tb_house_main'"
if not(rs.eof and rs.bof) then response.write rs("column_name")


获得任意一个存储过程的参数列表,这个对于想做完全松耦合系统的哥们一定有用的。这个程序获得名字为'pro_house_add_info'的存储过程的参数列表
connstr = "provider=msdaora.1;user id=liujincai;password=ljc1001;data source=hp1"
set mm=server.createobject("adodb.connection")
mm.open connstr
const adschemaprocedureparameters = 26
set rs=mm.openschema(adschemaprocedureparameters) rs.filter="procedure_name='pro_house_add_info'" response.write "<table cellspacing=0 border=1>"
response.write "<tbody><tr bgcolor=#dddddd>"
for i=2 to rs.fields.count-1
response.write "<td>" & rs(i).name & "</td>"
next
response.write "</tr>"
do while not rs.eof
response.write "<tr bgcolor=#e0d0c0>"
for i=2 to rs.fields.count-1
response.write "<td>" & rs(i) & "</td>"
next
response.write "</tr>"
rs.movenext
loop
response.write "</table>"


经过几个小时的实践探索,终于结束了对adodb.connection的openschema方法的研究。现在总结一下:
1,set recordset = connection.openschema (querytype, criteria, schemaid)
里边一共有三个参数,但是一般就用第一个,后两个得确切功能,我也没有搞懂,这里也不做说明,有兴趣的自己查资料。

2,这个的实现就是通过连接对象的openschema方法返回一个记录集。这样就可以遍历记录集来显示或者搜集感性趣的信息。

3,利用它你还可以获得关于表,视图,列,索引,外键等信息。

4,现在不能肯定的是,是否有些方式的模式查询受用户权限的限制。也就是如果没有足够的权限,是不能做某些类别的模式查询的(有待验证)

5,这里有一些关于openschema的资料连接,供查阅:
http://www.lyu.edu.cn/home/yss/ado/mdmthopenschema.htm
http://code365.com/html/asp/20040227/index/2004227113621.html

在asp中列出数据库中的表名和字段名的程序
set rstschema = oconn.openschema(adschemacolumns)
这是关键之处
<%
dsn = "dsn=dsnname"
const adschematables = 20
adschemacolumns = 4
set oconn = server.createobject("adodb.connection")

oconn.open dsn
set rstschema = oconn.openschema(adschemacolumns)
response.write "<table>"
response.write "<tr><td>table name</td><td>field name</td><td>field type</td><td>is nullable</td><td>field size</td></tr>"
tablename=""
do until rstschema.eof
response.write "<tr><td>"
if rstschema("table_name") <> tablename then
response.write rstschema("table_name")
tablename = rstschema("table_name")
else
response.write " "
end if
response.write "</td><td>"& rstschema("column_name") & "</td><td>"
select case rstschema("data_type")
case "130"
if rstschema("character_maximum_length") = 1073741823 then
response.write "memo"
else
response.write "text"
end if
case 135
response.write "date/time"
case 3
response.write "long integer"
case 11
response.write "yes/no"
case 131
response.write "currency"
case else
response.write rstschema("data_type")
end select
response.write "</td><td>" & rstschema("is_nullable") & "</td><td>"
if rstschema("character_maximum_length") <> 1073741823 then
response.write rstschema("character_maximum_length")
else
response.write " "
end if
response.write "</td></tr>"
rstschema.movenext
loop
response.write "</table>"
%>