BigDecimal dsl insert malfunction

[JC]

Hi

Using jooq 1.6.5 or 1.6.2 on Hsqldb 2.2.5

Im having a problem with scale when inserting into table a bigdecimal
when using dsl statement.
example:

table z (
d decimal(12,2)
)

create.insertInto(Z.Z,Z.D).values(new BigDecimal("5.19")).execute()

The value on the database is 5.00
Aparentely it scales before inserting into the database.

if i do

create.query("Insert into z values("+new BigDecimal("5.19+")")

works fine, database returned 5.19 as it should.With createStatement
it works fine to.

Tanks
Andre Bastos

[Lukas Eder]

Hi Andre

Thanks for reporting this. To ensure the strong typesafety required by
HSQLDB, jOOQ renders and prepares your insert statement as

insert into "Z" ("D") values (cast(? as numeric))

when it should be (in HSQLDB):

insert into "Z" ("D") values (cast(? as numeric(12,2))

Other RDBMS don't seem to truncate numerics when not supplied with
precision/scale explicitly.

Unfortunately jOOQ data types are missing precision and scale
information. This has been an open feature request:
https://sourceforge.net/apps/trac/jooq/ticket/456

I have increased the priority of that ticket. In the mean time. Before
this is fixed, you have these workarounds, to avoid the casting:

// Avoid bind variables
create.insertInto(Z.Z,Z.D).values(create.literal(new
BigDecimal("5.19"))).execute()

// With bind variables
create.insertInto(Z.Z,Z.D).values(create.field("?", new
BigDecimal("5.19"))).execute()

Cheers
Lukas

2011/8/31 JC <rip...@gmail.com>:

[Lukas Eder]

I have fixed #822. BigDecimals are truncated in INSERT (and probably
also UPDATE) statements in DB2, Derby, and HSQLDB:
https://sourceforge.net/apps/trac/jooq/ticket/822

A snapshot of the upcoming jOOQ 1.6.6 is available on the sonatype
maven repository:
https://oss.sonatype.org/content/repositories/snapshots/org/jooq/

The issue that you reported has more implications in DB2, Derby:
https://sourceforge.net/apps/trac/jooq/ticket/824

And in SQLite:
https://sourceforge.net/apps/trac/jooq/ticket/823

Other RDBMS are not affected: H2, Ingres, MySQL, Oracle, Postgres, SQL
Server, Sybase

Cheers
Lukas

[Lukas Eder]

Hello,

These tickets have been resolved in jOOQ 1.6.6:

https://sourceforge.net/apps/trac/jooq/ticket/822
https://sourceforge.net/apps/trac/jooq/ticket/823
https://sourceforge.net/apps/trac/jooq/ticket/824

Relevant integration tests have been added.
Thanks again for your feedback

Cheers
Lukas

2011/8/31 JC <rip...@gmail.com>: