Error in datatype conversion in XI

[Former Member]

Hello Experts,

I am receiving a "decimal" data in "string" format from the input proxy . I tried to insert the data into the table with column defined as decimal(15, 2). Its showing an error at JDBC adapter saying that "Error converting datatype nVarchar to decimal".

Experts please help me out in resolving this.

Scenario is Proxy to JDBC using Stored Procedure.

Thanks ,

Srivatsan

[Former Member · Accepted solution]

in ur target structure fro this field change the data type to xsd:decimal

[Former Member]

HI,

Try this in mapping.

SourceFormatNumber(NumberFormat: #.00 decimal separator: .)target field.

Regards,

Prakasu.M

[former_member200962]

"decimal" data in "string" format from the input proxy

If i get it right the source will be sending you data in a field which has type as xsd:string......and then you need to send this data to the DB which can accept only decimal with length 15 and 2 digits after decimal...

So in XI you will need to implement String_to_Decimal logic in your mapping program...also you need to take care that the target node that you are using in XI has type xsd:decimal....

this can be achieved by using a UDF (i suppose there is one available on SDN)....just search for it....

Regards,

Abhishek.

[Former Member]

Hi Srivatsan,

What does the "type" attribute for this field state in the XML SQL data type?

Is it "DECIMAL"?

Best regards,

Dennis

[Shabarish_Nair]

make sure the source data has the right format for the decimal.

Also note that you should not have any comma etc in the source data

eg.

the DB will accept values like 1000.50 but not 1,000.50

confirm on the data that you are sending and check if it is as expected by the DB

[Former Member]

Hi,

If you are using stored procedure then receive this string value in a varchar variable in JDBC side.

Then in the stored procedure you can type cast this to float or decimal and then update the table.

If from XI you directly try to update a decimal field in DB through a string field in XI it will give you a type mismatch error.

Regards,

Siddhesh S.Tawate

[Former Member]

Hi,

Thanks for the reply. But we have no access over the database stored procedures. We have to mange somehow from XI side itself before sending the data to JDBC.

Regards

Srivatsan

[Former Member]

Hi,

In that case the data type of your JDBC receiver side should have same data type as expected by your stored procedure. That is if Stored procedure expects a decimal field then you need to make your data type filed also xsd:decimal.

Regards,

Siddhesh S.Tawate