Below is a little web API hack I put together to locate static reports (in sub folders named 'UNIT TEST'), generate SQL for each report and capture the SQL into a date/time stamped filename on a drive local to the web server. It can assist with automating some of the mundane checks needed after performing an architecture change such as adding new data structures to an existing project. One may then compare old vs new SQL via your fav file comparison tool to ensure report results will not be adversely affected by the change.
Enjoy!
-- dmcmunn
<%@LANGUAGE=VBSCRIPT%>
<%
' --------------------------------------------------------------------------
dim sPass
sPass = Request.Form("pass") & ""
if len(sPass) = 0 then
InitialForm
else
if sPass = "filled" then ProcessForm: End If
if sPass = "processed" then NotifyComplete: End If
end if
' --------------------------------------------------------------------------
' --------------------------------------------------------------------------
Sub NotifyComplete
Response.Write ("<HTML>")
Response.Write ("<HEAD><TITLE>Get MSI 7.x Static Report SQL</TITLE></HEAD>")
Response.Write ("<BODY>")
Response.Write ("<H3>Processing Complete</H3>")
Response.Write ("</BODY>")
Response.Write ("</HTML>")
End Sub
' --------------------------------------------------------------------------
' --------------------------------------------------------------------------
Sub InitialForm
%>
<HTML>
<HEAD><TITLE>Get MSI 7.x Static Report SQL</TITLE></HEAD>
<BODY>
<FORM NAME="request" Action="GetReportSQL2.asp" method="POST">
<H3>Snapshot MSI 7.x Project Unit Test Static Report SQL</H3><BR>
<CENTER>
<TABLE CELLSPACING=3 CELLPADDING=3 border=1>
<TR>
<TD ALIGN="right">I-Server Name: </TD>
<TD><INPUT TYPE="text" size="30" name="isrv"> </TD>
</TR>
<TR>
<TD ALIGN="right">Project Name: </TD>
<TD><INPUT TYPE="text" size="30" name="pname"> </TD>
</TR>
<TR>
<TD ALIGN="right">Metadata DSN: </TD>
<TD><INPUT TYPE="text" size="30" name="dsn"> </TD>
</TR>
<TR>
<TD ALIGN="right">Metadata Owner: </TD>
<TD><INPUT TYPE="text" size="30" name="own"> </TD>
</TR>
<TR>
<TD ALIGN="right">Metadata UserID: </TD>
<TD><INPUT TYPE="text" size="30" name="uid"> </TD>
</TR>
<TR>
<TD ALIGN="right">Metadata Password: </TD>
<TD><INPUT TYPE="password" size="30" name="pwd"> </TD>
</TR>
<TR>
<TD ALIGN="right">MSI UserID: </TD>
<TD><INPUT TYPE="text" size="30" name="muid"> </TD>
</TR>
<TR>
<TD ALIGN="right">MSI Password: </TD>
<TD><INPUT TYPE="password" size="30" name="mpwd"> </TD>
</TR>
</TABLE>
<P>
<CENTER>
<INPUT TYPE="submit" name="snap" id="snap" value="Snapshot">
<INPUT TYPE="reset" value="Clear">
</CENTER>
<INPUT TYPE="hidden" name="pass" id="pass" value="filled">
</P>
</center>
</FORM>
</BODY>
</HTML>
<%
End Sub
'-------------------------------------------------------------------------------
' USAGE
' Save reports with their prompted values into folders with the name 'UNIT TEST'
' and run this application
' You will be asked to provide the following information:
' Name of the I-Server
' Name of the project
' Metadata database DSN Name for project
' Metadata database owner name
' Metadata database userid
' Metadata database password
' MSI Project userid
' MSI Project password
'
' FUNCTION
' The application will:
' 1. return a list of report objects contained inside 'UNIT TEST' folders for
' the project;
' 2. submit each one to the I-Server for SQL generation;
' 3. retrieve the SQL from the I-Server;
' 4. store each report's SQL in a unique file on the web server
'
' PURPOSE
' This was originally developed to snapshot all of the SQL for basic reports to
' provide a benchmark against which future Microstrategy architecture changes
' may be compared to provide an informed impact assessment.
'-------------------------------------------------------------------------------
' MODIFICATION HISTORY:
'-------------------------------------------------------------------------------
' Date Init Description
'-------------------------------------------------------------------------------
'
'-------------------------------------------------------------------------------
'
' --------------------------------------------------------------------------
' --------------------------------------------------------------------------
Sub ProcessForm()
const MY_REPORT_NAME = 0
const MY_REPORT_GUID = 1
Dim oConn, sConn, oRS
Dim sGET_UNIT_TEST_REPORT_SQL
Dim sREPORT_SQL_PATH
Dim sSQL
Dim vReport
Dim i
Dim sProjectName
Dim sIServerName
Dim sMetadataDSN
Dim sMetadataOwner
Dim sMetadataUserID
Dim sMetadataPassword
Dim sFQFilename
Dim sReportSQL
Dim sReportGUID
Dim sReportName
Dim oServerSession
Dim lngSessionFlags
sGET_UNIT_TEST_REPORT_SQL = "select object_name, object_id from dssmdobjinfo where parent_id = (select object_id from dssmdobjinfo where object_name = 'UNIT TEST' and Project_Id = (select project_id from dssmdobjinfo where parent_id = '00000000000000000000000000000000' and object_name = '@PROJECT_NAME') and object_type = 8 ) and object_type = 3 order by object_name"
sProjectName = trim(Request.Form("pname") & "")
sIServerName = trim(Request.Form("isrv") & "")
sMetadataDSN = trim(Request.Form("dsn") & "")
sMetadataOwner = trim(Request.Form("own") & "")
sMetadataUserID = trim(Request.Form("uid") & "")
sMetadataPassword = trim(Request.Form("pwd") & "")
sMSIUserID = trim(Request.Form("muid") & "")
sMSIPassword = trim(Request.Form("mpwd") & "")
Response.Write("<HTML>")
Response.Write("<HEAD><TITLE>Processing Static MSI 7.x Report SQL...</TITLE></HEAD>")
Response.Write("<BODY>")
Response.Write("<H3>Processing Static MSI 7.x Report SQL...</H3>")
Response.Write("<BR>Project Name: [" & sProjectName & "]")
Response.Write("<BR>I-ServerName: [" & sIServerName & "]")
sSQL = Replace(sGET_UNIT_TEST_REPORT_SQL, "@PROJECT_NAME", sProjectName)
sREPORT_SQL_PATH = "E:\\StaticReports"
Response.Write("<BR>Output to: [" & sREPORT_SQL_PATH & "\\" & sProjectName & "]")
Set oConn = Server.CreateObject("ADODB.Connection")
Set oRS = Server.CreateObject("ADODB.Recordset")
sConn = "DSN=" & trim(sMetadataDSN) & "; UID=" & trim(sMetadataUserID) & "; PWD=" & trim(sMetadataPassword)
oConn.Open sConn
set oRS = oConn.Execute(sSQL)
if oRS.EOF = false then
vReport = oRS.GetRows()
end if
oRS.Close
oConn.Close
set oRS = nothing
set oConn = nothing
if isarray(vReport) then
Response.Write("<BR>Processing [" & CStr(ubound(vReport,2)+1) & "] reports...")
for i = 0 to ubound(vReport,2)
sReportGUID = trim(vReport(MY_REPORT_GUID,i) & "")
sReportName = trim(vReport(MY_REPORT_NAME,i) & "")
Response.Write("<BR>" & sReportName)
Response.Flush
sReportSQL = GetReportSQL(sReportName, sReportGUID, sProjectName, sIServerName, sMSIUserID, sMSIPassword)
SaveSQLToLocalFile sREPORT_SQL_PATH, sProjectName, sReportName, sReportSQL
next
else
Response.Write("No UNIT TEST reports found")
end if
Response.Write("</BODY>")
Response.Write("</HTML>")
Response.Flush
Response.End
End Sub
' --------------------------------------------------------------------------
' --------------------------------------------------------------------------
Function GetReportSQL(sReportName, sReportID, sProjectName, sIServerName, sUserID, sPass)
Dim woFactory
Dim Isession
Dim sessionInfo
Dim wrpSource
Dim objSource
Dim wrpInstance
Dim reportID
Dim rpResultData
Dim rpStatus
Dim SessionID
Dim oServerSession
Dim lngSessionFlags
Set woFactory = Server.CreateObject("MBJWOBRI.WebObjectsFactory.1")
Set Isession = woFactory.IServerSession
Isession.serverName = sIServerName
Isession.ServerPort = 0
Isession.projectName = sProjectName
Set sessionInfo = Isession.Info
sessionInfo.login = sUserID
sessionInfo.PassWord = sPass
SessionID = sessionInfo.SessionID
'get report source
Set wrpSource = woFactory.ReportSource
'specify report ID
reportID = sReportID
wrpSource.ResultFlags = DssXmlResultXmlSQL
wrpSource.ExecutionFlags = DssXmlExecutionGenerateSQL
'create report instance
Set wrpInstance = wrpSource.GetNewInstance(reportID)
wrpInstance.Async = false
wrpInstance.MaxWait = 120000
wrpInstance.PollingFrequency = 250
rpStatus = wrpInstance.pollStatus
If rpStatus <> DssXMLStatusResult Then
Response.Write "Error:report status=" & rpStatus
Response.End
End If
'retrieving report SQL from WebReportData object
Set rpResultData = wrpInstance.GetResults()
Set oServerSession = Server.CreateObject("MSIXMLLIB.DSSXMLServerSession.1")
lngSessionFlags = DssXmlSessionCloseDeleteUnreadMsgs
oServerSession.CloseSession sessionID, lngSessionFlags
GetReportSQL = rpResultData.SQL
Set rpResultData = Nothing
Set wrpInstance = Nothing
Set wrpSource = Nothing
Set sessionInfo = Nothing
Set Isession = Nothing
Set woFactory = Nothing
END FUNCTION
' ----------------------------------------------------
' ----------------------------------------------------
Sub SaveSQLToLocalFile(sPath, sProject, sReportName, sSQL)
Dim oFileSystem
Dim oFolder
Dim oTextFile
Dim sFQFilename
Dim sDate
Dim sFmtSQL
on error resume next
Set oFileSystem = CreateObject("Scripting.FileSystemObject")
if err.number <> 0 then
Response.Write( "Error Creating FSO [#" & cstr(err.number) & "] - " & err.description & " in " & err.source)
end if
' build a date formatted like "_yyyymmdd_hhmm" to append to the reportname for uniqueness
sDate = "_" & Datepart("yyyy", Now) & right("00" & Datepart("m", Now), 2) & right("00" & Datepart("d", Now), 2) & "_" & right("00" & Datepart("h", Now), 2) & right("00" & Datepart("n", Now), 2)
If not oFileSystem.FolderExists(sPath & "\\" & sProject) Then
oFileSystem.CreateFolder sPath & "\\" & sProject
End If
sFQFilename = sPath & "\\" & sProject & "\\" & sReportName & sDate & ".sql"
Set oTextFile = oFileSystem.CreateTextFile(sFQFilename, True)
if err.number <> 0 then
Response.Write( "Error CreateTextFile [#" & cstr(err.number) & "] - " & err.description & " in " & err.source)
end if
sFmtSQL = Replace(sSQL, chr(10), chr(13) + chr(10))
oTextFile.Write(sFmtSQL)
if err.number <> 0 then
Response.Write( "Error Write [#" & cstr(err.number) & "] - " & err.description & " in " & err.source)
end if
oTextFile.Close
if err.number <> 0 then
Response.Write( "Error Close [#" & cstr(err.number) & "] - " & err.description & " in " & err.source)
end if
set oTextFile = nothing
set oFolder = nothing
set oFileSystem = nothing
End Sub
' ----------------------------------------------------
function reformatSQL2 (sSQL)
' ----------------------------------------------------
dim re
Set re = new regexp 'Create the RegExp object
re.Global = true
re.Pattern = "^$"
sSQL = re.replace(sSQL, "/")
set re = nothing
reformatSQL2 = sSQL
end function
' ----------------------------------------------------
' crude format of the sql returned
' ----------------------------------------------------
function reformatSQL (sSQL)
dim brk
dim re
Set re = new regexp 'Create the RegExp object
brk = "<BR>"
re.Global = true
re.Pattern = "create table "
sSQL = re.replace(mySQL, brk + brk + "CREATE TABLE ")
re.Pattern = "select "
sSQL = re.replace(mySQL, brk + "SELECT ")
re.Pattern = "from "
sSQL = re.replace(mySQL, brk + "FROM ")
re.Pattern = "where "
sSQL = re.replace(mySQL, brk + "WHERE ")
re.Pattern = "and "
sSQL = re.replace(mySQL, brk + "AND ")
re.Pattern = "group by "
sSQL = re.replace(mySQL, brk + "GROUP BY ")
re.Pattern = "ANALYZE TABLE "
sSQL = re.replace(mySQL, brk + brk + "ANALYZE TABLE ")
re.Pattern = "drop table "
sSQL = re.replace(mySQL, brk + brk + "DROP TABLE ")
re.Pattern = ","
sSQL = re.replace(mySQL, brk + ", ")
re.Pattern = "^<BR><BR>$"
sSQL = re.replace(mySQL, brk + "/" + brk)
set re = nothing
reformatSQL = sSQL
end function
' ----------------------------------------------------
%>
Enjoy!
-- dmcmunn
<%@LANGUAGE=VBSCRIPT%>
<%
' --------------------------------------------------------------------------
dim sPass
sPass = Request.Form("pass") & ""
if len(sPass) = 0 then
InitialForm
else
if sPass = "filled" then ProcessForm: End If
if sPass = "processed" then NotifyComplete: End If
end if
' --------------------------------------------------------------------------
' --------------------------------------------------------------------------
Sub NotifyComplete
Response.Write ("<HTML>")
Response.Write ("<HEAD><TITLE>Get MSI 7.x Static Report SQL</TITLE></HEAD>")
Response.Write ("<BODY>")
Response.Write ("<H3>Processing Complete</H3>")
Response.Write ("</BODY>")
Response.Write ("</HTML>")
End Sub
' --------------------------------------------------------------------------
' --------------------------------------------------------------------------
Sub InitialForm
%>
<HTML>
<HEAD><TITLE>Get MSI 7.x Static Report SQL</TITLE></HEAD>
<BODY>
<FORM NAME="request" Action="GetReportSQL2.asp" method="POST">
<H3>Snapshot MSI 7.x Project Unit Test Static Report SQL</H3><BR>
<CENTER>
<TABLE CELLSPACING=3 CELLPADDING=3 border=1>
<TR>
<TD ALIGN="right">I-Server Name: </TD>
<TD><INPUT TYPE="text" size="30" name="isrv"> </TD>
</TR>
<TR>
<TD ALIGN="right">Project Name: </TD>
<TD><INPUT TYPE="text" size="30" name="pname"> </TD>
</TR>
<TR>
<TD ALIGN="right">Metadata DSN: </TD>
<TD><INPUT TYPE="text" size="30" name="dsn"> </TD>
</TR>
<TR>
<TD ALIGN="right">Metadata Owner: </TD>
<TD><INPUT TYPE="text" size="30" name="own"> </TD>
</TR>
<TR>
<TD ALIGN="right">Metadata UserID: </TD>
<TD><INPUT TYPE="text" size="30" name="uid"> </TD>
</TR>
<TR>
<TD ALIGN="right">Metadata Password: </TD>
<TD><INPUT TYPE="password" size="30" name="pwd"> </TD>
</TR>
<TR>
<TD ALIGN="right">MSI UserID: </TD>
<TD><INPUT TYPE="text" size="30" name="muid"> </TD>
</TR>
<TR>
<TD ALIGN="right">MSI Password: </TD>
<TD><INPUT TYPE="password" size="30" name="mpwd"> </TD>
</TR>
</TABLE>
<P>
<CENTER>
<INPUT TYPE="submit" name="snap" id="snap" value="Snapshot">
<INPUT TYPE="reset" value="Clear">
</CENTER>
<INPUT TYPE="hidden" name="pass" id="pass" value="filled">
</P>
</center>
</FORM>
</BODY>
</HTML>
<%
End Sub
'-------------------------------------------------------------------------------
' USAGE
' Save reports with their prompted values into folders with the name 'UNIT TEST'
' and run this application
' You will be asked to provide the following information:
' Name of the I-Server
' Name of the project
' Metadata database DSN Name for project
' Metadata database owner name
' Metadata database userid
' Metadata database password
' MSI Project userid
' MSI Project password
'
' FUNCTION
' The application will:
' 1. return a list of report objects contained inside 'UNIT TEST' folders for
' the project;
' 2. submit each one to the I-Server for SQL generation;
' 3. retrieve the SQL from the I-Server;
' 4. store each report's SQL in a unique file on the web server
'
' PURPOSE
' This was originally developed to snapshot all of the SQL for basic reports to
' provide a benchmark against which future Microstrategy architecture changes
' may be compared to provide an informed impact assessment.
'-------------------------------------------------------------------------------
' MODIFICATION HISTORY:
'-------------------------------------------------------------------------------
' Date Init Description
'-------------------------------------------------------------------------------
'
'-------------------------------------------------------------------------------
'
' --------------------------------------------------------------------------
' --------------------------------------------------------------------------
Sub ProcessForm()
const MY_REPORT_NAME = 0
const MY_REPORT_GUID = 1
Dim oConn, sConn, oRS
Dim sGET_UNIT_TEST_REPORT_SQL
Dim sREPORT_SQL_PATH
Dim sSQL
Dim vReport
Dim i
Dim sProjectName
Dim sIServerName
Dim sMetadataDSN
Dim sMetadataOwner
Dim sMetadataUserID
Dim sMetadataPassword
Dim sFQFilename
Dim sReportSQL
Dim sReportGUID
Dim sReportName
Dim oServerSession
Dim lngSessionFlags
sGET_UNIT_TEST_REPORT_SQL = "select object_name, object_id from dssmdobjinfo where parent_id = (select object_id from dssmdobjinfo where object_name = 'UNIT TEST' and Project_Id = (select project_id from dssmdobjinfo where parent_id = '00000000000000000000000000000000' and object_name = '@PROJECT_NAME') and object_type = 8 ) and object_type = 3 order by object_name"
sProjectName = trim(Request.Form("pname") & "")
sIServerName = trim(Request.Form("isrv") & "")
sMetadataDSN = trim(Request.Form("dsn") & "")
sMetadataOwner = trim(Request.Form("own") & "")
sMetadataUserID = trim(Request.Form("uid") & "")
sMetadataPassword = trim(Request.Form("pwd") & "")
sMSIUserID = trim(Request.Form("muid") & "")
sMSIPassword = trim(Request.Form("mpwd") & "")
Response.Write("<HTML>")
Response.Write("<HEAD><TITLE>Processing Static MSI 7.x Report SQL...</TITLE></HEAD>")
Response.Write("<BODY>")
Response.Write("<H3>Processing Static MSI 7.x Report SQL...</H3>")
Response.Write("<BR>Project Name: [" & sProjectName & "]")
Response.Write("<BR>I-ServerName: [" & sIServerName & "]")
sSQL = Replace(sGET_UNIT_TEST_REPORT_SQL, "@PROJECT_NAME", sProjectName)
sREPORT_SQL_PATH = "E:\\StaticReports"
Response.Write("<BR>Output to: [" & sREPORT_SQL_PATH & "\\" & sProjectName & "]")
Set oConn = Server.CreateObject("ADODB.Connection")
Set oRS = Server.CreateObject("ADODB.Recordset")
sConn = "DSN=" & trim(sMetadataDSN) & "; UID=" & trim(sMetadataUserID) & "; PWD=" & trim(sMetadataPassword)
oConn.Open sConn
set oRS = oConn.Execute(sSQL)
if oRS.EOF = false then
vReport = oRS.GetRows()
end if
oRS.Close
oConn.Close
set oRS = nothing
set oConn = nothing
if isarray(vReport) then
Response.Write("<BR>Processing [" & CStr(ubound(vReport,2)+1) & "] reports...")
for i = 0 to ubound(vReport,2)
sReportGUID = trim(vReport(MY_REPORT_GUID,i) & "")
sReportName = trim(vReport(MY_REPORT_NAME,i) & "")
Response.Write("<BR>" & sReportName)
Response.Flush
sReportSQL = GetReportSQL(sReportName, sReportGUID, sProjectName, sIServerName, sMSIUserID, sMSIPassword)
SaveSQLToLocalFile sREPORT_SQL_PATH, sProjectName, sReportName, sReportSQL
next
else
Response.Write("No UNIT TEST reports found")
end if
Response.Write("</BODY>")
Response.Write("</HTML>")
Response.Flush
Response.End
End Sub
' --------------------------------------------------------------------------
' --------------------------------------------------------------------------
Function GetReportSQL(sReportName, sReportID, sProjectName, sIServerName, sUserID, sPass)
Dim woFactory
Dim Isession
Dim sessionInfo
Dim wrpSource
Dim objSource
Dim wrpInstance
Dim reportID
Dim rpResultData
Dim rpStatus
Dim SessionID
Dim oServerSession
Dim lngSessionFlags
Set woFactory = Server.CreateObject("MBJWOBRI.WebObjectsFactory.1")
Set Isession = woFactory.IServerSession
Isession.serverName = sIServerName
Isession.ServerPort = 0
Isession.projectName = sProjectName
Set sessionInfo = Isession.Info
sessionInfo.login = sUserID
sessionInfo.PassWord = sPass
SessionID = sessionInfo.SessionID
'get report source
Set wrpSource = woFactory.ReportSource
'specify report ID
reportID = sReportID
wrpSource.ResultFlags = DssXmlResultXmlSQL
wrpSource.ExecutionFlags = DssXmlExecutionGenerateSQL
'create report instance
Set wrpInstance = wrpSource.GetNewInstance(reportID)
wrpInstance.Async = false
wrpInstance.MaxWait = 120000
wrpInstance.PollingFrequency = 250
rpStatus = wrpInstance.pollStatus
If rpStatus <> DssXMLStatusResult Then
Response.Write "Error:report status=" & rpStatus
Response.End
End If
'retrieving report SQL from WebReportData object
Set rpResultData = wrpInstance.GetResults()
Set oServerSession = Server.CreateObject("MSIXMLLIB.DSSXMLServerSession.1")
lngSessionFlags = DssXmlSessionCloseDeleteUnreadMsgs
oServerSession.CloseSession sessionID, lngSessionFlags
GetReportSQL = rpResultData.SQL
Set rpResultData = Nothing
Set wrpInstance = Nothing
Set wrpSource = Nothing
Set sessionInfo = Nothing
Set Isession = Nothing
Set woFactory = Nothing
END FUNCTION
' ----------------------------------------------------
' ----------------------------------------------------
Sub SaveSQLToLocalFile(sPath, sProject, sReportName, sSQL)
Dim oFileSystem
Dim oFolder
Dim oTextFile
Dim sFQFilename
Dim sDate
Dim sFmtSQL
on error resume next
Set oFileSystem = CreateObject("Scripting.FileSystemObject")
if err.number <> 0 then
Response.Write( "Error Creating FSO [#" & cstr(err.number) & "] - " & err.description & " in " & err.source)
end if
' build a date formatted like "_yyyymmdd_hhmm" to append to the reportname for uniqueness
sDate = "_" & Datepart("yyyy", Now) & right("00" & Datepart("m", Now), 2) & right("00" & Datepart("d", Now), 2) & "_" & right("00" & Datepart("h", Now), 2) & right("00" & Datepart("n", Now), 2)
If not oFileSystem.FolderExists(sPath & "\\" & sProject) Then
oFileSystem.CreateFolder sPath & "\\" & sProject
End If
sFQFilename = sPath & "\\" & sProject & "\\" & sReportName & sDate & ".sql"
Set oTextFile = oFileSystem.CreateTextFile(sFQFilename, True)
if err.number <> 0 then
Response.Write( "Error CreateTextFile [#" & cstr(err.number) & "] - " & err.description & " in " & err.source)
end if
sFmtSQL = Replace(sSQL, chr(10), chr(13) + chr(10))
oTextFile.Write(sFmtSQL)
if err.number <> 0 then
Response.Write( "Error Write [#" & cstr(err.number) & "] - " & err.description & " in " & err.source)
end if
oTextFile.Close
if err.number <> 0 then
Response.Write( "Error Close [#" & cstr(err.number) & "] - " & err.description & " in " & err.source)
end if
set oTextFile = nothing
set oFolder = nothing
set oFileSystem = nothing
End Sub
' ----------------------------------------------------
function reformatSQL2 (sSQL)
' ----------------------------------------------------
dim re
Set re = new regexp 'Create the RegExp object
re.Global = true
re.Pattern = "^$"
sSQL = re.replace(sSQL, "/")
set re = nothing
reformatSQL2 = sSQL
end function
' ----------------------------------------------------
' crude format of the sql returned
' ----------------------------------------------------
function reformatSQL (sSQL)
dim brk
dim re
Set re = new regexp 'Create the RegExp object
brk = "<BR>"
re.Global = true
re.Pattern = "create table "
sSQL = re.replace(mySQL, brk + brk + "CREATE TABLE ")
re.Pattern = "select "
sSQL = re.replace(mySQL, brk + "SELECT ")
re.Pattern = "from "
sSQL = re.replace(mySQL, brk + "FROM ")
re.Pattern = "where "
sSQL = re.replace(mySQL, brk + "WHERE ")
re.Pattern = "and "
sSQL = re.replace(mySQL, brk + "AND ")
re.Pattern = "group by "
sSQL = re.replace(mySQL, brk + "GROUP BY ")
re.Pattern = "ANALYZE TABLE "
sSQL = re.replace(mySQL, brk + brk + "ANALYZE TABLE ")
re.Pattern = "drop table "
sSQL = re.replace(mySQL, brk + brk + "DROP TABLE ")
re.Pattern = ","
sSQL = re.replace(mySQL, brk + ", ")
re.Pattern = "^<BR><BR>$"
sSQL = re.replace(mySQL, brk + "/" + brk)
set re = nothing
reformatSQL = sSQL
end function
' ----------------------------------------------------
%>