CodeCharge Studio
search Register Login  

Web Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> Java

 How to implement transaction in CCS?

Print topic Send  topic

Author Message
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!


View profile  Send private message
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.
View profile  Send private message
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.
View profile  Send private message
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!
View profile  Send private message
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!
View profile  Send private message
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
View profile  Send private message
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.
View profile  Send private message
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!
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

PHP Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.