CODEWITHSUNDEEP
springspring-bootdb2jooq
Christian
Christian

Reputation: 11

Jooq exception when calling Db2 udf

from my spring boot app i'm trying to call a Db2 udf with jooq 3.2.5 (free version) using plain SQL:

@Autowired
private DSLContext context;

@Override
public List<DataItemEntity> find(int ident, int itemNumber, LocalDate date) {
    return context.//
        select() .//
        from("Table(db2schema.db2udfname("
                    + ident + ", "
                    + itemNumber + ", "
                    + LastChangeDateUtil.retrieveActualDateAtUtc().toLocalDate()
                    + ") with ur;"
            ).//
        fetchInto(ItemEntity.class);
}

Maven build is successful and app is starting, but once I call the find function above there is an exception:

[2025-02-21 13:48:14,749] [WARN] [main] [NONE] [o.j.i.D.logDefaultDialect] - Unsupported dialect
: An exception was thrown when executing a query with unsupported dialect: SQLDialect.DEFAULT.

This is usually due to one of 2 reasons:

[2025-02-21 13:48:14,752] [ERROR] [main] [NONE] [o.s.b.a.j.JooqExceptionTranslator] - Execution of SQL statement failed. org.springframework.jdbc.BadSqlGrammarException: jOOQ; bad SQL grammar [select * from Table(db2schema.db2udfname(258483, 47114711, 2025-02-21) with ur;]

Does that unsupported dialect exception happen because of free jooq restrictions - may I need the pro-version to successfully run my function - or is there another issue I'm not aware right now

thanks for input

I'm expecting to receive a result set with multiple records from that db2 udf

Upvotes: 1

Views: 20

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221155

You forgot a closing parenthesis around your table(db2schema.db2udfname(..)) function call. Also, your concatenating of values to the string leads to other syntax errors and SQL injection vulnerabilities!

Please note:

  • While trivial queries like this one may work without formal dialect support, you'll quickly run into limitations where the jOOQ Enterprise Edition could help you with your Db2 integration

  • Please avoid concatenating strings with jOOQ. The main benefit of using a query builder like jOOQ is you should never have to do this. Even if you're using a plain SQL template (like you're doing), you should safely pass bind values to the template like this:

    from("table(db2schema.db2udfname(?, ?, ?))", 
    ident, 
    itemNumber, 
    LastChangeDateUtil.retrieveActualDateAtUtc().toLocalDate()
)

    One side effect of this would have been that you probably wouldn't have run into your syntax error.

Upvotes: 0

Related Questions