option explicit
wscript.echo "Copying Website tables from Oracle to MSSQL"
' - Copy website tables
' - Declare variables
dim conDBOracleRead, conDBCommonRead, objFSO
dim rexObj, objFile, strConnectionStrings, aryConnectionStrings
dim intCount, strConnectionStringName, strConnectionStringContent
dim hashConnectionStrings, strContent, strWebTreeContent
dim aryOracleColumnNames, aryMSColumnNames, aryDataTypes
dim intNumRecords
' - Initialize variables
set conDBOracleRead=CreateObject("ADODB.Recordset")
set conDBCommonRead=CreateObject("ADODB.Recordset")
set objFSO=wscript.createobject("scripting.filesystemobject")
set hashConnectionStrings=createobject("scripting.dictionary")
set rexObj=new RegExp
rexObj.ignorecase=true
rexObj.global=true
' - Get connection strings from global.asa
set objFile=objFSO.opentextfile("\internet public\wwwroot\global.asa")
strConnectionStrings=objFile.readall
set objFile=nothing
for intCount=0 to ubound(aryConnectionStrings)
if instr(aryConnectionStrings(intCount), "application(")=0 then
aryConnectionStrings(intCount)=""
end if
if aryConnectionStrings(intCount)<>"" then
strConnectionStringName=aryConnectionStrings(intCount)
strConnectionStringName=mid(strConnectionStringName, instr(strConnectionStringName, chr(34))+1)
strConnectionStringName=left(strConnectionStringName, instr(strConnectionStringName, chr(34))-1)
strConnectionStringContent=aryConnectionStrings(intCount)
strConnectionStringContent=mid(strConnectionStringContent, instr(strConnectionStringContent, "=")+1)
strConnectionStringContent=replace(strConnectionStringContent, chr(34), "")
hashConnectionStrings(strConnectionStringName)=strConnectionStringContent
end if
next
' - Insert CLOBRead ASP function
' - CLOBRead(strTable, strCLOBColumn, strWhereClause, strConnectionString)
' - Note: I read the CLOBRead function from a database (overall, easier administration than using include files).
' - Take the CLOBRead function (from williammeitzen.netfirms.com), and replace:
' - FROM server.createobject TO createobject
' - FROM response.write TO wscript.echo
'conDBCommonRead.open "select dbms_lob.substr(content, dbms_lob.getlength(content), 1) as content from common_stuff where title='CLOBRead'", hashConnectionStrings("CommonStuffRead")
conDBCommonRead.open "SELECT content FROM common WHERE title='CLOBRead'", hashConnectionStrings("CommonRead")
strContent=conDBCommonRead("content")
conDBCommonRead.close
rexObj.pattern="server.createobject"
strContent=rexObj.replace(strContent, "createobject")
rexObj.pattern="response.write"
strContent=rexObj.replace(strContent, "wscript.echo")
execute(strContent)
' - Insert CLOBUpdate ASP subroutine
' - CLOBUpdate(strTableCU, strCLOBColumnCU, strContentCU, strWhereClauseCU, strConnectionStringCU)
' - Note: I read the CLOBUpdate function from a database (overall, easier administration than using include files).
' - Take the CLOBUpdate function (from williammeitzen.netfirms.com), and replace:
' - FROM server.createobject TO createobject
' - FROM response.write TO wscript.echo
'strContent=CLOBRead("common_stuff", "content", "title='CLOBUpdate'", hashConnectionStrings("CommonStuffRead"))
conDBCommonRead.open "SELECT content FROM common WHERE title='CLOBUpdate'", hashConnectionStrings("CommonRead")
'execute(conDBCommonRead("content"))
strContent=conDBCommonRead("content")
conDBCommonRead.close
rexObj.pattern="server.createobject"
strContent=rexObj.replace(strContent, "createobject")
rexObj.pattern="response.write"
strContent=rexObj.replace(strContent, "wscript.echo")
execute(strContent)
'wscript.quit
sub CopyData(strOracleTablename, strOracleKeyColumnname, intOracleKeyDatatype, aryOracleColumnNames, strOracleConnectionReadString, strMSTablename, aryMSColumnNames, aryDatatypes, strMSConnectionWriteString)
dim aryOracleData(200)
dim strOracleRead
dim conDBMSWrite
dim strMSColumnNames
dim strMSColumnData
dim strMSWrite
dim intRecordsAffected
wscript.echo "Copying Oracle table '" & strOracleTablename & "' to MSSQL table '" & strMSTablename & "' ..."
if ubound(aryOracleColumnNames)<>ubound(aryMSColumnNames) or ubound(aryOracleColumnNames)<>ubound(aryDatatypes) then
wscript.echo "Number of Oracle column names, MS Column names, and Data types do not match. Halting."
wscript.quit
end if
intNumRecords=0
set conDBOracleRead=CreateObject("ADODB.Recordset")
set conDBMSWrite=CreateObject("ADODB.Connection")
' - erase all existing data in destination MSSQL table
strMSWrite="delete from " & strMSTablename
conDBMSWrite.open strMSConnectionWriteString
conDBMSWrite.BeginTrans
conDBMSWrite.Execute strMSWrite, intRecordsAffected
conDBMSWrite.CommitTrans
conDBMSWrite.close
' - create string to read data from Oracle table
strOracleRead="select "
for intCount=0 to ubound(aryOracleColumnNames)
strOracleRead=strOracleRead & aryOracleColumnNames(intCount) & ", "
next
' - insert data into MSSQL table
' - create string to write data to MSSQL table
strMSWrite="insert into " & strMSTablename & " (" & strMSColumnNames & ") values (" & strMSColumnData & ")"
' wscript.echo strMSWrite
conDBMSWrite.open strMSConnectionWriteString
conDBMSWrite.BeginTrans
conDBMSWrite.Execute strMSWrite, intRecordsAffected
if intRecordsAffected=1 then
conDBMSWrite.CommitTrans
else
wscript.echo "More than 1 row would be affected by this SQL command. Halting."
wscript.echo strMSWrite
conDBMSWrite.RollbackTrans
end if
conDBMSWrite.close
conDBOracleRead.movenext
' - wend loop - read Oracle table data
wend
conDBOracleRead.close
wscript.echo " (" & intNumRecords & " records copied)"
set conDBMSWrite=nothing
set conDBOracleRead=nothing
end sub
' - data types (aryDataTypes):
' - 1: convert from Oracle number to MS integer
' - 2: convert from Oracle number to MS float
' - 3: convert from Oracle varchar2 to MS varchar (or text)
' - 4: convert from Oracle clob to MS text
' - 5: convert from Oracle date to MS datetime
' - 6: convert from Oracle number to MS bit
' - 7: convert from Oracle clob to MS varchar (or text)
' - Oracle key datatypes (intOracleKeyDatatype):
' - 1: number
' - 2: varchar2
' - CopyData(strOracleTablename, strOracleKeyColumnname, intOracleKeyDatatype, aryOracleColumnNames, strOracleConnectionReadString, strMSTablename, aryMSColumnNames, aryDatatypes, strMSConnectionWriteString)
