将Access数据库移植到Oracle笔记
来源:互联网 发布:零件加工erp软件 编辑:程序博客网 时间:2024/05/16 07:31
开始移植工作的时候,尝试了很多自动转换工具,包括access通过odbc导出,csv导入等等,发现有一些问题: 例如得到字段类型转换不能满意,象Boolean被变成Char等等.后来开始自己写转换脚本.
1 最简单的使用SQL插入.所有的表结构通过手工定义.
<%@ LANGUAGE = VBScript CodePage = 936%>
<%
dim ConnStr,conn1,conn2,Db
Db = "olddb.mdb"
ConnStr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & Server.MapPath(db)
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.open ConnStr
Dim SqlDatabaseName,SqlPassword,SqlUsername,SqlLocalName
SqlPassword = "test"
SqlUsername = "SYSTEM"
SqlLocalName = "ORCL"
ConnStr = "Provider = OraOLEDB.Oracle.1; Persist Security Info=True; User ID = " & SqlUsername & "; Password = " & SqlPassword & "; Data Source = " & SqlLocalName & ";"
Set conn2 = Server.CreateObject("ADODB.Connection")
conn2.open ConnStr
sql="select * from XXLX"
Set rs1 = Server.CreateObject("ADODB.RecordSet")
rs1.Open SQL, Conn1, 1, 1
DO WHILE NOT rs1.EOF
sql = "insert into XXLX (" _
& "XXLXID" & "," _
& "BUMEN" & "," _
& "XXLX" & ")" _
&"values('" _
& rs1("XXLXID") & "','" _
& rs1("BUMEN") & "','" _
& rs1("XXLX") & "')"
Response.Write sql & " "
conn2.execute(sql)
rs1.MoveNext
LOOP
rs1.close
set rs1 = Nothing
conn1.close
set conn1=nothing
conn2.close
set conn2=nothing
%>
使用sql方式时候,要注意将字符串里面的' " 转换掉.
If rs1("FILENAME") <> "" Then
filename = Replace(rs1("FILENAME"),"'","''")
filename = Replace(filename,"""","""""")
Else
filename = ""
End If
2 对于有些text字段很长,而oracle的SQL只能4000字的限制.将所有包含lob的字段通过ado 对拷的方式进行.
<%@ LANGUAGE = VBScript CodePage = 936%>
<%
dim ConnStr,conn1,conn2,Db,rs1,rs2
Db = "db.mdb"
ConnStr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & Server.MapPath(db)
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.open ConnStr
Dim SqlDatabaseName,SqlPassword,SqlUsername,SqlLocalName
SqlPassword = "test"
SqlUsername = "SYSTEM"
SqlLocalName = "ORCL"
ConnStr = "Provider = OraOLEDB.Oracle; Persist Security Info=True; User ID = " & SqlUsername & "; Password = " & SqlPassword & "; Data Source = " & SqlLocalName & ";"
Set conn2 = Server.CreateObject("ADODB.Connection")
conn2.open ConnStr
sql="SELECT * FROM MRCH ORDER BY ID ASC"
Set rs1 = Server.CreateObject("ADODB.RecordSet")
rs1.Open SQL, Conn1,1,1
sql="SELECT USERNAME USERNAME,ADDRESS ADDRESS,FILENAME FILENAME,INFO_TYPE INFO_TYPE,""DATE"" ""DATE"",TITLE TITLE,KEYWORD KEYWORD,SUMMARY SUMMARY,DOMAIN DOMAIN,DEPT DEPT,SENSITIVE SENSITIVE,SCBJ SCBJ,TIME TIME,JHXXLX JHXXLX FROM MRCH"
Set rs2 = Server.CreateObject("ADODB.RecordSet")
rs2.Open SQL, Conn2,1,4
Dim i,tmp
i = 0
DO WHILE NOT rs1.EOF
i= i+1
rs2.AddNew
rs2("USERNAME")=rs1("USERNAME")
rs2("ADDRESS")=rs1("ADDRESS")
rs2("FILENAME")=rs1("FILENAME")
rs2("INFO_TYPE")=rs1("INFO_TYPE")
rs2("DATE")=rs1("DATE")
rs2("TITLE")=rs1("TITLE")
rs2("KEYWORD")=rs1("KEYWORD")
rs2("SUMMARY")=rs1("SUMMARY")
rs2("DOMAIN")=rs1("DOMAIN")
rs2("DEPT")=rs1("DEPT")
rs2("SENSITIVE")=rs1("SENSITIVE")
rs2("SCBJ")=rs1("SCBJ")
rs2("TIME")=rs1("TIME")
rs2("JHXXLX")=rs1("JHXXLX")
Response.Write i & rs1("SUMMARY") & rs1("TIME") &"
"
rs1.MoveNext
LOOP
rs2.updatebatch
rs2.close
set rs2 = Nothing
rs1.close
set rs1 = Nothing
conn1.close
set conn1=nothing
conn2.close
set conn2=nothing
%>
这里有三点要说明
1 必须使用oracle自己的oledb,用ms的,有数据类型的问题
2 所有的字段必须显示给出,否则oracle 会省略一些字段名: 例如info_type变成IN
3 ADO记录必须用1 4打开,批处理模式
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=499340
- 将Access数据库移植到Oracle笔记
- 将Access数据库移植到Oracle笔记
- 将Access数据库移植到Oracle笔记
- 将Access数据库移植到Oracle笔记
- 将Access数据库移植到Oracle笔记
- 将Access数据库移植到Oracle笔记
- 将Access数据库移植到Oracle笔记
- 将Access数据库移植到Oracle笔记
- 将Access数据库移植到Oracle笔记
- 将Access数据库移植到Microsoft SQL Server 7.0
- 将Access数据库移植到Microsoft SQL Server 7.0
- 教你如何将Access数据库移植到SQL Server
- 教你如何将Access数据库移植到SQL Server
- 数据库移植(mssql移植到access)
- 数据移植 access数据移植到oracle access导入oracle
- 如何将图片保存到SqlServer、Oracle、Access数据库中。
- 如何将图片保存到SqlServer、Oracle、Access数据库中
- 如何将 Access 数据导出到 Oracle 数据库中?
- 程序员生活中的垃圾收集
- Tapestry Tacos,Java Web框架的Ajax组件
- 开年第一博
- 时间条控件制作简明教程
- Tuscany SCA案例分析(一)(连载中...)
- 将Access数据库移植到Oracle笔记
- BPEL4WS如何无缝的集成到ESB中
- 如何将 BPEL process 运行在 ServiceMix JBI Container 和 Fivesight's PXE上
- 将应用程序变成产品的过程中的十三条生存法则
- 服务器网络监控系统方案
- GData 和 INVOKE
- 使用XML-RPC来控制Webmin
- ADO访问数据库整理
- eXo 门户集成了更多的JCR,CMS,商业智能和Groupware