For the benefit of anyone who bumps into this issue again - there's a
"magic trick" not mentioned here that fixes things for people who don't use
the JOOQ code generator. In order for enum types to work properly without
any DSL.inline or casts, one has to override the method getSchema() of the
EnumType to return a non-null Schema instance. For example,
DSL.schema("public") works. This is related to
https://github.com/jOOQ/jOOQ/issues/7941.
On Wednesday, November 4, 2020 at 7:48:01 PM UTC+2 Victor Bronstein wrote:
> Have recently bumped into the exact same issue. DSL.inline did the trick
> but since it felt a bit unclean to me, I tried DSL.cast(value,
> field.getDataType()) and surprisingly enough it worked!
>
> On Tuesday, October 2, 2018 at 1:23:12 PM UTC+3 Ganesh wrote:
>
>> I think DSL.inline() has worked for me. I think the changes werent
>> applied correctly.
>>
>> Thanks a lot for helping me.
>>
>> regards,
>> Ganesh
>>
>> On Tue, Oct 2, 2018 at 3:03 AM Lukas Eder <lukas...@gmail.com> wrote:
>>
>>> Hi Ganesh,
>>>
>>> Are you sure your changes are applied correctly? Especially the latter
>>> (the one using DSL.inline()) would result in the status being inlined into
>>> the query rather than a bind variable being passed.
>>>
>>> Thanks,
>>> Lukas
>>>
>>> On Tue, Oct 2, 2018 at 11:50 AM Ganesh <ganeshra...@gmail.com> wrote:
>>>
>>>> Hi Lukas,
>>>>
>>>> Thanks for reply. I tried both of your suggestions as got the same
>>>> error. Can you please advice.
>>>>
>>>> 1) .......set(field(name("status"),
>>>> SQLDataType.VARCHAR.asEnumDataType(State.class)), request.getStatus())
>>>>
>>>> 2) ...... Object object = DSL.inline(request.getStatus();
>>>> ......set(field(name("status")), object)
>>>> Error:-
>>>>
>>>> org.jooq.exception.DataAccessException: SQL [insert into
>>>> gvsr_stack_info ("id", "user_name", "time_created", "product", "label",
>>>> "instance_type", "status") values (?, ?, cast(? as timestamp), ?, ?, ?,
>>>> ?)]; ERROR: column "status" is of type stack_status but expression is of
>>>> type character varying
>>>>
>>>> Hint: You will need to rewrite or cast the expression.
>>>>
>>>> Position: 163
>>>>
>>>> at org.jooq_3.11.4.POSTGRES.debug(Unknown Source) ~[?:?]
>>>>
>>>> at org.jooq.impl.Tools.translate(Tools.java:2384) ~[jooq-3.11.4.jar:?]
>>>>
>>>> at
>>>> org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:811)
>>>>
>>>> ~[jooq-3.11.4.jar:?]
>>>>
>>>> at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:364)
>>>> ~[jooq-3.11.4.jar:?]
>>>>
>>>> at
>>>> org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:127)
>>>>
>>>> ~[jooq-3.11.4.jar:?]
>>>>
>>>>
>>>>
>>>>
>>>> State class implements EnumType.
>>>>
>>>> import org.jooq.EnumType;
>>>>
>>>>
>>>> public enum State implements EnumType {
>>>>
>>>> *PENDING*("PENDING"),
>>>>
>>>> *IN_PROGRESS*("IN_PROGRESS"),
>>>>
>>>> *CREATE_COMPLETE*("CREATE_COMPLETE"),
>>>>
>>>> *DELETE_IN_PROGRESS*("DELETE_IN_PROGRESS"),
>>>>
>>>> *DELETED*("DELETED");
>>>>
>>>>
>>>> private final String literal;
>>>>
>>>>
>>>> private State(String literal) {
>>>>
>>>> this.literal = literal;
>>>>
>>>> }
>>>>
>>>> @Override
>>>>
>>>> public String getName() {
>>>>
>>>> return "state";
>>>>
>>>> }
>>>>
>>>> @Override
>>>>
>>>> public String getLiteral() {
>>>>
>>>> return this.literal;
>>>>
>>>> }
>>>>
>>>> }
>>>>
>>>>
>>>> Thanks,
>>>>
>>>> Ganesh
>>>>
>>>> On Tue, Oct 2, 2018 at 12:49 AM Lukas Eder <lukas...@gmail.com> wrote:
>>>>
>>>>>
>>>>>
>>>>> On Tue, Oct 2, 2018 at 7:35 AM Ganesh <ganeshra...@gmail.com> wrote:
>>>>>
>>>>>> Thanks for replying... I am not using JOOQ code generator.
>>>>>>
>>>>>
>>>>> Why not? :-) It would help you so much with your SQL, specifically
>>>>> when these vendor specific data types are involved. But of course, you
>>>>> can
>>>>> do the code generator's work manually. I'll explain below.
>>>>>
>>>>>
>>>>>> However, I am able to insert rows in the DB using SQL workbench which
>>>>>> happily accepts String values for enum columns..
>>>>>>
>>>>>
>>>>> That's because you're not using a bind variable in SQL workbench, but
>>>>> a literal. And PostgreSQL can convert between string literals and user
>>>>> defined types more easily than if you're using bind variables. You could,
>>>>> of course, tell jOOQ to use a literal as well, e.g. by using
>>>>> DSL.inline(request.getStatus()). Or, you use the code generator. Or, you
>>>>> explicitly specify types on your table columns. In your case:
>>>>>
>>>>> ...set(field(name("status"),
>>>>> SQLDataType.VARCHAR.asEnumDataType(StatusEnum.class)),
>>>>> request.getStatus())
>>>>>
>>>>>
>>>>> Your StatusEnum will need to implement org.jooq.EnumType for this to
>>>>> work correctly.
>>>>>
>>>>> Or really. You could just use the code generator :)
>>>>>
>>>>> I hope this helps.
>>>>> Lukas
>>>>>
>>>>> --
>>>>> You received this message because you are subscribed to a topic in the
>>>>> Google Groups "jOOQ User Group" group.
>>>>> To unsubscribe from this topic, visit
>>>>> https://groups.google.com/d/topic/jooq-user/K3sT3F5mnM0/unsubscribe.
>>>>> To unsubscribe from this group and all its topics, send an email to
>>>>> jooq-user+...@googlegroups.com.
>>>>> 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 jooq-user+...@googlegroups.com.
>>>> For more options, visit https://groups.google.com/d/optout.
>>>>
>>> --
>>> You received this message because you are subscribed to a topic in the
>>> Google Groups "jOOQ User Group" group.
>>> To unsubscribe from this topic, visit
>>> https://groups.google.com/d/topic/jooq-user/K3sT3F5mnM0/unsubscribe.
>>> To unsubscribe from this group and all its topics, send an email to
>>> jooq-user+...@googlegroups.com.
>>> 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 jooq-user+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/4b5dda5e-6633-4ff6-96bc-a1516f818c0fn%40googlegroups.com.
