VBScriptでフォルダ内のSQLを読んで実行結果をTAB区切りファイルに出力する

gistfile1.vb

Option Explicit

Private fs
Private conn

'結果出力
Private Sub writeResult(rs, sqlFile)
	'上書きか、追加書き込みか
	Dim mode: mode = "2" '2:ForWriting
	If WScript.Arguments.Count > 2 Then
		If WScript.Arguments(2) = "1" Then
			mode = "8" '8:ForAppend
		End If
	End If

	'出力フォルダ
	Dim outFolder: outFolder = WScript.Arguments(1)
	If Right(outFolder, 1) <> "\" Then
		outFolder = outFolder & "\"
	End If

	'出力ファイル
	Dim tsv: Set tsv = fs.OpenTextFile(outFolder & Replace(UCase(sqlFile.Name), ".SQL", ".tsv"), mode, True)

	'項目名 出力
	Dim iCol
	If mode = "2" Then '2:ForWriting
		For iCol = 1 To rs.Fields.Count
			tsv.Write rs.Fields(iCol - 1).Name & vbTab
		Next
	End If
	tsv.WriteLine

	'値 出力
	Do Until rs.EOF
		For iCol = 1 To rs.Fields.Count
			tsv.Write rs.Fields(iCol - 1).Value & vbTab
		Next
		tsv.WriteLine

		rs.MoveNext
	Loop

	tsv.Close
	Set tsv = Nothing
End Sub

'SQL 読み込み
Private Function getSql(sqlFile)
'	WScript.Echo sqlFile.Path
	WScript.Echo sqlFile.Name

	Dim tsSql:   Set tsSql = fs.OpenTextFile(sqlFile.Path)
	Dim sqlText: sqlText   = tsSql.ReadAll
'	WScript.Echo sqlText
	tsSql.Close
	Set tsSql = Nothing

	getSql = sqlText
End Function

'SQL 実行
Private Function openRecordSet(sqlText)
	Dim rs: Set rs = CreateObject("ADODB.Recordset")
	With rs
		.ActiveConnection = conn
		.CursorType       = 0 'adOpenForwardOnly
		.LockType         = 1 'adLockReadOnly
		.Source           = sqlText
		.Open
	End With

	Set openRecordSet = rs
End Function

'ＤＢ接続
Private Sub openDB()
	Set conn = CreateObject("ADODB.Connection")
	conn.Open "aaa","bbb","ccc"
End Sub

'ＤＢ切断
Private Sub closeDB()
	conn.Close
	Set conn = Nothing
End Sub

'主処理
Private Sub Main()
	Set fs = CreateObject("Scripting.FileSystemObject")

	'ＤＢ接続
	openDB

	'指定フォルダ内の全ＳＱＬファイルに対して処理を繰り返す
	Dim sqlFolder: Set sqlFolder = fs.GetFolder(WScript.Arguments(0))
	Dim sqlFile
	For Each sqlFile In sqlFolder.Files
		'SQL 読み込み
		Dim sqlText: sqlText = getSql(sqlFile)
		'SQL 実行
		Dim rs: Set rs = openRecordSet(sqlText)
		'結果出力
		Call writeResult(rs, sqlFile)
		rs.Close
		Set rs = Nothing
	Next
	Set sqlFile   = Nothing
	Set sqlFolder = Nothing

	'ＤＢ切断
	closeDB

	Set fs = Nothing
End Sub

'主処理 呼び出し
Call Main()

'実行形式
'cscript //nologo OracleToTsv.vbs "c:\sql_folder" "c:\result\"
'cscript //nologo OracleToTsv.vbs "c:\sql_folder" "c:\result\" 1