How to pass jsonb and inet arguments to a stored function with JDBC?

On Tue, Jun 14, 2016 at 9: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();

​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?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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?

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();

Regards
Alex

On Tue, Jun 14, 2016 at 3:45 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

​Please don't top-post.

On Tue, Jun 14, 2016 at 10:44 AM, Alexander Farber <
alexander.farber@gmail.com> wrote:

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?

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.​

​