Help : Problems with anonymous PL/SQL block with parameters via JDBC

[SergeyP]

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:

Spoiler

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:

Spoiler

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

[ankit818]

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