77
Views
10
Comments
How to catch exception in advanced SQL
Question

Hi ALL!


My Outsystems version is 10.0.910.0


My Problem is when I use advanced SQL to delete one record, it show me the message in error page.


"Error executing query"


That's all it shows, nothing else. But I want the detail message.


And I open the error log, I found it has two messages


  1. 2020-06-01 11:09:44 169 Error executing query
  2. 2020-06-01 11:10:00 169 Error executing query..... XXX..... ORA-02292: integrity constraint (xxxx) violated - child record found


So it catches the useful exception internal.


In our project, we want to catch the useful exception, and then do our logic, and show our own message instead.


Somebody has any idea?


Thanks!!!


mvp_badge
MVP
Rank: #91

Hi Bob,

Within your Action flow were you defined the Advance SQL Node, I think you missed defining the Exception Handling Flow. By defining the Exception Handler Flow you can showcase the end-user with custom or User-friendly Error message.

Hope this helps you!

Regards

Benjith Sam

mvp_badge
MVP
Rank: #69

Hello Bob.

You can set more specific error handling. Database exception for example.

And then you use the output DatabaseException.ExceptionMessage



Staff
Rank: #267

Hi Bob,

Before going to the exception handling part you should first check the error and understand if your code should be improved in order for this error to not happen at all. TechOnTheNet is a cool site that helps decipher errors from different database stacks, here's the page for the error you had: https://www.techonthenet.com/oracle/errors/ora02292.php

As you can see the error is because the user is trying to delete a record that has an Foreign Key to a record that still exists. Probably the delete action should not be available when that's the case.


Regarding exception handling, I'd advise going through the online lessons in this course: https://www.outsystems.com/learn/lesson/1829/logic-and-exception-handling

Rank: #969

Hi Bob,

Could you please go through the below thread if that helps your query.

https://www.outsystems.com/forums/discussion/39240/getting-error-from-service-center-to-display-at-screen/

mvp_badge
MVP
Rank: #2

Hi Bob,

There's no way to get the actual SQL error in this case. But like Cristiana wrote, this error shouldn't happen in the first place. It would be very bad user experience if a normal user would be shown a "ORA-02292: integrity constraint (xxxx) violated - child record found" error on the screen. You should prevent errors like this from happening at all, and explain the user why a certain action is not possible.

That said, you should be questioning why you want to use Advanced SQL for deleting a record, instead of using a DeleteXxx Entity Action.

Rank: #504

Hi,

In this case, it is foreign key record linked to. And, the only thing is that you need to 

  • check all the children of it to be deleted first: instead of deleted, please try some logic to add new attribute to set active true / false. Because every data still has its own value for analytics
  • or just set all the foreign key to null: by the second way, dummy data which is not linked to anything will be produced

Hope this help