Re: Invoking a function within a batch statement

The plot thickens. Upgrading jDBI to JDBC 9.4.1209 results in the test
passing. Walking it back a few versions, it appears that the transition
from 9.4.1207 to 9.4.1208 breaks the test.

Looking at the changelog of 9.4.1208, there are a couple changes related to
callable and batch statement handling, but I'm not familiar enough with the
codebase to make much immediate sense out of them:

https://github.com/pgjdbc/pgjdbc/pull/459
https://github.com/pgjdbc/pgjdbc/pull/503

On Sat, Aug 27, 2016 at 2:26 PM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:

> 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/407b3770fab7e606a88be44a
>> 5eb0e8141b012e04
>>
>> Here's the test output I'm seing locally: https://gist.github.c
>> om/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
>>
>
>

--
Evan Meagher