Passing date and smallint (etc) parameters to functions from Java - Mailing list pgsql-jdbc

This is how you pass date and smallint parameters to a function via JDBC.

It does work without casting to date if you have a Date object but it is more convenient to use a String.

          public InWOLStatus(String[] argv) throws ClassNotFoundException, SQLException

          {

                   String          host = argv[0];

                   String          database = argv[1];

                   String          username = argv[2];

                   String          password = argv[3];

                   Timestamp   startTimestamp;

                   // Load the driver

                   Class.forName("org.postgresql.Driver");

                   // Connect to the db

                   conn = DriverManager.getConnection("jdbc:postgresql://" + host + "/" + database, username, password);

                   // Transactions span multiple statements

    conn.setAutoCommit(false);

                   // Get MetaData to confirm connection

                   dbmd = conn.getMetaData();

                   System.out.println("Connection to " + dbmd.getDatabaseProductName() + " " + dbmd.getDatabaseProductVersion() + " successful.\n");

                   // Create a statement that we can use throughout

                    stat = conn.createStatement();

                   // Get current database time - part of primary key needed later

                   startTimestamp = PostgreSQLUtils.getCurrentTimestamp(conn);

                   System.out.println("timestamp is " + startTimestamp);

                   sql = "insert into ifc_feed_instance select " + FeedDefinition.FED_IN_STATUS_CHANGE

                   + ", '" + startTimestamp + "', null, '" + FeedDefinition.ERR_OK + "', null, '" + startTimestamp + "'";

                   stat.executeUpdate(sql);

//                 CallableStatement cs = conn.prepareCall("{ ? = call ifc_send_status_change( date(?), int2(?) )}");

                   CallableStatement cs = conn.prepareCall("{ ? = call ifc_send_status_change( cast(? as date), cast(? as smallint) )}");

//                 CallableStatement cs = conn.prepareCall("{ ? = call ifc_send_status_change( ?, int2(?) )}");

                   java.sql.Date date;

                   date = new java.sql.Date(2002 - 1900, 02 - 1, 26);

                   System.out.println("date is " + date);

                   cs.registerOutParameter(1, Types.INTEGER);

                   cs.setString(2, "2002-02-26");

//                 cs.setDate(2, date);

                   cs.setInt(3, 1);

                   // 2002-02-26, 1

                   cs.execute();

                   conn.commit();

                   int rowCount = cs.getInt(1);

                   System.out.println("row count is " + rowCount);

                   cs.close();

                   // Feed complete

                   sql =

                   "update        ifc_feed_instance set end_ts = current_timestamp, update_ts = current_timestamp "

                   +       "where         feed_id = " + FeedDefinition.FED_IN_STATUS_CHANGE

                   +       "and            start_ts = " + startTimestamp;

//                 stat.executeUpdate(sql);

                   // Close database connection

                   conn.close();

          }

For completeness this is the function:

create or replace function ifc_send_status_change(date, smallint)

returns int as

'

    declare

        a_file_creation_dt alias for $1;

        a_file_sequence_no alias for $2;

        v_now timestamp;

        v_row_count int;

    begin

        v_now := ''now'';

        raise notice ''%: in function ifc_send_status_change'', v_now;

        raise notice ''%: a_file_creation_dt %'', v_now, a_file_creation_dt;

        raise notice ''%: a_file_sequence_no %'', v_now, a_file_sequence_no;

        insert into acs_xml_registration

        (

            feed_id,                  start_ts,             record_seq_no,            order_id,

            action_ts,                action_cd,            status_cd,                feed_error_cd,

            feed_error_tx,            title_tx,             forename_tx,              initials_tx,

            surname_or_company_tx,    postcode_cd,          address_name_or_no_tx,    address_street_tx,

            address_town_tx,          address_county_tx,    country_iso_cd,           country_name_tx,

            external_product_cd,      first_pay_dt

        )

        select  2, current_timestamp, record_seq_no, 1,

                change_dt, change_cd, ''X'' as status, ''O'',

                '''' as feed_error_tx, title_tx, forename_tx, initials_tx,

                surname_or_company_tx, postcode_cd, address_line_1_tx, address_line_2_tx,

                address_town_tx, address_county_tx, ''UK'' as country_iso_cd, ''United Kingdom'',

                product_cd, first_pay_dt

        from    hld_in_hlcol1_wol_status

        where   file_creation_dt = a_file_creation_dt

        and     file_seq_no = a_file_sequence_no;

        -- How many rows affected?

        get diagnostics v_row_count := row_count;

        return v_row_count;

    end;

' language 'plpgsql';