Re: Invoking a function within a batch statement

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