jooq does not respect dollar quoted string literals convention in postgres

[ani...@thoughtspot.com]

Hi,

We are using jooq for generating sql statements for a postgres data store. And for string literals we are following the postgres convention of dollar quoting (http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html). jooq doesn't seem to be respecting the dollar quoted strings of postgres. 

Internally within our system all the string literals are dollar quoted. For instance, when given a string which has a single quote (special character) in it  - for example "it's" we would dollar quote it to $zLmv$it's$zLmv. However jooq converts such a string to $zLmv$it''s$zLmv during query generation (note that it adds an additional apostrophe). Is there a way to tell jooq not to handle any special characters within strings as we have handled it ourselves through dollar quoting ?

Any help would be appreciated.

Thanks!

[Lukas Eder]

Hello,

Just to be sure, you're explicitly inlining your bind variables, is that right?

That's an interesting feature, I wasn't aware of this. A similar issue may arise in MySQL, when people switch off the NO_BACKSLASH_ESCAPES flag, which means that the SQL standard quoting of apostrophes it''s is replaced by the non-standard backslash-quoting it\'s.

While handling of vendor-specific quoting in jOOQ is probably out of scope, there should be easy ways to configure and override these things if you want to inline your bind variables. One option for you is to implement your own bind values through a CustomField:

http://www.jooq.org/doc/3.2/manual/sql-building/queryparts/custom-queryparts/

Another option is to implement a VisitListener that listens on rendering events of org.jooq.Param types:

http://www.jooq.org/doc/3.2/manual/sql-building/queryparts/custom-sql-transformation/

You could intercept the rendering of "regular" bind variables and replace them with your own custom ones. This SPI is not well-documented yet, but we're here to help.

In the mean time, for jOOQ 3.4 or another future release, we'll think about how this can be resolved more thoroughly, through Settings or something similar. Congrats, you got a round issue number! Yay! :-)

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

Cheers

Lukas

2014-02-03 <ani...@thoughtspot.com>:

--
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/groups/opt_out.

[ani...@thoughtspot.com]

Thanks for the prompt reply Lukas!

This happens in the context of inserting a record using the JOOQ code-generated classes for Tables and Records:

Table<Record> table = ...

InsertQuery<Record> insertQuery = context.insertQuery(table);

MyRecord record = new MyRecord();

record.setName("$zLmv$it's$zLmv"); // name is the column name in table

insertQuery.addRecord(record);

The SQL is got from JOOQ and executed independently: 

String sql = insertQuery.getSQL(ParamType.INLINED);

... execute SQL ...

How does the suggestions related to CustomField or Param listener apply in this context?

Thanks!

Anirudh.

[Lukas Eder]

2014-02-05 <ani...@thoughtspot.com>:

Thanks for the prompt reply Lukas!

This happens in the context of inserting a record using the JOOQ code-generated classes for Tables and Records:

Table<Record> table = ...

InsertQuery<Record> insertQuery = context.insertQuery(table);

MyRecord record = new MyRecord();

record.setName("$zLmv$it's$zLmv"); // name is the column name in table

insertQuery.addRecord(record);

The SQL is got from JOOQ and executed independently: 

String sql = insertQuery.getSQL(ParamType.INLINED);

... execute SQL ...

How does the suggestions related to CustomField or Param listener apply in this context?

You'd need to go through the "regular" jOOQ insert DSL API:

DSL.using(configuration)

   .insertInto(MY_TABLE, MY_TABLE.NAME)

   .values(new MyCustomBindValue("$zLmv$it's$zLmv$"))

   .execute();

... and MyCustomBindValue would be a CustomField. I haven't tried this, it's just an idea. But then again, why not rely on jOOQ correctly quoting apostrophes?