Catching ADODB SQL errors.

[SpinningCone]

I've looked around but i'm having trouble finding what i need. I need to catch the SQL error output for a statement.

Example:

Global $adoCn = ObjCreate( "ADODB.Connection" )
$adoCn.Properties("Prompt") = 2; 1=PromptAlways, 2=PromptComplete

$ConnectionString = "DRIVER={Oracle in instantclient_11_2};data source=src"

        $adoCn.ConnectionString = $ConnectionString
        $adoCn.Open
        If @error Then
         MsgBox(0, "Problem", "SQL Connection Failed")
        Else


$adoCn.Execute("Blah blah SQL here")
... ?? catch error ?

So i can run a SQL statement. and something like "Select * from dba_users"  is easy to catch. if my record set is zero then i can do some error code.

Problem is i'm trying to write a little tool that can manage passwords. so i want to do an "alter user identified by "xxxx""  Statement. there's no return and if the alter fails i want to get back the error why (maybe password was too weak or other reasons)

But @error doesn't seem to contain anything from the sql.  in fact i'm not even sure how to continue the script . seems the whole thing just dies with a "The requested action with this object has failed.:" error.

[mLipok]

ObjEvent

Look in example for Func _ErrFunc....

[SpinningCone]

ObjEvent

Look in example for Func _ErrFunc....

 

is there a better example of this? doesn't appear to do anything.

if i do this 

ObjEvent ( $adoCn, "_ErrFunc" )

then define the _errFunc as in the example the script still crashes. all I get is "The requested action with this object has failed.:"   _errFunc is never called.

[water]

mLipok is talking about:

Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")

; Your code goes here

; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

[mLipok]

exactly.

here is extended example:

Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")
Global $adoCn = ObjCreate("ADODB.Connection")

$adoCn.Properties("Prompt") = 2; 1=PromptAlways, 2=PromptComplete

$ConnectionString = "DRIVER={Oracle in instantclient_11_2};data source=src"

$adoCn.ConnectionString = $ConnectionString
$adoCn.Open
If @error Then
    MsgBox(0, "Problem", "SQL Connection Failed")
Else


    $adoCn.Execute("Blah blah SQL here")
EndIf

; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

and console output:

>"C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "Z:\TOOLs\Macro\FORUM\test.au3" /UserParams    
+>22:56:58 Starting AutoIt3Wrapper v.14.801.2025.2 SciTE v.3.5.0.0   Keyboard:00000415  OS:WIN_7/Service Pack 1  CPU:X64 OS:X64    Environment(Language:0415)
+>         SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE   UserDir => C:\Users\user\AppData\Local\AutoIt v3\SciTE\AutoIt3Wrapper   SCITE_USERHOME => C:\Users\user\AppData\Local\AutoIt v3\SciTE 
>Running AU3Check (3.3.13.19)  from:C:\Program Files (x86)\AutoIt3  input:Z:\TOOLs\Macro\FORUM\test.au3
+>22:56:58 AU3Check ended.rc:0
>Running:(3.3.10.2):C:\Program Files (x86)\AutoIt3\autoit3.exe "Z:\TOOLs\Macro\FORUM\test.au3"    
--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
test.au3 (10) : ==> COM Error intercepted !
    err.number is:      0x80020009
    err.windescription: Wystąpił wyjątek.

    err.description is:     [Microsoft][Menedżer sterowników ODBC] Nie można odnaleźć nazwy źródła danych, a nie ma podanego sterownika domyślnego
    err.source is:      Microsoft OLE DB Provider for ODBC Drivers
    err.helpfile is:    
    err.helpcontext is:     0
    err.lastdllerror is:    0
    err.scriptline is:  10
    err.retcode is:     0x80004005

+>22:57:02 AutoIt3.exe ended.rc:0
+>22:57:02 AutoIt3Wrapper Finished.
>Exit code: 0    Time: 4.606

BTW.

Use code tag when you post code - Read:  How to post code on the forum

Edited September 22, 2014 by mLipok

[SpinningCone]

OK got it, I thought I was supposed to catch errors from the ADODB object directly. changing it to "AutoIt.Error" catches the COM error. thanks for the help