Fill Table widget with values from DB

Submitted by christopherf on Thu, 01/16/2014 - 07:39
1
+1
-1

Hi all

I am a newbie at Bonita, struggling with displaying values, retrieved from an MS SQL DB, in a table. I am using Bonita BPM Community v6.2.0 with MS SQL 2012, on Windows 8, with JRE 6. I am running the JBOSS bundle. I am retrieving the data using a datasource connector, so the pretty new connector interface where I can choose the output type (nRows by nColumns) is not available.

Creating and filling a list manually, with Groovy code, I am able to display the values in a table widget, so the setup works. My problem is taking a resultset with the values, and writing those values into a variable of type List.

Using the folowing code

List result = new ArrayList();
while (resultset.next()) {
String stringValue = resultset.getString(1)
result.add(stringValue);
}
return result

I am able to display a single column extracted from the resultset, but I am struggling with reading a complete row and adding it to the list, which I will then display in the table widget.

Any assistance would be greatly appreciated.

Kind regards,

Christopher

Table
list
resultset
8 answers

Submitted by haris.subasic on Thu, 01/16/2014 - 11:25
1
+7
-1
This one is the BEST answer!

Hello,

you should transform your ResultSet into a List<List<Object>>

Something like:

final List<List<Object>> resultTable = new ArrayList<List<Object>>();<br />
        int maxColumn = rSet.getMetaData().getColumnCount()+1;<br />
                        while(rSet.next()){<br />
                                final List<Object> row = new ArrayList<Object>();<br />
                                for(int colIndex = 1; colIndex < maxColumn ; colIndex++){<br />
                                        row.add(rSet.getObject(colIndex));<br />
                                }<br />
                                resultTable.add(Collections.unmodifiableList(row));

                }

Hope this helps

Comments

Submitted by kppatel on Fri, 06/13/2014 - 12:03

Thank you sooo much it really helps.

Permalink
Submitted by cdominguez.bravo on Tue, 06/17/2014 - 19:33

Thank you, this solved my problem... !

Permalink
Submitted by cdominguez.bravo on Tue, 06/17/2014 - 19:34

Thank you, this solved my problem... !

Permalink
Submitted by hectorta on Tue, 07/12/2016 - 17:44

It doesn't works for me I read the datas from a MySQl DAta base, and I get the next two errors:
In Chrome console:

Error: [$parse:syntax] http://errors.angularjs.org/1.3.18/$parse/syntax?p0=panel&p1=is%20an%20unexpected%20token&p2=7&p3=panel%20panel-default&p4=panel-default
    at Error (native)
    at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:6:417
    at lb.throwError (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:192:113)
    at lb.parse (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:190:406)
    at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:110:366
    at new n (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:10017)
    at Object.create (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:8209)
    at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:12401
    at Array.forEach (native)
    at Object.create (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:12373) <div pb-property-values="59c870ab-b357-45f3-b176-25f60338efb0" class="ng-scope">

vendor.min.js:102 SyntaxError: Unexpected end of JSON input
    at Object.parse (native)
    at tc (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:14:245)
    at bc (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:77:7)
    at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:77:369
    at q (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:7:322)
    at dd (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:77:351)
    at c (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:78:495)
    at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:112:182
    at m.$eval (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:126:250)
    at m.$digest (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:123:365)(anonymous function) @ vendor.min.js:102(anonymous function) @ vendor.min.js:76(anonymous function) @ vendor.min.js:112m.$eval @ vendor.min.js:126m.$digest @ vendor.min.js:123m.$apply @ vendor.min.js:127l @ vendor.min.js:81w @ vendor.min.js:85I.onload @ vendor.min.js:86

And in the log of Bonita:

GRAVE: ERROR: listando reportes*
2016-07-12 16:36:37.259 +0100 org.bonitasoft.groovy.script.leeReportes org.slf4j.Logger$error$1 call
GRAVE: Message Localized: No signature of method: java.util.ArrayList.getObject() is applicable for argument types: (java.lang.Integer) values: [1]
Possible solutions: getAt(int), getAt(int)

2016-07-12 16:36:43.323 +0100 org.restlet.Component.Server org.restlet.engine.adapter.ServerAdapter commit
GRAVE: An exception occured writing the response entity
com.fasterxml.jackson.databind.JsonMappingException: [no message for java.lang.NullPointerException]
        at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider.serializeValue(DefaultSerializerProvider.java:261)
        at com.fasterxml.jackson.databind.ObjectWriter._configAndWriteValue(ObjectWriter.java:802)
        at com.fasterxml.jackson.databind.ObjectWriter.writeValue(ObjectWriter.java:642)
