-Aaron Bono
On 6/17/06, Ash Grove <[EMAIL PROTECTED]> wrote:
Locks are released when the containing transaction
commits. There is no explicit "release."
Instead of calling "begin" and "commit" as statements,
I do something more like below. As Aaron mentioned,
this is JDBC, not SQL. Sorry people.
try {
...
conn.setAutoCommit(false);
//do the insert on the table that generates the
primary key via a sequence
PreparedStatement pstmt =
conn.prepareStatement ("my prepared statement");
pstmt.executeUpdate();
//your prepared statement above should do an
//insert on a table that calls nextval().
//Calling currval() below will guarantee that
you'll get
//the value created by the insert statement
//Check out the documentation on sequence
functions
//get the new primary key
String get_pkey = "{ ? = call currval('my_seq')
}";
CallableStatement = conn.prepareCall(get_pkey);
cstmt.registerOutParameter(1, Types.BIGINT);
cstmt.execute();
long new_pkey = cstmt.getLong(1);
//do all of your updates/inserts on tables using
new_pkey as a foreign key
//I like to do this in batches
Statement stmt = conn.createStatement();
stmt.addBatch("insert into... )
stmt.addBatch("update whatever set... )
stmt.executeBatch ();
conn.commit();
stmt.close();
conn.close();
} catch(SQLException e1) {
//do something with error 1
if (conn != null) {
try {
conn.rollback();
} catch(SQLException e2) {
//do something with error 2
}
}
}
