IDC资讯中心
很多人一定用过query analyzer，这个工具的功能如何如何，就不用我说了，这次给大家介绍下web下的query analyzer,界面如图1，使用asp写的。
(图1)
源程序如下：
<%
dim conn
dim connstr
on error resume next
if request("selectdb")="mdb" then
if request("dbname")<>"" then
connstr="dbq="+server.mappath(request("dbname"))+";defaultdir=;driver={microsoft access driver (*.mdb)};"
set conn=server.createobject("adodb.connection")
if err.number<>0 then
err.clear
set conn=nothing
response.write "错误：数据库连接出错！"
else
if request("dbpass")<>"" then
conn.open connstr
else
conn.open connstr,"admin",request("dbpass")
end if
if err then
set conn=nothing
response.write "错误：数据库连接出错！！！<br>" + err.description
err.clear
end if
end if
end if
elseif request("selectdb")="mssql" then
if request("dbname")<>"" then
connstr = "provider=sqloledb.1"
connstr = connstr & ";data source=" & request("sqlip") sql_server数据库
connstr = connstr & ";user id=" & request("sqladmin") 数据库服务器用户
connstr = connstr & ";password=" & request("sqlpass") 登录口令
connstr = connstr & ";initial catalog=" & request("sqldb") 数据库名
set conn=server.createobject("adodb.connection")
if err.number<>0 then
err.clear
set conn=nothing
response.write "错误：数据库连接出错！"
else
conn.open connstr
if err then
set conn=nothing
response.write "错误：数据库连接出错！！！<br>" + err.description
err.clear
end if
end if
end if
end if
sub endconnection()
conn.close
set conn=nothing
end sub
%>
<html>
<head>
<title>query analyzer</title>
<style type=text/css>body {font-family: "宋体", "arial narrow", "times new roman"; font-size: 9pt}
p {font-family: "宋体", "arial narrow", "times new roman"; font-size: 9pt}
br {font-family: "宋体", "arial narrow", "times new roman"; font-size: 9pt}
td {font-family: "宋体", "arial narrow", "times new roman"; font-size: 9pt}
.p9 {font-size: 9pt; line-height: 14pt}
a:link {color: #004080; font-size: 9pt; line-height: 14pt; text-decoration: none}
a:visited {color: #004080; font-size: 9pt; line-height: 14pt; text-decoration: none}
a:hover {color: #ff0000; font-size: 9pt; line-height: 14pt; text-decoration: underline}
.p105 {font-size: 10.5pt}
input.yellowbtn {background-color: #88a3f2; color: #000000; font-size: 9pt}
</style>
</head>
<body bgcolor="#ffffff" topmargin="0">
<script language=javascript>
function setwb(x) {
document.main.whichbutton.value = x;
document.main.submit();
}
function showsql(x) {
sqlvalue = document.main.sql.value;
if (x == 0) {
smsg = "\n怎样使用sql语句帮助：\n\n";
smsg = smsg + "1. 在选择框里选一个命令\n";
smsg = smsg + "2. 点击[显示]按钮\n";
smsg = smsg + "3. sql语句会显示在左边的文本框里面\n\n";
smsg = smsg + "4. 修改此语句来适合你数据库结构\n\n";
smsg = smsg + "5. 根据语句查询结果或执行命令\n\n";
alert(smsg); }
else if (x == 1) {
smsg = "select columnname1, columnname2\n";
smsg = smsg + " from tablename\n";
smsg = smsg + " where columnname = value\n\n";
document.main.sql.value = smsg;
}
else if (x == 2) {
smsg = "insert into tablename\n";
smsg = smsg + " (columnname, integercolumnname)\n";
smsg = smsg + " values (value, numericvalue)\n\n";
document.main.sql.value = smsg;
}
else if (x == 3) {
smsg = "update tablename\n";
smsg = smsg + " set columnname = value\n";
smsg = smsg + " where columnname = value\n\n";
document.main.sql.value = smsg;
}
else if (x == 4) {
smsg = "delete columnname\n";
smsg = smsg + " from tablename\n";
smsg = smsg + " where columnname = value\n\n";
document.main.sql.value = smsg;
}
else if (x == 5) {
smsg = "create table tablename\n";
smsg = smsg + " (columnname1 varchar(20),\n";
smsg = smsg + " columnname2 char(20),\n";
smsg = smsg + " columnname3 integer)\n\n";
document.main.sql.value = smsg;
}
else if (x == 6) {
smsg = "drop table tablename\n\n";
document.main.sql.value = smsg;
}
else if (x == 7) {
smsg = "select a.columnname, b.columnname\n";
smsg = smsg + " from tablename a, tablename b\n";
smsg = smsg + " where a.columnname = b.columnname\n\n";
document.main.sql.value = smsg;
}
}
function helpme(){
helpmsg="在上面的那个文本框输入一句sql语句，例如：\n\n";
helpmsg+="select * from tablename\n\n";
helpmsg+="如果需要查询结果，则按[查询结果]，如果只\n";
helpmsg+="需执行一句sql语句，则按[执行sql语句]，一\n";
helpmsg+="般select是用于查询的，update、delete、\n";
helpmsg+="create table等是用于执行的。\n\n";
helpmsg+="在sql语句帮助表里可以获得一些基本sql语句\n";
helpmsg+="的语法。";
alert(helpmsg);}
</script>
<form action="<%=request.servervariables("script_name")%>" method=post name=main>
<br>
<table border=0 cellpadding=2 cellspacing=2 width=100% align="center">
<tr bgcolor="#00ccff">
<td width="157">
<input type="radio" name="selectdb" value="mdb" <%if request("selectdb")="mdb" then response.write "checked" %>>
mdb数据库</td>
<td width="588"> 数据库：
<input type="text" name="dbname" value="<%=request("dbname")%>">
(例如：db.mdb,或dir/db.mdb)<br>
密 码：
<input type="password" name="dbpass" value="<%=request("dbpass")%>">
</td>
</tr>
<tr bgcolor="#00ffcc">
<td height="40" width="157">
<input type="radio" name="selectdb" value="mssql" <%if request("selectdb")="mssql" then response.write "checked" %>>
ms_sqlserver</td>
<td height="40" width="588"> 服务器：
<input type="text" name="sqlip" value="<%=request("sqlip")%>">
(sqlserver 的ip地址)<br>
数据库：
<input type="text" name="sqldb" value="<%=request("sqldb")%>">
<br>
登录名：
<input type="text" name="sqladmin" value="<%=request("sqladmin")%>">
<br>
密 码：
<input type="password" name="sqlpass" value="<%=request("sqlpass")%>">
</td>
</tr>
<tr bgcolor="#33ccff">
<td width="157" rowspan="2">
<table border=0 cellpadding=2 cellspacing=2
width="83%" align="center">
<tr>
<td align=center bgcolor=#00cccc
valign=center>sql向导</td>
</tr>
<tbody>
<tr>
<td align=left bgcolor=#00cccc
valign=center>
<div align="center">
<select
name=sqlsyntax size=5>
<option selected>choose sql</option>
<option>select</option>
<option>insert</option>
<option>update</option>
<option>delete</option>
<option>create table</option>
<option>drop table</option>
<option>simple join</option>
</select>
</div>
</td>
</tr>
<tr>
<td align=left bgcolor=#00cccc
valign=center>
<div align="center">
<input name=sqlasst1 onclick=showsql(document.main.sqlsyntax.selectedindex); type=button value="显示">
<input name=sqlasst2 onclick="document.main.sql.value=;" type=button value="清除">
</div>
</td>
</tr>
</table>
</td>
<td width="588"> sql query : </td>
</tr>
<tr>
<td width="588" align="center" bgcolor="#33ccff">
<textarea cols=50 name=sql rows=12 wrap=virtual></textarea>
</td>
</tr>
<tr>
<td colspan="2">
<input name=whichbutton type=hidden value="nors">
<input name=action2 type=hidden value=exec>
<input name=selindex type=hidden>
<b>
<input name=b1 class=yellowbtn onclick="setwb(getrs);" type=button value="查询结果">
<input name=b2 class=yellowbtn onclick="setwb(nors);" type=button value="执行sql语句">
<input name=b3 class=yellowbtn onclick="helpme();" type="button" value="帮助">
</b> <b>
<script language=javascript>
document.main.sql.focus();
if (document.main.selindex.value != "") { document.main.db.options[document.main.selindex.value].selected = true; }
</script>
</b></td>
</tr>
</table>
<br>
</form>
<p>
<%
dim sql,rs
ifrs=request("whichbutton")
sql=request("sql")
if sql<>"" then
select case ifrs
case "nors"
conn.execute sql
if err then
response.write "这句sql语句有错误，没有完全执行。<br>"&err.description&"<br>"&sql
else
response.write "执行成功！"
end if
case "getrs"
set rs=server.createobject("adodb.recordset")
rs.open sql,conn,1,1
if err then
response.write "这句查询sql语句有错误，没有完全执行。<br>"&err.description&"<br>"&sql
else
response.write "找到了<b>"&cstr(rs.recordcount)&"</b>个结果"
%>
<table border="1" align="center">
<tr>
<%
colnum=rs.fields.count
for i=0 to rs.fields.count-1 %>
<td bgcolor="#33ccff"><font color=red><%=rs(i).name%></font></td>
<%next%>
</tr>
<%do while not rs.eof
k=0 %>
<tr>
<%for k=0 to colnum-1%>
<td><%=rs(rs(k).name)%></td>
<%next %>
</tr>
<% rs.movenext
loop
%>
</table>
<%
rs.close
set rs=nothing
end if
end select
end if
endconnection
%>
</body>
</html>
query analyzer就这么简单，当然还需要改进，这就看你的了，呵呵…：）
