SQL connectors in Bonita Open Solution are very useful but they have a little drawback. Each time a connector is executed a new connection to the database is used.
Although you can resolve performance issues using database connection pooling, there is another alternative if you are dealing only with statements (excluding queries): the batch command SQL connector. This advanced connector allows us to execute multiple SQL statements with one connection. The only limitation is that those orders must not be “Select” queries as this connector. Let’s see how to use it :
My process is very simple. From an input I need to persist information in two different tables :
Let’s add this special connector to the automatic task “Insert DB” :
Let’s configure it. The first page is the classic connection parameter wizard inputs page. Then we need to write our script :
We can see two statements in the sql script. We can change the separator if we don’t like “;”. Please note to avoid to put comments between statements. ${…} are variables from my process.
We are done, let’s play it :
When submitting the form, the system starts the activity “InsertDb” and inserts all the information in two tables with only one JDBC connection !
Next step would be to create a connector that also accepts “Select” orders in a SQL scripts, don’t hesitate to share your contributions… Stay tuned.