Connector vs Call process
Hello,
I have a question about which is the best methodology concerning re-usability of "code".
In order to interact with the databases (MS SQL Server) we mainly use stored procedures. So we need a way to make these calls.
Creating every time a service task calling the db connector, writing from scratch the query, is time-consuming and error-prone.
So I think we should create either:
* a new Connector implementation, called in a service task of diagrams or
* a Process to be called as a Call Process inside diagrams.
The approach should be able to take as input stored procedure's "name", "parameters" (name, datatype and value), "target db alias", "timeout" and return as output the result dataset, output parameters values (if any) or exception message. It should also be able to recover after failure (e.g. retry after a while if timeout occurs), log execution, maybe notify the administrator.
Which is the best approach between in terms of supported features, diagram appearance, ease of implementation and call?
Thank you in advance,
Stelios
We use your second method, it's much easier to implement and can be updated quickly.
We send a single parameter which is a list (map) of elements (other parameters such as stored procedure's "name", "parameters" (name, datatype and value), "target db alias", "timeout" etc.) which means we don't have to change every process when we change the sub-process.
We also send back only two data fields, return as boolean (success or failure) and a List of Object for the data, we then manipulate it in the process itself.
Hope this helps,
regards
Seán
PS: If this reply answers your question, please mark as resolved.
Comments
Thank you for your answer Sean.
The single parameter you use is a variable or a contract input?
Regards,
Stelios
As it's a subprocess We simply use variables.
In terms of performance, could there be any significant issues? We are talking for thousands of db calls per day.
Outside of scope I'm afraid...we've done our work in this field and it works for us. Capacity planning is a bugger and depends on many many factors.
Thousands of calls per day is nothing, thousands of calls per minute or second though is a different matter. There are 86,400 seconds in a day...one call per second would be fine.
You really have to test this yourself and determine what is possible for your environment.
Lets see now,
Performance of the software - how well is it written
What about the servers? Big / Small? How much RAM, what CPU's
Network?
Disks, SSD or SATA?
RAID or not?
Which Database? Postgresql/mySQL/SQLServer? each has their flavor of performance.
Indexes?
etc...
etc...
etc...
regards
Do you use stored procedure calls?
Because I get no resultSet back by executing "exec sp_test".
I think the problem is here (in line 174, if statement does not start with "select" then a null resultSet is returned)
If this is the case, I am afraid I will have to implement both approaches...