How to load and show a list of datas from the Data base in a Select (Item)? with a data base connector or with a Groovy script

Hi everybody, any help will be appreciated, please.
I have been reading may post in this forum and also the Bonita Soft Documentation, but I don’t get anything clear. I don’t know to make it works.

I only want to show in a Select Item this query: “SELECT * FROM tipo_reporte;”

I have a list of Records type that I have to show in a Select item.
This data are in a data base mysql, I have creates a data base connector, but I don’t know how to map it to the select item that I show in UI Designer. I’m sure the Data base is ok, and the connection, because I have inserted many rows in this tables.

I also has tried it in another way, I created a Groovy script and try to connect and list the data but I can’t, my code is in the end of the post.
I always get an error saying the result set is closed when I try to get data from it or printing it into the bonita log.
Error: GRAVE: Message Localized: Operation not allowed after ResultSet closed

Thanks!!

This is the old version ofthe code, the new is in the comment.
/**************** SOURCE CODE *****************************************/

import static java.lang.System.out;
import groovy.sql.Sql;
import groovy.sql.ResultSetOutParameter;
import com.mysql.jdbc.Driver;
import static org.codehaus.groovy.runtime.DefaultGroovyMethods.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import groovy.json.JsonBuilder;
import com.mysql.jdbc.JDBC4ResultSet;

org.slf4j.Logger loggerJava = org.slf4j.LoggerFactory.getLogger(“org.bonitasoft.groovy.script.incioScript”);
try {
loggerJava.debug(“Entra list select item”);
String consulta = “SELECT * FROM tipo_reporte;;”
SMSOpConfig smsOpConfig = new SMSOpConfig();
SMSOpBase.connectSMS(smsOpConfig.getDBUrl(), smsOpConfig.getDBUser(), smsOpConfig.getDBPass());
loggerJava.info(“Execute Query select item list”);
JDBC4ResultSet resultset = SMSOpBase.getConn().executeQuery(consulta);
loggerJava.info("ResultSet: "+resultset);
List milista = new ArrayList();
def builder = new JsonBuilder();
loggerJava.info("objectJava: "+ objectJava.toString());
SMSOpBase.closeConnection();
loggerJava.info("ResultSet–despues while: ")
return milista;
} catch (e2) {
loggerJava.error(“ERROR: listando select Item desde BBDDen BBDD*********************************”);
loggerJava.error("Message Localized: "+ e2.getLocalizedMessage().toString());
loggerJava.error("Message: "+ e2.getMessage().toString());
loggerJava.error("GetStacktrace.ToString: "+ e2.getStackTrace().toString());
}

Now I have done what says in this link of this forum: http://community.bonitasoft.com/questions-and-answers/get-information-sql-data-base-and-insert-it-bonita-h2-database-automatically

I’m printing in bonita log the data from the data base, but I need to put it in a variable and show/map it in a Select in the form.

I don’t know wich scope (global, local, proces) of variable use, neither wich type(List , Objsct, List).

I can’t make it work to put the values in a variable and show it in a Select item in the Form.

Thanks.

I have solved it adding in the UI Designer dot value. listaTipoReporte.value.
An now I can see the values
http://i67.tinypic.com/2vl6rgm.png

Now I have done what says in this link of this forum: http://community.bonitasoft.com/questions-and-answers/get-information-sql-data-base-and-insert-it-bonita-h2-database-automatically

I’m printing in bonita log the data from the data base, but I need to put it in a variable and show/map it in a Select in the form.

I don’t know wich scope (global, local, proces) of variable use, neither wich type(List , Objsct, List).

I can’t make it work to put the values in a variable and show it in a Select item in the Form.

Thanks.

I have change the code, now I have made a Data base conector, and put the next code in It:

import static java.lang.System.out; import groovy.sql.Sql; import groovy.sql.ResultSetOutParameter; import com.mysql.jdbc.Driver; import static org.codehaus.groovy.runtime.DefaultGroovyMethods.*; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import groovy.json.JsonBuilder; import com.mysql.jdbc.JDBC4ResultSet;

