Focal Point
[CLOSED] Updating records in Focus DB files

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/8917012436

October 19, 2013, 01:05 AM
Mayank Jain
[CLOSED] Updating records in Focus DB files
Hi,

Could someone please tell me how to update records in a .foc file?

This message has been edited. Last edited by: <Kathryn Henning>,


WebFOCUS 7.6
Windows, All Outputs
October 19, 2013, 08:02 AM
George Patton
You need to use MATCH logic within a MODIFY routine or use the Maintain language.

With MODIFY You match an incoming value to your key field and then update other values in the related segment. This usually requires CASE logic as well.

You will probably find the full MODIFY syntax in the mainframe FOCUS manuals. They have discontinued it in the WebFocus product, but the MODIFY engine is still there – and at no additional cost – under the covers.

Abbreviated example:
MODIFY FILE EMPLOYEE

CASE UPDATE_SAL
    MATCH EMP_ID
    ON MATCH UPDATE CURR_SAL
    ON NOMATCH REJECT
ENDCASE

DATA ON DDname
END



WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
October 19, 2013, 12:20 PM
dbeagan
The manual is here:
Maintaining Databases

A working example:

 
 SQL CREATE TABLE TABLE1 ( FIELD1 CHAR(20));
 END

 MODIFY FILE TABLE1
 MATCH FIELD1
 ON NOMATCH INCLUDE
 DATA
 'Test field value 1.',$
 'Test field value 2.',$
 'Test field value 3.',$
 'Test field value 4.',$
 END

 TABLE FILE TABLE1
 PRINT *
 END
 


Note, the table is dynamically created and and goes away after the request.


WebFOCUS 8.2.06
October 19, 2013, 01:55 PM
Mayank Jain
I have a empdetails.foc file with information as
1. Name
2. Emp#
3. Mobile

In this file for some records mobile# is missing. Is it possible to get a promptto ask for Emp # & it's corresponding Mobile #, and then when Emp# matches the mobile number gets updated?


WebFOCUS 7.6
Windows, All Outputs
October 19, 2013, 03:30 PM
Danny-SRL
Could you post your Master File?


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

October 20, 2013, 10:19 AM
George Patton
I see where Danny is going with this, so I'll let him post the solution.

I don't know if PROMPT is supported any more within the WebFocus MODIFY application. The old PC FOCUS "CRTFORM" which was used for data input/updating certainly is not.

But if you can produce a text list of user names and phone numbers that need to be updated, you don't need to go the PROMPT route and it becomes much easier.
MODIFY FILE EMPDETAILS
FIXFORM EMP_ID PHONE
MATCH EMP_ID
ON MATCH UPDATE PHONE
DATA
123 456-7890
124 456-7899
125 456-7898
.
.
.
.
END


Note - I left a space between the EMP_ID and the phone number. Normally with a FIXFORM (i.e. fixed format) data input string you wouldn't do that.

Below is a working example from the IBI sample databases that updates the salary. If the EMP_ID doesn't exist it will add the new record of for the employee and his salary.

Here I have changed the salary for the first two employees from $11,000 and $13,200 to $20,000 and $50,000 respectively.
MODIFY FILE EMPLOYEE
FIXFORM EMP_ID CURR_SAL
MATCH EMP_ID 
ON MATCH UPDATE CURR_SAL
ON NOMATCH INCLUDE
DATA
07138266020000
11284761250000
END
-RUN

TABLE FILE EMPLOYEE
PRINT CURR_SAL BY EMP_ID
END


If you create your list as a separate text file - let's call it PHONES.TXT, with two fields, EMP_ID and PHONE, then you need to FILEDEF that file (let's call it PHONELIST) and then in the MODIFY it becomes:
MODIFY FILE EMPDETAILS
FIXFORM EMP_ID PHONE
MATCH EMP_ID 
ON MATCH UPDATE PHONE
ON NOMATCH INCLUDE
DATA ON PHONELIST
END

This message has been edited. Last edited by: George Patton,


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
October 21, 2013, 09:03 AM
Maintain Wizard
Then there is Maintain! Maintain is a product available on WebFOCUS as well as mainframe Focus. You can use it to create Winforms for the display of data as well as Update, Include and Delete.

