Skip site navigation (1) Skip section navigation (2)

Passing date and smallint (etc) parameters to functions from Java

From: Sean Elliott <Sean(dot)Elliott(at)which(dot)co(dot)uk>
To: "'pgsql-jdbc(at)postgresql(dot)org'" <pgsql-jdbc(at)postgresql(dot)org>
Subject: Passing date and smallint (etc) parameters to functions from Java
Date: 2004-03-03 10:33:27
Message-ID: 7928EBAE915DD411B2D20008C75D394805AB227D@vivaldi.which.co.uk (view raw or flat)
Thread:
Lists: 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';



CONFIDENTIAL NOTICE
This communication contains information which is confidential and may also
be privileged. It is for the exclusive use of the intended recipient(s). If
you are not the intended recipient please note that any distribution,
copying or use of this communication or the information in it is strictly
prohibited. If you received this communication in error, please notify us by
e-mail or by telephone (020 7770 7000) and then delete the e-mail and any
copies of it.

pgsql-jdbc by date

Next:From: James RobinsonDate: 2004-03-03 14:55:08
Subject: Re: calling function
Previous:From: Paul ThomasDate: 2004-03-03 09:14:34
Subject: Re: No Exception thrown when there is a constraint violation, and delete fails

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group