Hi,
A while back the Posgre Java driver had a breaking change for procedure calls: https://github.com/pgjdbc/pgjdbc/issues/1413
this is still unfortunately not addressed in: spring-jdbc:5.2.10.RELEASE
CallMetaDataContext.java:649
didnt find a ticket for this,
BR
Comment From: jhoeller
So we'd have to use plain call ... syntax rather than {call ... }? Is there any indication for this from the JDBC driver? Otherwise all we could do is to add an explicit flag on AbstractJdbcCall that indicates which syntax to use... In the latter case, I wonder what the name of the flag should be. We're usually not baking database-specific stuff into those generic classes, so we might have to use an abstract name (rather than something like "postgres11StoredProcedureSyntax").
Comment From: riba2101
yeah, would def agree...
it actually looks like Posgre specific impl.
hmm, ive also noticed some oracle specific code, maybe to unify an approach?
Comment From: jhoeller
Even if were to add a Postgres-specific check there, how would we derive the need to use the plain call ... syntax? Would it really have to be a dedicated flag that you would have to set on the user side? I mean we're going to find some name for that flag then, I just wonder whether this is totally Postgres-specific or whether Postgres is following some broader industry convention there.
Comment From: jhoeller
On a related note, we recently addressed another breaking change in the Postgres JDBC implementation regarding stored procedures vs functions: #25399
Comment From: riba2101
yeah, saw that one, and I presume its the same (part of) breaking change(s) from Postgres :/ I mean to be really clean, we would need to create templates for specific dialects - but would deem the effort as overkill!
anywho the driver converstaion can be found here , probably worth checking if there is something else
Comment From: jhoeller
Even worse than database-specific dialects, we got version-specific differences since only PostgreSQL 11 is affected, and even there only specific kinds of stored procedures? I guess a dedicated flag a la plainCallSyntax, documented to match that new PostgreSQL 11 call syntax rather than JDBC's abstracted syntax, is the most flexible since it can be customized per operation on the user side.
Comment From: jhoeller
According to https://jdbc.postgresql.org/documentation/head/callproc.html and https://jdbc.postgresql.org/documentation/head/connect.html, there is JDBC driver property escapeSyntaxCallMode=callIfNoReturn that can be set if Postgres 11 style procedures are in use, translating the JDBC call escape syntax into call instead of select. As far as I understand, this has been introduced in November 2019, half a year after the discussion in the Postgres bug tracker. From that perspective, there seems to be no need to make a Postgres-specific mode in Spring's support. I hope this works for you as well.
Comment From: riba2101
so for all who are interested in this - checked it and it works. @jhoeller tnx, somehow missed the flag