Caused by: java.lang.NullPointerException
        at org.bonitasoft.web.rest.server.utils.JacksonSerializerHelper.writeValueAndStringValue(JacksonSerializerHelper.java:48)
        at org.bonitasoft.web.rest.server.utils.DataInstanceSerializer.serialize(DataInstanceSerializer.java:43)
        at org.bonitasoft.web.rest.server.utils.DataInstanceSerializer.serialize(DataInstanceSerializer.java:27)
        at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider.serializeValue(DefaultSerializerProvider.java:250)
        ... 61 more

2016-07-12 16:36:43.325 +0100 org.restlet.Component.Server org.restlet.engine.adapter.HttpServerHelper handle
ADVERTENCIA: Error while handling an HTTP server call
java.lang.IllegalStateException: No puedo llamar a sendError() tras llevar a cabo la respuesta

Permalink

Submitted by christopherf on Thu, 01/16/2014 - 11:46
1
+2
-1

Haris, thank you very much

I tried your solution, and it worked first time. I am going to try and make this into a user script to call when ever I need to display a table, instead of having to have the code in each connector.

Thank you once again.

Comments

Submitted by bjnobre on Wed, 01/22/2014 - 17:55

Hi, christopherf.
Could you post here your configuration for the connector? I have a similar issue.
Thansk a lot.

Bene

Permalink
Submitted by christopherf on Thu, 01/23/2014 - 07:00

Hi Bene

I am using the standard MS SQL 2012 connector included with Bonita, and a standard SELECT statement to get the values I want from the database. On the output configuration screen, I use a Groovy script, supplied by Haris above, to transform the ResultSet into a LIST.

final List> resultTable = new ArrayList>();
    int maxColumn = rSet.getMetaData().getColumnCount()+1;
                    while(rSet.next()){
                            final List row = new ArrayList();
                            for(int colIndex = 1; colIndex < maxColumn ; colIndex++){
                                    row.add(rSet.getObject(colIndex));
                            }
                            resultTable.add(Collections.unmodifiableList(row));

    

        }





    On the form, I added a table widget, and on the widget's General>Data screen, I select "Edit as an expression", and select the LIST variable containing my SQL query output. 




    One other thing I have done is to create a Groovy User Script of the above code, be enclosing the script in the following code:




    def static getTableList(java.sql.ResultSet resultset) {




        [Original Groovy Script]




        return resultTable

    }




    I can then call this script on my connector:




    List result = new ArrayList();

    result = ConvertToTable.getTableList(resultset)

    return result




    Hope this answers your question. If you can produce a better solution, feel free to let us know here. As I've said, I am still a newbie, and will likely make silly mistakes from time to time.

		

			 Permalink
					

		

	



	

		

			
		

		Submitted by bjnobre on Thu, 01/23/2014 - 12:48
	

	

		

    Thanks, christopherf.




    It just worked perfectly om my application.




    Any further modifications I make I'll let you know.




    Again, thanks a lot!!!


		

			 Permalink
					

		

	





	

		

			
		

		Submitted by bjnobre on Thu, 01/23/2014 - 12:49
	

	

		

    Harris, thank U too!


		

			 Permalink
					

		

	





	

		

			
		

		Submitted by alfred.ayson on Tue, 03/18/2014 - 06:25
	

	

		

    hi christopherf,




    Im using BPMCommunity 6.2.1




    I tried the items you mentioned above. I'm up to the part of creating Groovy User Script. Is the steps to create this the following?




    1. Click on Development > Manage Groovy Scripts... menu




    2. Click on Create button




    3. Enter a name for the script




    4. Double-Click on the script




    5. In edit expression, enter the code below:




    def static getTableList(java.sql.ResultSet resultset) {

        

        final List> resultTable = new ArrayList>();

        int maxColumn = rSet.getMetaData().getColumnCount()+1;

        

        while(rSet.next()){

            final List row = new ArrayList();

            for(int colIndex = 1; colIndex < maxColumn ; colIndex++){

                row.add(rSet.getObject(colIndex));

                }

            resultTable.add(Collections.unmodifiableList(row));

            }

        

        return resultTable

    }




    

    Is this correct?




    Thank you in advance for the help.


		

			 Permalink
					

		

	





	

		

			
		

		Submitted by kppatel on Thu, 06/12/2014 - 07:52
	

	

		
Actually where i have to write that script . 
i m using oracle database connector .



on output window config . 
select > scripting mode . 
which will ask for variable . 
i create on variable of java.util.list.
in default data i paste that script 
but it prompt error .



Error



i can't understand what is exact process. 
please help



thanks


		

			 Permalink
					

		

	



  
      
    
  

      

          

  
      

          

  
  
  

    


  


              

        
      

        
        

    Submitted by hectorta on Tue, 07/12/2016 - 17:43    

    
    

      

            


1

0


-1

            

      

        

        
It doesn't works for me I read the datas from a MySQl DAta base, and I get the next two errors:

In Chrome console:


