SQL ADO Get Print Messages?

[mLipok]

Finally:

expandcollapse popup

#include <array.au3>
;~ @Humanzee question from   ; https://www.autoitscript.com/forum/topic/203766-sql-ado-get-print-messages/?do=findComment&comment=1512399
;~ https://www.autoitscript.com/forum/topic/203766-sql-ado-get-print-messages/?do=findComment&comment=1512424
;~ https://stackoverflow.com/questions/66211062/ado-in-case-of-error-how-to-get-information-about-query-line-number

Global $oADODB_Connection = ObjCreate("ADODB.Connection")

_Example()

Func _Example()
    ; Error monitoring. This will trap all COM errors while alive.
    ; This particular object is declared as local, meaning after the function returns it will not exist.
    Local $oErrorHandler = ObjEvent("AutoIt.Error", _ErrFunc)
    Local $_EventHandler = ObjEvent($oADODB_Connection, "__ADO_EVENT__")
    #forceref $oErrorHandler, $_EventHandler

    ; put your SQL sa password here
    Local $sPassword = 'AutoIt'

    #REMARK this script works properly only with DRIVER
;~  $oADODB_Connection.Open("DRIVER=SQL Server;SERVER=localhost\SQLExpress;uid=sa;pwd=" & $sPassword & ";")
    $oADODB_Connection.Open("DRIVER=SQL Server Native Client 11.0;SERVER=localhost\SQLExpress;uid=sa;pwd=" & $sPassword & ";")

    #REMARK and not wtih PROVIDER
;~  $oADODB_Connection.Open("PROVIDER=SQLOLEDB.1;SERVER=localhost\SQLExpress;uid=sa;pwd=" & $sPassword & ";")
;~  $oADODB_Connection.Open("PROVIDER=SQL Server Native Client 11.0;SERVER=localhost\SQLExpress;uid=sa;pwd=" & $sPassword & ";")

    $oADODB_Connection.Execute("PRINT 'testing message'")

    ; final test
    Local $sQuery = _
            "BEGIN TRY" & @CRLF & _
            "    PRINT ' WATCH: BEFORE_ERROR'" & @CRLF & _
            "    RAISERROR ( '?????', 19, 1 )" & @CRLF & _
            "    PRINT ' WATCH: AFTER_ERROR'" & @CRLF & _
            "END TRY" & @CRLF & _
            "" & @CRLF & _
            "BEGIN CATCH" & @CRLF & _
            "    PRINT ' WATCH: CATCH_START';" & @CRLF & _
            "    PRINT ' : NUMBER=' + CAST(ERROR_NUMBER() AS VARCHAR) + ' : SEVERITY=' + CAST(ERROR_SEVERITY() AS VARCHAR) + ' : STATE=' + CAST(ERROR_STATE() AS VARCHAR) + ' : LINE=' + CAST(ERROR_LINE() AS VARCHAR) + ' : MESSAGE=' + ERROR_MESSAGE();" & @CRLF & _
            "    PRINT ' WATCH: CATCH_END';" & @CRLF & _
            "END CATCH;" & @CRLF & _
            ""

    ConsoleWrite("- START" & @CRLF)
    Local $o_recordset3 = $oADODB_Connection.Execute($sQuery)
    ConsoleWrite("- END" & @CRLF)
    #forceref $o_recordset3

EndFunc   ;==>_Example

; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
    Local $iErrorCol_Max = $oADODB_Connection.errors.Count
    If $iErrorCol_Max = 0 Then
        ConsoleWrite(@CRLF & _
                "! ==> COM Error intercepted ==> NOT ADO related Error " & @CRLF & _
                "-" & @TAB & "$oError.description is: " & @TAB & $oError.description & @CRLF & _
                "-" & @TAB & "$oError.number is: " & @TAB & @TAB & $oError.number & "   in HEX is  0x" & Hex($oError.number) & @CRLF & _
                "-" & @TAB & "$oError.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
                "-" & @TAB & "$oError.windescription:" & @TAB & @TAB & $oError.windescription & @CRLF & _
                "-" & @TAB & "$oError.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
                "-" & @TAB & "$oError.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
                "-" & @TAB & "$oError.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
                "-" & @TAB & "$oError.scriptline is: " & @TAB & @TAB & $oError.scriptline & @CRLF & _
                "-" & @TAB & "$oError.retcode is: " & @TAB & @TAB & "0x" & Hex($oError.retcode) & @CRLF & _
                "! ==> COM End of Error dump." & @CRLF & _
                @CRLF)

    Else
        Local $oErr
        For $iErrorCol_idx = 0 To $iErrorCol_Max - 1
            If $iErrorCol_idx = 0 Then ConsoleWrite(@CRLF & _
                    "! ==> COM Error intercepted ==> ADO Error Collection: .errors.Count = " & $iErrorCol_Max & @CRLF & _
                    "")
            $oErr = $oADODB_Connection.errors.Item($iErrorCol_idx)
            ConsoleWrite(@CRLF & _
                    ">" & @TAB & "$oErr(" & $iErrorCol_idx & ").description is: " & @TAB & $oErr.description & @CRLF & _
                    ">" & @TAB & "$oErr(" & $iErrorCol_idx & ").number is: " & @TAB & @TAB & $oError.number & "   in HEX is  0x" & Hex($oErr.number) & @CRLF & _
                    ">" & @TAB & "$oErr(" & $iErrorCol_idx & ").source is: " & @TAB & @TAB & $oErr.source & @CRLF & _
                    ">" & @TAB & "$oErr(" & $iErrorCol_idx & ").SQLState is: " & @TAB & @TAB & $oErr.SQLState & @CRLF & _
                    ">" & @TAB & "$oErr(" & $iErrorCol_idx & ").NativeError is: " & @TAB & $oErr.NativeError & @CRLF & _
                    "-" & @TAB & "$oError.windescription:" & @TAB & @TAB & $oError.windescription & @CRLF & _
                    "-" & @TAB & "$oError.scriptline is: " & @TAB & @TAB & $oError.scriptline & @CRLF & _
                    "-" & @TAB & "$oError.helpfile is: " & @TAB & @TAB & $oError.helpfile & @CRLF & _
                    "-" & @TAB & "$oError.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
                    "-" & @TAB & "$oError.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
                    "-" & @TAB & "$oError.retcode is: " & @TAB & @TAB & "0x" & Hex($oError.retcode) & @CRLF & _
                    "-" & @TAB & "$oError.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
                    "")
        Next
        $oADODB_Connection.errors.clear
        ConsoleWrite("! ==> COM End of Error Collections dump." & @CRLF)
    EndIf
EndFunc   ;==>_ErrFunc


; #INTERNAL_USE_ONLY# ===========================================================================================================
; Name ..........: __ADO_EVENT__InfoMessage
; Description ...:
; Syntax ........: __ADO_EVENT__InfoMessage(Byref $oError, $i_adStatus, Byref $oConnection)
; Parameters ....: $oError              - [in/out] an object.
;                  $i_adStatus          - an integer value.
;                  $oConnection         - [in/out] an object representing ADO Connection.
; Return values .: None
; Author ........: mLipok
; Modified ......:
; Remarks .......: TODO - description
; Related .......:
; Link ..........: https://msdn.microsoft.com/en-us/library/windows/desktop/ms675859(v=vs.85).aspx
; Example .......: No
; ===============================================================================================================================
Func __ADO_EVENT__InfoMessage(ByRef $oError, $i_adStatus, ByRef $oConnection)
    #forceref $i_adStatus, $oConnection, $oError

    If @Compiled Then Return
    Local $iErrorCol_Max = $oConnection.errors.Count
    ConsoleWrite("! $iErrorCol_Max =" & $iErrorCol_Max & @CRLF)

    Local $oErr
    For $iErrorCol_idx = 0 To $iErrorCol_Max - 1
        $oErr = $oConnection.errors.Item($iErrorCol_idx)
        ConsoleWrite(' [ADO InfoMessage]=' & $oErr.description & @CRLF)
    Next
EndFunc   ;==>__ADO_EVENT__InfoMessage

with this SQL:

BEGIN TRY
    PRINT ' WATCH: BEFORE_ERROR'
    RAISERROR ( '?????', 19, 1 )
    PRINT ' WATCH: AFTER_ERROR'
END TRY

