Script stuck in a loop

[SethK]

I'm writing a script to pull data from one database into a set of txt files that can be FTP'd to a vendor and uploaded into their system.  The script is scheduled to run nightly on our jobs server.  It connects to our SQL database and creates 9 of the 10 files correctly.  On the tenth file it seems to get stuck in the first loop and keeps printing out line after line of delimiters, but no data from the database.  It does print out the correct number of delimiters per line.  If I run the script, let it get stuck in the loop, kill it, and then run it again everything works.  I'm assuming the problem lies somewhere in my SQL view, however it only takes about 30 seconds to run and returns the correct data.  Some of the other views take nearly the same amount of time to run without any problems.

I've tried running the script without creating any of the other files and it still errors on this function.  I tried having it run Attendance2 first and that resulted in it getting stuck in a loop of Attendance2 instead of Attendance1.  I also tried putting in a 40 second sleep timer after the select statement, but that didn't solve the problem either.  If anyone has any suggestions as to what I should try next please let me know.  I'm relatively new to AutoIt and I've exhausted all of my ideas.  Thank you for any help you can offer.

 

expandcollapse popup

Func Attendance_File()
    ;
   ; define local variables - only used within this function 
   ;
   local $var, $attendance, $attendancefile, $delimitstartend,$delimit,$Recordset, $SQL,$header

   ;
   ; load values into local variables - txt File creation 
   ;
   $attendancefile = "attendance"
   $var = $Outpath & $attendancefile  &".txt"
   
   $delimitstartend = '"' 
   $delimit = ','

   ;
   ; Set the Time variable to the current time
   ; 
   $time = @YEAR & "/" & @MON & "/" & @MDAY & "-" & @HOUR & ":" & @MIN & ":" & @SEC & " --> "  

   ;
   ; Write into the log the Entry to function data -  e.g function name, time entered, Parameter(s) etc.. 
   ; 
   FileWriteLine($file, $time & "Entered function Attendance_File 1" & @CRLF)

   ;
   ; create the txt file for our extracted data in the $Outpath folder. 
   ;
   $attendance = FileOpen($var,2)
   
   ; 
   ; Build the SQL statement to extract our data from the SQL server
   ;
   $SQL= "select * from [VIEWS].[dbo].[2017_PM_Attendance1]" 
   
   ; 
   ; Create the recordset object to point to our result set. 
   ;
   $Recordset = ObjCreate("ADODB.RecordSet")

   ; 
   ; Execute the SQL Query, check for the presence of data.  If there is no data - write that into the log file.
   ;
   $Recordset.Open($SQL,$Connection) 
   If $Recordset.EOF Then
        ;
        ; Set the Time variable to the current time
        ; 
        $time = @YEAR & "/" & @MON & "/" & @MDAY & "-" & @HOUR & ":" & @MIN & ":" & @SEC & " --> "  

        ;
        ; Write into the log the Entry to function data -  e.g function name, time entered, Parameter(s) etc.. 
        ; 
        FileWriteLine($file, $time & "NO Attendance RECORDS FOUND IN [VIEWS].[dbo].[2017_PM_Attendance1]" & @CRLF)
   Endif

   ; Loop through the resultset of the query and write each field of data into the txt file.  Note I trim'd the character variables in the VIEW in SQL to remove leading and trailing spaces   
   ;
      
  While $Recordset.EOF <> True
         ;
         ; Write all values which I need from this row into the txt file

         FileWriteLine($attendance, $Recordset.Fields("ID").Value & $delimit & $Recordset.Fields("Date1").Value & $delimit & $Recordset.Fields("Code").Value & $delimit & $Recordset.Fields("Title").Value & $delimit & $Recordset.Fields("Abbreviation").Value & $delimit & $Recordset.Fields("Type").Value & $delimit & $delimit & $delimit & @CRLF)
         ;
         ; Advance the recordset pointer to the next row of the query - loop Until we are out of rows 
         ;
         $Recordset.MoveNext  ; Moves to next record in result set
    WEnd
    ;
    ; Close the recordset object
    ; 
    $Recordset.Close

   
    $time = @YEAR & "/" & @MON & "/" & @MDAY & "-" & @HOUR & ":" & @MIN & ":" & @SEC & " --> "  

   ;
   ; Write into the log the Entry to function data -  e.g function name, time entered, Parameter(s) etc.. 
   ; 
   FileWriteLine($file, $time & "Entered function Attendance_File 2" & @CRLF)

   ; 
   ; Build the SQL statement to extract our data from the SQL server
   ;
   $SQL= "select * from [VIEWS].[dbo].[2017_PM_Attendance2]" 
   
   ; Create the recordset object to point to our result set. 
   ;
   $Recordset = ObjCreate("ADODB.RecordSet")

   ; 
   ; Execute the SQL Query, check for the presence of data.  If there is no data - write that into the log file.
   ;
   $Recordset.Open($SQL,$Connection) 
   If $Recordset.EOF Then
        ;
        ; Set the Time variable to the current time
        ; 
        $time = @YEAR & "/" & @MON & "/" & @MDAY & "-" & @HOUR & ":" & @MIN & ":" & @SEC & " --> "  

        ;
        ; Write into the log the Entry to function data -  e.g function name, time entered, Parameter(s) etc.. 
        ; 
        FileWriteLine($file, $time & "NO Attendance RECORDS FOUND IN [VIEWS].[dbo].[2017_PM_Attendance2]" & @CRLF)
   Endif

;
   ; Loop through the resultset of the query and write each field of data into the txt file.  Note I trim'd the character variables in the VIEW in SQL to remove leading and trailing spaces   
   ;
      
   While $Recordset.EOF <> True
         ;
         ; Write all values which I need from this row into the txt file

         FileWriteLine($attendance, $Recordset.Fields("ID").Value & $delimit & $Recordset.Fields("Date1").Value & $delimit & $Recordset.Fields("Code").Value & $delimit & $Recordset.Fields("Title").Value & $delimit & $Recordset.Fields("Abbreviation").Value & $delimit & $Recordset.Fields("Type").Value & $delimit & $delimit & $Recordset.Fields("Period1").Value & @CRLF)
         ;
         ; Advance the recordset pointer to the next row of the query - loop Until we are out of rows 
         ;
         $Recordset.MoveNext  ; Moves to next record in result set
    WEnd
    
    ;
    ; Close the recordset object
    ; 
    $Recordset.Close

    ;
    ; Close the txt file
    
    FileClose($attendance)
  
EndFunc

 

[aiter]

Can you check the result of each open and each sql statement?

I don't know the syntax of it, but in pseudo language :-
 

@recordset.MoveNext
if @recordset.Error <> 0 then
   exitloop
endif

You get the idea?

Also have this at top of script

global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")

and this function somewhere

Func _ErrFunc($oError)
    ; Do anything here.
    msgbox(0,'','we have an error')
    ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

 

Edited September 21, 2016 by aiter