Error: [$parse:syntax] http://errors.angularjs.org/1.3.18/$parse/syntax?p0=panel&p1=is%20an%20unexpected%20token&p2=7&p3=panel%20panel-default&p4=panel-default

    at Error (native)

    at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:6:417

    at lb.throwError (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:192:113)

    at lb.parse (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:190:406)

    at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:110:366

    at new n (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:10017)

    at Object.create (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:8209)

    at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:12401

    at Array.forEach (native)

    at Object.create (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:12373) <div pb-property-values="59c870ab-b357-45f3-b176-25f60338efb0" class="ng-scope">


vendor.min.js:102 SyntaxError: Unexpected end of JSON input

    at Object.parse (native)

    at tc (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:14:245)

    at bc (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:77:7)

    at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:77:369

    at q (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:7:322)

    at dd (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:77:351)

    at c (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:78:495)

    at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:112:182

    at m.$eval (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:126:250)

    at m.$digest (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:123:365)(anonymous function) @ vendor.min.js:102(anonymous function) @ vendor.min.js:76(anonymous function) @ vendor.min.js:112m.$eval @ vendor.min.js:126m.$digest @ vendor.min.js:123m.$apply @ vendor.min.js:127l @ vendor.min.js:81w @ vendor.min.js:85I.onload @ vendor.min.js:86


And in the log of Bonita:


GRAVE: ERROR: listando reportes*

2016-07-12 16:36:37.259 +0100 org.bonitasoft.groovy.script.leeReportes org.slf4j.Logger$error$1 call 

GRAVE: Message Localized: No signature of method: java.util.ArrayList.getObject() is applicable for argument types: (java.lang.Integer) values: [1]

Possible solutions: getAt(int), getAt(int)


2016-07-12 16:36:43.323 +0100 org.restlet.Component.Server org.restlet.engine.adapter.ServerAdapter commit 

GRAVE: An exception occured writing the response entity

com.fasterxml.jackson.databind.JsonMappingException: [no message for java.lang.NullPointerException]

        at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider.serializeValue(DefaultSerializerProvider.java:261)

        at com.fasterxml.jackson.databind.ObjectWriter._configAndWriteValue(ObjectWriter.java:802)

        at com.fasterxml.jackson.databind.ObjectWriter.writeValue(ObjectWriter.java:642)

Caused by: java.lang.NullPointerException

        at org.bonitasoft.web.rest.server.utils.JacksonSerializerHelper.writeValueAndStringValue(JacksonSerializerHelper.java:48)

        at org.bonitasoft.web.rest.server.utils.DataInstanceSerializer.serialize(DataInstanceSerializer.java:43)

        at org.bonitasoft.web.rest.server.utils.DataInstanceSerializer.serialize(DataInstanceSerializer.java:27)

        at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider.serializeValue(DefaultSerializerProvider.java:250)

        ... 61 more


2016-07-12 16:36:43.325 +0100 org.restlet.Component.Server org.restlet.engine.adapter.HttpServerHelper handle 

ADVERTENCIA: Error while handling an HTTP server call

java.lang.IllegalStateException: No puedo llamar a sendError() tras llevar a cabo la respuesta


        


                

                

        
              

    

  


      

          

  
      

          

  

  

  

    


  


              

        
      

        
        

    Submitted by machadofisher on Mon, 10/19/2015 - 17:49    

    
    

      

            


1

0


-1

            

      

        

        
Dear all


I want to populate a simple table widget with the List of objects I retrieve from database, I already used the graphical Mode in Bonita to fill a List variable, the gui inform that the result will be a List of List and I take the value to a variable of type List and the system let me do it, and also tried the approach of the Grovy Script you wrote in answers to create the list in code from the resultset.


In UIDesigner I only can get values from database using Simple variables and using the REST API like this:


../API/bpm/activityVariable/{{taskId}}/name


In other words, if I select a single object from MySql and map it in graphical mode to a single variable TEXT for example in activity I'm able to read it in UIDesigner creating an input and associate it to a variable associated with the external API. But mapping a list is not working.

I'm using this API ../API/bpm/activityVariable/{{taskId}}/myList

and than I use the column values with the names of the columns in database.

I supose the myList should have some kind of processing in first place to be transformed in JSON?


I'm very stucked here


Many thanks in advance


        


                

                

        
              

    

  


      

          

  
      

          

  

  

  

    


  


              

        
      

        
        

    Submitted by daniel1984 on Thu, 08/28/2014 - 17:23    

    
    

      

            


1

0


-1

            

      

        

        
hello is possible upload a video example for this case. Thanks


        


                

                

        
              

    

  


      

          

  
      

          

  

  

  

    


  


              

        
      

        
        

    Submitted by kppatel on Thu, 06/12/2014 - 07:23    

    
    

      

            


