Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     When a MODIFY fails a subsequent MODIFY doesn't update
Go
New
Search
Notify
Tools
Reply
  
When a MODIFY fails a subsequent MODIFY doesn't update
 Login/Join
 
Virtuoso
posted
Following is a REPRO of a problem I had:
  
-SET &ECHO=ALL;
-* File modprob.fex
-DEFAULT &DB=SQLMSS
-*APP HOLD BASEAPP 
-*TABLE FILE CAR
-*SUM SALES RETAIL_COST DEALER_COST
-*BY CAR BY MODEL
-*WHERE MODEL OMITS '2000'
-*ON TABLE HOLD AS MODPROB FORMAT &DB
-*END
-*-RUN
-*EX -LINES 2 EDAPUT ACCESS,MODPROB,C,FILE
-*SEGNAME=SEG01,  TABLENAME=MODPROB, KEYS=0, WRITE=YES, $
-*-EXIT
-* 2
TABLE FILE CAR
SUM SALES RETAIL_COST 
-*DEALER_COST 
COMPUTE DEALER_COST/I2=2; 
BY CAR BY MODEL
WHERE MODEL CONTAINS 'DOOR'
ON TABLE HOLD AS MODINP
END
-RUN
-* 3
MODIFY FILE MODPROB
FIXFORM FROM MODINP
MATCH *
ON NOMATCH INCLUDE
ON MATCH INCLUDE
DATA ON MODINP
END
-RUN
-* 4
TABLE FILE CAR
SUM SALES RETAIL_COST DEALER_COST 
BY CAR BY MODEL
WHERE MODEL CONTAINS 'AUTO'
ON TABLE HOLD AS MODINP
END
-RUN
-* 5
MODIFY FILE MODPROB
FIXFORM FROM MODINP
MATCH *
ON NOMATCH INCLUDE
ON MATCH INCLUDE
DATA ON MODINP
END

Notes:
1. Create a database table. This is done once, then the ACX file is replaced for KEYS=0 and the KEYS in the database are deleted.
2. Create input to the table with a field having a wrong format.
3. When updating the table, you get an error.
4. Create a "good" input.
5. When updating the table, all records are rejected.
See ECHO:
  
 -* 2
 TABLE FILE CAR
 SUM SALES RETAIL_COST
 -*DEALER_COST
 COMPUTE DEALER_COST/I2=2;
 BY CAR BY MODEL
 WHERE MODEL CONTAINS 'DOOR'
 ON TABLE HOLD AS MODINP
 END
 -RUN
 0 NUMBER OF RECORDS IN TABLE=       12  LINES=     12
 -* 3
 MODIFY FILE MODPROB
 FIXFORM FROM MODINP
 MATCH *
 ON NOMATCH INCLUDE
 ON MATCH INCLUDE
 DATA ON MODINP
 END
 -RUN
 0 ERROR AT OR NEAR LINE     27  IN PROCEDURE modprob FOCEXEC *
 (FOC437) THE FORMAT USED WITH FIXFORM IS INCOMPATIBLE WITH THE DATA FORMAT
 BYPASSING TO END OF COMMAND
 -* 4
 TABLE FILE CAR
 SUM SALES RETAIL_COST DEALER_COST
 BY CAR BY MODEL
 WHERE MODEL CONTAINS 'AUTO'
 ON TABLE HOLD AS MODINP
 END
 -RUN
 0 NUMBER OF RECORDS IN TABLE=        8  LINES=      8
 -* 5
 MODIFY FILE MODPROB
 FIXFORM FROM MODINP
 MATCH *
 ON NOMATCH INCLUDE
 ON MATCH INCLUDE
 DATA ON MODINP
 END
 -RUN
 0 WARNING.. ON MATCH INCLUDE INPUTS DUPLICATE SEGMENTS
 0 TRANSACTIONS:         TOTAL =     8  ACCEPTED=     0  REJECTED=     8
 SEGMENTS:             INPUT =     0  UPDATED =     0  DELETED =     0


