10 Replies Latest reply on Oct 21, 2010 3:54 PM by Tristan Tarrant

    BigDecimal truncation problem on insert / update

    Tristan Tarrant Master

      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">
              </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

        • 1. Re: BigDecimal truncation problem on insert / update
          Ramesh Reddy Master

          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..

          • 2. Re: BigDecimal truncation problem on insert / update
            Ramesh Reddy Master

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

            • 3. Re: BigDecimal truncation problem on insert / update
              Tristan Tarrant Master

              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

              • 4. Re: BigDecimal truncation problem on insert / update
                Ramesh Reddy Master

                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..

                • 5. Re: BigDecimal truncation problem on insert / update
                  Tristan Tarrant Master

                  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

                  • 6. Re: BigDecimal truncation problem on insert / update
                    Steven Hawkins Master

                    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

                    • 7. Re: BigDecimal truncation problem on insert / update
                      Tristan Tarrant Master

                      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">
                              </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

                      • 8. Re: BigDecimal truncation problem on insert / update
                        Steven Hawkins Master

                        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

                        • 9. Re: BigDecimal truncation problem on insert / update
                          Steven Hawkins Master

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

                          • 10. Re: BigDecimal truncation problem on insert / update
                            Tristan Tarrant Master

                            Steve, you are a star !!!!!

                            Thanks a bunch

                             

                            Tristan