One Star

Error : Type mismatch: cannot convert from int to BigDecimal

Hi,
I am working with an Oracle database and number column are translated to BigDecimal in java. But I always have a type mismatch.
Is there a workaround ? Besides, the number column is the PK and is loaded with an oracle sequence.
Thanks,
Jean Marie

  • Data Integration
16 REPLIES
Community Manager

Re: Error : Type mismatch: cannot convert from int to BigDecimal

Hello
Convert int to BigDecimal, try(for example, int id)
new BigDecimal(Integer.toString(row1.id))

Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star jmt
One Star

Re: Error : Type mismatch: cannot convert from int to BigDecimal

I might put this as bug because the bigdecimal type is used by default when i retrieve a schema from oracle in the metadata repository.
So my workaround id to change the type from bigDecimal to long for each table in the repository.
Jean Marie
Community Manager

Re: Error : Type mismatch: cannot convert from int to BigDecimal

Hello
I might put this as bug because the bigdecimal type is used by default when i retrieve a schema from oracle in the metadata repository

What's the data type in your table? number?numeric?decimal?
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

Re: Error : Type mismatch: cannot convert from int to BigDecimal

I think the systematic use of bigDecimal to translate Oracle number is not good.
There should be a test and if the precision is 0, then use int|Integer or bigInteger.
Employee

Re: Error : Type mismatch: cannot convert from int to BigDecimal

Hi Evrybody,
Shong, i think that is a serious problem, cause it gnenrates a java exception , and we can't do any thing sure at our level.
The solution i tried is to get the number type as long type (modifying the BigDecimal type in the Tmap), But some unexpected behaviour can occurs.
Best regards
One Star

Re: Error : Type mismatch: cannot convert from int to BigDecimal

Hi
J've the same problem :
- the datatype of Oracle column is INTEGER
- the datatype (DB TYpe) in Talend schema is NUMBER
Why not INTEGER ?
- the java type in Talend is BigDecimal
Note:
In TOS 2.3 it was: INTEGER->NUMBER-> Long
Expected, for me, is: INTEGER->INTEGER-> Int
Any solution ?
Best Regards
msosmo
One Star

Re: Error : Type mismatch: cannot convert from int to BigDecimal

Hi
Anybody has seen this problem ?
msomso
One Star

Re: Error : Type mismatch: cannot convert from int to BigDecimal

Same issue for me, automatic mapping of NUMBER to BigDecimal is not such a good idea.
Especially since NUMBER without precision is heavily used in a good number of oracle data model design as a datatype for the "id" primary key column of tables.
So I will try to modify the Oracle mapping file . It can be accessed via Windows > preferences >Talend > metadata of TalendType.
The file is pretty self explanatory and specifies the allowed and default mappings for both ways : dbToTalend and talendToDb and both languages : java and perl.
But Alexandre is right, Talend shoud have a way to detect if precision and length are provided in the oracle NUMBER datatype and then map accordingly.
Hope it helps.
David.
One Star

Re: Error : Type mismatch: cannot convert from int to BigDecimal

Hi,
J think J wasn't clear: the problem occurs before the "mapping file" translation.
I work with the INTEGER database columns, and they appear as NUMBER in Talend when the schema based on the table is generated.
Next it, the translation NUMBER -> BigDecimal seems be ok for me.
J think it is a bug, but how can it be rounded ?
Regards
msomso
One Star

Re: Error : Type mismatch: cannot convert from int to BigDecimal

Well I don't have a single column of the type Int or Integer in the Oracle database that I have to work with. Using Number instead is part of the design and coding conventions . It somehow makes sense as INT, INTEGER and even SMALLINT are sub-types or synonyms of NUMBER ( which one could name a "master type" )
see: http://www.psoug.org/reference/datatypes.html
But for the talend point of view this is still a rather worrying issue :
1. You use a tOracleInput on a repository schema that translated your int or number to bigdecimal.
2. you do whatever stuff you have to do on this data.
3. you try to write it back to Oracle , what does it do ? it tries to put a decimal type where there is not ?
hence those type mismatch ... maybe I'm wrong somewhere.
I got to say I'm only beginning to learn to use talend with Oracle.
It's a little bit off-topic but I have to say those date patterns that you cannot avoid to set drive me crazy.
Anyway Talend is still quite handy when working with files.
One Star

Re: Error : Type mismatch: cannot convert from int to BigDecimal

Hi,
J agree that
INT, INTEGER and even SMALLINT are sub-types or synonyms of NUMBER, ...

but there is an incompatibility when migrating the jobs ...
My jobs are migrated from 2.3 to 2.4 and 3.0.0.
So, J've some expressions in tMap like:
!aa10.AA10_CEPLB.equals("N")?L4:aa10.AA10_CEREMP.equals("P")?L3: aa10.AA10_CEREMP.equals("D")?L1:L2

It worked well < 2.4 because the Talend's out type for my column was Long (INTEGER -> NUMBER -> LONG)
With BigDecimal it generates:
Type mismatch. cannot convert from long to Bigdecimal.
L4 cannot be resolved ...

J'll have to modify all existing jobs Smiley Sad
Regards
msomso
One Star

Re: Error : Type mismatch: cannot convert from int to BigDecimal

Hi,
Even when J correct the tMap expressions, somme errors subsist:
- my schemas "IN" are defined with 2.3 and contain the datatype Long/long for database INTEGER columns.
- my new schemas "OUT" are reinitialised with 3.0 (new or modified table) and contain the datatype BigDecimal for database INTEGER columns
J've to do an explicite conversion for each BigDecimal out column: from long (in data) to BigDecimal (new out type).
Before 2.4 it wasn't necessary as J've had IN=long and OUT=long types.
Each job has to be modified when the schema is retrived (table modified).
Regards
msomso
One Star

Re: Error : Type mismatch: cannot convert from int to BigDecimal

... the joys of upgrading.
I don't know what was possible in 2.3 but if you used metadata repository instead of "built-in" for your schemas and queries, you should only change your metadata or maybe refresh / re-retrieve it after you modified oracle xml mapping file ( it worked for me).
change (in the java part)



to this :



Any further solution I must admit I don't know.

off-topic: Why do you use " J " like in " John " instead of " I " for the first person singular ? nothing serious but kind of weird to read.
One Star

Re: Error : Type mismatch: cannot convert from int to BigDecimal

Hi,
Global retriving - good idea, but I use also the built in schemas (lookup tables, when few columns are needed).
Built In = manual upgrade of type
I don't know what solution is preferable.
Thank you for your interest (and for your off-topic Smiley Wink).
Regards
msomso
One Star

Re: Error : Type mismatch: cannot convert from int to BigDecimal

Thanks for the tip DavidD above.
However I have a similar request which is can the default datatypes be overridden for non-database metadata? eg: Delimited Files.
I have some pipe-delimited files where the import of Metadata defaults to CHAR and DOUBLE when I'd like to default it to STRING and INTEGER. It can be quite time consuming going through every file to change the data types and some can get missed easily. The problem is that all the destination databases are Oracle which require a conversion to change CHAR to VARCHAR and DOUBLE to INTEGER as per the DB Schema, but String and Integer are handled without any issues.
Thanks.
Tim S
Five Stars

Re: Error : Type mismatch: cannot convert from int to BigDecimal

Can anyone post any latest update on this Issue ?