org.slf4j.Logger loggerJava = org.slf4j.LoggerFactory.getLogger(“org.bonitasoft.groovy.script.incioScript”);

try {

loggerJava.debug(“Entra list select item connect 15_43”);

 loggerJava.info("Execute Query select item list  connect 15_43");

 loggerJava.info("ResultSet: ");//+resultset);
 
 List	 milista = new ArrayList();
 
 def builder = new JsonBuilder();

 while (resultset.next()) {
 
	  def lin1 = builder {
		id resultset.getObject(1).toString()
		lavel resultset.getObject(2).toString()
		key resultset.getObject(4).toString()
	  }
	  milista.add(lin1);
	  loggerJava.info("linea1:"+lin1); //I print the data from the data base
 }
 loggerJava.info("ResultSet--despues while: connect 15_43 ")
 loggerJava.info("milista:"+milista);
 return milista;

	

} catch (e2) {
	loggerJava.error("ERROR: listando select Item desde BBDDen BBDD  connect 15_43*********************************");
	// loggerJava.error("Cause: "+		e2.getCause());
	loggerJava.error("Message Localized: "+		e2.getLocalizedMessage().toString());
	loggerJava.error("Message: "+		e2.getMessage().toString());
	loggerJava.error("GetStacktrace.ToString: "+		e2.getStackTrace().toString());
}

I only need to return this data in a correct format to show it in a Select Item component into a form.

This is what I got in the Log:

2016-06-30 13:15:13.069 +0100 org.bonitasoft.groovy.script.incioScript org.slf4j.Logger$info$0 call INFORMACIÓN: milista:[[id:1, label:Urgencia, key:urg], [id:2, label:Media, key:med]]

I’m getting this value in a text field in the form:

Tipo Reporte List: [ { "label": "Urgencia", "key": "urg" }, { "label": "Media", "key": "med" } ] with this code: Tipo Reporte List: {{listaTipoReporte.value| json}}

but in the Select I get nothing, it’s empty.

I am mapping the select with this: listaTipoReporte.value, where listaTipoReporte is a external API, form variable, mapped with : “”…/API/bpm/activityVariable/{{taskId}}/listTipoRepo""
http://i66.tinypic.com/2rr4aps.png
And the process variable: listTipoRepo is a “java.util.Collection”:
http://i68.tinypic.com/28qyb1y.png

In the select item I have the Value mapped with a bussines variable in this way: “reportes.tipoReporte”.

I dont know wich is the problem, if someone could help me I’ll appreciate it a lot.
I think may be the process variable listTipoRepo, instead of being a “java.util.Collection”, have to be a String with the json .
http://i66.tinypic.com/keitjn.jpg

for your code can you wrap it with

<~code~>
<~/code~>

and remove (delete) the ~

it will make it easier to read.

Please also make sure you COMMENT to your question and not submit an Answer…as it’s not an answer.

thanks

Thank you very much, I have just done it.

No you haven’t…well not quite,

You should have also removed the ~ from the <~code~> <~/code~> braces. It should be open bracket code close bracket no spaces etc…

regards

Ok, I didn’t understood it.
may be now?

hei hectorta,
I had the same problem.if you access mysql from phpmyadmin? Can you divide the problem solving? because I have tried to follow the tutorial on document or youtube ,but it still does not work.

thanks

One of the keys is that the select Widget need a JSON, in my case I add “.value” to the list that I was mapping with select because it uas a java collection and the select needs Strign json.

[ { “label”: “Urgencia”, “key”: “urg” }, { “label”: “Media”, “key”: “med” } ]

You can use the developer console to see is there is any error, the navigator print errors in the log

Also be sure that you push the fx(function) button when you write and introduce the text: “list.value” in the filed “Available values”, if not Bonita Won’t understand that you are writing a variable, it will think you are using a literal value.