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