CODEWITHSUNDEEP
javasqljooq
Jerry Zhou
Jerry Zhou

Reputation: 229

How to parser SQL string with self defined functions using jOOQ?

I am trying to parse SQL string with jOOQ SQL parser. The SQL contains self defined functions like this:

select dateadd([*]'20190809', 'yyyymmdd', 1) from table1

It can't be parsed with any dialect. I want to get the select fields and manipulate the limit-offset clause.

Is there any way to achieve this?

Upvotes: 0

Views: 299

Answers (1)

knutwannheden
knutwannheden

Reputation: 714

The somewhat standard DATEADD() function (DATE_ADD() in some SQL dialects) takes very many different forms, depending on the specific SQL dialect.

jOOQ's SQL parser currently only supports one of these variations: DATEADD(<datepart>, <interval>, <date>), which corresponds to the syntax in SQL Server (and some other dialects).

Applied to your example (assuming you want to add one day) this would be:

select dateadd(DAY, 1, date '2019-08-09') from table1

Note that the '20190809' isn't a proper date literal in most dialects, which is why I substituted it with date '2019-08-09'.

Also note that you can play around with these things using the jOOQ translator.

Assuming you have your own user-defined stored procedure also called DATEADD, then you are indeed out of luck, as jOOQ's parser attempts to parse the expression as described above. With any other name (e.g. DATEADD1) jOOQ's parser would not have any problem parsing this expression. You might however want to configure how jOOQ should deal with unknown functions.

Upvotes: 1

Related Questions