spooky101 Posted April 14, 2014 Share Posted April 14, 2014 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 Link to comment Share on other sites More sharing options...
water Posted April 14, 2014 Share Posted April 14, 2014 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
jchd Posted April 14, 2014 Share Posted April 14, 2014 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 hereRegExp tutorial: enough to get startedPCRE 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) Link to comment Share on other sites More sharing options...
trancexx Posted April 14, 2014 Share Posted April 14, 2014 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 Link to comment Share on other sites More sharing options...
water Posted April 14, 2014 Share Posted April 14, 2014 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
spooky101 Posted April 14, 2014 Author Share Posted April 14, 2014 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 Link to comment Share on other sites More sharing options...
trancexx Posted April 14, 2014 Share Posted April 14, 2014 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 Link to comment Share on other sites More sharing options...
water Posted April 14, 2014 Share Posted April 14, 2014 Thanks, good to know. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
jchd Posted April 15, 2014 Share Posted April 15, 2014 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 hereRegExp tutorial: enough to get startedPCRE 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) Link to comment Share on other sites More sharing options...
trancexx Posted April 15, 2014 Share Posted April 15, 2014 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now