Recordset.cursortype = 3 error

[Kurto2021]

I was running this the last few days without error.  I compiled it to an exe yesterday and ran it a few times with no error.  Today I am getting an error...ugh!!!!

"C:Program Files (x86)AutoIt3SciTEteradata auto query3.au3" (67) : ==> Variable must be of type "Object".:

$Recordset.cursortype = 3

$Recordset^ ERROR

->09:03:27 AutoIt3.exe ended.rc:1

+>09:03:27 AutoIt3Wrapper Finished.

 

 

I am trying to figure out what this means.  Is it the results from the query that are invalid?  Could a null value within one of the records trigger this.  My biggest issue is I just don't know how to troubleshoot this.

 

 

 

expandcollapse popup

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Icon=..\..\..\Users\a870631\Downloads\favicon.ico
#AutoIt3Wrapper_Outfile=C:\test.exe
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
#include <array.au3>
#include <file.au3>
#include <CSV.au3>
#include <clipboard.au3>


Dim $arr
Dim $i
$User = "xxxxxxx"
$Pass = "xxxxxxx" ; password
$DSN = "Production Teradata" ; dsn
$Delim = "|" ; Delimiter
$Line_N = 0
$DSN = 'DSN=' & $DSN & ';uid=' & $User & ';pwd=' & $Pass
$Connection = ObjCreate("ADODB.Connection")


$sCSV = FileRead("\\CS01Corp\Root\Files\Corp\RO\965090\PUB-DB\Daily Labor Reports\Automated_Query_File\Automated_Queries.txt") ; read the source txt file
;  Can't use CSV since queries have commas in them may need to be something other than pipes


Local $arr = _CsvToArray2D($sCSV) ; take results of the source csv and use the function to parse into an array


;_ArrayDisplay($arr) ;displays contents of the array


;output file has been created - Query is assigned to a variable now create the recordset and run the query
$Connection.open($DSN) ; open the DSN connection




; see these sites for information regarding recordsets
; https://www.autoitscript.com/wiki/ADO_RecordSet_Process
; https://www.autoitscript.com/wiki/ADO_RecordSet_Populate


For $i = 1 To UBound($arr) - 1
$err = 0
; variables designating the desired file location/name and query to be executed
$CSVLoc = $arr[$i][1] & $arr[$i][0] & ".txt"
$SqlString = $arr[$i][9]
$Recordset = ObjCreate("ADODB.Recordset") ; create the recordset


If FileExists($CSVLoc) Then ;Checking to see if the desired filename exists
FileSetAttrib($CSVLoc, "-RS") ; make sure file is not read only
FileDelete($CSVLoc) ;delete the file
If @error Then
$err = 1
EndIf
EndIf


If $err = 0 Then ; if the file delete was successful next step is to create the file
_FileCreate($CSVLoc) ;after the file is deleted or if it doesn't exist then create the new output file.


If @error Then
$err = 1
EndIf
EndIf


If $err = 0 Then  ; if the file create was successful then open the new file
$sFile = FileOpen($CSVLoc, 1) ;open the file to be output
If @error Then
$err = 1
EndIf
EndIf


If $err = 0 Then ; file delete, create, and open were successful
$Recordset = $Connection.Execute($SqlString) ;$SqlString
$Recordset.cursortype = 3 ; specifies the type of cursor to use when you open a Recordset object  -  see Recordset populate link above
$Recordset.open($SqlString);$SqlString


If Not $Recordset.BOF And $Recordset.EOF Then
$err = 1
;MsgBox(0, "Error", "Error Occurred trying to excute query.")
Else
$t = 0
$Recordset.MoveFirst
While Not $Recordset.EOF
$line = ""
For $fld In $Recordset.Fields
If $t = 0 Then
$line = $fld.value
$t = 1
Else
$line = $line & $Delim & $fld.value
EndIf
Next
$t = 0
FileWriteLine($sFile, $line & @CRLF)
;ConsoleWrite ($Line)
If @error Then
MsgBox(0, "Error Occurred", "Unable to write to File")
Exit
EndIf
$Recordset.MoveNext
WEnd
EndIf
FileClose($sFile)
FileSetAttrib($CSVLoc, "+RS") ; set read only with system
$Recordset.close
EndIf


If $err = 1 Then
;here I will call for a function to email to notify the user of the error
EndIf
Next
$Connection.close



Func _CsvToArray2D($sCSV)
Local $aTmp = StringRegExp($sCSV & @CR, '(\V*)\v{1,2}', 3)


Local $NumCols[UBound($aTmp)]
For $x = 0 To UBound($aTmp) - 1
StringReplace($aTmp[$x], "|", "|")
$NumCols[$x] = @extended + 1
Next
Local $Max = _ArrayMax($NumCols, 1)


Dim $aArr[UBound($aTmp)][$Max]


For $i = 0 To UBound($aArr, 1) - 1
Local $aTemp = StringSplit($aTmp[$i], "|")
For $j = 0 To $aTemp[0] - 1
$aArr[$i][$j] = $aTemp[$j + 1]
Next
Next
Return $aArr
EndFunc   ;==>_CsvToArray2D

 

[JLogan3o13]

Do an error check after $Recordset = $Connection.Execute($SqlString), to ensure it exists.

Also, out of curiosity, why are you overwriting the $Recordset variable? Use use it earlier in the script with your ObjCreate statement, then overwrite when you set it to $Connection.Execute($SqlString)
 

[Kurto2021]

JLogan a lot of failure probably is the reason it is there and once it worked I didn't want to mess with it.  Just commented it out and it works.....for the first query...still fails on the second query.

I put in this 

if @error Then
    msgbox(1,"error","recordset doesn't exist")
EndIf
$Recordset.cursortype = 3 ; specifies the type of cursor to use when you open a Recordset object  -  see Recordset populate link above

I do get the error.  Now to try and find out why the second recordset is not working

[Kurto2021]

OK this is weird....

I was thinking there was a connection of this thing returning null values in the results from the query.  I ran the query in the teradata application and found that 2 results contained a null value.  I simply put in a case statement for null value to be 0 and it worked......

The weird part is I asked my coworker if he ran the exe this morning and he said yes it worked great.  I then tried to run the exe again and it worked as well.  Is there any reason why I am randomly getting this error.  Is it possibly a timing issue?