SQL Error Handler

[Former Member]

What's the most efficient way to handle the SQL errors below:

insert into TABLE...

update TABLE...
insert into TABLE...

update TABLE...

Can I somehow use try catch here? Or any other way that I catch the errors in one block?

Thank you.

[Former Member · Accepted solution]

Thank you all. Sorry for the delay in my response. Since the first time I used try-catch block I had been wondering if I can apply something similar to PLSQL's exception handler:

begin

     select COL...

          from TABLE1

          where CONDITION;

     insert into TABLE2...

          where CONDITION;

     select COL...

          from TABLE3

          where CONDITION;

     exception

          when no_data_found then

               :error_msg := 'Not found'; pause;

end;

[Former Member · Accepted solution]

Ronald Valdehueza wrote:
Thank you all. Sorry for the delay in my response. Since the first time I used try-catch block I had been wondering if I can apply something similar to PLSQL's exception handler:

I have amended your suggestion slightly

TRY

     select COL...

          from TABLE1

          where CONDITION;

     insert into TABLE2...

          where CONDITION;

     IF SQLCA.SQLCode < 0 THEN

                    lex_database = CREATE exception

                    lex_database.setmessage( SQLCA.SQLErrText)

                    THROW lex_database

     END IF

     select COL...

          from TABLE3

          where CONDITION;

      IF SQLCA.SQLCode = 100 THEN

                    lex_database = CREATE exception

                    lex_database.setmessage( 'Not Found')

                    THROW lex_database

     END IF

CATCH  ( exception lex )

     MessageBox ( 'Database Exception' , lex.GetMessage())

END TRY

HTH

Lars

[Former Member · Accepted solution]

Thank you Lars but why put the 2nd select after the "sqlcode < 0 block"?

It seems this block will only capture the "insert..." error and not the 1st select?

With PL/SQL all the SQL errors can be caught with one exception sub-block.

Thus in my example:

begin

     select COL...

          from TABLE1

          where CONDITION;

      insert into TABLE2...

          where CONDITION;

     select COL...

          from TABLE3

          where CONDITION;

     exception

          when no_data_found then

               :error_msg := 'Not found'; pause;

end;

The single exception sub-block there handles the NOT FOUND code for "select .. from TABLE1...", "insert .. into TABLE2...", "select .. from TABLE3..."

[Former Member · Accepted solution]

This block:

select COL...

          from TABLE3

          where CONDITION;

      IF SQLCA.SQLCode = 100 THEN

                    lex_database = CREATE exception

                    lex_database.setmessage( 'Not Found')

                    THROW lex_database

     END IF

was meant to illustrate that you can choose to raise an error on a not found.

To use exceptions in conjunction with embedded sql

• You surround your embedded sql with try catches allowing you to handle all exceptions in the same place
• After each (and every) SQL statement you must check SQLCA,SQLCode to find out if something exceptional occurred
• If so, you raise the exception by creating an exception object, setting the message and then throw the exception
• caught by the CATCH, where you can show the user

[Former Member · Accepted solution]

I was hoping this can be somehow avoided by using a catch block (as with PL/SQL): "After each (and every) SQL statement you must check SQLCA,SQLCode to find out if something exceptional occurred."

Having used PL/SQL for some time, application of PB's try-catch with SQL/DML seems to defeat the purpose of a try-catch block.

[Former Member · Accepted solution]

You can throw the exception in an extended transaction object with a try catch around it and cut the checking down to one line per SQL Statement. (Threw in a ROLLBACK for at no extra cost)

Test code for illustration only:

n_trans myTrans

myTrans = CREATE n_trans

myTrans.DBParm = SQLCA.dbparm

myTrans.DBMS = 'ODBC'

CONNECT USING myTrans;

TRY

          INSERT code VALUES (1) using myTrans;

          myTrans.EVENT Check4Error()

          UPDATE glob set vary = vary + 7 using myTrans;

          myTrans.EVENT Check4Error()

CATCH ( exception lex )

          MessageBox ( 'Exception', lex.GetMessage() )

END TRY

The exported transaction object:

forward

global type n_trans from transaction

end type

end forward

global type n_trans from transaction

event check4error ( ) throws exception

end type

global n_trans n_trans

type variables

string isLastError

end variables

event Check4Error();

exception lex

IF isLastError <> '' THEN

          lex = CREATE exception

          lex.SetMessage ( isLastError )

          isLastError = ''

          ROLLBACK USING this;

          throw lex

