java -postgresql last inserted id on insertion not getting

Question

I have a function for insertion in postgresql as follows:

CREATE OR REPLACE FUNCTION insert_orderhead(order_id integer, order_dt text, customer_id integer, route_id integer, routenum integer, ordertype text, create_station_id integer, create_stationtype text, create_time text, create_user_id integer, tran_time text, tran_user_id integer) RETURNS integer AS $BODY$
INSERT INTO ordermaster
VALUES(DEFAULT,
       order_dt,
       customer_id,
       route_id,
       routenum,
       ordertype,
       create_station_id,
       create_stationtype,
       create_time,
       create_user_id,
       tran_time,
       tran_user_id) returning order_id $BODY$ LANGUAGE SQL VOLATILE COST 100;


ALTER FUNCTION insert_orderhead(integer, text, integer, integer, integer, text, integer, text, text, integer, text, integer) OWNER TO postgres;

And I am using java to insert and I am calling as follows:

cstorderhead = conn.prepareCall("{call insert_orderhead(?,?)}");
                    cstorderhead.setString(1, order_date);
                    cstorderhead.setInt(2, custidup);
                       .........
                    cstorderhead.executeUpdate();

The value is getting inserted correctly.

I need to return the last inserted id here it is the serial auto increment.

I tried as follows:

ResultSet rstd = cstorderhead.getGeneratedKeys();
                    if (rstd.next()) {
                        int newId = rstd.getInt(1);
                            out.print("Value returned=="+newId);
                    }

But it is not returning the last inserted id. What change I need to make my code to get it?

This is the table definition:

create table ordermaster 
(
  order_id serial NOT NULL, order_dt text, customer_id integer, route_id integer, routenum integer, ordertype text, create_station_id integer, create_stationtype text, create_time text, create_user_id integer, tran_time text, tran_user_id integer, CONSTRAINT order_id PRIMARY KEY (order_id)
);

If you want to get a generated key from a stored procedure (or function), then you will need to return it yourself from your function. The JDBC generated keys facility (which BTW needs to be explicitly enabled for a statement) only works for normal DML, and is not specified for callable statements. — Mark Rotteveel
– Mark Rotteveel, Commented Oct 19, 2015 at 11:00
Unrelated but: why are you passing a date as a String. Dates should never be passed nor stored as string values. — user330315
– user330315, Commented Oct 19, 2015 at 11:06
@a_horse_with_no_name my date is in string format yyyymmdd in string format so it is not a problem — Santhucool
– Santhucool, Commented Oct 19, 2015 at 11:08
Passing dates as strings is a problem. What prevents you of storing 20150231? — user330315
– user330315, Commented Oct 19, 2015 at 11:10
I said it was unrelated, but it is still a very bad idea to store a date in a varchar column. — user330315
– user330315, Commented Oct 19, 2015 at 11:19

score 4 · Accepted Answer · 2015-10-19 12:55:00Z

4

You can't use getGeneratedKeys() with a CallableStatement. However as your insert is "hidden" in the function, you also can't use a regular PreparedStatement with getGeneratedKeys() because the driver will append a RETURNING clause to the SQL statement - which doesn't work with a function call.

I see two solutions to your problem:

1. Change the function to return the value:

CREATE OR REPLACE FUNCTION insert_orderhead(
    p_order_id integer, 
    p_order_dt text, 
    p_customer_id integer, 
    p_route_id integer, 
    p_routenum integer, 
    p_ordertype text, 
    p_create_station_id integer, 
    p_create_stationtype text, 
    p_create_time text, 
    p_create_user_id integer, 
    p_tran_time text, 
    p_tran_user_id integer)
  RETURNS integer AS
$BODY$
   INSERT INTO ordermaster 
     (order_dt, customer_id, route_id, routenum, ordertype, create_station_id, create_stationtype, create_time,create_user_id,tran_time, tran_user_id)
   values 
     (p_order_dt, p_customer_id, p_route_id, p_routenum, p_ordertype, p_create_station_id, p_create_stationtype, p_create_time, p_create_user_id, p_tran_time, p_tran_user_id) 
   returning  orderline_id;
$BODY$
  LANGUAGE sql VOLATILE
  COST 100;

Note that I removed the unused order_id parameter and renamed the other two parameters - because it is usually not a good idea to have parameters with the same name as columns.

Then in your code you can use the function like this:

PreparedStatement pstmt = con.prepareStatement("select insert_order(?,?)");
pstmt.setString(1, "foo");
pstmt.setInt(2, 42);

rs = pstmt.executeQuery();

if (rs.next()) {
  System.out.println("Generated ID is: " + rs.getInt(1));
}

2. Query the sequence manually:

After calling your function, you can run another statement to obtain the last generated sequence value:

ResultSet rs = stmt.executeQuery("select lastval()");
if (rs.next()) {
  System.out.println("Generated ID is: " + rs.getInt(1));
}

The above will only work if the function doesn't insert into multiple tables. If it does, you need to use currval() with the sequence name:

ResultSet rs = stmt.executeQuery("select currval('ordermaster.order_id_seq')");
if (rs.next()) {
  System.out.println("Generated ID is: " + rs.getInt(1));
}

edited Oct 19, 2015 at 12:55

answered Oct 19, 2015 at 11:03

user330315

Sign up to request clarification or add additional context in comments.

12 Comments

Santhucool Over a year ago

Getting error ` You might need to add explicit type casts. Position: 8Error occurred while adding order details`

Santhucool Over a year ago

@ a_horse_with_no_name updated. please check it budddy!!

Santhucool Over a year ago

@ a_horse_with_no_name updated the stored procedure as per your suggestion, please check it buddy. help me out :(

user330315 Over a year ago

@Santhucool: I don't see a create table statement in your question. But the error message sounds as if you are not using the correct data types for either the parameters or the setXXX() call.

Santhucool Over a year ago

@ a_horse_with_no_name please check above!!

|

Collectives™ on Stack Overflow

java -postgresql last inserted id on insertion not getting

1 Answer 1

1. Change the function to return the value:

2. Query the sequence manually:

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1. Change the function to return the value:

2. Query the sequence manually:

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related