?
Solved

FileSystemObject and reading multiple script files

Posted on 2005-04-05
1
Medium Priority
?
264 Views
Last Modified: 2008-03-17
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
Comment
Question by:jayme9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 9

Accepted Solution

by:
mrGreen earned 200 total points
ID: 13706749
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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question