parameters on procedure (JDBC EDB)

251 Views Asked by At

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

0

There are 0 best solutions below