BOS 6.2.2 unable to serialize output from Progress Database

Q&A
1

I'm trying to connect a BOS 6.2. installation on a Windows 2008 R2 server via JDBC to Progress database using the following configuration

Driver: com.ddtek.jdbc.openedge.OpenEdgeDriver

URL: jdbc:datadirect:openedge://lwsv03dev02:30050;databasename=pinsys

Username: report

Password: ******

Query: SELECT TOP 10 "tran-code","td-stddescrn","rep-code","td-alias" FROM PUB.transdescrn

I was able to successfully connect and fetch data from Progrss databse using BOS 5.10.1. However, with BOS 6.2.2, it thows the following exception: 

java.lang.reflect.InvocationTargetException

org.bonitasoft.engine.exception.BonitaRuntimeException: java.lang.ClassCastException: com.ddtek.jdbc.openedgebase.BaseResultSet40 cannot be cast to java.io.Serializable

I found similar issue discussed at http://community.bonitasoft.com/groups/installation-6x/problem-using-bos-6-and-mysql and tried the script 

// Use a List<String> in order to be able to use it for dropdown list widget

List<String> result = new ArrayList<String>();

while(resultset.next()) {

  int intValue = resultset.getInt(1);

  result.add(String.valueOf(intValue));

}

return result;

so that BOS can serialize the output. However, the script retruned an error 

java.lang.reflect.InvocationTargetException

org.bonitasoft.engine.bpm.connector.ConnectorExecutionException: org.bonitasoft.engine.core.operation.exception.SOperationExecutionException: org.bonitasoft.engine.expression.exception.SExpressionEvaluationException: Script throws an exceptionSExpressionImpl

I understand the connectivity is fine but seralizing output from Prgoress database is an issue in BOS 6.2.2. Can anyone please be able to provide with some help here?

 

Sumesh

2

Hey,

Try to do something like this, adapt it:

My table:

Var1 Integer / var2 Char
1                    a
2                    b

Connector query : SELECT "var1", "var2"  FROM "public"."tabletest"

Output script:

List result = new ArrayList();
while(resultset.next()) {
    
List sousListe = new ArrayList();
sousListe.add(resultset.getInt(1));
sousListe.add(resultset.getString(2));
result.add(sousListe)
}
return result;

The output when you test : [[1,a],[2,b]]

-----------------------------------------------------------------------------------------------------------------------------------------------------

You can use the tool provide by my collegue on the forum awnser : https://github.com/Bonitasoft-Community/Bonita-Tools in the build folder.
Import the new jar in the studio and attach it to your process and finaly use this methode in the output of the connector:
 

  • How to get a list of elements ready to use into a SELECT or LIST widget?

    		<blockquote>
			<p>
				List listElements = BonitaDBTools.toList(resultSet, "column name to be considerated as displayed LABEL")</p>
		</blockquote>
	</li>
</ul>

<div>
	or select&nbsp;StringValue1 from yourtable</div>
 
List result = new ArrayList();
	<div>
		while(resultset.next()) {</div>

	<div>
		&nbsp;</div>

	<div>
		List sousListe = new ArrayList();</div>

	<div>
		sousListe.add(resultset.getString(1));</div>
</div>

<div>
	result.add(sousListe);</div>

<div>
	}</div>

<div>
	return result;</div>

<div>
	&nbsp;</div>

<div>
	&nbsp;</div>

<div>
	&nbsp;</div>

<div>
	Or if you wouldlike to display in the dropdown box the&nbsp;StringValue1 and save the&nbsp;StringValue2&nbsp;then you need to have something like or use the tool:</div>

<div>
	&nbsp;</div>

<div>
	select&nbsp;StringValue1,StringValue2 from yourtable</div>

<div>
	<ul>
		<li>
			<p>
				How to get a map of elements ready to use into a SELECT widget?</p>

			<blockquote>
				<p>
					Map mapElements = BonitaDBTools.toKeyValueMap(resultSet, "column name to be considerated as KEY", "column name to be considerated as displayed LABEL")</p>
			</blockquote>
		</li>
	</ul>
</div>

<div>
	<div>
		&nbsp;</div>

	<div>
		or this script:</div>

	<div>
		&nbsp;</div>

	<div>
		Map&lt;String, String&gt; result = new HashMap&lt;String,String&gt;();</div>

	<div>
		while(resultset.next()) {</div>

	<div>
		&nbsp;</div>

	<div>
		&nbsp;</div>

	<div>
		result.put(resultset.getString(1), resultset.getString(2));</div>

	<div>
		//if it's not the right element who is display put the 2 in the first position and the 1 on the second position :result.put(resultset.getString(2), resultset.getString(1));</div>

	<div>
		}</div>

	<p>
		&nbsp;</p>

	<div>
		return result;</div>
</div>

<div>
	&nbsp;</div>

<div>
	Be careful these scripts are specifics, the tool work everytime.</div>

 

 

3

Hi, did you try by using Bonita-Tools library? You can transform your database connector using one of the methods.

Don't hesitate to suggest new methods.

Here the link: https://github.com/Bonitasoft-Community/Bonita-Tools

Cheers

 

4

Hi Coucy,

Thanks for your response but the script thorws an error:

java.lang.reflect.InvocationTargetException
org.bonitasoft.engine.bpm.connector.ConnectorExecutionException: org.bonitasoft.engine.core.operation.exception.SOperationExecutionException: org.bonitasoft.engine.expression.exception.SExpressionEvaluationException: Script throws an exceptionSExpressionImpl [name=convertedResult, content=// Use a List in order to be able to use it for dropdown list widget

//List result = new ArrayList();

List result = new ArrayList();

while(resultset.next()) {

List sousListe = new ArrayList();
sousListe.add(resultset.getInt(1));
sousListe.add(resultset.getString(2));
result.add(sousListe);

}

return result;, returnType=java.util.List, dependencies=[SExpressionImpl [name=resultset, content=resultset, returnType=java.sql.ResultSet, dependencies=, expressionKind=ExpressionKind [interpreter=NONE, type=TYPE_INPUT]]], expressionKind=ExpressionKind [interpreter=GROOVY, type=TYPE_READ_ONLY_SCRIPT]]

Am I missing something here? I’m using Bonitasoft 6.2.2 Performance edition with TomCat Webserver and H2 DB on a 64-bit Windows 2007 Enterprise edition. I’m using openedge JDBC driver to connect to Progress database

Sumesh

5

Many thanks for your answers yhon and COUCY. I’ve resolved the issue using the following script

List result = new ArrayList();
while(resultset.next()) {

List sousListe = new ArrayList();
sousListe.add(resultset.getString(1));
sousListe.add(resultset.getString(2));
sousListe.add(resultset.getString(3));
sousListe.add(resultset.getString(4));
result.add(sousListe);

}
return result;

My SQL query was SELECT TOP 10 “tran-code”,“td-stddescrn”,“rep-code”,“td-alias” FROM PUB.transdescrn and I had to convert all parameters to List.

And thanks to pointing out to the API. I’ve not tried them as yet.

Sumesh