first off, here's a utility method that might be helpful:
/**
* When you execute getValue on a CSpDisplayField, you get a
CSpValue. This could represent
* a simple value, or a CSpVector of simple values, or a CSpVector
containing CSpVectors,
* depending on the number of rows and the hierarchical structure of the page.
* <br><br>
* This recursive utility method returns a Vector of simple CSpValues that
flattens
* out the passed CSpValue, with the values in the order in which they
appeared on the page,
* left to right and top to bottom.
* @param fieldvalue Returned from getValue
* @return Vector Contains all the simple CSpValues
* @see getWebVarOccurrences
*/
public static Vector extractSimpleValues(CSpValue fieldvalue) throws
Exception
{
try
{
Vector returnvector = new Vector();
if (!(fieldvalue instanceof CSpVector))
{
returnvector.addElement(fieldvalue);
}//if (!(fieldvalue instanceof CSpVector))
else
{
for (Enumeration e=((CSpVector)
fieldvalue).elements();e.hasMoreElements();)
{
CSpValue nextvalue = (CSpValue) e.nextElement();
Vector v = extractSimpleValues(nextvalue);
//add returned values to the list
for (Enumeration f = v.elements();f.hasMoreElements();)
{
CSpValue anothervalue = (CSpValue) f.nextElement();
returnvector.addElement(anothervalue);
}//for (Enumeration f = v.elements();f.hasMoreElements;)
}//for (Enumeration e=fieldvalue.elements();e.hasMoreElements;)
}//else
return(returnvector);
}//try
catch(Exception ex)
{
throw( new Exception("extractSimpleValues, unexpected exception:" + ex));
}//catch(Exception ex)
}//public static Vector extractSimpleValues( CSpValue fieldvalue)
code in actual page:
don't know if it works, but it does at least compile
also, there is field.getValue(i), where i is the rowindex. I have never
used this, not sure what happens when you overshoot, it might make things
a bit simpler below.
-- Curt
// This file has been generated by NetDynamics Studio on Mon Aug. 23, 1999
17:51
package curttest;
import java.awt.event.*;
import spider.event.*;
import spider.database.*;
import spider.visual.*;
import spider.util.*;
import spider.session.*;
import spider.*;
import java.util.*;
//this import is for the utility class that contains static method
extractSimpleValues
import edu.harvard.hms.hmsapi.netdynamics.*;
//[[SPIDER_CLASS BEGIN
public class BlankPage extends spider.visual.CSpPage
//]]SPIDER_CLASS END
{
//[[SPIDER_EVENTS BEGIN
//[[SPIDER_EVENT<this_onAfterInitEvent>
public int this_onAfterInitEvent(CSpInitEvent event)
{
//build an array containing references to the fields (not primary
key hidden field)
//array is an instance variable
_fieldarray = new CSpDisplayField[_num_of_fields]; //whatever
the number of fields actually is
_fieldarray[0] = getDisplayField("*tbLastName"); //e.g.
_fieldarray[1] = getDisplayField("*tbFirstName"); //e.g.
//create instance variable reference to data object
_dataobject=(CSpMultiSQL) CSpider.getDataObject("doSomeFile");
//build an array containing references to the 'bound' data object
columns
//(not bound in pointy-clicky studio sense), in the order of
fields in _fieldarray,
//such that _columnarray[n] will be updated with the value from
_fieldarray[n]
_columnarray = new CSpDataField[_num_of_fields]; //whatever
the number is
_columnarray[0] = _dataobject.getDataField("TABLE_NAME_LAST_NAME");
_columnarray[1] = _dataobject.getDataField("TABLE_NAME_FIRST_NAME");
//create a reference to hidden field containing primary key values
_primarykeyfield = getDisplayField("hdEmployeeID");
//get position of primary key column in data object
_primarykeycolpos =
_dataobject.getDataFieldPosition("TABLE_NAME_EMPLOYEE_ID");
return (PROCEED);
}
//]]SPIDER_EVENT<this_onAfterInitEvent>
//[[SPIDER_EVENT<btUpdate_onWebEvent>
public int btUpdate_onWebEvent(CSpWebEvent event)
{
int command = PROCEED;
try
{
//build array containing vectors of returned values.
//each entry in the array will be a vector containing the
repeating values for
//one field.
Vector[] valuesarray = new Vector[_num_of_fields];
for (int i = 0; i<_num_of_fields; i++)
{
valuesarray[i] =
NetDynamicsUtility.extractSimpleValues( _fieldarray[i].getValue());
}//for (int i = 0; i<_num_of_fields; i++)
//build vector containing primary key values
Vector primarykeyvaluesvector =
NetDynamicsUtility.extractSimpleValues( _primarykeyfield.getValue());
//create and begin transaction
CSpTransaction transaction = new CSpTransaction();
if (!transaction.begin("datasourcename"))
{
throw(new Exception("could not begin transaction"));
}//if (!transaction.begin("datasourcename"))
//now loop through and update the rows
for (int j = 0; j<primarykeyvaluesvector.size(); j++)
{
_dataobject.clearDynamicCriteria();
_dataobject.clearAllValues();
//set values from fields to columns
for (int k = 0; k<_num_of_fields; k++)
{
_columnarray[k].setValue((CSpValue)
valuesarray[k].elementAt(j));
}//for (int k = 0; k<_num_of_fields; k++)
//set where clause based on primary key
_dataobject.addDynamicCriterion(_primarykeycolpos,
CSpCriteriaSQLObject.EQUAL_TO_STR_O
CSpCriteriaSQLObject.EQUAL_TO_STR_OPERATOR, (CSpValue)
primarykeyvaluesvector.elementAt(j));
//execute the do in the context of the transaction
transaction.executeUpdate(_dataobject);
if (!transaction.succeeded())
{
if (transaction.isActive())
{
transaction.rollback();
}//if (transaction.isActive())
throw (new Exception("update failed for
row #"+j));
}//if (!transaction.succeeded())
}//for (int j = 0;
j<primarykeyvaluesvector.size(); j++)
//if all went well, commit the transaction
if (transaction.isActive())
{
transaction.commit();
}//if (transaction.isActive())
}//try
catch (Exception ex)
{
CSpLog.send(this, CSpLog.ERROR, "btUpdate_onWebEvent,
unexpected exception: "+ex);
}//catch (Exception ex)
return(command);
}
//]]SPIDER_EVENT<btUpdate_onWebEvent>
//]]SPIDER_EVENTS END
//instance variables
private int _num_of_fields = 2;//whatever it actually is, number
of fields logically
//bound to d.o. columns
private CSpDisplayField[] _fieldarray;
private CSpMultiSQL _dataobject;
private CSpDataField[] _columnarray;
private CSpDisplayField _primarykeyfield;
private int _primarykeycolpos; //position of primary key in data
object
}//public class...
At 07:10 PM 8/23/99 -0400, [EMAIL PROTECTED] wrote:
>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]
>
>
_________________________________________________________________________
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]
