jayme9
asked on
FileSystemObject and reading multiple script files
Goal: A vbscript that will execute multiple script files from directory that include batches with "GO"
Status: The code below allows me to successfully execute ONLY ONE script file, but I need to alter it a bit to loop through multiple script files in a specific directory(folder).
Also, in experimenting, I have several commented out lines of code, but still could not get it to work.
Question: How do I (1) specify the specific folder (2) loop through all the script files within this folder?
Thank you in advance.
'------------------------- ---------- ---------- ---------- ---------- -----code- ---------- ------
'Use Common.asp include file to get connection string
Dim DBTRACS
Set DBTRACS = new clsDBTRACS
DBTRACS.Open
sConn = DBTRACS.ConnectionString
DBTRACS.Close
Const ForReading = 1, ForWriting = 2
Dim cn, path, folder, File, fso, sConn, intCommentBegin, intCommentEnd, sqlScript, strAfterComment, strBeforeComment, strLine, strToGo, wshShell
'Execute sql script file(s) in directory that contain batches with "GO"
Set fso = CreateObject("Scripting.Fi leSystemOb ject")
Set cn = CreateObject("ADODB.Connec tion")
'test this line
path = "C:\Inetpub\wwwroot\INBOX\ JASP1-TRAC S-dbo-tbl_ contactPOC .sql"
'path = Server.Mappath("/INBOX/") & "\"
'Set folder = fso.GetFolder(path)
cn.ConnectionString = sConn
cn.Open
'For Each File in folder.Files
'Set sqlScript = fso.OpenTextFile(path, ForReading)
Set sqlScript = fso.OpenTextFile(path, ForReading)
'Next
Response.write path
While sqlScript.AtEndOfStream = False
strLine = sqlScript.ReadLine()
If StrComp(Trim(strLine),"go" ,1) = 0 Then
If Len(Trim(strToGo)) <> 0 Then
cn.Execute strToGo, , adCmdText
strToGo = ""
End If
Else
While InStr(1, strLine, "/*", 1) <> 0
intCommentBegin = InStr(1, strLine, "/*", 1)
strBeforeComment = Trim(Left(strLine, intCommentBegin - 1))
If Len(strBeforeComment) <> 0 Then
strToGo = strToGo & Chr(13) & strBeforeComment
End If
While InStr(intCommentBegin, strLine, "*/", 1) = 0
strLine = strLine & Chr(13) & sqlScript.ReadLine()
Wend
intCommentEnd = InStr(intCommentBegin, strLine, "*/", 1) + 1
strLine = Trim(Right(strLine, Len(strLine) - intCommentEnd))
Wend
strToGo = strToGo & Chr(13) & strLine
End If
Wend
If Len(Trim(strToGo)) <> 0 Then
cn.Execute strToGo, , adCmdText
strToGo = ""
End If
cn.Close
Set cn = Nothing
Set fso = Nothing
Status: The code below allows me to successfully execute ONLY ONE script file, but I need to alter it a bit to loop through multiple script files in a specific directory(folder).
Also, in experimenting, I have several commented out lines of code, but still could not get it to work.
Question: How do I (1) specify the specific folder (2) loop through all the script files within this folder?
Thank you in advance.
'-------------------------
'Use Common.asp include file to get connection string
Dim DBTRACS
Set DBTRACS = new clsDBTRACS
DBTRACS.Open
sConn = DBTRACS.ConnectionString
DBTRACS.Close
Const ForReading = 1, ForWriting = 2
Dim cn, path, folder, File, fso, sConn, intCommentBegin, intCommentEnd, sqlScript, strAfterComment, strBeforeComment, strLine, strToGo, wshShell
'Execute sql script file(s) in directory that contain batches with "GO"
Set fso = CreateObject("Scripting.Fi
Set cn = CreateObject("ADODB.Connec
'test this line
path = "C:\Inetpub\wwwroot\INBOX\
'path = Server.Mappath("/INBOX/") & "\"
'Set folder = fso.GetFolder(path)
cn.ConnectionString = sConn
cn.Open
'For Each File in folder.Files
'Set sqlScript = fso.OpenTextFile(path, ForReading)
Set sqlScript = fso.OpenTextFile(path, ForReading)
'Next
Response.write path
While sqlScript.AtEndOfStream = False
strLine = sqlScript.ReadLine()
If StrComp(Trim(strLine),"go"
If Len(Trim(strToGo)) <> 0 Then
cn.Execute strToGo, , adCmdText
strToGo = ""
End If
Else
While InStr(1, strLine, "/*", 1) <> 0
intCommentBegin = InStr(1, strLine, "/*", 1)
strBeforeComment = Trim(Left(strLine, intCommentBegin - 1))
If Len(strBeforeComment) <> 0 Then
strToGo = strToGo & Chr(13) & strBeforeComment
End If
While InStr(intCommentBegin, strLine, "*/", 1) = 0
strLine = strLine & Chr(13) & sqlScript.ReadLine()
Wend
intCommentEnd = InStr(intCommentBegin, strLine, "*/", 1) + 1
strLine = Trim(Right(strLine, Len(strLine) - intCommentEnd))
Wend
strToGo = strToGo & Chr(13) & strLine
End If
Wend
If Len(Trim(strToGo)) <> 0 Then
cn.Execute strToGo, , adCmdText
strToGo = ""
End If
cn.Close
Set cn = Nothing
Set fso = Nothing
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.