Sql Error Messages

Discussion forum about all things Team Developer 5.x and 6.x
analiz14
Turkey
Posts: 11
Joined: 27 Mar 2019, 08:14
Location: Nicosia Cyprus

Sql Error Messages

Post by analiz14 » 17 Feb 2020, 15:51

Hey Friends,

I am using TD 6.3

I am trying to handle and log Sql Errors, where I use functions

Call SqlExtractArgs( wParam, lParam, hSqlError, nErrorNo, nPos )
Call SqlGetErrorText( nErrorNo, sErrorText )

In the second function, I do not get the correct Error String related to the Error Number.
I always get 'Command not found for retrieval' as an Error String.

Has anybody faced such a thing before?

Regards
Mehmet

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 137
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

Re: Sql Error Messages

Post by Steve Leighton » 17 Feb 2020, 21:14

Hello Mehmet
You need to specify what database you are connecting to, and the connection method --> SQLBase, SQLServer, Oracle with native router, ODBC or OLE DB ? these things make a difference to approach for getting the error text you need.
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

analiz14
Turkey
Posts: 11
Joined: 27 Mar 2019, 08:14
Location: Nicosia Cyprus

Re: Sql Error Messages

Post by analiz14 » 18 Feb 2020, 21:55

Thanks, Steve
I realized that the error message I get comes from error.sql file located in "C:\Program Files (x86)\Gupta\Team Developer 6.3" directory
and as you said for example for SQLBASE error.sql file is different and located in "C:\Program Files (x86)\Gupta\Team Developer 6.3\sqlbase directory.
I connect to MSSQL and POSTGRESQL using oledb method. But I do know how to specify which connections I use.

All the best from Notrh Cyprus
Mehmet

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 137
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

Re: Sql Error Messages

Post by Steve Leighton » 18 Feb 2020, 23:30

Glad to see you are using OLE DB .
Yes the error.sql file is for SQLBase only.

For those who need a simple example of trapping and displaying any vendors OLE DB SQL errors:

Trap Session SQL errors on a global level via On SAM_SessionError ( and from there you can branch to Statement level errors )
Trap Statement SQL errors on a global level via On SAM_SqlError

e.g.

Code: Select all

On SAM_AppStartup
On SAM_AppExit
On SAM_SqlError
	Return AppOLEDBSessionStatementError( wParam, lParam )
On SAM_SessionError
	Return AppOLEDBSessionError( wParam, lParam, hWndNULL )

Code: Select all

Function: AppOLEDBSessionError
	Description: Function called when application receives a SAM_SessionError Message
	Returns
		Boolean:
	Parameters
		Number: p_wParam
		Number: p_lParam
		Sql Handle: p_hSqlError
	Static Variables
		Boolean: bStaticAlreadyFailed
	Local variables
		Number: nErrorNo
		Long String: sError
		String: sState
		Boolean: bQuit
	Actions
		!
		If bStaticAlreadyFailed
			Return FALSE
		Set bStaticAlreadyFailed = TRUE
		!
		Call SalSetBufferLength( sError, 500)
		If SqlGetSessionErrorInfo( ghSession, nErrorNo, sError, sState )
			Call SqlRollbackSession (ghSession)
			Set bQuit =  SalModalDialog(dlgSQLError, hWndForm, 'Global OLE DB Session Error:' || CTRL || CTRL ||  sError,  sState,  hWndNULL, '', ''  )
		Else If AppOLEDBSessionStatementError( p_wParam, p_lParam, p_hSqlError )
			Set bQuit = TRUE
		Else
			Set bQuit =  SalModalDialog(dlgSQLError, hWndForm, 'Global OLE DB Error:' || CTRL || CTRL ||
					'Unable to retrieve any OLE DB Session or Statement error information.',  STRING_Null,  hWndNULL, '', ''  )
		!
		If bQuit
			Call SqlRollbackSession (ghSession)
			Call SalQuit()
		!
		Set bStaticAlreadyFailed = FALSE
		Return FALSE

Code: Select all

Function: AppOLEDBSessionStatementError
	Description: Function called when application receives a SAM_SqlError Message
	Returns
		Boolean:
	Parameters
		Number: p_wParam
		Number: p_lParam
		Sql Handle: p_hSqlError
	Static Variables
	Local variables
		Number: nErrorNo
		Number: nErrorPos
		Long String: sError
		String: sState
		String: sSqlInError
		Boolean: bOk
	Actions
		!
		If p_hSqlError = hWndNULL
			Call SqlExtractArgs( p_wParam, p_lParam, p_hSqlError , nErrorNo, nErrorPos )
		!
		Call SalSetBufferLength( sError, 500)
		Call SalSetBufferLength( sSqlInError, 500)
		Call SqlGetCommandText (p_hSqlError,  sSqlInError)
		Set bOk = SqlGetStatementErrorInfo( p_hSqlError, nErrorNo, sError, sState )
		!
		If p_hSqlError
			Call SqlPrepareAndExecute( p_hSqlError, Constant_ROLLBACK )
		!
		If bOk
			Set bOk = SalModalDialog(dlgSQLError, hWndForm, 'OLE DB Statement Error:' || CTRL || CTRL ||  sError,  sSqlInError,  p_hSqlError, '', ''  )
		!
		Return bOk
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

analiz14
Turkey
Posts: 11
Joined: 27 Mar 2019, 08:14
Location: Nicosia Cyprus

Re: Sql Error Messages

Post by analiz14 » 20 Feb 2020, 06:17

Steve,

Thank a lot for very valuable info, plus an example.

Regards

Mehmet

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests