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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] How to set output format of SQL query

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] How to set output format of SQL query
 Login/Join
 
Dan Pinault
Guru
posted
Greetings,

Consider this code...
SQL
select distinct FIELDNAME
from SQL_MFD
order by FIELDNAME;
END


This works in a .fex because the SQL_MFD is a Master File that points to SQL tables. I like this method because it can return data faster than a TABLE request.

Obviously I can't use ON TABLE PCHOLD FORMAT xxx because this isn't a TABLE request.

My goal is to output the result set to XML but it keeps coming up as an HTML table.

Anybody know how I can set the format of this data WITHOUT holding it first?

Thanks!

Dan

This message has been edited. Last edited by: Dan Pinault,


7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.
 
Posts: 393 | Location: St. Paul, MN | Registered: November 06, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Something like this:
FILEDEF H001 DISK TEMP/H001.XML
-RUN

SQL
SELECT
APPL_PURP_CD, APPL_PURP_ENG_DS
FROM
BSLC_IB_APPL_PURP_C
ORDER BY APPL_PURP_CD;
TABLE
ON TABLE HOLD AS H001 FORMAT XML
END
-RUN


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, 2005Report This Post
Dan Pinault
Guru
posted Hide Post
Excellent! Thanks Francis.

This is what I ended up with and it works great.
SQL
select distinct FIELDNAME
from SQL_MFD
order by FIELDNAME;
TABLE
ON TABLE PCHOLD FORMAT XML
END


7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.
 
Posts: 393 | Location: St. Paul, MN | Registered: November 06, 2007Report This Post
Darin Lee
Virtuoso
posted Hide Post
That's really not buying you much because the internal matrix still needs to be created in order to output to xml format. And you're not using SQL passthru, either. I'm guessing that

TABLE FILE SQL_MFD
SUM FIELDNAME NOPRINT
BY FIELDNAME
ON TABLE PCHOLD FORMAT XML
END

is going to be just as fast.


Regards,

Darin


In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Dan,

I'm curious as to what the behaviour of WebFOCUS is when you use PCHOLD instead of HOLD.

On my PC, a "File Download - Security Warning" pops up with a random file name and unknown file type. When I click on the Save button, the "File name" field has that random name and the "Save as type" field has ".document". Not very intuitive for non technical users.


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, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
And the file really isn't XML. When I use HOLD FORMAT XML, it does create an XML file on the server.

I wonder if I have to change something somewhere for the PCHOLD to work.


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, 2005Report This Post
Darin Lee
Virtuoso
posted Hide Post
PCHOLD is kind of confusing as you would think that something else should happen, but it really is just returning the data to the browser just like ON TABLE SET ONLINE-FMT XML.
I do not get the error you describe, but my browser also know what a .XML file is. If I clikc on the Save As, it wants to save as a text document, which it really is, but the content of the text document is what makes it an XML file. So you don't need to change anything for the PCHOLD to work. I only use PCHOLD when I'm using REMOTE BEGIN/END, because it returns the data from the remote server back to the local server for further processing.


Regards,

Darin


In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Dan Pinault
Guru
posted Hide Post
Francis,

If I run the procedure from a .fex the XML file contents are displayed in my browser. The reason I am using PCHOLD FORMAT XML is that I using this code to populate a listbox in an html page. And it works great!

Dan


7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.
 
Posts: 393 | Location: St. Paul, MN | Registered: November 06, 2007Report This Post
Darin Lee
Virtuoso
posted Hide Post
Aha! In this case, the PCHOLD format XML is required because it is doing something similar in that the data is being returned (internally) to the server so that it can be used for further processing (use in the drop-down box). Glad it's working for you.


Regards,

Darin


In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
OK, so, I've changed the filetype for XML to be opened by Internet Explorer. Now when I PCHOLD an XML file, it opens in IE. How does one use the PCHOLD result in a list box, if the file opens in a web browser window? I thought you would have to HOLD the file on the server and then build the HTML file before sending it to the web browser.

I am very curious about this.

Thanks,


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, 2005Report This Post
Darin Lee
Virtuoso
posted Hide Post
In the HTML Painter, when you select a procedure to dynamically populate the control, that procedure must include ON TABLE PCHOLD FORMAT XML. The code that is added to the HTML page does the rest of the magic.


Regards,

Darin


In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Ah yes, that magical "HTML Painter" - I must look into it some day...


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, 2005Report 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     [SOLVED] How to set output format of SQL query

Copyright © 1996-2020 Information Builders