I need some suggestion on how to get the data through PostgreSQL JDBC driver from stored procedures using reference cursors.
Since PostgreSQL 11, it supports stored procedures with create procedure command instead of create function.
I have a scenario in which I want to fetch data from a stored procedure using a reference cursor.
My stored procedure SQL looks like as shown below
CREATE OR REPLACE PROCEDURE public.pr_sampleuser(
p_firstuser character varying,
INOUT p_qusers refcursor)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
OPEN p_qusers FOR
SELECT first_name,last_name,address
FROM public.test_user
WHERE UPPER(first_name) = UPPER(p_firstuser);
END;
$BODY$;
When we want to fetch the data using the JDBC driver, the first thing we need to add to the connection string is escapeSyntaxCallMode=call.
Following is the code-snippet that I am using to fetch the data,
try {
Properties props = new Properties();
props.setProperty("escapeSyntaxCallMode", "call");
Connection conn = DriverManager.getConnection(url,props);
String storedProc = "{call public.pr_sampleuser(?,?)}";
CallableStatement cs = conn.prepareCall(storedProc);
cs.setString(1,"Umesh");
cs.setObject(2,null);
cs.registerOutParameter(2,Types.REF_CURSOR);
conn.setAutoCommit(false);
// run StoredProcedure
cs.execute();
// get refcursor and convert it to ResultSet
ResultSet resultSet = (ResultSet) cs.getObject(2);
while (resultSet.next()) {
String firstName = resultSet.getString("first_name");
String lastname = resultSet.getString("last_name");
String address = resultSet.getString("address");
System.out.println(firstName);
System.out.println(lastname);
System.out.println(address);
}
} catch (SQLException e) {
System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
In this I am passing the second parameter as null using
cs.setObject(2,null);
I wanted to check if this is the correct way to fetch or if there is any better way to get the data.