You can easily create a next statement that will bring in all the data where there is no mobile number:

FOR ALL NEXT NAME EMP# INTO STACK
WHERE MOBILE EQ ''

Then you can display the stack on a form in a Grid and have the user enter the Mobile next to the Name. When done press a button and update the data to the database.

Not all companies have a license for Maintain. If you do I will be happy to help you create the code.

Mark
October 22, 2013, 09:20 AM
TexasStingray
Mayank Jain

The Master file has a SEGTYPE= statement in it. This defines the number of columns that make up a unique record, this is also known as the key(s) to the segment. If in your example it is an S1 and Name is the first feild in the master file you would have to code a report at would ask the user for an employee # and have a compute that would define the moble # making it equal to the variable. Then hold this record and use it in a modify simular to below.

WARNING BEFORE YOUR TRY SOMETHING FOR THE FIRST TIME ALWAYS BACKUP YOUR DATA FILES in this case that would be your .foc file.

TABLE FILE <YOURTABLEHERE>
PRINT 
  Name
  AND COMPUTE Moble/A10 = '&MOBLENUM';
WHERE Emp# EQ '&EMPNUM';
ON TABLE HOLD AS HOLD1
END

MODIFY FILE <YOURTABLEHERE>
FIXFORM FROM HOLD1
MATCH Name
ON MATCH UPDATE Moble
ON NOMATCH REJECT
DATA ON HOLD1
END
 





Scott

I'd love to know what "Judgement is coming are you ready?" is about...


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
@Francis.....I'm assuming it means when you meet your maker, but I was curious myself.


WebFOCUS Server 8.1.05
Windows 2008 Server
WebFOCUS AppStudio 8.1.05
Windows 7 Professional
IE 11 and Chrome Version 43.0.2357.124 m.
Mostly HTML, PDF, Excel, and AHTML
If it is that, then they've been saying it for a really long time...

This message has been edited. Last edited by: Francis Mariani,


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
Thanks George. Your strategy helped me get to the fix but now i have another problem, that somehow the JOIN is not working. Though the Source & lookup column have same data type & also the matching values are also present in both, still join is not working. Is there some setting that we need to do?

Thanks TexasStingray, will try the solution provided by you.


WebFOCUS 7.6
Windows, All Outputs
quote:
Originally posted by Francis Mariani:
If it is that, then they've been saying it for a really long time...


Your Lifetime vs Eternity is but a dot on the longest line anyone could imagine. So if your not ready then you have not heard it enough.




Scott

Since religious slogans are allowed here, then political comment must be welcome as well.

With what a certain government has continuously inflicted on my fellow citizens of the world for decades, judgement day is the last thing I'm worried about, in fact I am not concerned at all.


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
You haven't mentioned anything about a JOIN previously. You will have to provide more detail on that - and post your JOIN code.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
I'm with Francis. Though my ancestry is replete with ministers and priests, I'll be happy to be worm feed for eternity. If the everlasting is anything like this place, I've had enough already.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
I might be better to MODIFY your files piece-by-piece before getting into MODIFYing JOINed files. ... Baby Steps ... I've done complex MODIFYs back in the day... KISS...
Seems that back in the day, there was a COMBINE FILE command. One could combine several files, related or not, into a single file structure the you could use MODIFY to maintain both files in one procedure. I used it once or twice but that was over 20 years ago so my memory of the exact syntax is pretty foggy. I did find this:
The basic syntax for the COMBINE command is
COMBINE FILES file1 [PREFIX pref1|TAG tag1] [AND]
.
.
.
filen [PREFIX prefn|TAG tagn] AS asname
where:

file1 - filen
  Are the Master File names of the data sources you want to modify. You can specify up to 16 Master Files.

pref1 - prefn
  Are prefix strings for each file; up to four characters. They provide uniqueness for fieldnames. You cannot mix TAG and PREFIX in a COMBINE structure. Refer to the FOCUS for S/390 documentation for additional information.

tag1 - tagn
  Are aliases for the data source names; up to eight characters. FOCUS uses the tag name as a qualifier for fields that refer to that data source in the combined structure. You cannot mix TAG and PREFIX in a COMBINE, and you can only use TAG if FIELDNAME is set to NEW or NOTRUNC.

