Wiki Home

Trap Database Error


Namespace: VFP
Here is an example of a database level event being caught by VFP.

#DEFINE DSN "pw"
#DEFINE USERNAM "pwuser"
#DEFINE PASSWD "ABC"

Clear
* clean up previous runs
On Error ? Message()
Select v_foo
TableRevert()
Close Databases all
Delete Database Foo
On Error

lhx = SQLConnect( DSN, USERNAM, PASSWD )
lckErr( lhx, "conn" )

* More clean up
? SQLExec( lhx, "drop table dbo.foo" )
? SQLExec( lhx, "drop trigger fooupdate" )


* Make table
ExecSql( lhx, "create table dbo.foo (kFoo_pk uniqueidentifier primary key default newid(), cFid1 char(10), lClosed bit )" )

* Make Trigger
Text to lcSqlCmd noshow
create trigger fooupdate on dbo.foo for Update as
BEGIN
set nocount on
   RAISERROR ('no update for you!',16,1)
   ROLLBACK TRANSACTION
END
EndText
ExecSql( lhx, lcSqlCmd )

Create Database foo
Create Connection foocon datasource DSN userid USERNAM password PASSWD
Create SQL View v_foo remote connection foocon as select * from foo
DBSetProp("v_foo","VIEW","SendUpdates",.t.)

? "#1"
Insert into v_foo (cFid1) values ("A")
? UpdateTable('v_foo')

? "#2"
ExecSql( lhx, "Select * from foo" )
list

Select v_foo
use

? "#3"
Update v_foo set cFid1 = "B" where cFid1 = "A"
? UpdateTable('v_foo')

? "#4"
ExecSql( lhx, "Select * from foo" )
list

Return

*************************************************************************
Function UpdateTable( tcAlias )
If TableUpdate(.t.,.t.,tcAlias)
	Return .t.
else
	xErrHlr()
	Return .f.
Endif
*************************************************************************
Function ExecSql( tnH, tcSql )
lnR = SQLExec( tnH, tcSql )
lckErr( lnR, tcSql )
Return
*************************************************************************
Function lckErr( tnErr, tcDsc )
	If !Empty( tcDsc )
*		? tcDsc, Trans( tcErr )
	Endif
	If tnErr < 0
		xErrHlr()
	Endif
	Return .T.
Endfunc
*************************************************************************
Function xErrHlr( tnLin, tcLin )

	#Define CRLF Chr(13)+Chr(10)
	#Define CR_LF Chr(13) + Chr(10)

	Local ;
		lnErr, lnErr, ;
		llOleErr, llOdbcErr, llTrigErr, ;
		lcErrMsg

	Local ;
		array laErrs[1]

	If !Empty( tcLin )
		? "Line " + Transform( tnLin ) + ": " + Transform( tcLin )
	Endif

	For lnErr = 1 To Aerror( laErrs )

		llOleErr = Between( laErrs[lnErr, 1], 1426, 1429 )
		llOdbcErr = laErrs[lnErr, 1] = 1526
		llTrigErr = laErrs[lnErr, 1] = 1539

		lcErrMsg = ;
			'1. Error: ' + Trans( laErrs[lnErr, 1] ) + CRLF ;
			+ '2. Message: "' + laErrs[lnErr, 2] + '"' + CRLF ;
			+ '3. ' + Iif( llOleErr Or llOdbcErr, "OLE/ODBC: ", "EMP: " ) + Transform( laErrs[lnErr, 3] ) + CRLF ;
			+ '4. ' + Iif( llOleErr, "App: ", ;
			iif( llOdbcErr, "SQL State: ", ;
			"Work Area (?): " ) + Transform( laErrs[lnErr, 4] ) ) + CRLF ;
			+ '5. ' + Iif( llOleErr, "OLE Help file: " + Trans( laErrs[lnErr, 5] ), ;
			+ Iif( llOdbcErr, "ODBC Error: " + Transform( laErrs[lnErr, 5] ), ;
			+ Iif( llTrigErr, "Trigger: " + Substr( "InsertUpdateDelete", laErrs[lnErr, 5]*6-5, 6 ), ;
			+ "na: " + Transform( laErrs[lnErr, 5] ) ) ) ) + CRLF ;
			+ '6. ' + Iif( llOleErr, "OLE Help Context Id : ", ;
			+ Iif( llOdbcErr, "ODBC Connection Handle: ", ;
			+ "na: " ) ) + Transform( laErrs[lnErr, 6] ) + CRLF ;
			+ '7. ' + Iif( llOleErr, "OLE exception number: ", ;
			+ "na: " ) + Transform( laErrs[lnErr, 6] )

		Do Case
			Case laErrs(1) = 1539
				lcErrMsg = lcErrMsg ;
					+ "Trigger error #: " ;
					+ Transform( laErrs(5) ) + ', ' ;
					+ Substr( "InsertUpdateDelete", laErrs(5)*6-5, 6 ) ;
					+ CR_LF + CR_LF

				If Vartype( gaErrors[1] ) = 'N'
					For lnErrorRow = 1 To Alen( gaErrors, 1 )
						lcErrMsg = lcErrMsg +;
							"1. Error number: " + Trans( gaErrors[lnErrorRow,1] ) + CR_LF + ;
							"2. Error text: " + Trans( gaErrors[lnErrorRow,2] ) + CR_LF + ;
							"3. Source: " + gaErrors[lnErrorRow,3] + CR_LF + ;
							"4. Call stack: " + gaErrors[lnErrorRow,4] + CR_LF + ;
							"5. Parent table name: " + gaErrors[lnErrorRow,5] + CR_LF + ;
							"6. Parent record number: " + Trans( gaErrors[lnErrorRow,6] ) + CR_LF + ;
							"7. Original value of Primary Key of the Parent: " + Trans( gaErrors[lnErrorRow,7] ) + CR_LF + ;
							"8. Parent Primary Key (expression): " + gaErrors[lnErrorRow,8] + CR_LF + ;
							"9. Child Table Name: " + gaErrors[lnErrorRow,9] + CR_LF + ;
							"10. Child Record Number: " + Trans( gaErrors[lnErrorRow,10] ) + CR_LF + ;
							"11. Original value of Foreign Key in the Child: " + Trans( gaErrors[lnErrorRow,11] ) + CR_LF + ;
							"12. Child Foreign Key (expression): " + gaErrors[lnErrorRow,12 ] + CR_LF + CR_LF
					Endfor
				Endif
		Endcase

		? lcErrMsg
		_Cliptext = _Cliptext + CR_LF + lcErrMsg
	Endfor

	Cancel
	Return
Endfunc


Contributor: Carl Karsten

Category Code Samples
( Topic last updated: 2007.05.24 09:31:48 AM )