I've created a fex using SQL Passthru after a long time away from using this technique.
I'm looking for some guidance on how to use it with APP HOLD so I can run the SQL query
just once to extract the data and populate the HOLD FILE which I create from the SQLOUT.

I have created a 2 part blocks of code to demonstrate the SQL extract and SQLOUT queries
followed by the table aganist the HOLD FILE to generate a report.

My question is where do I insert the APP HOLD commands to "permanently store" the HOLD file
for future queries. I envision using Dialogue Manager logic to run PART_2 if I want to run a
report.

Can anyone give me some direction? Thank you !

Here is Part 1 of the fex.

ENGINE SQLDBC SET DEFAULT_CONNECTION FOODFUN
SQL SQLDBC PREPARE SQLOUT FOR
SELECT
HOTDOGS,
CHILLI,
GRITS,
SAUSAGE,
BRATS,
DESC,
ITEMS,
MILLER,
HEINEKIN,
STPAULIGIRL,
-*
WHERE FOODS_I=7
AND ICE_CREAM_CANDY NOT IN (35)
AND HAMBURGER='H'
AND SALADS='A'
ORDER BY 
HOTDOGS,
CHILLI,
GRITS,
SAUSAGE ;
END
-RUN

APP HOLD WORKDISK
-RUN

After I have run the SQL code above to extract the data I want to run the code below as
often as I like to create reports, etc.

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS GRITSY
END
-RUN

?FF GRITSY
-RUN

TABLE FILE GRITSY
HEADING CENTER
"Let's Eat the Food"
""
PRINT
HOTDOGS,
CHILLI,
GRITS,
SAUSAGE,
-*
BY STPAULIGIRL
END
-RUN

This message has been edited. Last edited by: Tomsweb,

If you just say ON TABLE HOLD, you'll create an .ftm and the next time you need to TABLE FILE HOLD, you'll have to issue a FILEDEF. That won't be the case if you put the HOLD file in FOCUS or some other database format (e.g. HOLD FORMAT FOCUS).

Look up APP HOLD and APP HOLDMETA syntax in the documentation. I think issuing them before you issue your SQL passthru is fine. The important thing to remember is that the code you run to create the hold file should be run once, so users don't overwrite each other's hold files. Otherwise, you should code different paths based on userids to keep each user's hold file in a separate directory.

I did some digging and some playing and I think I have it coded correctly.

  
-GOTO RPT&X

-RPT1

APP HOLD WORKDISK
-RUN

ENGINE SQLDBC SET DEFAULT_CONNECTION FOODFUN
SQL SQLDBC PREPARE SQLOUT FOR
SELECT
HOTDOGS,
CHILLI,
GRITS,
SAUSAGE,
BRATS,
DESC,
ITEMS,
MILLER,
HEINEKIN,
STPAULIGIRL,
-*
WHERE FOODS_I=7
AND ICE_CREAM_CANDY NOT IN (35)
AND HAMBURGER='H'
AND SALADS='A'
ORDER BY HOTDOGS,
         CHILLI,
         GRITS,
         SAUSAGE;
END
-RUN

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS HF2 FORMAT FOCUS
END
-RUN

-GOTO GUBYE

-RPT2
APP PREPENDPATH WORKDISK
-RUN

TABLE FILE FH2
HEADING CENTER
"LET'S SEE THE FOOD"
PRINT
HOTDOGS 
CHILLI 
GRITS 
SAUSAGE
-*
BY STPAULIGIRL
END
-RUN


-GUBYE

-EXIT

Another useful option from WF767 onward is to save your data to FOCCACHE explicitly. It will then be available for you to use in other procedures.

This negates the need of an APP HOLD and having to clean up your left over HOLD files manually.

Also using FOCCACHE in place of APP HOLD allows multiple users to run the same FEX without overwriting each others data which can happen with APP HOLD.

Stuff saved to FOCCCAHE is available until that user's session closes in WebFOCUS or after a default 30 minutes if the user doesn't log off.

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS FOCCACHE/HF2 FORMAT FOCUS
END

FOCCACHE Reference

Cheers

Stu