Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> wrote:
On Tue, Jun 14, 2016 at 9:33 AM, Alexander Farber <alexander.farber@xxxxxxxxx> 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.



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux