Thread: Invoking a function within a batch statement
Hello,
I'm having trouble invoking a PostgreSQL function within a prepared batch statement, and I'm wondering if it's possible that I'm encountering a limitation in the JDBC driver.
--
For context, my application uses jDBI v2.63.1 with version 9.4-1204-jdbc42 of the Postgres driver. I've implemented a feature which requires insertion into certain tables using a custom PL/pgSQL function. In order to issue batches of these queries within a single transaction, I'd like to use jDBI's `@SqlBatch` annotation. 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.
The jDBI-annotated DAO method in question:
@SqlBatch(
transactional=true,
value="SELECT append_to_time_series(<
public void insertV2(
...
);
This results in a BatchUpdateException wrapping a PSQLException which indicates a root cause of an unexpected null result: https://gist.github.
This issue is being tracked in a jDBI issue on GitHub, which links to some relevant discussion that took place on the jDBI mailing list. Any insight from the JDBC community would be greatly appreciated.
Evan Meagher