Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

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.FileSystemObject")
Set cn = CreateObject("ADODB.Connection")
'test this line
path = "C:\Inetpub\wwwroot\INBOX\JASP1-TRACS-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
0
jayme9
Asked:
jayme9
1 Solution
 
mrGreenCommented:
give this a try, might want to test the lines that get your paths first:

especially this line: Set sqlScript = fso.OpenTextFile(path & objFile.Name, ForReading)

do a reponse.write the end to see what   path & objFile.Name returns



'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.FileSystemObject")
Set cn = CreateObject("ADODB.Connection")

'test this line
'path = "C:\Inetpub\wwwroot\INBOX\JASP1-TRACS-dbo-tbl_contactPOC.sql"
path = Server.Mappath("/INBOX/") & "\"
Set folder = fso.GetFolder(path)
cn.ConnectionString = sConn
cn.Open

For Each objFile in folder.Files

      Set sqlScript = fso.OpenTextFile(path & objFile.Name, ForReading)      
      
      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

Next

cn.Close

Set cn = Nothing
Set fso = Nothing

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now