-
1. Re: BigDecimal truncation problem on insert / update
rareddy Oct 12, 2010 11:18 AM (in response to nadirx)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
rareddy Oct 12, 2010 11:21 AM (in response to nadirx)Also, are you sure the column precision is wide enough on the table in Sybase?
-
3. Re: BigDecimal truncation problem on insert / update
nadirx Oct 12, 2010 11:21 AM (in response to rareddy)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
rareddy Oct 12, 2010 11:42 AM (in response to nadirx)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
nadirx Oct 14, 2010 9:30 AM (in response to rareddy)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
shawkins Oct 14, 2010 10:22 AM (in response to nadirx)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
nadirx Oct 20, 2010 3:02 AM (in response to shawkins)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_IMMAs 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
-
testSybase.vdb.zip 4.9 KB
-
-
8. Re: BigDecimal truncation problem on insert / update
shawkins Oct 21, 2010 2:31 PM (in response to nadirx)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]: 0PARAMS Token (0xD7); variable length.
Param 1
Length [1]: 3
Param data [3]: 5.66With 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]: 0PARAMS Token (0xD7); variable length.
Param 1
Length [1]: 2
Param data [2]: 5This 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
shawkins Oct 21, 2010 2:35 PM (in response to shawkins)I should mention that you can workaround by turning off prepared statements on you translator.
-
10. Re: BigDecimal truncation problem on insert / update
nadirx Oct 21, 2010 3:54 PM (in response to shawkins)Steve, you are a star !!!!!
Thanks a bunch
Tristan