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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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.

Browse pgsql-jdbc by date

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