BEGIN CATCH
    PRINT ' WATCH: CATCH_START';
    PRINT ' : NUMBER=' + CAST(ERROR_NUMBER() AS VARCHAR) + ' : SEVERITY=' + CAST(ERROR_SEVERITY() AS VARCHAR) + ' : STATE=' + CAST(ERROR_STATE() AS VARCHAR) + ' : LINE=' + CAST(ERROR_LINE() AS VARCHAR) + ' : MESSAGE=' + ERROR_MESSAGE();
    PRINT ' WATCH: CATCH_END';
END CATCH;

I get such result:

 

>Running:(3.3.16.1):Z:\AutoItPortable\App\autoit3_x64.exe "Z:\!!!_SVN_AU3\UDF_Forum\mLipok\Examples\ADO\ADO_EXAMPLE_MS_SQL_buymeapc__Get Print Messages_v2__ADO_Error_Collections.au3"    
[ADO InfoMessage]=[Microsoft]

Changed database context to 'master'.
[ADO InfoMessage]=[Microsoft][SQL Server Native Client 11.0][SQL Server]Changed language setting to us_english.

ADO EVENT fired function: __ADO_EVENT__ExecuteComplete:   $iRecordsAffected=-1   $i_adStatus=1

[ADO InfoMessage]=[Microsoft][SQL Server Native Client 11.0][SQL Server]testing message

- START
 ADO EVENT fired function: __ADO_EVENT__ExecuteComplete:   $iRecordsAffected=-1   $i_adStatus=1
[ADO InfoMessage]=[Microsoft][SQL Server Native Client 11.0][SQL Server] WATCH: BEFORE_ERROR
[ADO InfoMessage]=[Microsoft][SQL Server Native Client 11.0][SQL Server] WATCH: CATCH_START
[ADO InfoMessage]=[Microsoft][SQL Server Native Client 11.0][SQL Server] : NUMBER=2754 : SEVERITY=16 : STATE=1 : LINE=3 : MESSAGE=Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.
[ADO InfoMessage]=[Microsoft][SQL Server Native Client 11.0][SQL Server] WATCH: CATCH_END
- END
>
+>12:28:58 AutoIt3 ended. rc:0

 

Edited May 14 by mLipok
script cleanup and comments

[mLipok]

WARNING:
Please notice:

#REMARK this script works properly only with DRIVER
;~  $oADODB_Connection.Open("DRIVER=SQL Server;SERVER=localhost\SQLExpress;uid=sa;pwd=" & $sPassword & ";")
    $oADODB_Connection.Open("DRIVER=SQL Server Native Client 11.0;SERVER=localhost\SQLExpress;uid=sa;pwd=" & $sPassword & ";")

    #REMARK and not wtih PROVIDER
;~  $oADODB_Connection.Open("PROVIDER=SQLOLEDB.1;SERVER=localhost\SQLExpress;uid=sa;pwd=" & $sPassword & ";")
;~  $oADODB_Connection.Open("PROVIDER=SQL Server Native Client 11.0;SERVER=localhost\SQLExpress;uid=sa;pwd=" & $sPassword & ";")

using PROVIDER you will hit the problem that only first PRINT :

Local $sQuery = _
            "BEGIN TRY" & @CRLF & _
            "    PRINT ' WATCH: BEFORE_ERROR'" & @CRLF & _
            "    RAISERROR ( '?????', 19, 1 )" & @CRLF & _
            "    PRINT ' WATCH: AFTER_ERROR'" & @CRLF & _
            "END TRY" & @CRLF & _
            "" & @CRLF & _
            "BEGIN CATCH" & @CRLF & _
            "    PRINT ' WATCH: CATCH_START';" & @CRLF & _
            "    PRINT ' : NUMBER=' + CAST(ERROR_NUMBER() AS VARCHAR) + ' : SEVERITY=' + CAST(ERROR_SEVERITY() AS VARCHAR) + ' : STATE=' + CAST(ERROR_STATE() AS VARCHAR) + ' : LINE=' + CAST(ERROR_LINE() AS VARCHAR) + ' : MESSAGE=' + ERROR_MESSAGE();" & @CRLF & _
            "    PRINT ' WATCH: CATCH_END';" & @CRLF & _
            "END CATCH;" & @CRLF & _
            ""

 

Will be outputed to console by __ADO_EVENT__InfoMessage()

I think that this is some kind of limitation how events are captured by PROVIDER.

[mLipok]

Please anybody test it on other DB as I test it only with MS SQL.

Edited May 23 by mLipok