1

0


-1

            

      

        

        
Hello all , i m totally new in this environment. 
i have same issue . i want to display database data into table into user's form



i m using bonitasoft 6.3 community
database oracle 10g



now i create connector of oracle . 
then as per mentioned i input credentials and then write query 
then go to output screen .



on output screen of connector 
there are two option .



    

  1. Graphical mode
    2. 

  2. scripting mode. 
    3. 




actually where i have to write that code ? 
because i go to scripting code it directly as for variable . 
not for any code. 
what i mistaken .
pl help me



thanks kandarp


        


                

                

        
              

    

  


      

          

  
      

          

  

  

  

    


  


              

        
      

        
        

    Submitted by jocafi on Sun, 05/18/2014 - 21:04    

    
    

      

            


1

0


-1

            

      

        

        
In the community version, it does NOT seem to work. I created a new table and



1) Marked the option Data -> Allow selection
2) "Initial Values I put the code below with return type = "java.util.List"



final List resultTable = new ArrayList();



int maxColumn = 5;



final List row = new ArrayList();

for(int colIndex = 1; colIndex < maxColumn ; colIndex++){

        row.add("col " + colIndex);

}

resultTable.add(Collections.unmodifiableList(row));



return resultTable



What I am doing wrong ?



Thx !!!


        


                

                

        
        

          
Comments

      
  


	

		

			
		

		Submitted by kppatel on Thu, 06/12/2014 - 13:38
	

	

		
Hello , thanks for ans 
i successfully done to populate data from db to table . 
but i want to use html and provide border to table change heading color etc . how can i


		

			 Permalink
					

		

	





	

		

			
		

		Submitted by christopherf on Thu, 06/12/2014 - 13:44
	

	

		
Hi, kppatel, unfortunately, I have no experience with what you are requesting. The bit of work I did with Bonita did not involve any HTML, so I won't be able to assist. Sorry.


		

			 Permalink
					

		

	





	

		

			
		

		Submitted by daniel1984 on Thu, 08/28/2014 - 20:44
	

	

		
Cristofer please help me. I have a error in my process. Need fill a table from sql server but this message error



ADVERTENCIA: THREAD_ID=403 | HOSTNAME=ntbkdbarraza | TENANT_ID=1 | org.bonitasoft.engine.core.process.instance.api.exceptions.SActivityStateExecutionException : "PROCESS_DEFINITION_ID=8710241522510931473 | PROCESS_NAME=Pool_Consulta_Libro_Novedades | PROCESS_VERSION=1.0 | PROCESS_INSTANCE_ID=4 | ROOT_PROCESS_INSTANCE_ID=4 | FLOW_NODE_DEFINITION_ID=-8130366540862793297 | FLOW_NODE_INSTANCE_ID=8 | FLOW_NODE_NAME=Tarea1 | org.bonitasoft.engine.expression.exception.SExpressionEvaluationException: Expression lista4 with content = > resultTable = new ArrayList<List>();



int maxColumn = resultSet.getMetaData().getColumnCount()+1;



while(resultSet.next()){



final List row = new ArrayList();



for(int colIndex = 1; colIndex < maxColumn ; colIndex++){



row.add(resultSet.getObject(colIndex));



}



resultTable.add(Collections.unmodifiableList(row));}> depends on resultSet is neither defined in the script nor in dependencies."

		

			 Permalink
					

		

	

  
      
    
  

      

          

  
      

          

  
  
  

    


  


              

        
      

        
        

    Submitted by christopherf on Tue, 03/18/2014 - 06:57    

    
    

      

            


1

0


-1

            

      

        

        

    Hi alfred.ayson.




    That is 100% correct. On your connector, you then call the code like this:




     




    List result = new ArrayList();

    result = ConvertToTable.getTableList(resultset)

    return result




     




    Hope this helps.


        


                

                

        
        

          
Comments

      
  


	

		

			
		

		Submitted by kppatel on Thu, 06/12/2014 - 08:02
	

	

		
Pl sir describe me procedure to display data into table . 
i can't figure out



thanks kandarp


		

			 Permalink
					

		

	



  

      

    

  


      

          

  
      

          

  

  

    
  
  
  
  
  
  
    
  
      

          

  


  
      

                      
            
    
  




  

      

    


      
  


Get updates from the worldwide Bonita Community!












      
  
Bonitasoft fully supports digital operations and IT modernization with Bonita, an open-source and extensible platform for automation and optimization of business processes. The Bonita platform accelerates development and production with clear separation between capabilities for visual programming and for coding. Bonita integrates with existing information systems, orchestrates heterogeneous systems, and provides deep visibility into processes across the organization.



             



 



Terms of use



© Bonitasoft, S.A. All rights reserved. Privacy. Legal notices



 



Home 




  

  



    

    

    

        Notifications