0

I'm migrating application from Oracle to EDB, and I'm having some problems with stored procedures in packages.

Supposing I have this type defined:

CREATE TYPE public.obj_test AS
(
    version_in character varying(5),
    sessionid_in character varying(40)
);

and i create this procedure:

CREATE OR REPLACE PACKAGE public.pkgtest
IS
 PROCEDURE test_proc(param1 public.obj_test, OUT param2 mad.obj_test);
END pkgtest;

CREATE OR REPLACE PACKAGE BODY public.pkgtest
IS
     PROCEDURE test_proc(param1 public.obj_test, OUT param2 public.obj_test) IS
   BEGIN
      param2 := public.OBJ_TEST('1', '2');

   END;

END pkgtest;

In my java application, I created the connection properly, something like:

public class MyObj {
    protected String version;
    protected String sessionId;

    @Override
    public String toString() {
        return "MyObj{" +
                "version='" + version + '\'' +
                ", sessionId='" + sessionId + '\'' +
                '}';
    }
}

 String url = "jdbc:postgresql://x.x.x.x/public";
 Properties props = new Properties();
 props.setProperty("user","myuser");
 props.setProperty("password","mypassword");
 Connection conn = DriverManager.getConnection(url, props);
        
 CallableStatement ps3 = conn.prepareCall("CALL public.pkgtest.test_proc(?,?)");

 MyObj rq = new MyObj();
 MyObj rs = new MyObj();
 String abc = null;
 ps3.setObject(1, rq, Types.OTHER);
 ps3.setObject(2, rs, Types.OTHER);

 ResultSet rsSet = ps3.executeQuery();
 while (rsSet.next()) {
      System.out.println(rsSet.getObject(1));
 }

but, is throwing exception. I tried with some Types like JAVA_OBJECT, STRUCT, OTHER, ... I simply want to pass my java object as a parameter, and receive another java object as "reply".

In my real case, both parameters are different classes.

What I'm doing wrong?

edit: i forgot to say, is language edbspl

sirsanchez
  • 1
  • 1
  • I forgot to say, is edbspl – sirsanchez Feb 24 '21 at 13:02
  • Does the "JDBC standard" work: `"{call public.pkgtest.test_proc(?,?)}"` (which is essentially the way you do it in Oracle) –  Feb 24 '21 at 13:04
  • Yes. The fully code works if i change types from "public.obj_test" to for example a "text" – sirsanchez Feb 24 '21 at 13:06
  • Are you using an `edb-jdbc` driver or a `pgjdbc` driver? If you want to call a procedure, I believe you need `edb-jdbc` and your connection `url` should be `jdbc:edb` instead of `jdbc:postgresql` – richyen Mar 11 '21 at 22:29

0 Answers0