📄 db_createsqle1.05.asp
字号:
<% @ LANGUAGE="VBSCRIPT" CODEPAGE="936"%>
<%Option Explicit
response.buffer=true
Response.Expires = -1
Response.AddHeader "Pragma","no-cache"
Response.AddHeader "cache-ctrol","no-cache"
'build2004-11-20 V1.05
%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE>CooSel2.0 Access to SQLserver 数据库生迁脚本编写器 V1.05(V37 PaintBlue.Net 2004 Acp Code)</TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="V37">
<META NAME="Keywords" CONTENT="PaintBlue.Net,PaintBlue">
<META NAME="Description" CONTENT="PaintBlue.Net">
<style>
table{ color: #000000;
font-size: 9pt;
FONT-FAMILY: "Tahoma","MS Shell Dlg";
}
td { color: #000000;
font-size: 9pt;
}table{ color: #000000;
font-size: 9pt;
FONT-FAMILY: "Tahoma","MS Shell Dlg";
}
body { color: #000000;
font-size: 9pt;
}
</style>
</HEAD>
<body bgCOLOR=eeeeee text="#000000" leftmargin="0" marginwidth="100%" topmargin="0" bottommargin="20">
<%
'2004-11-18/
'fix exec=0 =1 type
'fix conv now() date() time() type
'fix binary ole conv 不做导入
'fix Asp代码生成
dim enMode,UniCodeMode
dim DB_Name,ExtName,FileName
dim rs,CONN,CONNstr
DB_Name=questStr("DB_Name")
FileName=questStr("DB_Name")
enMode=questStr("enMode")
UniCodeMode=questStr("UniCodeMode")
if not isnumeric(enMode) then enMode=0
'2004-11-18
dim databaseName,darr,errinfo
dim loginName
dim loginPassword
dim sapass
errinfo=""
databaseName=questStr("databaseName")
loginName=questStr("loginName")
loginPassword=questStr("loginPassword")
sapass=questStr("sapass")
if not checkchar(loginName) then
errinfo=errinfo & "要生成的SQL数据库登陆名称含不合法字符\n"
end if
if not checkchar(databaseName) then
errinfo=errinfo & "要生成的SQL数据库名称含不合法字符\n"
end if
if errinfo<>"" then GetAlert errinfo
if databaseName="" and DB_Name<>"" then
darr=split(DB_Name,"\")
databaseName=split(darr(ubound(darr)),".")(0)
end if
'--------/
if DB_Name<>"" then
enMode=clng(enMode)
if enMode=0 then
ExtName=".Sql"
else
ExtName=".Asp"
end if
Call openDB(DB_Name)
Call CreateSQL(DB_Name,enMode)
else
if DB_Name="" then DB_Name="data/mydb.mdb"
Call Main()
end if
'2004-11-18
Function CheckChar(testchar)
CheckChar=true
dim chars,i,j,charlen
chars=testchar
dim ichar
ichar=array("=","\","(",")","/","%",chr(32),"?"," & ","$",";",",","'",chr(34),chr(9),chr(0),"*",">","<","|",":","#")
charlen=len(chars)
for i=0 to ubound(ichar)
if instr(chars,ichar(i))>0 then
CheckChar=false
exit function
end if
next
End function
SUB GetAlert(errinfo)
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>CooSel GetAlert Error</TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="V37"></head>
<body leftmargin="0" rightmargin="0" topmargin="0" bgcolor="#D4D0C8">
</BODY>
</HTML>
<SCRIPT LANGUAGE="JavaScript">
<!--
alert("<%=errinfo%>");
window.history.back();
//-->
</SCRIPT><%
if isObject(CONN) then closeDB
response.end
End SUB
Sub CloseDB
CONN.close
Set CONN=nothing
End Sub
Sub MAIN()
%>
<style>
.titlebar {
FONT-WEIGHT: bold; FONT-SIZE: 12pt; FILTER: dropshadow(color=#333333, offx=1, offy=2); WIDTH: 100%; COLOR: #ffffff; FONT-FAMILY: Tahoma,Verdana, Arial, sans-serif; POSITION: relative; TOP: 1px
}
</style>
<FORM METHOD=POST ACTION="?action=1" Name=DBform>
<TABLE width="100%" cellspacing=0 border=0>
<TR bgcolor=#D4D0C8>
<TD align=center height=32><a href=http://www.paintblue.net/ target=_blank><img src=http://www.paintblue.net/bbs/images/TitleLogo.gif border=0></a></td><td><span class=titlebar><font color=#ffffff><b>MiniAccess Editor V1.0 P3 (Access To SQLserver 数据升迁 脚本编写器)</b></font></span></TD>
<td></td></TR>
<TABLE align=center width="100%" cellspacing=1 cellpadding=3 border=0>
</TABLE>
<TABLE align=center width="100%" cellspacing=1 cellpadding=3 border=0>
<TR bgcolor=#667766><TD align=right height=10></TD><TD></TD></TR>
<TR bgcolor=#D4D0C8>
<TD align=right><span id=a>编写模式</span></TD>
<TD>
<INPUT TYPE="radio" NAME="enMode" value="0" <%if enMode=0 then response.write "checked" end if%> >Sql文本
<INPUT TYPE="radio" NAME="enMode" value="1" <%if enMode=1 then response.write "checked" end if%> >Asp代码
<!-- <INPUT TYPE="radio" NAME="enMode" value="2" <%if enMode=2 then response.write "checked" end if%> >编写完后直接运行 -->
<INPUT TYPE="checkbox" NAME="UniCodeMode" value="1" checked> 文本和备注按Unicode导入
</TD>
</TR>
<TR bgcolor=#D4D0C8>
<TD align=right width=250>MDB数据库路径</TD>
<TD><INPUT TYPE="text" NAME="DB_Name" value="<%=DB_Name%>" style="width:70%;"> </TD>
</TR>
<TR bgcolor=#D4D0C8>
<TD align=right width=250>SQLserver登陆帐号(sa)</TD>
<TD><INPUT TYPE="password" NAME="sapass" value="" style="width:30%;"> SQL数据库(sa)登陆密码,可以不用输入,生成完脚本再提供</TD>
</TR>
<TR bgcolor=#D4D0D8>
<TD align=right width=250>导入SQL的后的数据库名</TD>
<TD><INPUT TYPE="text" NAME="databasename" value="<%="myDatabase"%>" style="width:30%;"> </TD>
</TR>
<TR bgcolor=#D4D0D8>
<TD align=right width=250>导入SQL的数据库登陆帐号</TD>
<TD><INPUT TYPE="text" NAME="loginName" value="<%="my_login"%>" style="width:30%;"> </TD>
</TR>
<TR bgcolor=#D4D0D8>
<TD align=right width=250>导入SQL的数据库登陆密码</TD>
<TD><INPUT TYPE="password" NAME="loginPassword" value="<%="my_pass"%>" style="width:30%;"> </TD>
</TR>
<TR bgcolor=#667766><TD align=right height=10></TD><TD></TD></TR>
<TR >
<TD height=38></TD>
<TD bgcolor=#D4D0C8> <INPUT TYPE="submit" value=" 确 定 " style="width:80;"></TD>
</TR>
<TR >
<TD height=38></TD>
<TD bgcolor=#D4D0C8>
<li><<简介>>
<li>For Access 数据库导入 SQLserver 的版本，生成的在SQL2000下执行的 SQL脚本，<br> 除了还原库结构，还同时将Access的数据导入 SQLserver
<br> 由于SQLserver的视图不一样，Access能自动处理同名列，<br> 脚本生成对含Select *有同名列的联合查询作了自动转换，有可能需要对照重修改一下
<li>功能:可编写Access数据库的常用的主要对象,包括 <br> <b>表,视图,索引,约束,包括 默认值,主键,自动编号,外键</b>(表关系)
<li>编写完自动保存为原数据库名+相应扩展的文件
<li>Asp模式可直接生成带表单输入的可执行的Asp文件,用生成的Asp文件即可生成新的数据库
<li>Sql模式可直接生成纯Sql语句文本</li><br><br></TD>
</TR>
</Table>
</FORM>
<%
End SUB
'====MiniAcces Editor1.0part2 Access SQL脚本编写器(V37 PaintBlue.Net 2004 Acp Code)=========
SUB openDB(DB_Name)
if inStr(DB_Name,":/")=0 and inStr(DB_Name,":\")=0 then
DB_Name=server.mappath(DB_Name)
end if
Set CONN = Server.CreateObject("ADODB.CONNection")
on error resume next
CONN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DB_Name
if err.number<>0 then
rw "数据库打开失败,错误为:" & err.description,0
err.clear
else
Set rs=Server.CreateObject("adodb.recordSet")
end if
End SUB
SUB CreateSQL(DB_Name,exec)
'创建模式
'exec = 0 : 生成SQL语句
'exec = 1 : 生成Asp程序
dim tbls,tabsArr,ub,I,TtempStr,TtempStrHead,remchar
dim TableStr
if exec=1 then
TtempStrHead="<" & "% @ LANGUAGE=""VBSCRIPT""%" & ">" & vbcrlf
TtempStrHead=TtempStrHead & "<" & "%Option Explicit" & vbcrlf
TtempStrHead=TtempStrHead & "response.buffer=true" & vbcrlf & vbcrlf
TtempStrHead=TtempStrHead & "'=========================================================================" & vbcrlf & "'Access 数据库 SQL 脚本生成 by MiniAccess Edit V1.0 P2(V37 PaintBlue.Net 2004 Asp Code)" & vbcrlf & "'=========================================================================" & vbcrlf & vbcrlf
end if
if instr(DB_Name,":\")=0 and instr(DB_Name,":/")=0 then
DB_Name=Server.MapPath(DB_Name)
end if
CONNstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DB_Name
Set CONN = Server.CreateObject("ADODB.Connection")
CONN.Open CONNstr
'rs.open "[查询3]",CONN
'for i=0 to rs.fields.count-1
' rw rs(i).name,1
'next
'response.end
'编写CONN对象
if exec=1 then
TtempStr=TtempStr & "SUB CreateDB(DB_Name,NewDB_Name,loginName,loginPassword,sapass,DTS)" & vbcrlf
TtempStr=TtempStr & "DIM CONN,CONNstr" & vbcrlf
'TtempStr=TtempStr & "CONNStr=""Provider=Microsoft.Jet.OLEDB.4.0;Data Source="" & DB_Name" & vbcrlf
TtempStr=TtempStr & "CONNStr=""Provider=SQLOLEDB.1;Password='"" & sapass & ""';Persist Security InFso=true;User ID='sa';Initial Catalog='Master';Data Source='(local)';CONNect Timeout=30""" & vbcrlf
TtempStr=TtempStr & "Set CONN=Server.CreateObject(""ADODB.Connection"")" & vbcrlf
TtempStr=TtempStr & "CONN.open CONNStr" & vbcrlf & vbcrlf
'2004-11-18
TtempStr=TtempStr & "CONN.execute(""Create Database ["" & NewDB_Name & ""]"")" & vbcrlf
TtempStr=TtempStr & "CONN.close" & vbcrlf
TtempStr=TtempStr & "CONNStr=""Provider=SQLOLEDB.1;Password='"" & sapass & ""';Persist Security InFso=true;User ID='sa';Initial Catalog='"" & NewDB_Name & ""';Data Source='(local)';CONNect Timeout=30""" & vbcrlf
TtempStr=TtempStr & "CONN.open CONNStr" & vbcrlf & vbcrlf
'2004-11-18
TtempStr=TtempStr & "CONN.execute(""exec sp_addlogin '"" & loginName & ""','"" & loginPassword & ""','"" & NewDB_Name & ""'"")" & vbcrlf
TtempStr=TtempStr & "CONN.execute(""exec sp_adduser '"" & loginName & ""','"" & loginName & ""','db_owner'"")" & vbcrlf
'-----/
elseif exec=0 then
TtempStr=TtempStr & "Create Database [" & databaseName & "]" & vbcrlf & " go" & vbcrlf
TtempStr=TtempStr & "use [" & databaseName & "]" & vbcrlf & " go" & vbcrlf & vbcrlf
'2004-11-18
TtempStr=TtempStr & "exec sp_addlogin '" & loginName & "','" & loginPassword & "','" & databaseName & "'" & vbcrlf & " go" & vbcrlf
TtempStr=TtempStr & "exec sp_adduser '" & loginName & "','" & loginName & "','db_owner'" & vbcrlf & " go" & vbcrlf
'-----/
end if
'编写表/索引对象
Set tbls=CONN.openSchema(20) 'adSchemaPrimaryKeys
tbls.Filter =" TABLE_TYPE='TABLE' " '筛选出有默认值，但允许null的列
while Not tbls.eof
TableStr=TableStr & "|" & tbls("TABLE_Name")
tbls.movenext
wend
tbls.filter=0
tbls.close
set tbls=nothing
TableStr=mid(TableStr,2)
if exec=1 then
remchar="'"
elseif exec=0 then
remchar="--"
end if
if TableStr<>"" then
tabsArr=split(TableStr,"|")
ub=ubound(tabsArr)
for I=0 to ub
TtempStr=TtempStr & remchar & "[" & tabsArr(I) & "]:" & vbcrlf
TtempStr=TtempStr & CreatTableSql(tabsArr(I),exec) & vbcrlf & vbcrlf
next
end if
'编写数据导入
if exec=1 then TtempStr=TtempStr & "If DTS=1 then " & vbcrlf
TtempStr=TtempStr & CreateOpenDataSource(TableStr,DB_Name,exec)
if exec=1 then TtempStr=TtempStr & "End iF " & vbcrlf
'编写表关系
if TableStr<>"" then TtempStr=TtempStr & CreatForeignSql(exec)
'编写视图
TtempStr=TtempStr & CreatViewSql(exec) & vbcrlf
if exec=1 then
TtempStr=replace(TtempStr,">",""" & chr(62) & """)
TtempStr=replace(TtempStr,"<",""" & chr(60) & """)
TtempStr=TtempStr & "End SUB" & vbcrlf & vbcrlf
TtempStr=TtempStr & Add_aspExec()
TtempStr=TtempStrHead & TtempStr & vbcrlf & "%" & ">"
elseif exec=0 then
TtempStr=TtempStr & "--=========================================================================" & vbcrlf & "--Access To SQL 数据库升迁脚本 by MiniAccess Edit V1.0 P2(V37 PaintBlue.Net 2004)" & vbcrlf & "--=========================================================================" & vbcrlf & vbcrlf
TtempStr = TtempStr & vbCrLf & "--连接字串:CONNstr=""Provider=SQLOLEDB.1;Persist Security InFso=true;Data Source='(local)';Initial Catalog='" & databaseName & "';User ID='" & loginName & "';Password='" & loginPassword & "';CONNect Timeout=30""" & vbCrLf & vbCrLf
end if
call Ados_Write(TtempStr,DB_Name & ExtName,"gb2312")
rw "<br><img width=100 height=0>" & DB_Name & "的SQL脚本编写完成",1
rw "<img width=100 height=0>已经保存文件为<b><font color=blue>" & DB_Name & ExtName & "</font></b>[<a href=?>返回</a>]:",1
rw "<center><textarea style=""width:70%;height:500px;"" wrap=""off"">" & server.Htmlencode(TtempStr) & "</textarea></center>",1
End SUB
function CreatViewSql(exec)
dim cols
dim FKtable,PK_cols,FK_cols,tmpStr,tmpStr1,VIEW_DEFINITION
Set cols=CONN.openSchema(23)
cols.filter=0
while not cols.eof
tmpStr1=""
VIEW_DEFINITION=replace(cols("VIEW_DEFINITION"),chr(13),"")
VIEW_DEFINITION=replace(VIEW_DEFINITION,chr(10)," ")
VIEW_DEFINITION=left(VIEW_DEFINITION,len(VIEW_DEFINITION)-1)
VIEW_DEFINITION=TransView(cols("TABLE_NAME"),VIEW_DEFINITION)
tmpStr1="Create view [dbo].[" & cols("TABLE_NAME") & "] As " & VIEW_DEFINITION & ""
if exec=1 then tmpStr1="CONN.execute(""" & tmpStr1 & """)"
tmpStr=tmpStr & vbcrlf & tmpStr1
if exec=0 then tmpStr=tmpStr & vbcrlf & " go"
cols.movenext
wend
cols.close
set cols=nothing
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -