problem setting up jooq to update a record, it feels my postgres is not happy with the generated sql

[kalev...@gmail.com]

I want to update a row,

I've tried statements like:

dsl.update(AUTH_LOGIN_USER)

                .set(AUTH_LOGIN_USER.FAILED_LOGIN_COUNT, 0)

                .where(AUTH_LOGIN_USER.LOGIN_NAME.eq(credentials.getUsername())).execute();

and

AuthLoginUserRecord authuser = dsl.fetchOne(AUTH_LOGIN_USER, AUTH_LOGIN_USER.LOGIN_NAME.equal(credentials.getUsername()));

        authuser.setFailedLoginCount(0);

        authuser.update();

they failed with:

"Access database using jOOQ; bad SQL grammar [update \"public\".\"auth_login_user\" set \"public\".\"auth_login_user\".\"failed_login_count\" = ? where \"public\".\"auth_login_user\".\"login_name\" = ?]; nested exception is org.postgresql.util.PSQLException: ERROR: column \"public\" of relation \"auth_login_user\" does not exist\n  Position: 39",

The sql looks like:

update "public"."auth_login_user" set "public"."auth_login_user"."failed_login_count" = ? where "public"."auth_login_user"."login_name" = ?

and when I was trying to play with direct sql, I got working:

String sql = "update public.auth_login_user set failed_login_count = 0 where public.auth_login_user.login_name = '" + credentials.getUsername() + "'";

dsl.execute(sql);

which failes as soon as I add table as prefix to columname, i.e  "... auth_login_user.failed_login_count = 0 ..." fails.

I guess I'm supposed to alter the jooq's update command to have no schema/table name here... or there is something else wrong here.

btw similar select (and delete) work just fine:

dsl.select(AUTH_LOGIN_USER.LOGIN_NAME, AUTH_LOGIN_USER.FAILED_LOGIN_COUNT).from(AUTH_LOGIN_USER)

                .where(AUTH_LOGIN_USER.LOGIN_NAME.eq(credentials.getUsername())).execute());

db is pstgresql 9.6.2  on OSX

[Lukas Eder]

Indeed, PostgreSQL (like SQLite and Vertica) do not support qualified column names in the UPDATE's SET clause. This should be handled correctly by jOOQ. Are you sure your dsl reference is really using the SQLDialect.POSTGRES? You can check like this:

System.out.println(dsl.configuration().dialect());

Hope this helps,

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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[kalev...@gmail.com]

thnks Lukas,  this was the issue.

My SpringBoot setup did not used configured dialect from application.properties