**Help! This one needs a speedy reply, please!**
I'm trying to make my Save button UPDATE all rows in my Repeated in one nice
Transaction. But alas, I'm getting an error right after I call trx. execute():
that says:
"right after I call the trx. execute()....I get this error:
"spider.database.CSpTransaction.execute(): Called when database transaction is
still 'active'. The current active one is
automatically 'rolledback"
This is my first time doing a "Trx on a Repeated" stuff, so likely I've got
something messed up.
Way down below is my code. (I've also attached it in case that's easier to read)
You'll notice I'm doing this with my DO:
DO.setSqlTextOverrideInsert(CSpSQLObject.FULL_TEXT_OVERRIDE);
for (int i=numOfRows-1; i>=0; i--) {
sSQL = new String(...);
DO.setSqlTextFullInsert(sSQL);
trx.addDataObject(DO);
}
if ((trx != null) && (trx.isActive ()) )
{
trx.execute();
}
I wonder if maybe i have to do this kind of thing instead:
int numOfRows = _questionIndex.size();
for (int i=numOfRows-1; i>=0; i--) {
DO.clearAllValues();
DO.setValue(0,name);
DO.setValue(1,address.get(i));
DO.addDataObjec(tDOt);
}
insertTrans.execute();
if (!insertTrans.succeeded()) {
returnValue = false;
}
...
I would try that but my only problem (a very basic embarrassing one!) how do I
specifiy what ROW (i.e the WHERE clause) I'm setting those values for? NOTE: I'm
doing UPDATE not an INSERT on each row, so would I have to do "setDynCriteria"
on the DO in each row (i.e inside the FOR loop) before I then do a setValue?
Any pseuodocode, snippets are appreciated!
Thanks....below is my code.....Janet
=====================================================
//[[SPIDER_EVENT<BtnSave_onWebEvent>
public int BtnSave_onWebEvent(CSpWebEvent event)
{
int command = PROCEED;
CSpTransaction trx = null;
CSpMultiSQL doMulti;
int update = 0;
int nNumRows;
try {
trx = new CSpTransaction();
if (trx.begin ("dsProjMgmtDB"))
{
// determine number of displayed rows
doMulti = (CSpMultiSQL) CSpider.getDataObject("doWPBdgtLevel");
doMulti.execute();
nNumRows = doMulti.getNumOfRows();
//get a handle to the dummy DO to use in the UPDATE transaction
CSpMultiSQL doUpdate = (CSpMultiSQL) CSpider.getDataObject("doDummy");
//this is cheap trick we need to do to get around a ND bug.
//must do a dummy setValue on one of the fields to get the insert to
work
doUpdate.setValue ("informix_iproj_wp_bdgt_iproj_id", new
CSpInteger(0));
//NOTE: it doesn't seem to matter if I use setSqlTextOverrideInsert
//or setSqlTextOverrideUpdate
doUpdate.setSqlTextOverrideInsert(CSpSQLObject.FULL_TEXT_OVERRIDE);
for(int i = 0; i < nNumRows; i++){
//here I get the hidden fields & current values
//next I call this method to see if anything has been changed
update = isUpdate(hStart, sStart, hFinish, sFinish,
hHrs, sHrs, hStatus, sStatus);
// if row is being updated
if (update > 0){
//next I build up the SQL syntax, and it's perfectly correct!
//Build the SQL for the update of the current table
sSQL = new String("UPDATE iproj_wp_bdgt SET " +
"plnd_bgn_dt = '" + sStart + "', plnd_end_dt = '" + sFinish +
"', iproj_wp_stat_cd = " + sStatus + ", wp_task_bdgt_hrs = " +
sHrs + ", last_upd_user_id = '" + sUserID + "', last_upd_ts =
CURRENT " +
"WHERE iproj_id = '" + sProjID + "' AND iproj_wp_nm = '" + sWP +
"' AND wp_phs_cd = " + sPhs + " AND wp_seg_cd = " + sSeg +
" AND wp_step_cd = " + sStep + " AND wp_task_cd = " + sTask +
" AND wp_sub_task_id = '" + hSubtask + "'");
doUpdate.setSqlTextFullInsert(sSQL);
//clone the DO to make an identical copy of it to hold until
execution
trx.addDataObject(doUpdate);
}//end of if row is being updated.
else
{//nothing to update
CSpHtml.sendMessage ("nothing to update <BR>");
//do something here
}//end of if (update > 0)
}//for loop end.
//if something has been added to the trx, execute the transaction
if ((trx != null) && (trx.isActive ()) )
{
trx.execute();
}
}
} //end of the try
catch (Exception ex)
{
/// JT later fix or remove, or maybe OK. chk how this exception affects
trx.
CSpHtml.sendMessage ("An error occurred while saving the " +
"budgeting information for this segment. Please notify a " +
"ProjTrac Administrator. Details: "+ ex + " <BR>");
}
finally
{
//NOTE: this code is executed regardless if an exception
//occurred or not.
//HELP!!! Here's where it dies. . it hits this IF test:
//It says there's no active TRX.
//this gets written to Software messages:
// if ((trx != null) && (trx.isActive ()) )
//and that is FALSE, so it won't go further. if I remove that if
//and force it to go furhter, sure enough I get this error:
//"spider.database.CSpTransaction.commit: Called when database transaction
//is NOT 'active'. Call has been ignored"
if ((trx != null) && (trx.isActive ()) )
{
if (trx.succeeded ())
{
trx.commit();
CSpHtml.sendMessage("commit the trx <BR>");
//load the same page
command = load("PgBudgetLevel");
}
else
{
trx.rollback();
//failure, display error page
CSpString sErrMsg = new CSpString
("An error occurred while saving the budgeting information "
+
"for this segment.<BR>Please notify a ProjTrac
Administrator.");
CSpider.putUserSessionObject("soErrMsg",sErrMsg);
command = load("PgErrorPage");
}
} //if active transaction
else
{//no active transaction. load same page...though really do nothing! return
(SKIP)?
//load the same page
command = load("PgBudgetLevel");
}
} //end of the try-catch-finally
return(command);
}
//]]SPIDER_EVENT<BtnSave_onWebEvent>
//]]SPIDER_EVENTS END
}
============================================
THX!
Janet
_________________________________________________________________________
For help in using, subscribing, and unsubscribing to the discussion
forums, please go to: http://www.netdynamics.com/support/visitdevfor.html
For dire need help, email: [EMAIL PROTECTED]
