Get External Database information for select or table widget

Q&A
1

Hello community, I would like to consult a topic. It is key for a Process system to interact with several sources of information and use them to generate the instances of the Process. Now for this we have the connectors, the basics of BD are already important to connect to different sources of information.

What is the simplest way to have the information of a table in mysql for example and to be able to select it in a combo or display it in a Table widget in the UIDesigner.

Is the mysql connector used by saving the recordset to a process variable (which is not advised)?

Would it be convenient to use the REST type connector to get the json object and use them easily in the UI widget?

I have seen several alternatives if I come to a solution or understanding.

If someone can save me a few tens of tests, I will thank them.

Thank you very much

2

Hola (English Version below)

Construir una lista de elementos para mostrarlos en un formulario/página en una variable de proceso no es una buena practica.

  • Cada instancia del proceso (caso) persistira en BD una copia de dicha lista
  • Es un dato de referencia, si se cambia despues de crear la variable. esta no se vera modificada...

MI recomendación es o bien utilizar un objeto BDM tipo "DatosReferenciales" con una estructura tipo Key / Value que puede ser recuperada con el API (../API/bdm/businessData/<fullClassName ... com.company.model.DatosReferenciales>?c=100&p=0&q=find )

o bien acceder a esa base de datos externa a traves de una rest api Extension como la compartida anteriormente (https://github.com/Bonitasoft-Community/rest-api-sql-data-source)

------
Build a List of elements to be displayed in an UI as a process variable is a Bad practice as:

  • Each process instance / case will keep a copy of that list/variable in DB
  • If this referencial data is changed in the source, the process variable will not be updated

My recomendation is or use a BDM object "ReferencialData" with key and value properties, that is accessible with regular REST APIs or use the previously shared REST API Extension (or equivalent) to access an external DB

3

hi, i have done anything similar.
I use an rest api extension that I call from the page and I show the information in a table

…/API/extension/Conexiones?queryId=getSolicitudes&datasourceName=Mysql

you can pass parameters if you need.

I saw the rest api info here:

https://github.com/Bonitasoft-Community/rest-api-sql-data-source

Regards

4

Thanks for your help. I think I did not explain very well but for now I will solve it through the BDM directly. That is, for the particular case, I create the object in the BDM to use in the UI. As I have the server in Mysql and the table is for complementary information of the main instance of the process, I will import the data I already have and, if necessary, create a form in php to load or update information in the BDM table.

What I am not clear is if the output of a default connector mysql or rest can be assigned to a process variable or local variable and then be accessed in the UI Designer? using / API / bpm / activityVariable /: activityId /: variableName or something like this.

Thank you

5

yes, I have to get in a proces variable the information that returns the sql of the connector, it has to be of type java.util.List, just like the script to collect the information. Then in the form you access the process variable like this:
./API/bpm/activityVariable/{{taskId}}/variableName

and you can show it in a combo or a table, putting as “Column Key” the name of fields of your sql and in the “Content” your variable.value (property table).

I hope I have helped you, I am spanish and my english is a bit basic.
if you need the script for the connector you tell me.

Regards,

6

Hello, my English is a bit basic too, I would like to ask you to ask some questions that I am missing to close a simple project. Tell me how I can connect you in Spanish. If you want, I follow the comments here.

Juan

7

Por favor, sé que esta publicación es muy antigua, pero tengo el problema de buscar datos de una tabla externa postgresql y mostrarlos en mi Diseñador de IU en un widget "Seleccionar". Usé un conector y regresé en una variable de proceso como java.util.list. Yo uso Bonita 7.10 Comunidad. Pero no puedo mostrar los datos seleccionados que uso en el conector para mostrar en el widget. Creé una variable API en el formulario que busca la variable que creé. ¿Podría enviarme el script para que el conector monte la lista en la variable, por favor?
Marco