END IF

end event

on n_trans.create

call super::create

TriggerEvent( this, "constructor" )

end on

on n_trans.destroy

TriggerEvent( this, "destructor" )

call super::destroy

end on

event dberror;isLastError = SQLerrText

end event

PS: Feel free to like or mark any of the many answers helpful ( even if you like PL/SQL better 🙂

[Former Member · Accepted solution]

O wow, thank you for the elaborate solution, Lars. Currently im already doing something like

INSERT code VALUES (1) using myTrans;

if wf_Err_Handler(myTrans) < 0 then RETURN -1

UPDATE glob set vary = vary + 7 using myTrans;

if wf_Err_Handler(myTrans) < 0 then RETURN -1

without the try-catch which makes my code less cluttered, but I would've preferred an 'elegant' catch-all block, something like

INSERT code VALUES (1) using myTrans;

UPDATE glob set vary = vary + 7 using myTrans;

          catch (exception lex)

               wf_Catch_All_Err_Handler(myTrans) < 0 then RETURN -1

but I guess it all boils down to what PB sees try-catch should be used for.

[arnd_schmidt · Accepted solution]

Ronald,

you can throw an exception in the dberror event of the transaction.

hth

Arnd

[Former Member · Accepted solution]

How about if we do this?

        ...

event dbnotification;

exception lex

IF SQLerrText <> '' THEN

          lex = CREATE exception

          lex.SetMessage ( SQLerrText)

          ROLLBACK USING this;

          throw lex

END IF

end event

        on n_trans.create

call super::create

TriggerEvent( this, "constructor" )

end on

on n_trans.destroy

TriggerEvent( this, "destructor" )

call super::destroy

end on

So we can do this

TRY

                    INSERT code VALUES (1) using myTrans;

                    UPDATE glob set vary = vary + 7 using myTrans;

CATCH ( exception lex )

          MessageBox ( 'Exception', lex.GetMessage() )

END TRY

[Former Member · Accepted solution]

No, it won't work.

1) DbNotification is for other purposes (server crash, mirroring)

2) You can't throw an exception in a native event (can't modify the "throws" declaration)

You could have a function that takes a SQL request as a string argument and throws an exception if an error occurs.

So you can do this :

TRY

     myTrans.of_Exec("INSERT code VALUES (1);UPDATE glob set vary = vary + 7")

CATCH (n_transaction_exception lex)

     MessageBox("Exception", lex.GetMessage())

END TRY

[Former Member · Accepted solution]

The dberror event didn't let me throw an exception as I couldn't declare it in the definition of a predefined event, BUT I cheated:

DBERROR event of transaction descendant:

exception lex

try

          IF this.SQLCode <> 0 THEN

                    lex = CREATE exception

                    lex . SetMessage (  SQLerrtext )

                    throw lex

          END IF

catch ( exception lex2 )

          lex2.SetMessage ( lex.GetMessage () )

          throw lex2

end try

BUT, you end up having TRY catch around every embedded sql block in your application

[Former Member]

Thank you so much everybody

[Former Member]

Hi Ronald,

Can you close the question,if you think it has been well covered.

Thanks

Guillaume

[Former Member]

Hi Ron;

  FWIW: Always check the value in SQLCA.SQLCode after every DML statement execution. If the RC is not zero - then you need to check SQLCA.DBCode and SQLCA.ErrText for the reason(s) for the command failure.

   As Lars correctly points out as well - the DataWindow Control and DataStore have a DBError event that you should always code for to trap DML problems.

HTH

Regards ... Chris

[Former Member]

Hi Ronald,

It's not clear what errors you're talking about.

If you're using a custom transaction object, you can use the dberror event.

Guillaume

[Former Member]

DBerror event with transaction object? Im still using PB11 but DBerror is only with datawindows and datastore. From Help: DBerror occurs when a database error occurs in the DataWindow or DataStore.

Transaction object has only this

Constructor, DBNotification, Destructor

[Former Member]

Right, dberror event in transaction object.

It has been added in pb 11.5 :

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc37781_1150/html/psref/CAIECBBATO.htm

[Former Member]

You can manually throw and exception in your update block and catch it appropriately

exception lex_database

UPDATE .....

IF SQLCA.SQLCode < 0 THEN

                    lex_database = CREATE exception

                    lex_database.setmessage( SQLCA.SQLErrText)

                    THROW lex_database

END IF