ADO Event handling

From AutoIt Wiki
Jump to navigation Jump to search

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

Retrieved from "https://www.autoitscript.com/w/index.php?title=ADO_Event_handling&oldid=14427"