Hi,
I am writing some stored procedure for Derby. And I am confused about the
transaction settings for a Derby stored procedure using a nested connection.
Actually I am trying to modify the transaction isolation level to something
like Serializable or Read Committed, and also change the auto commit to be
false so that I can manually control the rollback or commit in the stored
procedure. What I am doing is just something like:
connection = DriverManager.getConnection
("jdbc:default:connection");
connection.*setAutoCommit(false)*;
connection
.*setTransactionIsolation(
Connection.TRANSACTION_READ_COMMITTED)*;
PreparedStatement insertToIndexPerson = connection
.prepareStatement("INSERT INTO index_time_person
(index_time, person_email) VALUES (?, ?)");
insertToIndexPerson.setTimestamp(1, the_time);
insertToIndexPerson.setString(2, the_person_email);
insertToIndexPerson.executeUpdate();
insertToIndexPerson.close();
connection.*commit()*;
I found both the autocommit mode and isolation level can be modified
successfully.
But when I read Derby guide documents (derbydev.pdf "Programming
database-side JDBC procedures), I found these items which is obvious forbids
what I was doing:
*In order to preserve transactional atomicity, database-side JDBC procedures
that use
nested connections have the following limitations.
• Cannot issue a commit or rollback, unless called within a CALL statement.
• Cannot change connection attributes such as auto-commit.
*
If these items are true for my stored procedure, then I cannot modify the
isolation level and neither change the autocommit mode.
And I am yet confused about the first statement "*Cannot issue a commit or
rollback, unless called within a CALL statement."* Could a Derby procedure
be called without a "CALL statement"?
I am new to Derby and quite curious about these. Thanks!
