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

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(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Tue, Jun 14, 2016 at 9:33 AM, Alexander Farber <
> alexander(dot)farber(at)gmail(dot)com> wrote:
>
>>
>> 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.
>
>