Обсуждение: Stored Procedure call using JDBC is failing
Hi,
Stored procedure execution using JDBC is failing.
JDBC driver build number: 42.2.5
Server version: PostgreSQL 11
Server version: PostgreSQL 11
Exact error message and stacktrace:
org.postgresql.util.PSQLException: ERROR: testproc(integer, integer, character varying) is a procedure
Hint: To call a procedure, use CALL.
Position: 15
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
at org.postgresql.jdbc.PgCallableStatement.executeWithFlags(PgCallableStatement.java:77)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:132)
at StoredProcedureTest.properCase(StoredProcedureTest.java:42)
at StoredProcedureTest.main(StoredProcedureTest.java:65)
What you were doing, ideally in code form:
CREATE OR REPLACE PROCEDURE public.testproc(
batchcount bigint DEFAULT 100000,
INOUT outcount bigint DEFAULT 0,
INOUT errormsg character varying DEFAULT ' ')
..............................
batchcount bigint DEFAULT 100000,
INOUT outcount bigint DEFAULT 0,
INOUT errormsg character varying DEFAULT ' ')
..............................
Java code:
String commandText = "{ call testproc(?,?,?) }";
CallableStatement stmt = conn.prepareCall(commandText);
String commandText = "{ call testproc(?,?,?) }";
CallableStatement stmt = conn.prepareCall(commandText);
stmt.setInt(1, new Integer("10000"));
stmt.setInt(2, new Integer("0"));
stmt.setString(3, new String("Start"));
stmt.registerOutParameter(2, Types.INTEGER);
stmt.registerOutParameter(3, Types.VARCHAR);
stmt.setInt(2, new Integer("0"));
stmt.setString(3, new String("Start"));
stmt.registerOutParameter(2, Types.INTEGER);
stmt.registerOutParameter(3, Types.VARCHAR);
stmt.execute();
Regards,
Kalyan Koka
Execute stored procedures from JDBC is not supperted per discussion of the CALL behavior: https://www.postgresql.org/message-id/4285.1537201440%40sss.pgh.pa.us
People will need to realize that CALL behavior is still a work in
progress. That may mean that JDBC etc shouldn't try to support it
yet, which isn't great, but none of the above options would have
led to near-term support of CALL in JDBC ...
On Mon, Oct 22, 2018 at 6:03 PM Kalyan Koka <Kalyan.Koka@microfocus.com> wrote:
Hi,Stored procedure execution using JDBC is failing.JDBC driver build number: 42.2.5
Server version: PostgreSQL 11
Exact error message and stacktrace:
org.postgresql.util.PSQLException: ERROR: testproc(integer, integer, character varying) is a procedure
Hint: To call a procedure, use CALL.
Position: 15
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
at org.postgresql.jdbc.PgCallableStatement.executeWithFlags(PgCallableStatement.java:77)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:132)
at StoredProcedureTest.properCase(StoredProcedureTest.java:42)
at StoredProcedureTest.main(StoredProcedureTest.java:65)
What you were doing, ideally in code form:CREATE OR REPLACE PROCEDURE public.testproc(
batchcount bigint DEFAULT 100000,
INOUT outcount bigint DEFAULT 0,
INOUT errormsg character varying DEFAULT ' ')
..............................Java code:
String commandText = "{ call testproc(?,?,?) }";
CallableStatement stmt = conn.prepareCall(commandText);stmt.setInt(1, new Integer("10000"));
stmt.setInt(2, new Integer("0"));
stmt.setString(3, new String("Start"));
stmt.registerOutParameter(2, Types.INTEGER);
stmt.registerOutParameter(3, Types.VARCHAR);
stmt.execute();
Regards,
Kalyan Koka