Programming with dBASE - raise rror in mssql triggers

Author

raise rror in mssql triggers

Robert Bravery

2006-11-25, 5:12 am

HI all,

I am having a problem returning errors that are raised in a trigger of a
mssql statement
I have a SP that adds rows, on the table is a trigger to check a date range,
if the date range is out, I raise an error:
raiserror('claim not in insurance period',16,1).
This error gets raised when adding rows via the query analyzer or other
client.
But in dbase I try to execute the SP via sqlexec("exec addclaimheader")
the claim is not added, neither is the error returned
sqlexec returns o
sqlerror returns 0
error returns 0
dberror returns 0

Ay ideas

Thanks
RObert

*Lysander*

2006-11-26, 7:12 pm

Robert Bravery schrieb:

> But in dbase I try to execute the SP via sqlexec("exec addclaimheader")
> the claim is not added, neither is the error returned

That's expected behaviour to me, because the sqlexec() and/or
database.executesql() are one-way commands.

You could try to catch the error by wrapping the command into a
try/catch/endtry.

form.database1.begintrans()
try
form.database1.executeSQL("exec addclaimheader")
form.database1.commit()
catch (dbexception db)
form.database1.rollback()
messagebox(db.message)
endtry

That's how I usually can catch all db-errors from any server, though
lately I did not try with MS-SQL.

If that does NOT work, you should be using the storedProc-Objects from
within dBase. They definitely allow full error-handling.

André
====================
====================
====================
=====
dB-D-A-CH // deutschsprachige Usergroup für alles rund um dBase
Jahreskonferenz 2007: 09. und 10. November 2007 in Marienheide
Jahreskonferenz 2008: 14. und 15. November 2008 in Marienheide
Jahreskonferenz 2009: (wird noch nicht verraten...)

Robert Bravery

2006-11-26, 7:12 pm

HI,

I see where youre getting with that. But not according to the help file:
"SQLEXEC( ) returns error codes with the same values as those returned by
ERROR( ) and MESSAGE( ); a value of zero indicates that no error occurred as
a result of the statement's execution. If an error occurs, you can use
DBERROR( ) and DBMESSAGE( ) functions to return BDE errors or use the
SQLERROR( ) and SQLMESSAGE( ) functions to obtain information directly from
the database server about the cause of an error. (Also, the ERROR( )
function returns an error code of 240 if a server error occurs.)"

Also I tried a simple Sp that returns an error, which works. SO there must
be some other thing wrong that dbase, sqlexec executesql is not picking up

Robert

"*Lysander*" <nobody@nowhere.com> wrote in message
news:5CemGDWEHHA.1420@news-server...
> Robert Bravery schrieb:
>
>
> That's expected behaviour to me, because the sqlexec() and/or
> database.executesql() are one-way commands.
>
> You could try to catch the error by wrapping the command into a
> try/catch/endtry.
>
> form.database1.begintrans()
> try
> form.database1.executeSQL("exec addclaimheader")
> form.database1.commit()
> catch (dbexception db)
> form.database1.rollback()
> messagebox(db.message)
> endtry
>
> That's how I usually can catch all db-errors from any server, though
> lately I did not try with MS-SQL.
>
>
> If that does NOT work, you should be using the storedProc-Objects from
> within dBase. They definitely allow full error-handling.
>
> André
> ====================
====================
====================
=====
> dB-D-A-CH // deutschsprachige Usergroup für alles rund um dBase
> Jahreskonferenz 2007: 09. und 10. November 2007 in Marienheide
> Jahreskonferenz 2008: 14. und 15. November 2008 in Marienheide
> Jahreskonferenz 2009: (wird noch nicht verraten...)
>

Robert Bravery

2006-11-26, 7:12 pm

If there is no error How am I suppose to catch it.
THe problem is that the error is occuring but dbase is not catching it
Robert

"*Lysander*" <nobody@nowhere.com> wrote in message
news:5CemGDWEHHA.1420@news-server...
> Robert Bravery schrieb:
>
>
> That's expected behaviour to me, because the sqlexec() and/or
> database.executesql() are one-way commands.
>
> You could try to catch the error by wrapping the command into a
> try/catch/endtry.
>
> form.database1.begintrans()
> try
> form.database1.executeSQL("exec addclaimheader")
> form.database1.commit()
> catch (dbexception db)
> form.database1.rollback()
> messagebox(db.message)
> endtry
>
> That's how I usually can catch all db-errors from any server, though
> lately I did not try with MS-SQL.
>
>
> If that does NOT work, you should be using the storedProc-Objects from
> within dBase. They definitely allow full error-handling.
>
> André
> ====================
====================
====================
=====
> dB-D-A-CH // deutschsprachige Usergroup für alles rund um dBase
> Jahreskonferenz 2007: 09. und 10. November 2007 in Marienheide
> Jahreskonferenz 2008: 14. und 15. November 2008 in Marienheide
> Jahreskonferenz 2009: (wird noch nicht verraten...)
>

Ivar B. Jessen

2006-11-26, 7:12 pm

On Sun, 26 Nov 2006 19:08:03 +0200, in dbase.programming,
Subject: Re: raise rror in mssql triggers,
Message-ID: <Mq2R44XEHHA.1524@news-server>,
"Robert Bravery" <me@u.com> wrote:

>If there is no error How am I suppose to catch it.
>THe problem is that the error is occuring but dbase is not catching it

Try the code below my signature. I know it is a Firebird database and it is not a SP. The result I
get is displayed below and it illustrates that you can catch an error message even if there is no
error ;-)

0 0 0 Successful completion.

Record# EMP_NO FIRST_NAME LAST_NAME
1 2 Robert Nelson

Ivar B. Jessen

//-----
clear
close databases
open database "FDBemployee"
set database to "FDBemployee"
? sqlexec("select emp_no, first_name, last_name from employee where emp_no <= 2", ;
" :localDatabase:answe
r.dbf"), error(), message(), dberror(), dbmessage()
close databases
?
use answer
list
use
//-----

Sponsored Links