Retrieving Primary Key Generated By Trigger (Java)

Question

Here's what I'm attempting to do. I have a Java program that decomms a telemetry stream into the individual raw fields. I am passing these raw fields values into a MySQL table where each column is one of the fields. In this database I also have a view that grabs all the telemetry data and calculates a few new derived columns based on raw data (e.g. raw counts to engineering units). In my Java program, after insertion I would like to grab the corresponding row from the VIEW (raw + derived) and pass that data along elsewhere.

Originally I thought I could simply insert the raw data into the VIEW and have the row returned to me in a ResultSet in the Java program. Unfortunately the data isn't returned in a ResultSet.

What I'm attempting to do now is insert the raw data into the table (this part works), get the primary key, then lookup the row from the VIEW. The part I'm struggling with is retrieving the primary key from the INSERT. I'm using a PreparedStatement generated from my Connection object and have supplied it with Statement.RETURN_GENERATED_KEYS. However, when I call getGeneratedKeys() on my statement after I call executeUpdate() my ResultSet is always empty. I can watch it insert the rows into the table while it's running...what am I doing wrong?

Can I not retrieve the generated primary key in this fashion if the primary key is generated via a trigger?

UPDATE: I've tried swapping out the Statement.RETURN_GENERATED_KEYS for a String array with the name of the primary key column, but that doesn't seem to work either.

I apologize for not including my code, but it would be difficult for me to do so. I've attempted to describe what I'm doing to the best of my abilities.

Answer 1

Ive seen this in another post. That claimed to work. Is this what yours looks like?

Statement stmt = db.prepareStatement(query,  Statement.RETURN_GENERATED_KEYS);
numero = stmt.executeUpdate();

ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()){
    risultato=rs.getInt(1);
}

I have used SP's and here is an example of what I did. I used an in/out parm to get it back...

CREATE DEFINER=`scaha`@`localhost` PROCEDURE `updateprofile`(
    IN in_idprofile INT(10),
    IN in_usercode VARCHAR(50),
    IN in_pwd VARCHAR(50),
    IN in_nickname VARCHAR(50),
    IN in_isactive tinyint,
    IN in_updated timestamp,
    OUT out_idprofile INT(10))
BEGIN

/*  If the idprofile is < 1 then we insert a new record.. */
/*  otherwise its an update */

if (in_idprofile < 1) then

    insert into scaha.profile (usercode, pwd, nickname, isactive,updated) values (in_usercode,in_pwd,in_nickname,in_isactive,in_updated);
    SET out_idprofile = LAST_INSERT_ID();

else 
    update scaha.profile set usercode = in_usercode, pwd = in_pwd, nickname = in_nickname, isactive = in_isactive, updated = in_updated 
    where idprofile = in_idprofile;
    SET out_idprofile = in_idprofile;

end if;

END