Thread: How to pass jsonb and inet arguments to a stored function with JDBC?
Dear PostgreSQL users,
I have a stored procedure defined as:CREATE OR REPLACE FUNCTION words_merge_users(
IN in_users jsonb,
IN in_ip inet,
OUT out_uid integer)
RETURNS integer AS
$func$
...............
$func$ LANGUAGE plpgsql;
Which I can successfully call at psql 9.5.3 prompt:IN in_users jsonb,
IN in_ip inet,
OUT out_uid integer)
RETURNS integer AS
$func$
...............
$func$ LANGUAGE plpgsql;
# SELECT out_uid FROM words_merge_users('[{"sid":"abcde","auth":"xxxx","social":2,"given":"Abcde2","female":0,"stamp":1450102880}]'::jsonb, '0.0.0.0'::inet);
out_uid
---------
1
(1 row)
ERROR: function words_merge_users(character varying, character varying) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Being a JDBC newbie I wonder, how to perform the call properly in Java?
PreparedStatement st = mDatabase.prepareStatement("SELECT out_uid FROM words_merge_users(?, ?)");
String str1 = JSON.toString(users);
String str2 = mSession.getRemoteAddress().getAddress().getHostAddress();
st.setString(1, str1); // {"social":1,"auth":"xxxx","action":"login","users":[{"given":"alex","social":1,"auth":"xxxx","sid":"12345"}],"sid":"12345"}
st.setString(2, str2); // "127.0.0.1"
ResultSet rs = st.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();
st.close();
Thank you for any hints
Alex
Re: How to pass jsonb and inet arguments to a stored function with JDBC?
From
"David G. Johnston"
Date:
However an SQLException is thrown, when trying to call the same function via JDBC driver 9.4.1208.jre7:Dear PostgreSQL users,I have a stored procedure defined as:CREATE OR REPLACE FUNCTION words_merge_users(Which I can successfully call at psql 9.5.3 prompt:
IN in_users jsonb,
IN in_ip inet,
OUT out_uid integer)
RETURNS integer AS
$func$
...............
$func$ LANGUAGE plpgsql;
# SELECT out_uid FROM words_merge_users('[{"sid":"abcde","auth":"xxxx","social":2,"given":"Abcde2","female":0,"stamp":1450102880}]'::jsonb, '0.0.0.0'::inet);
out_uid
---------
1
(1 row)ERROR: function words_merge_users(character varying, character varying) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.Being a JDBC newbie I wonder, how to perform the call properly in Java?
PreparedStatement st = mDatabase.prepareStatement("SELECT out_uid FROM words_merge_users(?, ?)");
String str1 = JSON.toString(users);
String str2 = mSession.getRemoteAddress().getAddress().getHostAddress();
st.setString(1, str1); // {"social":1,"auth":"xxxx","action":"login","users":[{"given":"alex","social":1,"auth":"xxxx","sid":"12345"}],"sid":"12345"}
st.setString(2, str2); // "127.0.0.1"
ResultSet rs = st.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();
st.close();
Just add casts like you did for the psql version.
SELECT out_uid FROM words_merge_users(?::jsonb, ?::inet);
David J.
> On Jun 14, 2016, at 7:33 AM, Alexander Farber <alexander.farber@gmail.com> wrote: > > Dear PostgreSQL users, > > I have a stored procedure defined as: > > CREATE OR REPLACE FUNCTION words_merge_users( > IN in_users jsonb, > IN in_ip inet, > OUT out_uid integer) > RETURNS integer AS > $func$ > ............... > $func$ LANGUAGE plpgsql; > > Which I can successfully call at psql 9.5.3 prompt: > > # SELECT out_uid FROM words_merge_users('[{"sid":"abcde","auth":"xxxx","social":2,"given":"Abcde2","female":0,"stamp":1450102880}]'::jsonb, '0.0.0.0'::inet); > > out_uid > --------- > 1 > (1 row) > > However an SQLException is thrown, when trying to call the same function via JDBC driver 9.4.1208.jre7: > > ERROR: function words_merge_users(character varying, character varying) does not exist > Hint: No function matches the given name and argument types. You might need to add explicit type casts. > > Being a JDBC newbie I wonder, how to perform the call properly in Java? > > PreparedStatement st = mDatabase.prepareStatement("SELECT out_uid FROM words_merge_users(?, ?)"); > > String str1 = JSON.toString(users); > String str2 = mSession.getRemoteAddress().getAddress().getHostAddress(); > > st.setString(1, str1); // {"social":1,"auth":"xxxx","action":"login","users":[{"given":"alex","social":1,"auth":"xxxx","sid":"12345"}],"sid":"12345"} > st.setString(2, str2); // "127.0.0.1" > > ResultSet rs = st.executeQuery(); > while (rs.next()) { > System.out.println(rs.getString(1)); > } > > rs.close(); > st.close(); > > Thank you for any hints > Alex Probably a permission or ownership issue. Are you using same credentials for jdbc and psql?
Re: How to pass jsonb and inet arguments to a stored function with JDBC?
From
Alexander Farber
Date:
Thank you, David -
PreparedStatement st = mDatabase.prepareStatement("SELECT out_uid FROM words_merge_users(?::jsonb, ?::inet)");
seems to work. Is it ok to call setString() then below?PreparedStatement st = mDatabase.prepareStatement("SELECT out_uid FROM words_merge_users(?::jsonb, ?::inet)");
String str1 = JSON.toString(users);
String str2 = mSession.getRemoteAddress().getAddress().getHostAddress();
st.setString(1, str1);
st.setString(2, str2);
ResultSet rs = st.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();
st.close();
On Tue, Jun 14, 2016 at 3:45 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
However an SQLException is thrown, when trying to call the same function via JDBC driver 9.4.1208.jre7:CREATE OR REPLACE FUNCTION words_merge_users(Which I can successfully call at psql 9.5.3 prompt:
IN in_users jsonb,
IN in_ip inet,
OUT out_uid integer)
RETURNS integer AS
$func$
...............
$func$ LANGUAGE plpgsql;
# SELECT out_uid FROM words_merge_users('[{"sid":"abcde","auth":"xxxx","social":2,"given":"Abcde2","female":0,"stamp":1450102880}]'::jsonb, '0.0.0.0'::inet);
out_uid
---------
1
(1 row)ERROR: function words_merge_users(character varying, character varying) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.Being a JDBC newbie I wonder, how to perform the call properly in Java?
PreparedStatement st = mDatabase.prepareStatement("SELECT out_uid FROM words_merge_users(?, ?)");
String str1 = JSON.toString(users);
String str2 = mSession.getRemoteAddress().getAddress().getHostAddress();
st.setString(1, str1); // {"social":1,"auth":"xxxx","action":"login","users":[{"given":"alex","social":1,"auth":"xxxx","sid":"12345"}],"sid":"12345"}
st.setString(2, str2); // "127.0.0.1"
ResultSet rs = st.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();
st.close();Just add casts like you did for the psql version.SELECT out_uid FROM words_merge_users(?::jsonb, ?::inet);David J.
Re: How to pass jsonb and inet arguments to a stored function with JDBC?
From
"David G. Johnston"
Date:
Please don't top-post.
Thank you, David -seems to work. Is it ok to call setString() then below?
PreparedStatement st = mDatabase.prepareStatement("SELECT out_uid FROM words_merge_users(?::jsonb, ?::inet)");
String str1 = JSON.toString(users);
String str2 = mSession.getRemoteAddress().getAddress().getHostAddress();
st.setString(1, str1);
st.setString(2, str2);
ResultSet rs = st.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();
st.close();
I don't understand the question...
David J.