How to invoke additional SQL before each query?

Question

I have a database that uses plv8 engine and have stored procedures written in coffescript.

When I use jDBI, in order to call those procedures, after I open connection I have to run:

SET plv8.start_proc = 'plv8_init';

Can I do a similar thing when using JOOQ with javax.sql.DataSource?

score 0 · Answer 1 · answered Jul 22 '16 at 21:35

0

One option is to use an ExecuteListener. You can hook into the query execution lifecycle by implementing the executeStart() method:

new DefaultExecuteListener() {
    @Override
    public void executeStart(ExecuteContext ctx) {
        DSL.using(ctx.connection()).execute("SET plv8.start_proc = 'plv8_init'");
    }
}

Now, supply the above ExecuteListener to your Configuration, and you're done.

See also the manual: http://www.jooq.org/doc/latest/manual/sql-execution/execute-listeners

answered Jul 22 '16 at 21:35

Lukas Eder

211,314
129
689
1,509

Thanks I'll try that. One more thing. When I just do fetch("SET plv8.start_proc = 'plv8_init'; select * from my_stored_proc()") it throws and exception just like I wouldn't add that plv8.start_proces. In other words it behaves like it would split the statement with ; and run each separatelly. Anyway I'll try today your solution – bodziec Jul 23 '16 at 05:37
@bodziec: You might achieve the desired behaviour by using [PostgreSQL's anonymous code blocks](https://www.postgresql.org/docs/9.4/static/sql-do.html), but I suspect that would be quite a bit intrusive and possibly changing the outcome of your queries – Lukas Eder Jul 23 '16 at 06:16

How to invoke additional SQL before each query?

1 Answers1