My script gets a list of text files (standard formatted audit files with extension *.aud), uses ReadLine to parse each file line-by-line, extracts strings to variables, then jams the data into a MS SQL 2000 table so the audit data is actually useful to us. Here's the odd thing: if I run this in debug mode holding down my return key to complete the script, it works flawlessly. If I try to execute the script at regular speed it only writes about 60% of the lines to the SQL database. Which records get written is random each time it runs. There are no errors.
I'm looking for help figuring out where the problem is when I run at full speed. One suspicion I have is that maybe the SQL server is choking on the speed data is being sent to it. Everything is running directly on the SQL server so no network is involved--even the files being parsed are on it. The server itself is extremely powerful and very underutilized so, I don't know, maybe I'm off on that. The files being parsed are rarely larger than 60K but are occasionally as large as 700 K. Any ideas are appreciated. Yes, I know the code is kludgy, horribly structured and supremely ugly...
Code:
; Declare global Variables
Dim $AudFile
Dim $Server
Dim $Database
Dim $OraclePN
Dim $UnixPID
Dim $DateTime
Dim $Action
Dim $DBUser
Dim $Priv
Dim $CliUser
Dim $Terminal
Dim $Status
Dim $LineStr
Dim $FileLines
Dim $CurrLine
Dim $RecordTime
; Open connection to the SQL database
$objConn = DBConnOpen("Driver={SQL Server};Server=Kaos;Database=OraAudit;Trusted_Connection=yes")
; Gather a list of all *.aud files
$FileList = DirPlus("c:\oraaudit\current\","/S /A-D /F aud")
$TotFiles = 0
For Each $FileElem in $FileList
$TotFiles = $TotFiles+1
Next
; Just leave if there's nothing to do
If $TotFiles = 0
Goto LEAVE
EndIf
$FileCount = 0
$TotFiles = $TotFiles-1
:NEXTFILE
$AudFile = $FileList[$FileCount]
; Open the current Oracle Audit file being processed
Open(1, $AudFile, 0)
:NEXTREC
; Read current line into string variable
$LineStr = ReadLine(1)
If @ERROR <> 0
Goto CLOSEFILE
EndIf
; Decide whether to change session or action variables
Select
Case Left ("$LineStr", 10) = "Audit File"
Gosub SETSESS
Case Left ("$LineStr", 3) = "Mon" Or
Left ("$LineStr", 3) = "Tue" Or
Left ("$LineStr", 3) = "Wed" Or
Left ("$LineStr", 3) = "Thu" Or
Left ("$LineStr", 3) = "Fri" Or
Left ("$LineStr", 3) = "Sat" Or
Left ("$LineStr", 3) = "Sun"
Gosub SETACTION
EndSelect
:CLOSEFILE
; Closes the current Oracle Audit file being processed
Close(1)
; Move on to process next file in the list
If $FileCount < $TotFiles
$FileCount = $FileCount+1
Goto NEXTFILE
EndIf
:LEAVE
; Close connection to sql database
DBConnClose($objConn)
Quit
:SETSESS
; Subroutine to parse session-level lines and assign to variables
Select
Case Left ("$LineStr", 10) = "Audit File"
$AudFile = Right ("$LineStr", -11)
Case Left ("$LineStr", 10) = "Node name:"
$Server = Right ("$LineStr", -11)
Case Left ("$LineStr", 14) = "Instance name:"
$Database = Right ("$LineStr", -15)
Case Left ("$LineStr", 14) = "Oracle process"
$OraclePN = Right ("$LineStr", -23)
Case Left ("$LineStr", 12) = "Unix process"
$UTempPID = Right ("$LineStr", -18)
Gosub UNIXPID
$LineStr = ReadLine(1)
$LineStr = ReadLine(1)
Goto SETACTION
EndSelect
$LineStr = ReadLine(1)
If @ERROR <> 0
Goto CLOSEFILE
EndIf
Goto SETSESS
Return
:UNIXPID
For $i = 1 to 7
If SubStr("$UTempPID", $i,1) = ","
$UnixPID = Left("$UTempPID", $i-1)
EndIf
Next
Return
:SETACTION
; Subroutine to parse action-level lines and assign to variables
Select
Case Left ("$LineStr", 3) = "Mon" Or
Left ("$LineStr", 3) = "Tue" Or
Left ("$LineStr", 3) = "Wed" Or
Left ("$LineStr", 3) = "Thu" Or
Left ("$LineStr", 3) = "Fri" Or
Left ("$LineStr", 3) = "Sat" Or
Left ("$LineStr", 3) = "Sun"
$DateTime = SubStr("$LineStr", 5, 20)
Case Left ("$LineStr", 8) = "ACTION :"
Gosub ACTION
Case Left ("$LineStr", 14) = "DATABASE USER:"
$DBUser = Right ("$LineStr", -15)
Case Left ("$LineStr", 11) = "PRIVILEGE :"
$Priv = Right ("$LineStr", -12)
Case Left ("$LineStr", 12) = "CLIENT USER:"
$CliUser = Right ("$LineStr", -13)
Case Left ("$LineStr", 16) = "CLIENT TERMINAL:"
$Terminal = Right ("$LineStr", -17)
Case Left ("$LineStr", 7) = "STATUS:"
$Status = Right ("$LineStr", -8)
Goto WRITE
EndSelect
$LineStr = ReadLine(1)
If @ERROR <> 0
Goto CLOSEFILE
EndIf
Goto SETACTION
Return
:ACTION
; Combines ACTION lines if it spans multiple lines
$LineStr = Right ("$LineStr", -9)
$J = Len($LineStr)-2
$LineStr = SubStr("$LineStr",2,$J)
If InStr($LineStr, "'")
Gosub QUOTES
Else $Action = $LineStr
EndIf
$LineStr = ReadLine(1)
If @ERROR<> 0
Goto CLOSEFILE
EndIf
If Left ("$LineStr", 14) = "DATABASE USER:"
$DBUser = Right ("$LineStr", -15)
$LineStr = ReadLine(1)
If @ERROR<> 0
Goto CLOSEFILE
EndIf
Goto SETACTION
EndIf
While Left ("$LineStr", 14) <> "DATABASE USER:"
If InStr("$LineStr", "'")
Gosub QUOTES
Else
$Action = $Action + $LineStr
EndIf
$LineStr = ReadLine(1)
If @ERROR<> 0
Goto CLOSEFILE
EndIf
Loop
Goto SETACTION
:QUOTES
; Change single'quotes to two single-quotes so SQL statement handles correctly
For $k = 1 to (Len($LineStr))
If SubStr($LineStr, $k, 1) <> Chr(39)
$Action=$Action+SubStr($LineStr, $k, 1)
Else
$Action=$Action+Chr(39)+Chr(39)
EndIf
Next
Return
:WRITE
; Timestamp each record for ordering, and because nothing else is unique
$RecMon = @MONTHNO
$RecMDay = @MDAYNO
$RecTime = @TIME
$RecMsec = @MSECS
$RecYr = @YEAR
$RecordTime = Chr(39)+'$RecYr'+Chr(45)+'$RecMon'+Chr(45)+'$RecMDay'+Chr(32)+'$RecTime'+Chr(46)+'$RecMsec'+Chr(39)
;Write record to SQL database
$sql = "INSERT DBAAudit VALUES ("+Chr(39)+$AudFile+Chr(39)+","+Chr(39)+$Server+Chr(39)
+","+Chr(39)+$Database+Chr(39)+","+$OraclePN+","+$UnixPID+","+Chr(39)+$DateTime+Chr(39)
+","+Chr(39)+$Action+Chr(39)+","+$DBUser+","+Chr(39)+$Priv+Chr(39)+","+Chr(39)+$CliUser+Chr(39)
+","+Chr(39)+$Terminal+Chr(39)+","+$Status+","+$RecordTime+")"
$retcode = DBExecuteSQL($objConn, $sql)
; skip the blank line after STATUS and then cleanup action variable
$LineStr = ReadLine(1)
If @ERROR <> 0
Goto CLOSEFILE
EndIf
$action = ''
Goto NEXTREC
Function DBConnOpen($ConnDSN, optional $ConnTimeout, optional $CmdTimeout)
Dim $objConn, $adStateOpen
$adStateOpen=Val('&00000001')
If VarType($ConnTimeout)
$ConnTimeout=Val($ConnTimeout)
Else
$ConnTimeout=15
EndIf
If VarType($CmdTimeout)
$CmdTimeout=Val($CmdTimeout)
Else
$CmdTimeout=30
EndIf
$ConnDSN=Trim($ConnDSN)
If Not $ConnDSN
Exit 87
EndIf
$objConn = CreateObject("ADODB.Connection")
If @ERROR
Exit @ERROR
EndIf
$objConn.ConnectionTimeout = $ConnTimeout
If @ERROR
Exit @ERROR
EndIf
$objConn.CommandTimeout = $CmdTimeout
If @ERROR
Exit @ERROR
EndIf
$objConn.Open($ConnDSN)
If @ERROR
Exit @ERROR
EndIf
If Not $objConn.State=$adStateOpen
$objComm=''
$DBConnOpen=''
Exit @ERROR
EndIf
$DBConnOpen=$objConn
EndFunction
Function DirPlus($path,optional $Options, optional $f, optional $sfflag)
If Not VarType($f) Dim $f EndIf
If Not VarType($sfflag) Dim $sfflag EndIf
Dim $file, $i, $temp, $item, $ex1, $mask,$mask1,$maskArray,$maskarray1,
$ex2, $code, $CodeWeight, $targetWeight, $weight, $masktrue
Dim $tarray[0]
$ex1 = SetOption(Explicit,on)
$ex2 = SetOption(NoVarsInStrings,on)
$codeWeight = 0
If Not Exist($path)
$temp = SetOption(Explicit,$ex1)
$temp = SetOption(NoVarsInStrings,$ex2)
Exit @ERROR
EndIf
If Not VarType($f)
$f = CreateObject("Scripting.FileSystemObject").getfolder($path)
EndIf
If @ERROR
$temp = SetOption(Explicit,$ex1)
$temp = SetOption(NoVarsInStrings,$ex2)
Exit @ERROR
EndIf
For Each $temp in Split($options,"/")
$temp=Trim($temp)
Select
Case Left($temp,1) = "s"
If Not VarType($sfflag)
If Val(Right($temp,-1)) = 0
$sfflag = -1
Else
$sfflag = Val(Right($temp,-1))
EndIf
EndIf
Case Left($temp,1) = "a"
Select
Case Right($temp,-1)="d"
$codeWeight = $codeWeight + 1
$temp = "if $file.attributes & 16 " ;"if $file.type = 'File Folder' "
Case Right($temp,-1)="-d"
$codeWeight = $codeWeight + 1
$temp = "if ($file.attributes & 16)=0 " ;"if $file.type <> 'File Folder' "
Case Right($temp,-1)="s"
$codeWeight = $codeWeight + 1
$temp = "if $file.attributes & 4 "
Case Right($temp,-1)="-s"
$codeWeight = $codeWeight + 1
$temp = "if ($file.attributes & 4)=0 "
Case Right($temp,-1)="h"
$codeWeight = $codeWeight + 1
$temp = "if $file.attributes & 2 "
Case Right($temp,-1)="-h"
$codeWeight = $codeWeight + 1
$temp = "if ($file.attributes & 2)=0 "
Case Right($temp,-1)="r"
$codeWeight = $codeWeight + 1
$temp = "if $file.attributes & 1 "
Case Right($temp,-1)="-r"
$codeWeight = $codeWeight + 1
$temp = "if ($file.attributes & 1)=0 "
Case Right($temp,-1)="a"
$codeWeight = $codeWeight + 1
$temp = "if $file.attributes & 32 "
Case Right($temp,-1)="-a"
$codeWeight = $codeWeight + 1
$temp = "if ($file.attributes & 32)=0 "
EndSelect
$code = $temp + "$weight=$weight+1 endif" +@CRLF + $code
Case Left($temp,1) = "m"
$maskarray = Split(Right($temp,-2),"|")
$codeweight = $codeweight + 1
$code = "$masktrue=0 for Each $mask in $maskarray if instr($file.name,$mask) $masktrue=1 " +
"EndIf Next If $masktrue $weight=$weight+1 endif" + @CRLF +$code
Case Left($temp,1) = "f"
$maskarray1 = Split(Right($temp,-2)," ")
$codeweight = $codeweight + 1
$code = "$masktrue=0 for Each $mask1 in $maskarray1 if substr($file.name,Instrrev($file.name,'.')+1)" +
"=$mask1 $masktrue=1 EndIf Next If $masktrue $weight=$weight+1 endif" + @CRLF +$code
EndSelect
Next
$code = "$weight = 0 $targetWeight = " + $codeweight + @CRLF + $code
$code = $code + "if $weight = $targetweight Exit 1 endif"
For Each $file in $f.subfolders
If Execute($code)
$tarray[$i] = $file
$i = $i + 1
ReDim preserve $tarray[$i]
EndIf
If $sfflag
$temp = dirplus($file,$options,$file,$sfflag-1)
For Each $item in $temp
$tarray[$i] = $item
$i = $i + 1
ReDim preserve $tarray[$i]
Next
EndIf
Next
For Each $file in $f.files
If Execute($code)
$tarray[$i] = $file
$i = $i + 1
ReDim preserve $tarray[$i]
EndIf
Next
If $i
ReDim preserve $tarray[$i-1]
$i=0
Else
$tarray = 0
EndIf
$dirplus = $tarray
$temp = SetOption(Explicit,$ex1)
$temp = SetOption(NoVarsInStrings,$ex2)
Exit @ERROR
EndFunction
Function ArrayDim($array, optional $dims)
Dim $a
If VarType($array) & 8192
$a=0
Do
$a=$a+1
Until UBound($array,$a)=-1
$arraydim=$a-1
If Val($dims)
Dim $b[$arraydim-1]
For $a=1 to $arraydim
$b[$a-1]=UBound($array,$a)
Next
$arraydim=$b
EndIf
Else
$arraydim=-1
EndIf
EndFunction
Function PadStr($Input, $Pad, $Length, optional $PadSide)
Dim $i, $x
$PadStr = ""
$Input = "" + $Input
$Pad = "" + $Pad
$Length = 0 + $Length
If $PadSide="" Or Len($PadSide)>1 Or InStr("LR",$PadSide)= 0
$PadSide = "L"
EndIf
$x = Len($Input)
For $i=$x to $Length - 1 Step Len($Pad)
If $PadSide = "L"
$Input = $Pad + $Input
Else
$Input = $Input + $Pad
EndIf
Next
If $PadSide = "L"
$Input = Right($Input, $Length)
Else
$Input = Left($Input, $Length)
EndIf
$PadStr = $Input
Exit 0
EndFunction
Function DBExecuteSQL($objConn, $sql, optional $cmdType)
Dim $cmdCommand, $rsRecordset
Dim $adCmdUnspecified, $adCmdText, $adCmdTable, $adCmdStoredProc, $adCmdUnknown, $adCmdFile, $adCmdTableDirect
$adCmdUnspecified = -1
$adCmdText = 1
$adCmdTable = 2
$adCmdStoredProc = 4
$adCmdUnknown = 8
$adCmdFile = 256
$adCmdTableDirect = 512
$cmdType=IIf(VarType($cmdType),Val($cmdType),$adCmdText)
If VarType($objConn)<>9 Or $sql=''
Exit 87
EndIf
$cmdCommand = CreateObject('ADODB.Command')
If @ERROR
Exit @ERROR
EndIf
$cmdCommand.ActiveConnection = $objConn
If @ERROR
Exit @ERROR
EndIf
$cmdCommand.CommandType = $cmdType
If @ERROR
Exit @ERROR
EndIf
$cmdCommand.CommandText = $sql
If @ERROR
Exit @ERROR
EndIf
$rsRecordset=$cmdCommand.Execute()
$rsRecordset=''
$cmdCommand=''
$DBExecuteSQL=@ERROR
Exit @ERROR
EndFunction
Function DBConnClose($objConn)
Dim $adStateOpen
$adStateOpen = 1
If VarType($objConn)=9
If $objConn.State = $adStateOpen
$objConn.Close()
If @ERROR
Exit @ERROR
EndIf
EndIf
$objConn=''
Else
Exit 87
EndIf
$DBConnClose=0
Exit 0
EndFunction
**Moderator Edit - Richard H. - Split long line to enhance readability **
