Re: Invoking a function within a batch statement

I've created a branch which adds a test to jDBI's TestPreparedBatchGenerateKeysPostgres class which triggers the "A result was returned when none was expected" error: https://github.com/evnm/jdbi/commit/407b3770fab7e606a88be44a5eb0e8141b012e04

Here's the test output I'm seing locally: https://gist.github.com/evnm/03f8f12fe61ba8830a4f81623b8076ed

This branch is based on the jdbi-2.75 tag, which pulls in pgjdbc 9.1-901-1.jdbc4.

On Tue, Aug 23, 2016 at 11:35 AM, Evan Meagher <evan.meagher@gmail.com> wrote:

Sorry, I noticed that mistake after sending as well. The query statement I used included the `call` keyword, I just missed it when augmenting the simplified example code from my initial email.

I modified by original jDBI DAO method with the `{call ...}` syntax thusly:

    @SqlBatch(

      transactional=true,

      value="{call append_to_time_series(...)}")

    public void insert(...);

> Evan, it would be great if you could provide a self-contained test case that fails with "pre 1210" version so we won't accidentally break that behavior.

I will see if I can tickle this issue with an addition to jDBI's test suite. It already has a few test classes that target specific corners of Postgres/pgjdbc. I'll update this thread once I have something replicable.

On Tue, Aug 23, 2016 at 11:25 AM, Vitalii Tymchyshyn <vit@tym.im> wrote:

Correct syntax is {call procedure(params)}. I think you forgot "call".

Пн, 22 серп. 2016 23:38 користувач Evan Meagher <evan.meagher@gmail.com> пише:

> Let us know how the call syntax works out for you

Using `{append_to_time_series(...)}` results in a o.p.u.PSQLException with message 'ERROR: syntax error at or near "{"'

> Can you please try the latest pgjdbc 9.4.1210-SNAPSHOT + @GetGeneratedKeys near your @SqlBatch("select ...") kind of statement?

That works! In fact, on 9.4.1210-SNAPSHOT, it works with and without the @GetGeneratedKeys annotation.

I guess I'll just stay tuned for a stable 9.4.1210 release and make do with the snapshots in the meantime. Thanks to all for the responses, and thanks Vladimir for the workaround!

On Fri, Aug 19, 2016 at 8:00 AM, Vladimir Sitnikov <sitnikov.vladimir@gmail.com> wrote:

Evan>However, because Postgres functions are invoked using SELECT, they return a table-like result, so even though my PL/pgSQL function returns VOID, the queries fail in the JDBC driver because it's expecting a null result.

Evan,

Can you please try the latest pgjdbc 9.4.1210-SNAPSHOT + @GetGeneratedKeys near your @SqlBatch("select ...") kind of statement?

The idea is as follows:

1) jDBI would issue prepareStatement(..., Statement.RETURN_GENERATED_KEYS);

2) pgjdbc has recently learned to handle "return generated keys" better, so that "return_generated_keys" would hint pgjdbc that it should expect some response (including empty rowset), so it won't fail with "none was expected".

Technically speaking, the question "if pgjdbc should fail when unexpected row data comes in a response to a query" was raised (see https://github.com/pgjdbc/pgjdbc/issues/488#issuecomment-237908650 ), however historical behavior was just fail with "A result was returned when none was expected"

The solution is to use proper API when executing statements that return something. For instance: executeQuery, or use "generated keys" API.

Vladimir

--

Evan Meagher

--

Evan Meagher

--

Evan Meagher