0

I'm trying to store a row in a DB2 database table where the primary key is an autoincrement. This works fine but I'm having trouble wrapping my head around how to retrieve the primary key value for further processing after successfully inserting the row. How do you achieve this? @JdbcInsert only returns the amount of rows that were inserted ...

Gernot Hummer
  • 33
  • 5
  • Perhaps this can help http://www.openntf.org/internal/home.nsf/response.xsp?action=openDocument&documentId=84B68167F84911AF862579910036B4AD&MainID=40D5F2DFF8587C688625795A006F23C4 – Fredrik Norling Feb 20 '13 at 15:45
  • Thanks @FredrikNorling. From what I can read from the conversation, the problem was to get an insert to work at all. The insert itself works, it's just not giving back the generated key value, instead it hands back the amount of rows inserted. Whenever that newbie - blocking period on here (have to wait 8 hours) is over, I will post my approach as an answer as it's too long for a comment... – Gernot Hummer Feb 20 '13 at 17:10
  • What I could read there was one extra param added at the end, and that was id column that should be returned. But I don't know if it was implemented, no documentation about it as I have found. – Fredrik Norling Feb 20 '13 at 18:51
  • Now I see what you mean, will test that tomorrow. Thanks! – Gernot Hummer Feb 20 '13 at 19:04

2 Answers2

1

Since there does not seem to be a way to do this with SSJS (at least to me), I moved this particular piece of logic from my SSJS controller to a Java helper bean I created for JDBC related tasks. A Statement is capable of handing back generated keys (using the method executeUpdate()). So I still create my connection via @JdbcGetConnection, but then hand it in into the bean. This is the interesting part of the bean:

/**
 * SQL contains the INSERT Statement
 */    
public int executeUpdate(Connection conn, String SQL){

  int returnVal;
  Statement stmt = conn.createStatement();

  stmt.executeUpdate(SQL,
    Statement.RETURN_GENERATED_KEYS);

  if(!conn.getAutoCommit()) conn.commit();

  ResultSet keys = stmt.getGeneratedKeys();

  if(keys.next()){
    returnVal = keys.getInt(1);
  } else {
    returnVal = -1;
  }

  return returnVal;
}

If you insert more than one row at a time, you'll need to change the key retrieval handling, of course.

Gernot Hummer
  • 33
  • 5
0

In newer DB2 Versions you can transform every Insert into a Select to get automatic generated key columns. An example is:

select keycol from Final Table (insert into table (col1, col2) values (?,?))

keycol is the name of your identity column

The Select can be executed with the same @Function than your usual queries.

Ralf M Petter
  • 106
  • 5