Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Help ? SQL Passthru and APP HOLD [SOLVED]

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Help ? SQL Passthru and APP HOLD [SOLVED]
 Login/Join
 
Master
posted
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,


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Virtuoso
posted Hide Post
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.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Master
posted Hide Post
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


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Guru
posted Hide Post
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


WebFOCUS 8.2.03 (8.2.06 in testing)
 
Posts: 253 | Location: Melbourne, Australia | Registered: February 07, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Help ? SQL Passthru and APP HOLD [SOLVED]

Copyright © 1996-2020 Information Builders