Re: Invoking a function within a batch statement

Evan,

On 22 August 2016 at 23:37, Evan Meagher <evan(dot)meagher(at)gmail(dot)com> wrote:

> > 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 show us exactly what you did here the { should not be getting
through to the backend.

Dave Cramer

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

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