AND
  Is an optional word to enhance readability.

asname
  Is the required name of the combined structure to use in MODIFY procedures and CHECK FILE commands.



In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
quote:
Originally posted by George Patton:
I see where Danny is going with this, so I'll let him post the solution.

I don't know if PROMPT is supported any more within the WebFocus MODIFY application. The old PC FOCUS "CRTFORM" which was used for data input/updating certainly is not.

But if you can produce a text list of user names and phone numbers that need to be updated, you don't need to go the PROMPT route and it becomes much easier.
MODIFY FILE EMPDETAILS
FIXFORM EMP_ID PHONE
MATCH EMP_ID
ON MATCH UPDATE PHONE
DATA
123 456-7890
124 456-7899
125 456-7898
.
.
.
.
END


Note - I left a space between the EMP_ID and the phone number. Normally with a FIXFORM (i.e. fixed format) data input string you wouldn't do that.

Below is a working example from the IBI sample databases that updates the salary. If the EMP_ID doesn't exist it will add the new record of for the employee and his salary.

Here I have changed the salary for the first two employees from $11,000 and $13,200 to $20,000 and $50,000 respectively.
MODIFY FILE EMPLOYEE
FIXFORM EMP_ID CURR_SAL
MATCH EMP_ID 
ON MATCH UPDATE CURR_SAL
ON NOMATCH INCLUDE
DATA
07138266020000
11284761250000
END
-RUN

TABLE FILE EMPLOYEE
PRINT CURR_SAL BY EMP_ID
END


If you create your list as a separate text file - let's call it PHONES.TXT, with two fields, EMP_ID and PHONE, then you need to FILEDEF that file (let's call it PHONELIST) and then in the MODIFY it becomes:
MODIFY FILE EMPDETAILS
FIXFORM EMP_ID PHONE
MATCH EMP_ID 
ON MATCH UPDATE PHONE
ON NOMATCH INCLUDE
DATA ON PHONELIST
END


Hi can we match on multiple fields using this method?


WebFOCUS 7.6
Windows, All Outputs
Yes indeed.
It depends on what you mean though i.e.

Does the FOCUS DB have a single segment with e.g. an S2 segtype?
If so, then you would simply say:

MATCH KEYFIELD1 KEYFIELD2

However, if your FOCUS DB has multiple segments e.g. like the CAR file, it becomes a little more tricky, because you need to think about what you want to do for each segment, and then there is a whole new world of active / inactive fields/segments etc.

For multi-segment files you need to start at the top, and work your way down a path.

An example of the structure of the code would then be:

MATCH SEG1_KEYFIELD
ON NOMATCH REJECT
ON MATCH CONTINUE
MATCH SEG2_KEYFIELD
ON NOMATCH INCLUDE
ON MATCH UPDATE SEG2_DATAFIELD


WebFOCUS 8.2.06 mostly Windows Server
Yes you can.

MATCH FIELD1 FIELD2 FIELD3
ON MATCH ....
etc

Grrrr... Twanette is way ahead of me...


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
LOL

Sorry George!

Smiler


WebFOCUS 8.2.06 mostly Windows Server
Thanks Twanette & George. This indeed work, but when i tried to manually pass data using "DATA" command, it did not work. any idea about that?

For ex-
MODIFY FILE EMPLOYEE
FIXFORM EMP_ID CURR_SAL
MATCH EMP_ID EMPNAME
ON MATCH UPDATE CURR_SAL
ON NOMATCH INCLUDE
DATA
071382660Kate20000
112847612John50000
END
-RUN


WebFOCUS 7.6
Windows, All Outputs
I see that you are not referencing the IBI EMPLOYEE database, which is OK.

Your FIXFORM command has to reference every field in the DATA statement and has to have the length of the field specified.

So:

FIXFORM EMP_ID/9 EMPNAME/4 SURR_SAL/5

I'm assuming that the EMP_ID and the EMPNAME are your keys, so that would be:
MODIFY FILE EMPLOYEE
FIXFORM EMP_ID/9 EMPNAME/4 CURR_SAL/5
MATCH EMP_ID EMPNAME
ON MATCH UPDATE CURR_SAL
ON NOMATCH INCLUDE
DATA
071382660Kate20000
112847612John50000
END



WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP