Question Re: FLOAT/XSD:FLOAT Data Type Issue in MS SQL and SAP PI in Technology Q&A

FLOAT/XSD:FLOAT Data Type Issue in MS SQL and SAP PI

former_member183908 — Tue, 18 Nov 2014 10:42:04 GMT

Hi Every one,

I am facing strange issue with particular filed value from the MS SQL DB.

Value Field in the DB of type FLOAT is getting exponential values after executing the query by the sender JDBC channel in SAP PI.Based on my analysis if the Value filed in the DB is 8 chars length then i can see the exponential values in PI sender JDBC channel log and from there i am passing the same values to the target system.

In PI i am using the XSD:FLOAT data type.I have tried with another different data types in PI however still no luck.I am getting the same issue.I am planning to convert the exponential value to normal value by writing a small UDF in mapping however before that i wanted to check if some one faced similar kind of issue and fix for this.

Actual Issue in PI711 Prod system.I replicated in PI 730 however the same issue.Please find below screens for reference.I really appreciate if some one can help me on this.I guess some thing we can do with JVM parameters settings but not sure.

Float data type in MS SQL which is by default 8 chars:

Records in DB:

Channel Log:

Cheers

Pavan

Re: FLOAT/XSD:FLOAT Data Type Issue in MS SQL and SAP PI

Former Member — Tue, 18 Nov 2014 12:55:19 GMT

Hi Pavan,

I think the problem is in the JDBC adapter that makes that transformation: java - JDBC: jtds getString() returns doubles in scientific notation - Stack Overflow

In my opinion you need to do the conversion in the message mapping, you have an example, in the code provided by Grzegorz Glowacki, in this thread:

Regards.

Re: FLOAT/XSD:FLOAT Data Type Issue in MS SQL and SAP PI

former_member183908 — Tue, 18 Nov 2014 14:39:24 GMT

Thank you so much Inaki.It worked like a charm

I just tried the input arg in UDF with both float and string and i don't found any issues in UDF.

I hope there will be no other alternative for this kind of issue except UDF.

Thanks alot.