From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to pass jsonb and inet arguments to a stored function with JDBC? |
Date: | 2016-06-14 13:45:56 |
Message-ID: | CAKFQuwaCiohXy1Yn1yq6Nw9E4rrhPXTbsGfp+ZNZpRdRt+vDkw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, Jun 14, 2016 at 9:33 AM, Alexander Farber <
alexander(dot)farber(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2016-06-14 14:19:14 | Re: How to pass jsonb and inet arguments to a stored function with JDBC? |
Previous Message | Alexander Farber | 2016-06-14 13:33:01 | How to pass jsonb and inet arguments to a stored function with JDBC? |