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(dot)meagher(at)gmail(dot)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
> <https://github.com/jdbi/jdbi/blob/master/src/test/java/org/skife/jdbi/v2/TestPreparedBatchGenerateKeysPostgres.java>
> few
> <https://github.com/jdbi/jdbi/blob/master/src/test/java/org/skife/jdbi/v2/sqlobject/TestPostgresBugs.java> test
> classes
> <https://github.com/jdbi/jdbi/blob/master/src/test/java/org/skife/jdbi/v2/sqlobject/TestGetGeneratedKeysPostgres.java>
> 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(at)tym(dot)im> wrote:
>
>> Correct syntax is {call procedure(params)}. I think you forgot "call".
>>
>>
>>
>> Пн, 22 серп. 2016 23:38 користувач Evan Meagher <evan(dot)meagher(at)gmail(dot)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(dot)vladimir(at)gmail(dot)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_KEY
>>>> S);
>>>> 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