' **********************************************************' 使用方法' cscript.exe execute.vbs 対象SQLの書かれたファイルのパス' **********************************************************' **********************************************************' SQLコマンド分割文字' **********************************************************
strDelimiter = "/"' **********************************************************' MDB 接続用の dummy.mdb 作成' **********************************************************
Set Fs = CreateObject( "Scripting.FileSystemObject" )
Set Adox = CreateObject( "ADOX.Catalog" )
strCurPath = WScript.ScriptFullName
Set obj = Fs.GetFile( strCurPath )
Set obj = obj.ParentFolder
strCurPath = obj.Path
strTarget = strCurPath & "\dummy.mdb"
on error resume next
Fs.DeleteFile( strTarget )
on error goto 0
ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strTarget & ";"
on error resume next
Adox.Create ConnectionString
if Err.Number <> 0 then
WScript.Echo Err.Description
WScript.Quit
end if
on error goto 0
Set Adox = Nothing
' **********************************************************' 接続文字列作成' **********************************************************
ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strTarget & ";"
Set WshShell = CreateObject("WScript.Shell")
if WScript.Arguments.Count <> 0 then
ScriptPath = WScript.Arguments(0)
else
MsgBox "SQL が書かれたテキストへのパスを指定して下さい "
Wscript.Quit
end if
if Trim(ConnectionString) = "" then
MsgBox "接続文字列を指定して下さい "
Wscript.Quit
end if
if vbOk <> MsgBox( "対象ファイルは " & ScriptPath & " です" & vbCrLf & vbCrLf & _
"実行前に現在の接続文字列を確認して下さい-> " & _
vbCrlf & vbCrLf & ConnectionString, vbOkCancel ) then
Wscript.Quit
end if
' **********************************************************' オブジェクト作成' **********************************************************
Set Cn = CreateObject( "ADODB.Connection" )
' **********************************************************' 接続' **********************************************************
on error resume next
Cn.Open ConnectionString
if Err.Number <> 0 then
Wscript.echo Err.Description
Wscript.quit
end if
on error goto 0
' **********************************************************' 読み出し' **********************************************************
Set fp = Fs.OpenTextFile( ScriptPath, 1 )
strCommand = " "
nCommand = 0
Do While fp.AtEndOfStream <> True
Buff = fp.ReadLine
if not Left( Trim( Buff ), 2 ) = "--" then
aCommand = Split(strCommand,strDelimiter)
if Ubound( aCommand ) <> 0 then
if Trim(aCommand(0)) <> "" then
on error resume next
nCommand = nCommand + 1
if nCommand mod 1000 = 0 then
Call WshShell.LogEvent( 4, _
"ADOによる更新SQLのバッチ実行 : " & _
nCommand & " 件の処理が終了しました" )
end if
Wscript.Echo nCommand & ":" & aCommand(0)
Cn.Execute aCommand(0)
if Err.Number <> 0 then
Wscript.Echo nCommand & ":ERR:" & Err.Description
end if
on error goto 0
end if
For i = 1 to Ubound( aCommand )
if i = 1 then
strCommand = aCommand(i)
else
strCommand = strCommand & strDelimiter & aCommand(i)
end if
Next
end if
strCommand = strCommand & vbCrLf & Buff
end if
Loop
fp.Close
aCommand = Split(strCommand,strDelimiter)
For i = 0 to Ubound( aCommand )
if Trim(Replace(aCommand(i),vbCrLf,"")) <> "" then
on error resume next
nCommand = nCommand + 1
Wscript.Echo nCommand & ":" & aCommand(i)
Cn.Execute aCommand(i)
if Err.Number <> 0 then
Wscript.Echo nCommand & ":ERR:" & Err.Description
end if
on error goto 0
end if
Next
' **********************************************************' 接続解除' **********************************************************
Cn.Close
MsgBox ScriptPath & vbCrLf & "を使用した更新 SQL のバッチ実行が終了しました "
実行サンプルテキスト
拡張子:
select *
into [ODBC;DSN=COPY_OUT;UID=ora_01;PWD=ora_01].[社員マスタ1]
from
[ODBC;DSN=COPY_IN;UID=lightbox;PWD=lightbox].[lightbox.社員マスタ]
/
select *
into [ODBC;DSN=COPY_OUT;UID=ora_01;PWD=ora_01].[社員マスタ2]
from
[ODBC;DSN=COPY_IN;UID=lightbox;PWD=lightbox].[lightbox.社員マスタ]
/
select *
into [ODBC;DSN=COPY_OUT;UID=ora_01;PWD=ora_01].[社員マスタ3]
from
[ODBC;DSN=COPY_IN;UID=lightbox;PWD=lightbox].[lightbox.社員マスタ]
実行サンプル
拡張子:
C:\user\lightbox\Oracle>cscript execute_mdb.vbs oracle_in_mdb.sql
Microsoft (R) Windows Script Host Version 5.7
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
1:
select *
into [ODBC;DSN=COPY_OUT;UID=ora_01;PWD=ora_01].[社員マスタ1]
from
[ODBC;DSN=COPY_IN;UID=lightbox;PWD=lightbox].[lightbox.社員マスタ]
2:
select *
into [ODBC;DSN=COPY_OUT;UID=ora_01;PWD=ora_01].[社員マスタ2]
from
[ODBC;DSN=COPY_IN;UID=lightbox;PWD=lightbox].[lightbox.社員マスタ]
3:
select *
into [ODBC;DSN=COPY_OUT;UID=ora_01;PWD=ora_01].[社員マスタ3]
from
[ODBC;DSN=COPY_IN;UID=lightbox;PWD=lightbox].[lightbox.社員マスタ]