ADO Event handling

From AutoIt Wiki
Revision as of 01:28, 18 October 2020 by MLipok (talk | contribs) (→‎Second example)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

This page is still a work in progress.

First example

Global $__g_oConnection = ObjCreate("ADODB.Connection")
Global $__g_oErrorHandler = ObjEvent("AutoIt.Error", _ErrFunc)
Global $__g_oADO_EventHandler = ObjEvent($__g_oConnection, "__ADO_EVENT__")

_Example()
Func _Example()
	Local $sPassword = 'AutoIt' ; change to your own pass
	Local $sInstance = 'localhost\SQLExpress' ; change to your own SQL server Instances
	$__g_oConnection.Open("PROVIDER=SQLOLEDB.1;SERVER=" & $sInstance & ";uid=sa;pwd=" & $sPassword & ";")
	Local $objquery = $__g_oConnection.Execute("print 'this is a test - hello - Line 1'")
	$objquery.parent2

	$__g_oConnection.Execute("UPDATE [BAZA].[dbo].[Adresy] SET [Numer] = 1 WHERE [Numer] = 1")
	$__g_oConnection.Execute("UPDATE [BAZA].[dbo].[Adresy] SET [Numer] = [numer]")
	#forceref $objquery

EndFunc   ;==>_Example

Func _ErrFunc(ByRef $oError)
	ConsoleWrite(@CRLF & _
			"! ==> COM Error intercepted ==>" & @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)
EndFunc   ;==>_ErrFunc

Func __ADO_ConsoleWrite_Blue($sText)
	ConsoleWrite('>>' & $sText & @CRLF)
EndFunc   ;==>__ADO_ConsoleWrite_Blue

; ####################################

Func __ADO_EVENT__WillConnect($sConnection_String, $sUserID, $sPassword, $iOptions, $i_adStatus, ByRef $oConnection)
;~ 	Return
	__ADO_ConsoleWrite_Blue(@CRLF)
	__ADO_ConsoleWrite_Blue("__ADO_EVENT__WillConnect:")
;~ 	__ADO_ConsoleWrite_Blue("   $sConnection_String=" & $sConnection_String) ; for security reason should not be populated to output
	__ADO_ConsoleWrite_Blue("   $sConnection_String=" & StringLen($sConnection_String)) ; for security reason should not be populated to output
	__ADO_ConsoleWrite_Blue("   $sUserID=" & $sUserID)
	__ADO_ConsoleWrite_Blue("   $sPassword=" & $sPassword)
	__ADO_ConsoleWrite_Blue("   $iOptions=" & $iOptions)
	__ADO_ConsoleWrite_Blue("   $i_adStatus=" & $i_adStatus)
	#forceref $oConnection
EndFunc   ;==>__ADO_EVENT__WillConnect

Func __ADO_EVENT__ConnectComplete(ByRef $oError, $i_adStatus, ByRef $oConnection)
	#forceref $oError, $oConnection
	__ADO_ConsoleWrite_Blue(@CRLF)
	__ADO_ConsoleWrite_Blue("__ADO_EVENT__ConnectComplete:")
	__ADO_ConsoleWrite_Blue("   $i_adStatus=" & $i_adStatus)
EndFunc   ;==>__ADO_EVENT__ConnectComplete

Func __ADO_EVENT__InfoMessage(ByRef $oError, $i_adStatus, ByRef $oConnection)
	#forceref $oError, $oConnection
	__ADO_ConsoleWrite_Blue(@CRLF)
	__ADO_ConsoleWrite_Blue("__ADO_EVENT__InfoMessage:")
	__ADO_ConsoleWrite_Blue("   $i_adStatus=" & $i_adStatus)
EndFunc   ;==>__ADO_EVENT__InfoMessage

Func __ADO_EVENT__ExecuteComplete($iRecordsAffected, $oError, $i_adStatus, ByRef $oCommand, ByRef $oRecordset, ByRef $oConnection)
	#forceref $oCommand, $oRecordset
	__ADO_ConsoleWrite_Blue(@CRLF)
	__ADO_ConsoleWrite_Blue("__ADO_EVENT__ExecuteComplete:")
	__ADO_ConsoleWrite_Blue("   $iRecordsAffected=" & $iRecordsAffected)
	__ADO_ConsoleWrite_Blue("   VarGetType($oError)=" & VarGetType($oError))
	__ADO_ConsoleWrite_Blue("   $i_adStatus=" & $i_adStatus)
	Local $iErrorCol_Max = $oConnection.errors.Count
	If $iErrorCol_Max = 0 Then Return
	Local $oErr
	For $iErrorCol_idx = 0 To $iErrorCol_Max - 1
		If $iErrorCol_idx = 0 Then __ADO_ConsoleWrite_Blue("> ==> ADO Error Collection: .errors.Count = " & $iErrorCol_Max)
		$oErr = $oConnection.errors.Item($iErrorCol_idx)
		If Not ($oErr.NativeError Or $oErr.number) Then
			__ADO_ConsoleWrite_Blue(@TAB & "$oErr.Item(" & $iErrorCol_idx & ").description is: " & @TAB & @TAB & $oErr.description)
			__ADO_ConsoleWrite_Blue(@TAB & "$oErr.Item(" & $iErrorCol_idx & ").source is: " & @TAB & @TAB & $oErr.source)
			__ADO_ConsoleWrite_Blue(@TAB & "$oErr.Item(" & $iErrorCol_idx & ").SQLState is: " & @TAB & @TAB & $oErr.SQLState)
			__ADO_ConsoleWrite_Blue(@TAB & "$oErr.Item(" & $iErrorCol_idx & ").NativeError is: " & @TAB & @TAB & $oErr.NativeError)
		Else
			__ADO_ConsoleWrite_Blue(@TAB & "$oErr.Item(" & $iErrorCol_idx & ").description is: " & @TAB & @TAB & $oErr.description)
			__ADO_ConsoleWrite_Blue(@TAB & "$oErr.Item(" & $iErrorCol_idx & ").source is: " & @TAB & @TAB & $oErr.source)
			__ADO_ConsoleWrite_Blue(@TAB & "$oErr.Item(" & $iErrorCol_idx & ").NativeError is: " & @TAB & @TAB & $oErr.NativeError)
			__ADO_ConsoleWrite_Blue(@TAB & "$oErr.Item(" & $iErrorCol_idx & ").SQLState is: " & @TAB & @TAB & $oErr.SQLState)
			__ADO_ConsoleWrite_Blue(@TAB & "$oErr.Item(" & $iErrorCol_idx & ").windescription:" & @TAB & @TAB & $oErr.windescription)
;~ 			__ADO_ConsoleWrite_Blue(@TAB & "$oErr.Item(" & $iErrorCol_idx & ").scriptline is: " & @TAB & @TAB & $oErr.scriptline )
			__ADO_ConsoleWrite_Blue(@TAB & "$oErr.Item(" & $iErrorCol_idx & ").helpfile is: " & @TAB & @TAB & $oErr.helpfile)
			__ADO_ConsoleWrite_Blue(@TAB & "$oErr.Item(" & $iErrorCol_idx & ").helpcontext is: " & @TAB & $oErr.helpcontext)
			__ADO_ConsoleWrite_Blue(@TAB & "$oErr.Item(" & $iErrorCol_idx & ").lastdllerror is: " & @TAB & $oErr.lastdllerror)
			__ADO_ConsoleWrite_Blue(@TAB & "$oErr.Item(" & $iErrorCol_idx & ").number is: " & @TAB & @TAB & "0x" & Hex($oErr.number))
			__ADO_ConsoleWrite_Blue(@TAB & "$oErr.Item(" & $iErrorCol_idx & ").retcode is: " & @TAB & @TAB & "0x" & Hex($oErr.retcode))
		EndIf
	Next
;~ 	$oConnection.errors.clear
EndFunc   ;==>__ADO_EVENT__ExecuteComplete

Second example

_Example()

Func _Example()
	; Error Handler - will trap all COM errors while alive.
	Local $oCOM_ErrorHandler = ObjEvent("AutoIt.Error", _COM_ErrorHandler_Function)

	; Create connection object
	Local $oConnection = ObjCreate('ADODB.Connection')

	; Assing EVENT Handler
	Local $oADO_EventsHandler = ObjEvent($oConnection, "__ADO_EVENT__")
	#forceref $oADO_EventsHandler

	Local $sPassword = 'AutoIt' ; CHANGE TO YOUR OWN PASS
	Local $sInstance = 'localhost\SQLExpress' ; CHANGE TO YOUR OWN MS SQL SERVER INSTANCES

	; Open connection with desired "ConnectionString"
	$oConnection.Open("PROVIDER=SQLOLEDB.1;SERVER=" & $sInstance & ";uid=sa;pwd=" & $sPassword & ";")
	$oConnection.Execute("PRINT 'this is a test - Hello World - Line 1'")
	$oConnection.Execute("PRINT 'this is a test - Hello World - Line 2'")
	$oConnection.Execute("PRINT 'this is a test - Hello World - Line 3'")
	$oConnection.Close

EndFunc   ;==>_Example

Func _ADO_Execute(ByRef $oConnection, $sQUERY)
	$oConnection.Execute($sQUERY)
	ConsoleWrite("- " & @CRLF)
EndFunc


; User's COM error function. Will be called if COM error occurs
Func _COM_ErrorHandler_Function($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   ;==>_COM_ErrorHandler_Function

Func __ADO_EVENT__ExecuteComplete($iRecordsAffected, $oError, $i_adStatus, ByRef $oCommand, ByRef $oRecordset, ByRef $oConnection)
	ConsoleWrite(" ADO EVENT fired function: __ADO_EVENT__ExecuteComplete" & @CRLF)
	#forceref $iRecordsAffected, $oError, $i_adStatus, $oCommand, $oRecordset, $oConnection
EndFunc   ;==>__ADO_EVENT__ExecuteComplete


Func __ADO_EVENT__InfoMessage(ByRef $oError, $i_adStatus, ByRef $oConnection)
	ConsoleWrite(" ADO EVENT fired function: __ADO_EVENT__InfoMessage" & @CRLF)
	ConsoleWrite("[ADO InfoMessage]=" & $oError.description & @CRLF)
	#forceref $oError, $i_adStatus, $oConnection
EndFunc   ;==>__ADO_EVENT__InfoMessage

Func __ADO_EVENT__WillExecute($sSource, $iCursorType, $iLockType, $iOptions, $i_adStatus, ByRef $oCommand, ByRef $oRecordset, ByRef $oConnection)
	ConsoleWrite(" ADO EVENT fired function: __ADO_EVENT__WillExecute" & @CRLF)
	#forceref $sSource, $iCursorType, $iLockType, $iOptions, $i_adStatus, $oCommand, $oRecordset, $oConnection
EndFunc   ;==>__ADO_EVENT__WillExecute


Level 2 Heading