How to use jdbc call for postgres procedure with INOUT parameter

Question

I am not much into Postgres and has been lately doing a lot of surfing due to some specific requirement. I have this specific procedure

p_user_name character varying, 
p_password character varying, 
p_ip character varying, 
INOUT p_return_id integer, 
INOUT p_message character varying, 
INOUT p_user_id integer, 
INOUT p_name character varying, 
INOUT p_mobile character varying, 
INOUT p_email character varying, 
INOUT p_last_login timestamp without time zone, 
INOUT module_list refcursor, 
INOUT method_list refcursor)
 LANGUAGE plpgsql
AS $procedure$
DECLARE 
VAR_EXST_USER       INT;
VAR_USER_ID         INT;
VAR_NAME            varchar;
VAR_MOBILE          varchar;
VAR_EMAIL           varchar;
VAR_PASS            varchar;
VAR_LAST_LOGIN_TIME timestamp;
VAR_IS_LOGGED_IN    int;
VAR_IS_BLOCKED      int;
VAR_PRIVILEGE       int;
P_OSUSER  VARCHAR(100);
P_PROGRAM VARCHAR(100);
P_MACHINE VARCHAR(100);
P_USERNAME VARCHAR(100);
ipaddr varchar(500);
P_SID  varchar(500);
P_PORT  varchar(500);
P_SQL_ID varchar(500);

BEGIN
 ....         
END;
$procedure$;

I require to use this procedure to call in my jdbc connection and use the INOUT parameters. I am calling it in this way,

            String encodedPassword = getEncodedPassword(requestJson.getPassword());
            System.out.println("Encoded Password: " + encodedPassword);
            String sql = "{call public.admin_user_login_proc(?,?,?,?,?,?,?,?,?,?,?,?)}";
            System.out.println(sql);
            callableStatement = conn.prepareCall(sql);
            System.out.println("callableStatement : " + callableStatement);
            callableStatement.setString(1, requestJson.getUser_name());
            System.out.println("callableStatement : " + callableStatement);
            callableStatement.setString(2, encodedPassword);
            callableStatement.setString(3, requestJson.getIp_address());
            callableStatement.setInt(4, 0);
            callableStatement.registerOutParameter(4, Types.INTEGER);
            callableStatement.setString(5, null);
            callableStatement.registerOutParameter(5, Types.VARCHAR);
            callableStatement.setInt(6, 0);
            callableStatement.registerOutParameter(6, Types.INTEGER);
            callableStatement.setString(7, null);
            callableStatement.registerOutParameter(7, Types.VARCHAR);
            callableStatement.setString(8, null);
            callableStatement.registerOutParameter(8, Types.VARCHAR);
            callableStatement.setString(9, null);
            callableStatement.registerOutParameter(9, Types.VARCHAR);
            callableStatement.setTimestamp(10, null);
            callableStatement.registerOutParameter(10, Types.TIMESTAMP);
            callableStatement.setObject(11, null);
            callableStatement.registerOutParameter(11, Types.REF);
            callableStatement.setObject(12, null);
            callableStatement.registerOutParameter(12, Types.REF);

but not working.

I am having problem with finding the right approach to get through and also can't quite understand how to set parameter for ref cursors. Please help and explain a bit

How to use jdbc call for postgres procedure with INOUT parameter

0 Answers0