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

Submitted by hectorta on Wed, 06/29/2016 - 14:37

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());
}

list data base connector mysql

Log in or register to post comments
6597 reads

Comments

Submitted by hectorta on Thu, 06/30/2016 - 09:27

Now I have done what says in this link of this forum: http://community.bonitasoft.com/questions-and-answers/get-information-sq...

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.

Permalink

Submitted by hectorta on Thu, 06/30/2016 - 09:46

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

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.

Permalink

Submitted by hectorta on Thu, 06/30/2016 - 15:20

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]]

Permalink

Submitted by hectorta on Thu, 06/30/2016 - 18:59

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""

And the process variable: listTipoRepo is a "java.util.Collection":

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 .

Permalink

2 answers

Submitted by hectorta on Fri, 07/01/2016 - 10:46

This one is the BEST answer!

I have solved it adding in the UI Designer dot value. listaTipoReporte.value.
An now I can see the values

Log in or register to post comments
594 reads

Submitted by hectorta on Wed, 06/29/2016 - 17:12

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.

Log in or register to post comments
801 reads

Comments

Submitted by Sean McP on Wed, 06/29/2016 - 19:13

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

Permalink

Submitted by hectorta on Thu, 06/30/2016 - 09:29

Thank you very much, I have just done it.

Permalink

Submitted by Sean McP on Thu, 06/30/2016 - 09:34

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

Permalink

Submitted by hectorta on Thu, 06/30/2016 - 09:47

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

Permalink

Submitted by nidanda on Sun, 07/03/2016 - 17:01

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

Permalink

Submitted by hectorta on Mon, 07/04/2016 - 08:45

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

Permalink

Submitted by hectorta on Mon, 07/04/2016 - 12:12

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.

Permalink