Geting last insert ID

[deltoideus]

Well, does anyone know how to get the
Last Insert ID from the INSERT query?

conn.executeUpdate("INSERT...");

? insert ID ?

[chrisl]

Re: Geting last insert ID

Not sure what you mean, can you clarify?

[circuitshaman]

Re:Geting last insert ID

I'd like to know how to do the same so I'll clarify.

On a database writer (in my case mysql) there is an auto-incrementing int field called id in the table I am making an insert into. That number is created by mysql on insert. I would like to get that ID and send it to another mirth channel. The reason (though I'm sure obvious to you but I state just in case) that the id needs to be retrieved as part of the insert statement is that other inserts can potentially happen before a select query is made for the last entry; effectively corrupting the information passed to a new channel.

Does mirth provide a nice way of doing this or am I going to have to turn my database writer into mirth flavored javascript and perform a select hoping that mirth is never multi-threading HL7 messages? The ideal would be a variable automatically created on the channel by any database writer, ie, destination "my_db_writer" would have a "my_db_writer_record_id" mapped to the channel. If there is nothing like that is there a way to have the return of a insert query in javascript return that database ID?

[geraldb]

Re:Geting last insert ID

Take a look at the getGeneratedKeys() method:

http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html

Set your Database Writer to use JavaScript, use that method to get a ResultSet of the newly generated IDs, then insert them into the Channel Map or Global Map for use in other connectors.

Hope this helps.

[circuitshaman]

RETURN_GENERATED_KEYS attempt

I'm looking to do the same thing. However, the following didn't work:

var dbConn = DatabaseConnectionFactory.createDatabaseConnection('com.mysql.jdbc.Driver','jdbc:mysql://somdb.com:3306/testdb','dbuser','dbpassword');

var result = dbConn.executeUpdate("INSERT IGNORE INTO hl7_messages (message) VALUES ('who doesn't like bananas?')", dbConn.RETURN_GENERATED_KEYS);
globalMap.put('id_for_test',result);

dbConn.close();

This snippet of code results in the following error:

Can't find method com.webreach.mirth.server.util.DatabaseConnection.executeUpdate(string,org.mozilla.javascript.Undefined)

I've never been big into java so it's entirely possible that I've misunderstood the documentation. If so I'd appreciate being corrected.

As a side note, doing this type of exploratory code/testing is a serious pain in mirth. Is there a rhino shell or interactive interpreter available for mirth?

Regards,
-Max

[sween]

circuitshaman,

I couldnt get RETURN_GENERATED_KEYS to work either, but the below works. Its kind of hackish and will work fine if you are NOT worried about external connections making updates in between your call, this may work, it may not, but I tested it and it appears to return what you want.

var dbConn = DatabaseConnectionFactory.createDatabaseConnection('com.mysql.jdbc.Driver','jdbc:mysql://shportal.com:3306/shportal_prod','bleh','bleh');
var result = dbConn.executeUpdate("INSERT IGNORE INTO registrations (firstname) VALUES ('thundercats')"); 
var lastid = dbConn.executeCachedQuery('SELECT LAST_INSERT_ID()');
var bleh = "nothing";
while (lastid.next()) {
var bleh = lastid.getInt(1);
}
 
globalMap.put('last_id', bleh);
dbConn.close();

/shrug, hope this helps.

[mnowlin]

circuitshaman,Its kind of hackish and will work fine if you are NOT worried about external connections making updates in between your call, this may work

I'm more of a Postgres guy, but I did a little digging, and it looks like MySQL handles this correctly.

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

This is from http://dev.mysql.com/doc/refman/5.0/...last-insert-id

As long as your query and resulting last_insert_id() are over the same connection to MySQL, this should return the correct ID regardless of other external activity.

FWIW, Postgres provides a shortcut - "INSERT IGNORE INTO junk (data1) VALUES ('asdf') RETURNING seqnum". The "returning seqnum" bit returns the given column value that resulted from the Insert.

[circuitshaman]

that'll do...

I've done some testing with it and it looks good to me. I'll start running it on a fatty dataset and do some spot testing. Thanks for the help.

-Max

[lijun]

Seems "INSERT IGNORE INTO junk (data1) VALUES ('asdf') RETURNING seqnum" is not supported in the postgreSql < V8.2. For PG versions below 8.2, is there workaround? use currval?

[apatwa]

In order to get last_insert_id (or last update id in some cases) in MySQL,

var sql = "INSERT IGNORE INTO <TABLE> SET `<COLUMN1>`='<VALUE1>',`<COLUMN2>`='<VALUE2>', `<COLUMN3>`='<VALUE3>' ON DUPLICATE KEY UPDATE `<ID_COLUMN>`=LAST_INSERT_ID('<ID_COLUMN>')";

// all updates go here (if you want)
sql +=  ",`<COLUMN2>`='<VALUE2>', `<COLUMN3>`='<VALUE3>'"; 


var result = dbConn.executeUpdate(sql);

// done create/update at this point.

// to get last inserted/updated id, do this
var res = dbConn.executeCachedQuery("SELECT LAST_INSERT_ID() AS id");
var id;
if (res.next()) {
      id = id_res.getString('id');
}

// id is your last_insert_id