ADODB.Recordset unable to run SQL

[spooky101]

Hello,

I seem to be having an issue with one of my scripts. The purpose of the script is to connect to a DSN connection (in this case TERADATA connection), run SQL, and spool out the recordset that is returned. The problem I seem to be having is that the SQL is not valid and I have no way of knowing where the error is. I used SQL Assistant to error check the SQL, but it runs without any issues. Because of this, I have a feeling this may be due to a compatibility issue. Is there perhaps a way to check the SQL and locate the issue before opening the actual recordset? Listed below is my code. I know the failure occurs here "$Recordset.open ($SqlString)"

Any advice would be appreciated!

I'm still rather knew to scripting, so feel free to yell at me for bad practice
 

Thanks!!

Michael

expandcollapse popup

#include <array.au3>
#include <file.au3>

$User       = $CmdLine[1] ; user
$Pass       = $CmdLine[2] ; password
$DSN        = $CmdLine[3] ; dsn
$SQLLoc     = $CmdLine[4] ; SQL File
$CSVLoc     = $CmdLine[5] ; Output File
$header_f   = $CmdLine[6] ; Headers Flag
$Delim      = $CmdLine[7] ; Delimiter

$Line_N = 0
$DSN = 'DSN=' & $DSN & ';uid=' & $User & ';pwd=' & $Pass

global $aFile [1]
$SqlString = ''
$Fr = _FileReadToArray($SQLLoc, $aFile)
if @error Then
    msgbox (0, "Error Occurred", "Unable to find SQL input file.")
    Exit
EndIf

for $i = 1 to ubound($aFile) - 1
    $SqlString = $SqlString & $aFile[$i] & @CR
Next

if FileExists($CSVLoc) Then
    FileSetAttrib($CSVLoc, "-RS")
    FileDelete($CSVLoc)
    if @error Then
        msgbox (0, "Error Occurred", "Unable to delete CSV output file.")
        Exit
    EndIf
EndIf
_FileCreate($CSVLoc)
if @error Then
    msgbox (0, "Error Occurred", "Unable to create CSV output file.")
    Exit
EndIf
$sFile = FileOpen ($CSVLoc,1)
if @error Then
    msgbox (0, "Error Occurred", "Unable to open CSV output file.")
    Exit
EndIf

$Connection = ObjCreate("ADODB.Connection")
$Recordset = ObjCreate("ADODB.Recordset")

$Connection.open ($DSN)
$Recordset.cursortype = 3
msgbox (0, "", @error)
$Recordset.open ($SqlString);$SqlString
msgbox (0, "", @error)

If Not $Recordset.BOF and $Recordset.EOF Then
    msgbox (0,"Error", "Error Occurred trying to excute query.")
Else
    ; Concatenate headers
    $t = 0
    if $header_f = 1 Then
        $line = ""
        for $i in $RecordSet.fields
            if $t = 0 Then
                $line = $i.name
                $t = 1
            Else
                $line = $line & $Delim & $i.name
            EndIf
        Next
        filewriteline ($sFile, $Line)
    $t = 0
    EndIf
    $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)
        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
$Connection.close

[water]

I suggest to add a COM error handler to your script. Examples can be found in the help file for ObjEvent.

[jchd]

Pretty hard to say without seeing both the DB schema and the SQL query. Your DBA would be a better advisor.

[trancexx]

There is a bug in AutoIt COM code that prevents using ADODB.Connection, and few more objects, as intended. It's even possible to have hard-crash due to the bug. The code is written by Jon during one of his "periods".

However, I revised the code several times since then and missed the bug because of some other things that were masking it. So, you could say that it's also my fault for the bug to still exist.

I don't see this fixed soon or ever (smart reader will figure out the reasons), so the smart thing would be to seak alternative solutions for working with DB-s.

[water]

Are this bugs related to databases?

In my ADO tutorial in the wiki I've only created examples for text files and Excel worksbooks but didn't encounter any problems.

[spooky101]

Thanks for the help everyone! It's quite amazing how fast the responses are in this forum.

I actually managed to solve the issue. My code was wrong and I was utilizing the recordset incorrectly. I managed to fix my issue by utilizing the .EXECUTE method

;$oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")
$Connection = ObjCreate("ADODB.Connection")
;$Recordset = ObjCreate("ADODB.Recordset")

$Connection.open ($DSN)
$Recordset = $Connection.Execute ($SqlString) ;$SqlString

Although, i'm finding it hard to understand "ObjEvent" and how it works. From reading the help file, it sounds like it actually returns the events of the Object you call. Which would give you a more in-depth understanding of what error occurred (if one had occurred). Would it be possible to return the events for the ADO event "ExecuteComplete"? If so, what would be a simple script that would do so?

Thanks again for the help!

Michael

[trancexx]

Are this bugs related to databases?

In my ADO tutorial in the wiki I've only created examples for text files and Excel worksbooks but didn't encounter any problems.

To the objects for DB manipulation, yes. More serious examples will reveal the bug very quickly.

[water]

Thanks, good to know.

[jchd]

trancexx,

Now would be the right time to disclose a significant example of the bug so that Jon can fix it soon. He just asked for open issues so he can publish an intermediate release.

Beside a strange (i.e. wrong) behavior of eof() when the resultset is empty (or something close to that, I don't remember exactly) I never had isues with ADO COM either, maybe because all actual ADO invokation is inside simple wrappers which don't use ADO to its limits. Also not all ADO features are implemented in all "ODBC" drivers so it isn't a bad idea to keep things simple and portable, sacrificying efficiency a few % of the time. Still if something wrong lies under the hood it needs to be smashed.

[trancexx]

Try creating database and add table with some sensible data.

You can use ADOX.Catalog object (there are nice VBS examples all around the net). See if that works for you.

I'm not interested in what Jon asks on some private forums.