result of a "MySQL select" query in a form list
Hi,
in bonita 5.x I can have the result of a mySQL select query in a form list as shown in http://www.youtube.com/watch?v=Xw1JWRnejeA
in bonita 6.x, I tried to do the same thing but It doesnt' work because I cant't transform the result of the query to a list.
How can I do?
Thank you.
I create a diagram that includes two processes illustrating two different solutions to initialize a drop down list (select widget).
First solution use a transient step data. The transient step data is a Text (java.lang.String) with option multiple active (becoming a java.util.List<java.lang.String>). The transient step data is initialized with a MySQL connector on enter of the step. Connector is configured to connect to the database and run a query that select one column in a database table. Content of the column will be saved in the transient step data. The data is used to defined the available values of the select widget. Main limitation of this solution is that in case of server shutdown, if the task is pending, drop down list will be empty. In fact connector on step enter is only run once and transient data are not saved. A second limitation related to the fact that connector is only run once is that for a pending step, if database is updated, change will not be reflected in the drop down list.
The second solution is a lot more technical. It use a form transient data initialized with a Groovy script. Form transient data are initialized each time you load the form and so will reflect after a refresh any database modification. The Groovy script that initialize the form transient data instantiate, configure and run the MySQL connector programmatically. This workaround also require to manually add the MySQL connector jar file (and dependencies) in process configuration.
Note that the second option is a lot easier to use with Bonita BPM subscription editions as form transient data can be initialized using connectors.
Please one more question about that.
the connector works if I use yours. But when I try to create my own connector, the last step is different from you. The connector does not recognize the city_name data field used in the query.
Please why?
Comments
city_name is a name of a column of one of the tables of my test database.
You should adapt the SQL query to your own scenario.
Maybe you can share with me your process diagram (the .bos file) and a dump of your database in order for me to do some test?
In your connector configuration, you should select "Graphical mode" instead of "Script mode". Select the option "n rows x 1 colulmn".
I recommend you reconfigured you connector from scratch as an issue might exist if you just update the existing configuration (I'll try to do more test).
Comments
Hi,
thank you very much. It works.
But why using transient data?
As data is already stored in MySQL database I assume that we don't want to duplicate them.
Using a non-transient data would lead the Bonita Engine to store the data in database associated with the Engine and so would lead to duplication. Still this is a valid solution that should also cover the use case.