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

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: How to pass jsonb and inet arguments to a stored function with JDBC?
Date: 2016-06-14 13:33:01
Message-ID: CAADeyWg5dBSBeZ2886W7A96EAsRAiJk=w4NRzUNVtLcq12rFpg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2016-06-14 13:45:56 Re: How to pass jsonb and inet arguments to a stored function with JDBC?
Previous Message David G. Johnston 2016-06-14 13:32:15 Re: Index seems "lost" after consecutive deletes