Error retrieving NUMBER field from Oracle to Bonita variable

Submitted by kari.paukku on Mon, 05/02/2016 - 13:13
1
0
-1

Hi,

When retrieving a field defined as NUMBER(10) in Oracle database into a variable defined as Integer I get the following conversion error message

Java.lang.ClassCastException: java.math.BigDecimal cannot be cast to java.lang.Integer
at org.bonitasoft.engine.data.instance.model.impl.SIntegerDataInstanceImpl.setValue

The same happens if the variable is defined as long or double and in the BDM.
e.g. when having it as double, the error is "java.math.BigDecimal cannot be cast to java.lang.Double"

How can I retrieve or cast this correctly or tell Bonita that it is receiving a whole number.

Thanks.
kp

oarcle
bigdecimal
inteher
cast/convert

Comments

Submitted by kari.paukku on Mon, 05/02/2016 - 13:21

If I convert the number to string in the database retrieval and change the parameters in Bonita to be string, then all works.

So select TO_CHAR(col1) from table, where col1 is defined as NUMBER(5) works but I'm hoping that there is a way to work with the actual datatypes.

Permalink
3 answers

Submitted by Sean McP on Tue, 05/03/2016 - 14:33
1
+1
-1

Glad you got it.

There is an easier way we found rather than writing three scripts, but I can't find it at the moment. if I do i'll let you know.

regards
Seán

PS: I believe this fully answers your question, can you please mark as resolved.

Comments

Submitted by kari.paukku on Thu, 05/05/2016 - 21:25

Sean,
tried to mark this answer but not sure if I managed to do it...

in any case your answers did provide the solution to the whole issue.
Thanks.
Kari

Permalink

Submitted by kari.paukku on Mon, 05/02/2016 - 18:35
1
0
-1

Submitted by Sean McP on Mon, 05/02/2016 - 15:24
1
0
-1

Here is an official list:

https://docs.oracle.com/cd/E19501-01/819-3659/gcmaz/

however:

https://docs.oracle.com/cd/B19306_01/java.102/b14188/datamap.htm

but these are the best resources:

http://stackoverflow.com/questions/3504521/what-java-data-type-corresponds-to-the-oracle-sql-data-type-numeric : See the answer with 8 ticks, it will tell you.

http://stackoverflow.com/questions/3504751/what-object-type-does-spring-hibernate-template-execute-method-return-for-a-count

Google Search is a wonderful thing.

regards
Seán

PS: If this reply answers your question, please mark a resolved.

Comments

Submitted by kari.paukku on Mon, 05/02/2016 - 15:35

Sean,
I did find some of these links before - the problems is - I think - that "the driver maps everything to BigDecimal always". As such that's fine with me but what datatype should I use in Bonitia to get the value to a variable?

These is no "BigDecimal" type in the list of possible options (test, long, double etc.) in the window where one defines the variable in Bonita.

I would need to convert/cast it as it comes from the database before it goes to the variable and I don't know how to that kind of thing in Bonita.

Permalink
Submitted by Sean McP on Mon, 05/02/2016 - 16:14

Have you rtried to

import java.math.BigInteger;

then use BigInteger.intValue() method.

http://www.tutorialspoint.com/java/math/biginteger_intvalue.htm

regards
Seán

Permalink
Submitted by kari.paukku on Mon, 05/02/2016 - 16:35

This may be silly question but where should I put this?

Currently I'm getting the data from the database this way - I read it from the database and map it to local variables

pnHYAis1g

The variable ContractKey then takes the value of field contract_key as returned from the database.Field is defined in the database as NUMBER(5).

My guess is that this is the point where the error gets raised. If I could specify that the variable ContractKey is BigDecimal it most likely would work. (Provided that it is true that the driver returns NUMBER fields as BigDecimal) .
I can change the SQL query to convert the field (CONTRCAT_KEY) at the point it is retrieved to string and then it will work.

But it would be so much nicer to work with the original datatypes....

Thanks,
ktp

Permalink
Submitted by Sean McP on Mon, 05/02/2016 - 17:59

can you show the connector code?

Permalink
Submitted by kari.paukku on Mon, 05/02/2016 - 18:44

yes, sure - anything that might help you to solve this.
The database is Oracle and here is the query that does the retrieve

plG888uBg

As such there is not much code - here are the local variables

plep2JO4g

And the mapping of the three columns retrieved to the variables

pnxG3fOpg

all done in graphical mode.

Here are the operations

pmSHdtM9g

The table contain these columns is
CONTRACT_KEY NUMBER(10),
CONTRACT_REF VARCHAR2(20)
TITLE VARCHAR2(100)

The error from Bonita log is here

pn4aY2CQg

As I see it the problem is at the point when Bonita tries to put the data from database to the contractKey variable.
(This is highlighted by the fact that if I convert it to string and make the variable type of string, it works)

So to me it would appear that either I need to make the driver to understand to return integer or make a variable (contractKey) in Bonita that can accept the BigDecimal the driver is returning.

Thanks,
ktp

Permalink
Submitted by Sean McP on Mon, 05/02/2016 - 21:01

First things first, I can't choose Graphical Mode for this query, in fact I never use it. I always use Scripting mode.

Use Script and add script code as follows (changing as necessary):

def contractRef1 = -1;

while (resultSet.next()) {
        contractRef1 = resultSet.getLong("contractRef").toInteger();
}

return contractRef1;

Using the def sets an internal false (no record found) and the while makes sure there is a record and when there is a record sets the return as an Int

I also saw these posts which were interesting:

http://www.coderanch.com/t/307108/JDBC/databases/BIGINT-return-type-Java
http://www.coderanch.com/t/301210/JDBC/databases/equivalent-bigInt-java
http://docs.oracle.com/javase/tutorial/jdbc/basics/retrieving.html

regards
Seán

Permalink
Submitted by kari.paukku on Tue, 05/03/2016 - 08:51

Sean,
thanks. I did managed to test your example and after modifications it worked. The main thing is that now I have an idea how it can work.

As I have three parameters I still have to figure out how to get values to those two others ones. I think that the solution is: if the first variable has a result.next(), the two others perhaps must have resultset.first().

Unfortunately, just at the point I started to test this (after a number of failed prior attempts) my Bonita decided to stop working. Something that it cant get a lock in the bonita database. Have reinstalled Bonita but no luck, will get back to you when I have managed to get everything to work again.

Thanks,
ktp

Permalink
Submitted by kari.paukku on Tue, 05/03/2016 - 09:50

Hi,
had to fully reinstall Bonita and then it started to work again. Not that serious as I could import back the process I was working with.

And it works!

Using your example I assigned a script to each of the variables as follows.

This is for the first parameter

def contractRef1 = '';
while (resultset.next()) {
contractRef1 = resultset.getString("contract_ref").toString();
}
return contractRef1;

this one is for the second one - here the thing is that I'm using **resultset.first() ** to "rewind" the cursor back to the first and only record. Don't know if this is the right or best way to do it but it works.

def contractKey1 = -1;

resultset.first();
contractKey1 = resultset.getLong("contract_key").toInteger();

return contractKey1;

and this the for the third one

def contractTitle1 = 'title';

resultset.first();
contractTitle1 = resultset.getString("title").toString();

return contractTitle1;

Thanks to you I can retrieve data from the database to the variables and populate the BDM and forms.

ktp

ps. Next step is then to have a go with actual process - already have some questions on that, e.g. how to get the service task to link to a human task so that the service task create a separate step.

Permalink
Notifications