eserver220
Posts: 41
|
Posted: 08/29/2006, 6:25 PM |
|
I noticed Richard used the code below to implement transaction. It can have atomic
database operation in custom event such as beforeUpdate/afterUpdate, but how can I
include the Record Update operation into this transaction. Is it possible for me to get
the very connection used to update database when form submitted, and rollback all the
database operation before/after the form submitted if any database error occurs during
db operation in custom events.
DBConnectionManager DBM;
Connection cn = null;
Statement stmt = null;
ResultSet rs = null;
int pk = 0;
Integer num = 0;
try {
DBM = DBConnectionManager.getInstance();
cn = DBM.getConnection("connection1");
cn.setAutoCommit(false);
stmt = cn.createStatement();
stmt.execute("update nextkey set id = id+1 where tablename='rep_composition'");
rs = stmt.executeQuery("select id from tablename where tablename='rep_composition'");
// Get new ID.
pk = Utils.convertToLong(rs.getInt("id")).intValue();
num = new Integer(pk);
// commit trans.
cn.commit();
} catch (Exception ex) {
try {
if (cn != null) {
cn.rollback();
}
e.getRecord().addError("Database error: " + ex.getMessage());
} catch (Exception ex2) {
e.getRecord().addError("Database error: " + ex2.getMessage());
}
} finally {
try {
if (rs != null) {
rs = null;
}
if (stmt != null) {
stmt = null;
}
if (cn != null) {
if (cn.isClosed() == false) {
cn.close();
}
cn = null;
}
} catch (Exception ex) {
e.getRecord().addError("Database error: " + ex.getMessage());
}
}
Best Regards!
|
|
|
eserver220
Posts: 41
|
Posted: 08/29/2006, 8:20 PM |
|
I have checked CCS 2.3 JDBCConnection.java. it seems ccs not support transaction so far.
Maybe I have to take a mixed programming approach. If the page contains critical business
logic, the only way to resolve this is to write without CCS IDE.
|
|
|
MWarloc
|
Posted: 08/30/2006, 6:01 AM |
|
In the BeforeExecuteUpdate and AfterExecuteUpdate event you can get Command abd then JDBCConnection. JDBCConnection class have private member named "conn". This is java.sql.Connection. You need change common file com.codecharge.db.JDBCConnection to allow get this member. In fact just add getter.
This way you can get java.sql.Connection from your JDBCConnection in BeforeExecuteUpdate.
Do some operation. Then record do his work.
And in AfterExecuteUpdate you can get THIS java.sql.Connection and check results and correct handle it. In example add errors to record. And don't close this connection here. It can brak error messages and perhaps some other functionality.
|
|
|
eserver220
Posts: 41
|
Posted: 08/30/2006, 11:10 PM |
|
Thanks a lot. I'll modify JDBCConnection.java and give it a try!
Quote MWarloc:
In the BeforeExecuteUpdate and AfterExecuteUpdate event you can get Command abd then JDBCConnection. JDBCConnection class have private member named "conn". This is java.sql.Connection. You need change common file com.codecharge.db.JDBCConnection to allow get this member. In fact just add getter.
This way you can get java.sql.Connection from your JDBCConnection in BeforeExecuteUpdate.
Do some operation. Then record do his work.
And in AfterExecuteUpdate you can get THIS java.sql.Connection and check results and correct handle it. In example add errors to record. And don't close this connection here. It can brak error messages and perhaps some other functionality.
|
|
|
eserver220
Posts: 41
|
Posted: 08/31/2006, 12:58 AM |
|
I'd like to follow the steps below:
1. Add a Session/Request variable Isdboperr.
2. Modify JDBCConnection.java , add conn.setAutoCommit(false) and set
Isdboperr = false during initialization.
3. Modify executeUpdate in JDBCConnection.java, if db operation error occurs
set Isdboperr = true
4. add custom event afterInsert/afterUpdate, at the end of the event let's judge
Whether Isdboperr is true, then we can rollback or commit the transaction.
after that set isdboperr = false;
Any one can give me a better solution?
Best Regards!
|
|
|
MWarloc
|
Posted: 08/31/2006, 1:29 AM |
|
Hm...
I think folowing is better way:
1. Don't modify JDBCConnection.executeUpdate(...)
Just modify JDBCConnection. Add getter for getting conn.
2. In BeforeExecuteUpdate set Autocommit false. (It'll be custom code. Not change common file)
3. Judge rollback or not in AfterExecuteUpdate. You can get ionformation about errors from JDBCConnection by of follow methods :
boolean hasErrors(), Vector getErrors(), String getErrorsAsString()
I think it's enough to decide is operation successful or not.
Why i think so.
1. I think you don't need any additional varibles.
2. add one getter method provide more easy way to update your code files again (if need) than chage any methods inside pattern. It can be helpfull when you update your CCS and all common file will be instaling again. They(new files) will not contain your changes. And if this file(s) is modifed and this is synchronic midification more then one file it can cause much problems.
Which pattern are using?
|
|
|
eserver220
Posts: 41
|
Posted: 08/31/2006, 3:05 AM |
|
Quote MWarloc:
Hm...
I think folowing is better way:
1. Don't modify JDBCConnection.executeUpdate(...)
Just modify JDBCConnection. Add getter for getting conn.
2. In BeforeExecuteUpdate set Autocommit false. (It'll be custom code. Not change common file)
3. Judge rollback or not in AfterExecuteUpdate. You can get ionformation about errors from JDBCConnection by of follow methods :
boolean hasErrors(), Vector getErrors(), String getErrorsAsString()
I think it's enough to decide is operation successful or not.
Why i think so.
1. I think you don't need any additional varibles.
2. add one getter method provide more easy way to update your code files again (if need) than chage any methods inside pattern. It can be helpfull when you update your CCS and all common file will be instaling again. They(new files) will not contain your changes. And if this file(s) is modifed and this is synchronic midification more then one file it can cause much problems.
Which pattern are using?
Your posts here helps me solve the problem which puzzled me for a long time, thanks again.
Wish you have a prosperous day!
Best Regards!
|
|
|
matheus
Posts: 386
|
Posted: 09/13/2006, 12:27 PM |
|
Okay okay, but this seems not the perfect.
Someone could do this transaction implementation with EditableGrid??
Seems like EditableGrid (in CCS2.3) create one connection to each row, so I tryed do something to resolve, without success.
Someone could do this?
_________________
Matheus Trevizan
Dynamix Software Ltda.
Blumenau SC Brasil
www.dynamix.com.br |
|
|
eserver220
Posts: 41
|
Posted: 09/17/2006, 7:32 PM |
|
I've already noticed this in EditableGridProcessor.java. If you need the same connection
to process next record, you have to make lots of modifications.
Maybe you can use request variable to save the cmd execution result, and determine
whether or not undo the operation based on that request variable.
|
|
|
eserver220
Posts: 41
|
Posted: 09/26/2006, 5:53 PM |
|
I think you can use the code below to get around the problem with Editgrid of transaction
implementation.
1. Use code below to retrieve corresponding parameter for jdbc connection creation.
HttpServletRequest req = e.getPage().getRequest();
HttpSession sess = req.getSession();
ServletContext ctx = sess.getServletContext();
String driver = ctx.getInitParameter("driver");
String url = ctx.getInitParameter("url");
String usr = ctx.getInitParameter("usr");
String ps = ctx.getInitParameter("passwd");
2. Save the connection in request variable, and execute Editgrid custom update with
that same jdbc connection, save another request variable as jdbc error indicator.
3. Once the jdbc error indicator is set, roll back all the operation so far, and cancel
all the custom update after.
Best Regards!
|
|
|