Sign in to follow this  
Followers 0
spooky101

ADODB.Recordset unable to run SQL

10 posts in this topic

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

#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

Share this post


Link to post
Share on other sites



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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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.


♡♡♡

.

eMyvnE

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

♡♡♡

.

eMyvnE

Share this post


Link to post
Share on other sites

Thanks, good to know.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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.


♡♡♡

.

eMyvnE

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0