- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
Help : Problems with anonymous PL/SQL block with parameters via JDBC
Hello,
It seems to be a problem with parameter passing.
Let’s create two functions for test purposes:
create or replace procedure test_proc_01 is begin null; end; create or replace procedure test_proc_02(pParam in number) is begin null; end;
And try to call them via JDB:
import com.edb.jdbc.*; import java.sql.CallableStatement; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class EdbTest01 { public static void main(String[] args) throws SQLException { final String url = "jdbc:edb://127.0.0.1:5444/epas12"; final String userName = "test"; final String userPwd = "123456"; PgConnection con = (PgConnection) DriverManager.getConnection(url, userName, userPwd); con.setSchema("test"); try (CallableStatement stmt1 = con.prepareCall("{ call test_proc_01() }")) { stmt1.execute(); } try (CallableStatement stmt2 = con.prepareCall("{ call test_proc_02(?) }")) { stmt2.setInt(1, 42); stmt2.execute(); } try (CallableStatement stmt3 = con.prepareCall("begin call test_proc_01(); end;")) { stmt3.execute(); } try (PreparedStatement stmt4 = con.prepareStatement("begin call test_proc_01(); end;")) { stmt4.execute(); } try (CallableStatement stmt5 = con.prepareCall("begin call test_proc_02(?); end;")) { stmt5.setInt(1, 42); stmt5.execute(); } try (PreparedStatement stmt6 = con.prepareStatement("begin call test_proc_02(?); end;")) { stmt6.setInt(1, 42); stmt6.execute(); } } }
In reality there will be not function call, but mix of insert+update+delete with parameters. But function call for test purposes seems to be ok.
Statements 01-04 works fine.
Statement 05 throws an exception:
Exception in thread "main" com.edb.util.PSQLException: ERROR: there is no parameter $1 Where: edb-spl function inline_code_block line 1 at CALL at com.edb.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2728) at com.edb.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2340) at com.edb.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:343) at com.edb.jdbc.PgStatement.executeInternal(PgStatement.java:479) at com.edb.jdbc.PgStatement.execute(PgStatement.java:397) at com.edb.jdbc.PgCallableStatement.executeWithFlags(PgCallableStatement.java:94) at com.edb.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:173) at EdbTest01.main(EdbTest01.java:38)
Statement 06 throws similiar exception:
Exception in thread "main" com.edb.util.PSQLException: ERROR: there is no parameter $1 Where: edb-spl function inline_code_block line 1 at CALL at com.edb.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2728) at com.edb.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2340) at com.edb.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:343) at com.edb.jdbc.PgStatement.executeInternal(PgStatement.java:479) at com.edb.jdbc.PgStatement.execute(PgStatement.java:397) at com.edb.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:184) at com.edb.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:173) at EdbTest01.main(EdbTest01.java:43)
Is there a solution to this problem (without splitting prepared statement to multiple or moving it to package function/procedure)?
Versions:
EDB docker image: ubi7-12.1.2-2.7
EDB JDBC Driver: jdbc18-42.2.8.1-1
Archived Discussions
Effective March 31st, we will no longer engage on PostgresRocks.
How to engage with us further?
- Thought Leadership: EDB Blogs
- Tips and Tricks: Postgres Tutorials
- Customer Support: Create a Case Please note: Only customers with an active EDB support subscription and support portal authorization can create support ticket
- Engage on Stackoverflow While engaging on Stackoverflow tag the question with EDB or EnterpriseDB.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
Re: Help : Problems with anonymous PL/SQL block with parameters via JDBC
Hi Team,
We may need to work with our connectors engineering team on this case. Could you please help us by logging a case on support@enterprisedb.com so that we can check and report it to our engineering team?
Let us know your feedback.
--Ankit