Re: Invoking a function within a batch statement

Evan,

we aren't going to ever fix 9.1-901 JDBC code is there any way to get jdbi
to use a current driver ?

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 27 August 2016 at 15:06, Evan Meagher <evan(dot)meagher(at)gmail(dot)com> wrote:

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