... the more I'm reading about this money type, the more I'm convinced it
should not be used at all:
https://stackoverflow.com/a/18170030/521799
The decimal precision is fixed by the server's relevant lc_monetary
setting, but what if that setting changes? Will we lose precision,
suddenly? What if we're in Switzerland (0.## precision) but we want to
trade with stocks (often usually 0.#### precision)? Is it still an
appropriate type?
Given that not even the JDBC driver correctly handles the money type after
a simple test (pardon my German):
ResultSet rs = s.executeQuery("SELECT 1234.50::money");
rs.next();
rs.getObject(1);
org.postgresql.util.PSQLException: Unzulässiger Wert für den Typ double :
CHF 1'234.50.
at org.postgresql.jdbc.PgResultSet.toDouble(PgResultSet.java:2921)
at org.postgresql.jdbc.PgResultSet.getDouble(PgResultSet.java:2338)
at org.postgresql.jdbc.PgResultSet.internalGetObject(PgResultSet.java:193)
at org.postgresql.jdbc.PgResultSet.getObject(PgResultSet.java:2589)
at Postgres.main(Postgres.java:28)
I'm getting to the conclusion that we should unsupport the money type and
recommend not to use it. Nevertheless, you can always implement your own
data type binding for top-level money type usage:
https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings
Which will probably, unfortunately, not work if your type is inside of a UDT
So, I'm afraid, I won't have a solution here.
Lukas
2017-11-23 13:07 GMT+01:00 Lukas Eder <[email protected]>:
> Hmm, this will be tricky without actually supporting the money type
> formally (https://github.com/jOOQ/jOOQ/issues/4364).
>
> The PostgreSQL JDBC driver will "toString" the monetary amount, which
> produces a locale-dependent value. This is hard to parse back again into
> jOOQ's current BigDecimal mapping. Will need to think about this a bit more
> thoroughly. Further discussion directly on the issue #6850
>
> 2017-11-23 12:47 GMT+01:00 Lukas Eder <[email protected]>:
>
>> Thank you very much for your report. I can reproduce this issue:
>> https://github.com/jOOQ/jOOQ/issues/6850
>>
>> Will look into this soon,
>> Lukas
>>
>> 2017-11-12 23:11 GMT+01:00 <[email protected]>:
>>
>>> Below are some example PostgreSQL (DB version 10.0) UDTs: one with a
>>> NUMERIC amount and one with a MONEY amount.
>>>
>>>
>>> Inserting a Bar row and then calling getBars() works fine, while inserting
>>> a Foo row and then calling getFoos() does *not* work because (the debugger
>>> reveals) the string supplied to the BigDecimal constructor is "$10.00" and
>>> it chokes on that leading '$' character.
>>>
>>>
>>> Frankly I don't know if this is a JDBC or a JOOQ problem and I am working
>>> around it by just using NUMERIC types but I did want to just flag this up...
>>>
>>>
>>> Cheers,
>>>
>>>
>>> Greg
>>>
>>>
>>> PS: Thanks for JOOQ, is it truly marvellous!
>>>
>>>
>>> ----
>>>
>>>
>>> ----- PostgreSQL
>>>
>>>
>>> CREATE TYPE MONEY_UDT AS (
>>> amount MONEY,
>>> description TEXT
>>> );
>>>
>>> CREATE TYPE NUMERIC_UDT AS (
>>> amount NUMERIC,
>>> description TEXT
>>> );
>>>
>>> CREATE TABLE foo (
>>> price MONEY_UDT
>>> );
>>>
>>> CREATE TABLE bar (
>>> price NUMERIC_UDT
>>> );
>>>
>>> ----- Kotlin
>>>
>>>
>>> // Assuming some java.sql.Connection conn:
>>>
>>>
>>> val ctx = DSL.using(conn, SQLDialect.POSTGRES_9_5)
>>>
>>> fun insertFoo() {
>>> ctx.insertInto(FOO, FOO.PRICE)
>>> .values(MoneyUdtRecord(BigDecimal.TEN, "Hi"))
>>> .execute()
>>> }
>>>
>>> fun getFoos(): List<Foo> =
>>> ctx.selectFrom(FOO)
>>> .fetch { record ->
>>> val price = record.price
>>> Foo(MoneyUdt(price.amount, price.description))
>>> }
>>>
>>> fun insertBar() {
>>> ctx.insertInto(BAR, BAR.PRICE)
>>> .values(NumericUdtRecord(BigDecimal.TEN, "Hi"))
>>> .execute()
>>> }
>>>
>>> fun getBars(): List<Bar> =
>>> ctx.selectFrom(BAR)
>>> .fetch { record ->
>>> val price = record.price
>>> Bar(NumericUdt(price.amount, price.description))
>>> }
>>>
>>> ----- Kotlin
>>>
>>> kotlin
>>> insertBar()
>>> insertFoo()
>>> getBars()
>>> getFoos()
>>>
>>>
>>> ----- Error message
>>>
>>> org.jooq.exception.DataAccessException: SQL [select "cusoon"."foo"."price"
>>> from "cusoon"."foo"]; Error while reading field: "cusoon"."foo"."price", at
>>> JDBC index: 1
>>> at org.jooq_3.10.0.POSTGRES_9_5.debug(Unknown Source)
>>> at org.jooq_3.10.0.POSTGRES_9_5.debug(Unknown Source)
>>> at org.jooq.impl.Tools.translate(Tools.java:2199)
>>> at
>>> org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:677)
>>> at
>>> org.jooq.impl.CursorImpl$CursorIterator.fetchNext(CursorImpl.java:1619)
>>> at org.jooq.impl.CursorImpl$CursorIterator.hasNext(CursorImpl.java:1570)
>>> at org.jooq.impl.CursorImpl.fetchNext(CursorImpl.java:392)
>>> at org.jooq.impl.CursorImpl.fetch(CursorImpl.java:378)
>>> at org.jooq.impl.CursorImpl.fetch(CursorImpl.java:289)
>>> at
>>> org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:288)
>>> at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:344)
>>> at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:314)
>>> at
>>> org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:1434)
>>> at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:3639)
>>> at net.gtod.cusoon.model.Model.getFoos(database.kt:268)
>>> at net.gtod.cusoon.integrationtest.ModelTest$1$1.invoke(ModelTest.kt:51)
>>> at net.gtod.cusoon.integrationtest.ModelTest$1$1.invoke(ModelTest.kt:40)
>>> at org.jetbrains.spek.engine.Scope$Test.execute(Scope.kt:106)
>>> at org.jetbrains.spek.engine.Scope$Test.execute(Scope.kt:83)
>>> at
>>> org.junit.platform.engine.support.hierarchical.Node.execute(Node.java:94)
>>> at
>>> org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.lambda$execute$1(HierarchicalTestExecutor.java:81)
>>> at
>>> org.junit.platform.engine.support.hierarchical.SingleTestExecutor.executeSafely(SingleTestExecutor.java:66)
>>> at
>>> org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:76)
>>> at
>>> org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.lambda$execute$1(HierarchicalTestExecutor.java:91)
>>> at
>>> org.junit.platform.engine.support.hierarchical.SingleTestExecutor.executeSafely(SingleTestExecutor.java:66)
>>> at
>>> org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:76)
>>> at
>>> org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.lambda$execute$1(HierarchicalTestExecutor.java:91)
>>> at
>>> org.junit.platform.engine.support.hierarchical.SingleTestExecutor.executeSafely(SingleTestExecutor.java:66)
>>> at
>>> org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:76)
>>> at
>>> org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:51)
>>> at
>>> org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:43)
>>> at
>>> org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:137)
>>> at
>>> org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:87)
>>> at
>>> org.jetbrains.spek.tooling.runner.junit.JUnitPlatformSpekRunner.run(JUnitPlatformSpekRunner.kt:107)
>>> at org.jetbrains.spek.tooling.MainKt.main(Main.kt:58)
>>> Caused by: java.sql.SQLException: Error while reading field:
>>> "cusoon"."foo"."price", at JDBC index: 1
>>> at
>>> org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.setValue(CursorImpl.java:1714)
>>> at
>>> org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.operate(CursorImpl.java:1674)
>>> at
>>> org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.operate(CursorImpl.java:1639)
>>> at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:122)
>>> at
>>> org.jooq.impl.CursorImpl$CursorIterator.fetchNext(CursorImpl.java:1603)
>>> ... 30 more
>>> Caused by: java.lang.NumberFormatException
>>> at java.math.BigDecimal.<init>(BigDecimal.java:494)
>>> at java.math.BigDecimal.<init>(BigDecimal.java:383)
>>> at java.math.BigDecimal.<init>(BigDecimal.java:806)
>>> at org.jooq.impl.DefaultBinding.pgFromString(DefaultBinding.java:2304)
>>> at org.jooq.impl.DefaultBinding.pgSetValue(DefaultBinding.java:2549)
>>> at org.jooq.impl.DefaultBinding$2.operate(DefaultBinding.java:2439)
>>> at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:122)
>>> at org.jooq.impl.DefaultBinding.pgNewRecord(DefaultBinding.java:2432)
>>> at org.jooq.impl.DefaultBinding.get(DefaultBinding.java:1632)
>>> at
>>> org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.setValue(CursorImpl.java:1704)
>>> ```
>>>
>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "jOOQ User Group" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to [email protected].
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>>
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
