Adding semicolon at the end of sql statement

[Rohit]

Hi, 

I am using jOOQ as a simple SQL builder for Postgres database with inline parameters. E.g. 

String sql = DSL.using(SQLDialect.POSTGRES)  

                 .update(MY_TABLE)

                 .set(A, 1)

                 .where(B.greaterThan(5))

                 .getSQL(ParamType.INLINED);

This returns: "update MY_TABLE set A = 1 where B > 5"

Is there way to add a semicolon at the end in that result? 

Thanks for your help! 

- Rohit

[Lukas Eder]

Hi Rohit,

The *easiest* way would be to implement an ExecuteListener and hook into the renderEnd() event, adding the semi-colon to your query:

http://www.jooq.org/doc/latest/manual/sql-building/dsl-context/custom-execute-listeners/

So, you'd have to setup your own DefaultConfiguration and pass it to DSL.using:

String sql = DSL.using(myConfiguration)

                .update(...)

                .set(...)

                .where(...)

                .getSQL(ParamType.INLINED);

Note that once you're working with your own DefaultConfiguration, you might as well add a Setting to enforce the inlining of bind variables:

http://www.jooq.org/doc/latest/manual/sql-building/dsl-context/custom-settings/

That can be achieved with the StatementType.STATIC_STATEMENT setting.

Hope this helps,

Lukas

2015-03-13 23:48 GMT+01:00 Rohit <rohi...@gmail.com>:

Hi, 

I am using jOOQ as a simple SQL builder for Postgres database with inline parameters:

String sql = DSL.using(SQLDialect.POSTGRES)  

                 .update(MY_TABLE)

                 .set(A, 1)

                 .where(B.greaterThan(5))

                 .getSQL(ParamType.INLINED);

This returns: "update MY_TABLE set A = 1 where B > 5"

Is there way to add a semicolon at the end in that result? 

Thanks for your help! 

- Rohit

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

[Witold Szczerba]

Hi guys!
I think Lukas is wrong. The easiest way to add a semicolon is just to... add it:

String sql = DSL.using(myConfiguration)
         .update(...)
         .set(...)
         .where(...)

         .getSQL(ParamType.INLINED) + ';';

Voilà! :-)

Regards,
Witold Szczerba

[Lukas Eder]

Witold ;-)

That's certainly the easiest way when doing it for that one single query... But I suspect that Rohit will want to add semicolons to all his queries

[Witold Szczerba]

Lukas :-)

One can add a semicolon to all queries using my approach :-)

It's not much different than using an extra (missing) parameter like 'withSemicolon'.

Rohit wants to use jOOQ as part of something bigger, so he can probably  append a char at the adapter between his query runner and generated SQL string (I guess). Sometimes the simplest solution is the best one, ha :-)

Regards,
Witold Szczerba

[Lukas Eder]

Witold, if I didn't know better, I'd think you're trolling me ;-)

Oh well, fine. You found the easiest way to implement what Rohit wanted to implement.

[Witold Szczerba]

Ha!

You are right, my intent was not to troll around :)

It's just that I do see over-engineering all over the place almost every day. I don't say your solution was too complex or what so ever, just wanted to provide kind of diversity in a field of available solutions. Sometimes, the darkest place is under the candlestick, you know...

Regards,

Witold Szczerba

[Lukas Eder]

2015-03-16 13:00 GMT+01:00 Witold Szczerba <witold...@gmail.com>: 

Sometimes, the darkest place is under the candlestick, you know...

That is brilliant. I will need to cite that on some occasion :-) 

[Rohit]

Thanks Lukas. My intent for the question was to see if there already exists a setting in jOOQ to add semicolon. And even though it doesn't, I learned about the extensibility of jOOQ using the custom ExecuteListners. That will help me do some other things. Thank you and jOOQ is an awesome product! 

[Lukas Eder]

Hi Rohit,

I knew there would be other things :-)

Cheers! And thanks for the nice feedback

Lukas

--