Looking to get more error checking from SQL

[JLogan3o13]

I am currently working with a large script that connects to and pulls data from several databases, both Oracle and SQL Server. In both cases, I am connecting to the db using the locally installed client and then passing in a query. Everything works as expected, I am just looking for some improvement.

One section of the code takes input from the end user (policy number, for example) and then modifies the sql query appropriately. What I am having a hard time with is the error checking. I have a COM error handler in there, and can catch the error, but mainly what I get is the standard "requested action with this object failed" verbiage. I am just curious if any SQL experts know how I can retrieve better errors through the local client, something akin to RapidSQL or SQL Management Studio that would tell me "no records returned" or "invalid identifier" if a value is missing. These queries are quite large in some cases, and I am sure they'll change or be updated from time to time. I am just looking for a method to better troubleshoot a failure (see below for example).

; User prompted for Policy Number. Policy Number added to query

;Query
$sCallersSQLStatement = StringTrimLeft(_ArrayToString($aQuery, " "), 3)

;connect to Oracle db
$sCallersConnectionString = "Provider=MSDAORA.1;User ID=svcaccount;Password=dev;Data Source=ORADEV;Persist Security Info=False"

$ado = ObjCreate("ADODB.Connection")
    If Not IsObj($ado) Then MsgBox(64, "", "Error creating Connection Object")
$adors = ObjCreate("ADODB.RecordSet")
    If Not IsObj($adors) Then MsgBox(64, "", "Error creating RecordSet Object")
$ado.Open($sCallersConnectionString)
    If @error Then
       MsgBox(64, "", "Error opening Connection")
    Else
       _FileWriteLog($log, "Successfully opened connection to Database.")
    EndIf
$adors.Open($sCallersSQLStatement, $ado)
    If @error Then
       MsgBox(64, "", "Error calling Statement")
    Else
       _FileWriteLog($log, "Successfully passed query to database and returned record set.")
    EndIf

;typically the issue is with trying to access the record set - $adors.Open($sCallersSQLStatement, $ado). This is caught with the standard error handling, but I am not 

;provided with a ton of information regarding the cause of the failure (could be one of several things wrong). Just curious if there is a way to pull specific error messages from the Oracle client.

[trancexx]

I'm not SQL expert you ask for but I know this:

$sCallersConnectionString = "WTF"
$oADO = ObjCreate("ADODB.Connection")
If Not IsObj($oADO) Then Exit MsgBox(64, "", "Error creating Connection Object")

$oADO.Open($sCallersConnectionString)
If @error Then
    For $oError In $oADO.Errors
        ConsoleWrite("! " & $oError.Description & @CRLF)
    Next
Else
    ;...
EndIf

That helps?

[JLogan3o13]

Thanks for the suggestion. Unfortunately, it doesn't write anything to the console - I get the same error.

Edited September 26, 2013 by JLogan3o13

[trancexx]

You are running 3.3.8.1 AutoIt. For that one you need COM error handler if you want to continue execution on COM error.

This was incredibly lame reaction so I changed that eventually.

Error function can be simple dummy or full version. Latter will give you full error report just like ADO error object would.

[trancexx]

BTW, on that pic you have hidden path to your script, not.

[JLogan3o13]

I wondered as much. And my fault as well, I apologize for not putting in my original post that, due to some other project constraints, I am not free to move up from 3.3.8.1 at the moment. But, at least knowing that it will be available when I am able to update is good news.

[JLogan3o13]

BTW, on that pic you have hidden path to your script, not.

 

Yeah, knee-jerk reaction. After posting I wondered why I bothered

[trancexx]

You wonder too much .

What I wonder myself is if you have forgotten how to use COM error handler. This is wrong:

ObjEvent("AutoIt.Error", "_ErrFunc")
Func _ErrFunc($oError)
    ; Nothing
EndFunc

$sCallersConnectionString = "WTF"
$oADO = ObjCreate("ADODB.Connection")
If Not IsObj($oADO) Then Exit MsgBox(64, "", "Error creating Connection Object")

$oADO.Open($sCallersConnectionString)
If @error Then
    For $oError In $oADO.Errors
        ConsoleWrite("! " & $oError.Description & @CRLF)
    Next
Else
    ;...
EndIf

This is right:

$oCOMErrorObject = ObjEvent("AutoIt.Error", "_ErrFunc")
Func _ErrFunc($oError)
    ; Nothing
EndFunc

$sCallersConnectionString = "WTF"
$oADO = ObjCreate("ADODB.Connection")
If Not IsObj($oADO) Then Exit MsgBox(64, "", "Error creating Connection Object")

$oADO.Open($sCallersConnectionString)
If @error Then
    For $oError In $oADO.Errors
        ConsoleWrite("! " & $oError.Description & @CRLF)
    Next
Else
    ;...
EndIf

Do you see the difference?

Edited September 27, 2013 by trancexx

[mLipok]

This is great.

--> Press Ctrl+Alt+F5 to Restart or Ctrl+Break to Stop
! [Microsoft][Menedżer sterowników ODBC] Nie można odnaleźć nazwy źródła danych, a nie ma podanego sterownika domyślnego

+>09:57:38 AutoIt3.exe ended.rc:0
>Exit code: 0    Time: 0.518

TRANSLATION: ! [Microsoft] [ODBC Driver Manager] Can not find the data source name, and no default driver specified.

 

very useful

Thanks @trancexx

EDIT: typo

Edited September 27, 2013 by mlipok

[JLogan3o13]

You wonder too much .

What I wonder myself is if you have forgotten how to use COM error handler. This is wrong:

ObjEvent("AutoIt.Error", "_ErrFunc")
Func _ErrFunc($oError)
    ; Nothing
EndFunc

$sCallersConnectionString = "WTF"
$oADO = ObjCreate("ADODB.Connection")
If Not IsObj($oADO) Then Exit MsgBox(64, "", "Error creating Connection Object")

$oADO.Open($sCallersConnectionString)
If @error Then
    For $oError In $oADO.Errors
        ConsoleWrite("! " & $oError.Description & @CRLF)
    Next
Else
    ;...
EndIf

This is right:

$oCOMErrorObject = ObjEvent("AutoIt.Error", "_ErrFunc")
Func _ErrFunc($oError)
    ; Nothing
EndFunc

$sCallersConnectionString = "WTF"
$oADO = ObjCreate("ADODB.Connection")
If Not IsObj($oADO) Then Exit MsgBox(64, "", "Error creating Connection Object")

$oADO.Open($sCallersConnectionString)
If @error Then
    For $oError In $oADO.Errors
        ConsoleWrite("! " & $oError.Description & @CRLF)
    Next
Else
    ;...
EndIf

Do you see the difference?

 

I do see the difference; and see why I was being thick. When copying over your example I didn't include my error handler. I see that done properly (imagine that), it gives me the information I need, even in 3.3.8.1. Thanks for the nudge.