User friendly error messages for "Database connector" actions in process flows

  • 22 October 2020
  • 1 reply
  • 99 views

Userlevel 4
Badge +4

Goal

Process flows are great in flexibility. But when you don't add your own error handling, the user might not notice when something went wrong and strange issues might be reported. 

 

Solution

Automatically generate error handling for specific process actions.

 

Database connector

First, you need a process variable to store the error code. Then you need to link this error code to the output of the process action. And last, if not already done, use processes in this process action. This can all be set automatically in a dynamic procedure:

-- Add a process variable for every process flow with a "write file" action
insert into process_variable
(
project_id,
project_vrs_id,
process_flow_id,
process_variable_id,
dom_id,
insert_user,
insert_date_time,
update_user,
update_date_time
)
select
@project_id,
@project_vrs_id,
f.process_flow_id,
'db_connector_status_code',
'status_code', -- Add this domain or replace it with another
dbo.tsf_user(),
sysdatetime(),
dbo.tsf_user(),
sysdatetime()
from process_flow f
where f.project_id = @project_id
and f.project_vrs_id = @project_vrs_id
and exists (select 1
from process_action a
where a.project_id = f.project_id
and a.project_vrs_id = f.project_vrs_id
and a.process_flow_id = f.process_flow_id
and a.process_action_type = 590 -- Database connector
)
and not exists (select 1
from process_variable v
where v.project_id = f.project_id
and v.project_vrs_id = f.project_vrs_id
and v.process_flow_id = f.process_flow_id
and v.process_variable_id = 'db_connector_status_code'
)

-- Link the parameter to the action output so it is filled with the error code in the process flow
insert into process_action_fixed_output_parmtr
(
project_id,
project_vrs_id,
process_flow_id,
process_action_id,
output_parmtr_id,
process_variable_id,
insert_user,
insert_date_time,
update_user,
update_date_time
)
select
a.project_id,
a.project_vrs_id,
a.process_flow_id,
a.process_action_id,
'status_code',
'db_connector_status_code',
dbo.tsf_user(),
sysdatetime(),
dbo.tsf_user(),
sysdatetime()
from process_action a
where a.project_id = @project_id
and a.project_vrs_id = @project_vrs_id
and a.process_action_type = 590 -- Database connector
and not exists (select 1
from process_action_fixed_output_parmtr p
where p.project_id = a.project_id
and p.project_vrs_id = a.project_vrs_id
and p.process_flow_id = a.process_flow_id
and p.process_action_id = a.process_action_id
and p.output_parmtr_id = 'status_code'
)

-- Enable the process procedure
update a
set use_processes = 1
from process_action a
where a.project_id = @project_id
and a.project_vrs_id = @project_vrs_id
and a.process_action_type = 590 -- Database connector
and a.use_processes = 0

 

With a dynamic CP, it is possible to automatically add this error logging to ever process flow.

insert into prog_object_item
(
project_id,
project_vrs_id,
prog_object_id,
prog_object_item_id,
order_no,
control_proc_id,
template_id
)
select @project_id,
@project_vrs_id,
'prc_' + process_flow_id + '_' + process_action_id,
@control_proc_id,
10,
@control_proc_id,
@control_proc_id
from process_action p
where p.project_id = @project_id
and p.project_vrs_id = @project_vrs_id
and p.process_action_type = 590 -- Database connector

I've used every error code from the documentation and given it a user-friendly message. Feel free to modify the text for your own needs.

if @db_connector_status_code <> 0 --error
begin
declare @msg_text varchar(500)

select @msg_text = '<text>'
+ case @db_connector_status_code
when -2 then 'The database connector failed because there is no connection string.'
when -3 then 'The database connector failed because there is no command text.'
when -4 then 'The database connector failed because of an invalid parameter Json structure.'
when -5 then 'The database connector failed because JSON parameters could not be combined with mapped process variable parameters.'
when -6 then 'The database connector failed because an input or output parameter was not found as mapped process variable parameter.'
when -7 then 'The database connector failed because a mapped process variable parameter was not set as input or output.'
when -8 then 'The database connector failed because a mapped process variable parameter was not found as process variable.'
when -9 then 'The database connector failed because the command delimiter regex could be not parsed.'
else 'The database connector failed. Error: ' + coalesce(@sql_error_msg, 'unknown') + '. Status code: ' + coalesce(cast(@db_connector_status_code as varchar(10)), 'unknown') + '.'
end
+ '</text>'

exec tsf_send_message 'default' , @msg_text, 1
end

For this template, I used ‘default’, but I would recommend changing it to a newly created message instead. This is for a product that is one-language, if you want multi-language you could create messages for every error code like this:

if @db_connector_status_code <> 0 --error
begin
declare @msg_id tsf_msg_id

select @msg_id = case @db_connector_status_code
when -1 then 'msg_db_connector_unknown_error'
when -2 then 'msg_db_connector_empty_connection_string'
when -3 then 'msg_db_connector_no_commend_text'
when -4 then 'msg_db_connector_invalid_parameter_json_structur'
when -5 then 'msg_db_connector_cannot_combine_json_parameters_with_mapped_process_variables'
when -6 then 'msg_db_connector_input_or_output_not_found'
when -7 then 'msg_db_connector_process_variable_not_set_as_input_or_output'
when -8 then 'msg_db_connector_process_variable_not_found'
when -9 then 'msg_db_connector_could_not_parse_regex'
end

exec tsf_send_message @msg_id, null, 1
end

 


1 reply

Userlevel 5
Badge +9

This is very helpful indeed, for the HTTP connector I solved this by creating a domain with the translations for the error statuses and showing the result in a separate task on error. since there are two status messages: the Status code and the HTTP status code. 

Reply