How to write Enum into a PostGres DB using JOOQ

[ganeshra...@gmail.com]

Hi,

I am trying to insert a row into the table using the query below using JOOQ.

create.insertInto(
        table("stack_info"),
        fieldByName("id"),
        fieldByName("user_name"),
        fieldByName("time_created"),
        fieldByName("product"),
        fieldByName("label"),
        fieldByName("instance_type"),
        fieldByName("status"))
        .values(
                request.getStackId(),
                request.getUserName(),
                request.getTimeCreated(),
                request.getProduct().toString(),
                request.getLabel(),
                request.getInstanceType(),
                request.getStatus().toString()
        ).execute();

Running the above query, I get the following error.

Caused by: org.jooq.exception.DataAccessException: SQL [insert into 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

stack_status is defined as an ENUM in the DB. It has the following values:-

stack_status

: PENDING,CREATE_COMPLETE,DELETE_IN_PROGRESS,DELETED,DELETE_FAILED

Any pointers, how to fix this ?

Thanks,

Ganesh

[ganeshra...@gmail.com]

I upgraded JOOQ to version 3.11. And used the following code.

create.insertInto(table("stack_info"))
        .set(field(name("id")), request.getStackId())
        .set(field(name("user_name")), request.getUserName())
        .set(field(name("time_created")), request.getTimeCreated())
        .set(field(name("product")), request.getProduct().toString())
        .set(field(name("label")), request.getLabel())
        .set(field(name("instance_type")), request.getInstanceType())
        .set(field(name("status")), request.getStatus())
        .execute();

I still get the following error:-

Caused by: org.postgresql.util.PSQLException: 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

Can someone please help.

Thanks,

Ganesh

[Rob Sargent]

What data type does request.getStatus() return? and are you using the jOOQ code generator?

-- 
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.

[Ganesh]

Thanks for replying... I am not using JOOQ code generator.

request.getStatus()  returns State which is an Java enum and has the following values.

PENDING,CREATE_COMPLETE,DELETE_IN_PROGRESS,DELETED,DELETE_FAILED

The following query doesnt work too

create.insertInto(table("stack_info"))
        .set(field(name("id")), request.getStackId())
        .set(field(name("user_name")), request.getUserName())
        .set(field(name("time_created")), request.getTimeCreated())
        .set(field(name("product")), request.getProduct().toString())
        .set(field(name("label")), request.getLabel())
        .set(field(name("instance_type")), request.getInstanceType())

        .set(field(name("status")), request.getStatus().toString())
        .execute();

However, I am able to insert rows in the DB using SQL workbench which happily accepts String values for enum columns.. 

Thanks,

Ganesh

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.

[Lukas Eder]

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

[Ganesh]

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

--

[Lukas Eder]

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

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.

[Ganesh]

I think  DSL.inline() has worked for me. I think the changes werent applied correctly.

Thanks a lot for helping me.

regards,

Ganesh

[Victor Bronstein]

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!

[bronstei...@gmail.com]

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.

[Lukas Eder]

Thanks for your hint, that'll be useful to some users.

Please note that this will no longer be necessary starting from jOOQ 3.19, due to changes made in this issue:

https://github.com/jOOQ/jOOQ/issues/15207#issuecomment-1598510422

It has always been possible for the Schema of a type to be null. Theoretically even in generated code. With #15207, this became more probable, so I had to address EnumType::getSchema being unavailable even for generated PostgreSQL EnumType instances, meaning that the EnumType::getName will be sufficient to trigger PostgreSQL specific casts, etc. going forward.

I've also added comments on your linked issue, and closed it:

https://github.com/jOOQ/jOOQ/issues/7941

I hope this helps,
Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/4b5dda5e-6633-4ff6-96bc-a1516f818c0fn%40googlegroups.com.