hello again Aby & Curt,
1. i"ll search forums on the errror aftter I try Curt's advice:
"There could be some problem with trying to execute your data object,
outside the context of the transaction, in order to see how many rows there
are. We ran into something like this once. Maybe try executing the DO for
this purpose before doing 'begin' on the transaction."
Some followups:
1.Why are you executing setSQLTextFullInsert when you are doing an update,
not an insert?
I did setSQLTextFullUpdate but it didnt'work. So just as a stab I tried INSERT
version. I see I pasted the INSERT version in my original post by accident,
sorry! didn't mean to confuse the issue. (BTW, I've done UPDATES with the
setSQLTextFullInsert command many times with no problems. (no smart but it
works!)
2. The other thing I noticed is that variables like sTask,hSubTask, which
form the Where clause are being used without being set to a value. Is
there something missing
I snipped them from the code listing to shorten it for the paste. I get those
values fine. hthe SQL being written is good. I'll dbl chk it all again in a
minute.
3. Request for Context:
What are you trying to do? How does this relate to values coming back from
the user? Do the rows share a common foreign key value? How are primary
key values handled? Is it possible to change primary key values?
My scenario:
User sees a Repeated. They want to update all or some of the rows. They enter
new values. hit SAVE. I manually handle the save via a FOR loop that dynamically
builds the UPDATE SQL. I have all the PK values from hiddens. . No user cannot
change a PK. I want one big TRX for this update.
My question is:
does this setSQLTextFullUpdate work the way I'm using it?
Or should I be using this kind of thing:
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;
}
If I use the above, how do I set the WHERE clause?
Anyone have any snippets of this? That would be helpful as I'm not even sure
what code to use to do stuff like this:
BEGIN...if ((trx != null) && (trx.isActive ()) )...etc
thanks!
janet
(Embedded
image moved Aby Mathew <[EMAIL PROTECTED]>
to file: 08/23/99 06:51 PM
pic13514.pcx)
To: Janet Traub/IS/SSC/THD,
[EMAIL PROTECTED]
cc:
Subject: Re: [ND] Transaction for Repeated's SAVE button
Hi Janet,
I am not very familiar with this message:
"spider.database.CSpTransaction.execute(): Called when database
transaction is
still 'active'. The current active one is
automatically 'rolledback'"
But remember seeing it in the forum recently. Somebody must have an
answer for you.
The other thing I noticed is that variables like sTask,hSubTask, which
form the Where clause are being used without being set to a value. Is
there something missing?
Aby
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, August 23, 1999 3:23 PM
To: [EMAIL PROTECTED]
Subject: Re: [ND] Transaction for Repeated's SAVE button
Hi Aby,
1. I tried setSqlTextFullUpdate (instead of INSERT) to agree with my
flavor of
DML, but it didn't work,
2. my WHERE clause that I build manually in my code is specific enough.
i.e. it
included the full key ( I construct it from hidden values). And it has
so syntax
errors (I copy/ paste it from the log into a SQL editor)
Hmm, do you know if the setSqlTextFullUpdate doesn't work nicely with
this type
of transction logic?
More info about what i'm doing, maybe you can suggest a better way:
I have a DO that is bound to my repeated. it's a MULTISQL DO, but when I
hit the
SAVE button, I'm not using ND's built-in UPDATE button cause I need to
do too
much manual validating.
so I just want a way to walk thru (in a FOR loop say) each row in my DO
and
specify the WHERE clause as well as the value of the columns I am
updating.
i thought just doing a FULL_TEXT_OVERRIDE and specify a SQl string then
addDO
with these command would do it:
doUpdate.setSqlTextFullUpdate(sSQL);
trx.addDataObject(doUpdate);
but darn, in then doesn't like this when I get out of the FOR loop and
try to
execute it:
trx.execute();
it says:
"spider.database.CSpTransaction.execute(): Called when database
transaction is
still 'active'. The current active one is
automatically 'rolledback'"
Do that error give you any ideas about what could be going wrong?
Can you suggest a better way to do this?
Thanks!
Janet
(Embedded
image moved Aby Mathew <[EMAIL PROTECTED]>
to file: 08/23/99 06:10 PM
pic09588.pcx)
To: Janet Traub/IS/SSC/THD,
[EMAIL PROTECTED]
cc:
Subject: RE: [ND] Transaction for Repeated's SAVE button
Hi Janet,
A quick look at your code told me a couple of things:
1. You are doing
>> setSqlTextFullInsert and
setSqlTextOverrideInsert
<<
instead of
>>setSqlTextFullUpdate and
setSqlTextOverrideUpdate.
<<
(ND needs much less of a reason to be terribly upset!)
2.
>>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?
<<<
When you are doing FullOverride, DynamicCriteria has no role to play.
What you need to do is add one more AND clause to the 'where' string you
are already constructing, which is row specific. Normally the key field
will not be given to the user for update, but will be kept in the form
as a hidden field. getDisplayFieldValue() for that field will give you a
CSpVector or a CSpString depending whether you have more than one row or
not (you need to check this). From this vector you get the key value for
each row.
If you still have problems we'll see.
Regards,
Aby
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, August 23, 1999 2:33 PM
To: [EMAIL PROTECTED]
Subject: [ND] Transaction for Repeated's SAVE button
**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]
_________________________________________________________________________
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]
pic13514.pcx
