BigDecimal truncation problem on insert / update

[nadirx]

Dear all,

we are using Teiid 7.1 as a frontend to 4 Sybase databases and we are experiencing a problem where BigDecimals are truncated on insertion/update.

If we SELECT an existing value everything is ok.

This is the declaration of the column within the xmi:

<columns xmi:uuid="mmuuid:8bcf541e-e20d-4ecd-a51d-632bb97afcb5" name="NR_COSTO" nameInSource="nr_costo" nativeType="numeric" fixedLength="true" precision="19" scale="2" caseSensitive="false" searchability="ALL_EXCEPT_LIKE">

          <type href="http://www.metamatrix.com/metamodels/SimpleDatatypes-instance#bigdecimal"/>

        </columns>

{code:xml}<columns xmi:uuid="mmuuid:8bcf541e-e20d-4ecd-a51d-632bb97afcb5" name="NR_COST" nameInSource="nr_cost" nativeType="numeric" fixedLength="true" precision="19" scale="2" caseSensitive="false" searchability="ALL_EXCEPT_LIKE">

          <type href="http://www.metamatrix.com/metamodels/SimpleDatatypes-instance#bigdecimal"/>

</columns>{code}

We have this problem both using a Squirrel SQL and both via Hibernate.

Any hints ?

Tristan

[rareddy]

Tristan,

May be the JDBC driver you are using for the Sybase is narrowing the datatype on insert. Try searching any issues on that, or possibly change to another vendor's driver for Sybase?

Hope this helps.

Ramesh..

[rareddy]

Also, are you sure the column precision is wide enough on the table in Sybase?

[nadirx]

Hi Ramesh,

executing the query directly on the database with that driver the result is as expected, i.e. there is no truncation

could it be this:  https://jira.jboss.org/browse/TEIID-7

Note: the workaround suggested in the jira issue doesn't work for us...

Tristan

[rareddy]

Tristan,

Working directly mean with your Java code right?  When you said SquirrL, do you mean SquirreL on top of Teiid or directly to the database?

If it is on top Teiid, it does not matter, then it is then TEIID-7, if directly to db, it is something else.

Also, what you meant by workaround "doesn't work for us", as you can not use it or not working. Not working would mean that Teiid's convert is not behaving as you would expect.

Is your column on the table in the model, modeled as bigdecimal?

Ramesh..

[nadirx]

Hi Ramesh,

directly means, using JDBC without going through Teeid.

The problem occurs only when going via Teiid.

The workaround described in TEIID-7 doesn't solve the problem: the value is still truncated.

I'll make a few more tests and report back.

Tristan

[shawkins]

Just to confirm the scenario, you have:

select bigdecimal_val from x

which returns values such as 1.2345, but if you do

insert into x (bigdecimal_val) values (1.2345)

the resulting inserted value is 1, not 1.2345

If this is the case, some additional information may help.  What JDBC driver are you using through Teiid - JTDS, Sybase, DataDirect?  Are you performing the insert directly against a physical table, or are you going through a view?  Can you provide an example of an insert/update?  If there is nothing obvious still, then we'll need to open an issue and probably have you attach a stripped down version of your VDB.

Thanks,

Steve

[nadirx]

Ok, I've finally managed to collect all of the information (I hope). I am using Sybase Anywhere 9 with the official jconnect driver 6.05. I have turned on query logging on the DB to capture what is going on.

This is the result of a PreparedStatement going directly to Sybase (i.e. without Teiid):

** REQUEST conn: 1059  STMT_EXECUTE_ANY_IMM    " UPDATE infinity04.dbo.CX SET nr_cxvalue = @p0 WHERE infinity04.dbo.CX.id_cx = @p1"
** HOSTVAR conn: 1059  0 numeric(3,2) '5.66'
** HOSTVAR conn: 1059  1 int '4'

Using Teiid we get this:

** REQUEST conn: 282   STMT_EXECUTE_ANY_IMM    "UPDATE infinity04.dbo.CX SET nr_cxvalue = @p0 WHERE infinity04.dbo.CX.id_cx = @p1"
** HOSTVAR conn: 282   0 numeric(1,0) '5'
** HOSTVAR conn: 282   1 int '4'
** DONE    conn: 282   STMT_EXECUTE_ANY_IMM

As you can see the numeric precision and scale is completely off.

I have attached a stripped down VDB.

Here is an excerpt of the definition in the xmi of that particular column:

<columns xmi:uuid="mmuuid:43e4cc03-3899-4310-9772-e1c9612381be" name="NR_CXVALUE" nameInSource="nr_cxvalue" nativeType="numeric" fixedLength="true" precision="9" scale="2" caseSensitive="false" searchability="ALL_EXCEPT_LIKE">

          <type href="http://www.metamatrix.com/metamodels/SimpleDatatypes-instance#bigdecimal"/>

        </columns>

<columns xmi:uuid="mmuuid:43e4cc03-3899-4310-9772-e1c9612381be" name="NR_CXVALUE" nameInSource="nr_cxvalue" nativeType="numeric" fixedLength="true" precision="9" scale="2" caseSensitive="false" searchability="ALL_EXCEPT_LIKE">

          <type href="http://www.metamatrix.com/metamodels/SimpleDatatypes-instance#bigdecimal"/>

</columns>

Tristan

[shawkins]

Using JTDS or presumably DataDirect as the driver the relevant setObject call with a BigDecimal values gives me:

LANGUAGE Token (0x21); variable length.
   Length [4]:                   48
   Status [1]:                   PARAMETERIZED (0x01)
   Text Length [0]:              [47]
   Text [47]:                    "UPDATE infinity04.dbo.CX SET nr_cxvalue =  @P0 "

PARAMFMT Token (0xEC); variable length.
   Length [2]:                   13
   Number of Params [2]:         1
   Param 1
     Name Length [1]:            0
     Status [1]:                 <unrecognized> (0x00)
     User Type [4]:              0
     Data Type [1]:              DECN
     Length [1]:                 17
     Precision [1]:              0x26
     Scale [1]:                  0x02
     Locale Length [1]:          0

PARAMS Token (0xD7); variable length.
   Param 1
     Length [1]:                 3
     Param data [3]:             5.66

With jConnect I see your issue:

PARAMFMT Token (0xEC); variable length.
   Length [2]:                   13
   Number of Params [2]:         1
   Param 1
     Name Length [1]:            0
     Status [1]:                 <unrecognized> (0x00)
     User Type [4]:              0
     Data Type [1]:              NUMN
     Length [1]:                 54
     Precision [1]:              0x01
     Scale [1]:                  0x00
     Locale Length [1]:          0

PARAMS Token (0xD7); variable length.
   Param 1
     Length [1]:                 2
     Param data [2]:             5

This seems to be a  bug with jConnect.  The are defaulting the scaleOrLength parameter to 0  when calling the three argument form of setObject.  You would see the  same behavior from a test client calling ps.setObject(1, new  BigDecimal("5.66"), Types.NUMERIC)

I'll open a JIRA to provide a fix.

Steve

[shawkins]

I should mention that you can workaround by turning off prepared statements on you translator.

[nadirx]

Steve, you are a star !!!!!

Thanks a bunch

Tristan