Avoid database error messages

I created a reactive web application with a form. This form is based on a entity called "Employee" with the attributes "Name" and "CompanyId". "Company" is a static entity.

All attributes in the entity "Employee" are mandatory. When the user adds a new employee in the form without setting the CompanyId he gets the error message:


The INSERT statement conflicted with the FOREIGN KEY constraint "OSFRK_OSUSR_B6C_EMPLOYEE_OSUSR_B6C_COMPANY1_COMPANYID". The conflict occurred in database "OSDEV1", table "dbo.OSUSR_B6C_COMPANY1", column 'ID'.

The statement has been terminated.


I know why the user gets this message and I can add a client side validation to avoid this error. But what I do not want is that the user sees this error message even if there is no client side validation.

Is there a possibility to override the database error message with a user friendly text?

I mean I can create a server action to validate the input before sending the data to the database. In this example I have only two attributes to check but in a bigger application I could have a entity with 10 attributes or more.

The server action to check every attribute will be big, e.g.:

if(attribute1 = NullTextIdentifier()) -> return "attribute1 is missing"
if(Length(attribute1) > 50) -> return "attribute1 is too long"

if(attribute2 = NullTextIdentifier()) -> return "attribute2 is missing"
if(Length(attribute2) > 50) -> return "attribute2 is too long"

and so on.

Is there a better way to check the attributes on the server side and to avoid sending the database error message to the user?

Check the relationship between employee-compny in the diagram

Thorsten Schönbohm wrote:

I created a reactive web application with a form. This form is based on a entity called "Employee" with the attributes "Name" and "CompanyId". "Company" is a static entity.

All attributes in the entity "Employee" are mandatory. When the user adds a new employee in the form without setting the CompanyId he gets the error message:


The INSERT statement conflicted with the FOREIGN KEY constraint "OSFRK_OSUSR_B6C_EMPLOYEE_OSUSR_B6C_COMPANY1_COMPANYID". The conflict occurred in database "OSDEV1", table "dbo.OSUSR_B6C_COMPANY1", column 'ID'.

The statement has been terminated.


I know why the user gets this message and I can add a client side validation to avoid this error. But what I do not want is that the user sees this error message even if there is no client side validation.

Is there a possibility to override the database error message with a user friendly text?

I mean I can create a server action to validate the input before sending the data to the database. In this example I have only two attributes to check but in a bigger application I could have a entity with 10 attributes or more.

The server action to check every attribute will be big, e.g.:

if(attribute1 = NullTextIdentifier()) -> return "attribute1 is missing"
if(Length(attribute1) > 50) -> return "attribute1 is too long"

if(attribute2 = NullTextIdentifier()) -> return "attribute2 is missing"
if(Length(attribute2) > 50) -> return "attribute2 is too long"

and so on.

Is there a better way to check the attributes on the server side and to avoid sending the database error message to the user?

Hi,


You have option to show customize messages instead of the actual exceptions.for this, you have to call OnException properly.

Expectation handling documentation

https://success.outsystems.com/Documentation/11/Developing_an_Application/Implement_Application_Logic/Handle_Exceptions


Hi,

In your action you can define an Database Exception and, than a feed back message with the message that you want:



Hope this can help.


Best regards,

Ricardo M Pereira

With the database feedback message I can show the user a better error message.

But in this case I can't distinguish between two different database error.


If the attribute1 is missing I would like to send the feedback message "Attribute1 is missing". And if the attribute2 is missing I would like to send the feedback message "attribute2 is missing".


The database feedback message seems to be one message text for all database exceptions in my server action, right?

Hi Thorsten,


Ok, so you understand about client side validations, and about server side validations in a wrapper server action before actually updating the database.  

I think you are looking for a cheap way to maybe not do all these validations, because there are too many attributes in an entity and maybe the updates are done in other ways than from a client side form where you can let the automatic validations do the heavy lifting for you.

So you were thinking about just going ahead and attempting a create/update and somehow use the database exception as the source of information about what exactly is missing, is that what your were after ? 

Your problem is that there is only one generic database exception, and the distinctions on what exactly is wrong is only inside the message in the exception.  And this message is too ugly and technical to show to your users.

So, unless you want to unleash some funky regex on the database error message, I'm afraid there's no such thing as a free lunch.

If you want to have nice messages matching the different reasons why something fails on the database, you'll have to do the validations in some form or other.


Dorine


Hey Dorine,

thank you for your detailed information. I thought maybe there is a better solution instead of creating a validation action to check every attribut, but it isn't.

Hi,

please check this post, the same problem.

Cheers
Miguel

Hi Thorsten,


I was triggered by your question to see if I could come up with an idea to make this less work and/or produce better error messages.  I have added a component in the forge that might be a possible way to deal with this.  

I do not promote taking a short cut with validations, I think the design of your UI should make it hard for the user to enter bad data, and you should make use of built-in and custom validations both on client as on server side to protect the quality of your data.

But adding this component after server validations and before the actual create/update on the database, can be an extra tool to fill gaps of forgotton validations and get better messages.

Maybe something for you to explore, it's this component.  

I'm still working on it, and will add sample app on how to use it.

Good luck,

Dorine