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.
