过程myProcedure(text，text)不存在提示:没有任何过程与给定的名称和参数类型匹配

Question

无论何时尝试从我的java应用程序调用PostgreSQL 11.4中的任何存储过程，但收到此问题procedure pkg$my_procedure(text, text) does not exist。请注意，我可以从DB调用SP。

im使用PostgreSQL JDBC版本42.2.16

SP声明

create procedure pkg$my_procedure(i_param_name text, i_param_2 text, INOUT o_object refcursor)
language plpgsql
as
$$
BEGIN
// myLogic

调用SP 的Java代码

Connection con = null;
CallableStatement callableStatement = null;
ResultSet rs = null;
Object obj = null;
try {
con = eRestaurantConnection.getConnetion();
callableStatement = con.prepareCall("call pkg$my_procedure(cast(? as text),cast(? as text),?)");
callableStatement.setString(1, string1);
callableStatement.setString(2, string2);
callableStatement.registerOutParameter(3, Types.REF_CURSOR);
callableStatement.execute();
rs = (ResultSet) callableStatement.getObject(3);
obj = fillObjectInfo(rs);
} catch (Exception ex) {
LOG.error(ex.getLocalizedMessage(), ex);
} finally {
if (rs != null)
rs.close();
if(callableStatement!=null)callableStatement.close();
if(con!=null)con.close();
}
return obj;

异常

ERROR: procedure pkg$my_procedure(text, text) does not exist
Hint: No procedure matches the given name and argument types. You might need to add explicit type casts.
Position: 6
org.postgresql.util.PSQLException: ERROR: procedure pkg$my_procedure(text, text) does not exist
Hint: No procedure matches the given name and argument types. You might need to add explicit type casts.
Position: 6
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
at org.postgresql.jdbc.PgCallableStatement.executeWithFlags(PgCallableStatement.java:83)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:153)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyCallableStatement.execute(HikariProxyCallableStatement.java)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at net.ttddyy.dsproxy.proxy.StatementProxyLogic.performQueryExecutionListener(StatementProxyLogic.java:310)
at net.ttddyy.dsproxy.proxy.StatementProxyLogic.access$700(StatementProxyLogic.java:36)
at net.ttddyy.dsproxy.proxy.StatementProxyLogic$1.execute(StatementProxyLogic.java:122)
at net.ttddyy.dsproxy.listener.MethodExecutionListenerUtils.invoke(MethodExecutionListenerUtils.java:41)
at net.ttddyy.dsproxy.proxy.StatementProxyLogic.invoke(StatementProxyLogic.java:119)
at net.ttddyy.dsproxy.proxy.jdk.CallableStatementInvocationHandler.invoke(CallableStatementInvocationHandler.java:36)
...

来自PostgreSQL 的调用

do $$
declare
result refcursor = 'generated_result_cursor';
rec record;
begin
open result for call pkg$my_procedure(i_param_name  := 'name', i_param_2 := 'param', o_object := null);
LOOP
FETCH from result into rec;
EXIT WHEN NOT FOUND;
raise notice 're: %',rec;
EXIT;
END LOOP;
end
$$;

Answer 1

您创建的存储过程没有对名称进行双引号引用，因此它以小写形式存储。

错误消息报告一个包含大写字母的函数名。由于PG区分大小写，因此找不到存储过程。

-->使用小写的函数名

callableStatement = con.prepareCall("call myprocedure(cast(? as text),cast(? as text),?)");

Answer 2

所有这一点都需要在上面的代码中解决。感谢@JGH找到第一期。

1. 您需要确保大小写匹配
2. 需要设置all参数，即使它们用于输出。如果有输出，则需要将它们设置为null
3. 最后一件事需要禁用自动提交

调用SP 的Java代码

Connection con = null;
con.setAutoCommit(false);
CallableStatement callableStatement = null;
ResultSet rs = null;
Object obj = null;
try {
con = eRestaurantConnection.getConnetion();
callableStatement = con.prepareCall("call pkg$my_procedure(cast(? as text),cast(? as text),?)");
callableStatement.setString(1, string1);
callableStatement.setString(2, string2);
callableStatement.setNull(3,  Types.OTHER);
callableStatement.registerOutParameter(3, Types.REF_CURSOR);
callableStatement.execute();
rs = (ResultSet) callableStatement.getObject(3);
obj = fillObjectInfo(rs);
} catch (Exception ex) {
LOG.error(ex.getLocalizedMessage(), ex);
} finally {
if (rs != null)
rs.close();
if(callableStatement!=null)callableStatement.close();
if(con!=null)con.close();
}
return obj;