However when all inputs are "good" then it works:
  
 -* 2
 TABLE FILE CAR
 SUM SALES RETAIL_COST
 DEALER_COST
 -*COMPUTE DEALER_COST/I2=2;
 BY CAR BY MODEL
 WHERE MODEL CONTAINS 'DOOR'
 ON TABLE HOLD AS MODINP
 END
 -RUN
 0 NUMBER OF RECORDS IN TABLE=       12  LINES=     12
 -* 3
 MODIFY FILE MODPROB
 FIXFORM FROM MODINP
 MATCH *
 ON NOMATCH INCLUDE
 ON MATCH INCLUDE
 DATA ON MODINP
 END
 -RUN
 0 WARNING.. ON MATCH INCLUDE INPUTS DUPLICATE SEGMENTS
 0 TRANSACTIONS:         TOTAL =    12  ACCEPTED=    12  REJECTED=     0
 SEGMENTS:             INPUT =    12  UPDATED =     0  DELETED =     0
 -* 4
 TABLE FILE CAR
 SUM SALES RETAIL_COST DEALER_COST
 BY CAR BY MODEL
 WHERE MODEL CONTAINS 'AUTO'
 ON TABLE HOLD AS MODINP
 END
 -RUN
 0 NUMBER OF RECORDS IN TABLE=        8  LINES=      8
 -* 5
 MODIFY FILE MODPROB
 FIXFORM FROM MODINP
 MATCH *
 ON NOMATCH INCLUDE
 ON MATCH INCLUDE
 DATA ON MODINP
 END
 -RUN
 0 WARNING.. ON MATCH INCLUDE INPUTS DUPLICATE SEGMENTS
 0 TRANSACTIONS:         TOTAL =     8  ACCEPTED=     8  REJECTED=     0
 SEGMENTS:             INPUT =     8  UPDATED =     0  DELETED =     0


Anybody has an idea how to circumvent this problem?


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1934 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
[maybe]Use a different HOLD file name for each step.[/maybe]


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Francis,
No such luck...


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1934 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Danny - My first hunch is that after the "bad" transaction, your database or database connection is left in some sort of inconsistent state (and yes I know that's very vague).

What is your AUTOCOMMIT setting on your database connection? You could try doing something like this:
  
SQL SQLMSS SET AUTOCOMMIT     ON FIN;

-*  Do your 1st Modify
-*  Check &FOCERRNUM.  If 0 proceed to your next MODIFY.  Otherwise do a rollback:
SQL SQLMSS
ROLLBACK [WORK] ;
END
-*  Next, do you want to do your 2nd MODIFY even if the first fails?  If so, go ahead.
-*  Finally, when you're all done, do a commit:
SQL SQLMSS
COMMIT ;
END
 
Posts: 164 | Registered: March 26, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
I assumed this is a FOCUS DB. Perhaps you can try WebFOCUS COMMIT within the MODIFY.

MODIFY FILE NXMBRRSE
 COMMIT 1
 FIXFORM FROM TBRRDTAY
  MATCH USERID
   ON NOMATCH REJECT
   ON MATCH UPDATE &UPDATEFIELD DATE_CHANGED
 DATA ON TBRRDTAY
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Francis,
SET SHADOW=ON
and use of COMMIT...
nope!


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1934 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Eric,
No luck either...


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1934 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
[maybe]Use a different HOLD file name for each step.[/maybe]

...or toss in a CHECK FILE and ?FF right after creating each Hold file.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Maybe add a COMPUTE after the FIXFORM in each MODIFY?

FIXFORM FROM MODINP
COMPUTE DEALER_COST/D7 = DEALER_COST ;


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
Its been a while, but

Instead of issuing COMMIT 1, use ON MATCH COMMIT or Call a subroutine with a COMMIT on MATCH.

I seem to remember doing this in the 80's


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6115 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Master
posted Hide Post
If your first MODIFY fails because of the wrong format you could just set a flag. Use that to loop back to the code that recreates the database and from there to the second MODIFY - assuming you are always creating a new database each time.

But assuming you want the data from both passes why not just use a DEFINE to reformat the problematic DEALER COST right from the get go, which would ensure you don't have any incorrect data types at the MODIFY stage.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     When a MODIFY fails a subsequent MODIFY doesn't update

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.