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     Handling of LONG datatype in Webfocus

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Handling of LONG datatype in Webfocus
 Login/Join
 
Gold member
posted
I am facing an issue in reading (-READ command) LONG oracle database column in the FEX.
I have a requirement to generate report output using SQL pass-through method . All my queries will be stored in a Oracle table REPORT_TABLE with ID as key and REP_SQL (LONG datatype) to store SQLS,We have more than 100 reports in the table now.I am planing to use report caster to distribute each report by scheduling a single template file passing report id as a parameter.
my template code look like as given below.

-DEFAULT RPTID = '11'
TABLE FILE REPORT_TABLE
PRINT
NAME
DESCRIPTION
REP_SQL
WHERE ID EQ &RPTID
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
-READ HOLD1 &DUMMY1.A6. &VNAME.A100. DUMMY1.A6. &VDESC.A500. &DUMMY1.A6. &VSQL.A4000.
-TYPE VNAME &VNAME
-TYPE VDESC &VDESC
-TYPE VSQL &VSQL
-* If I try to print the value of &VSQL I am not getting anything, even if , value is available in the database table. Except that all other are coming correctly
-* if I use a TABLE FILE HOLD1 PRINT * END command , It is correctly printing even LONG value
-* Issue 2 is in the next step . I chnaged the REP_SQL as varchar2(4000) and now I am able to read the SQL, but , in the SQL if I reference any LONG columns of any tables , it will give strange errors not for LONG columns but the columns next to that.
-*
ENGINE SQLORA SET DEFAULT_CONNECTION myconn
SQL SQLORA PREPARE SQLOUT FOR
&VSQL.EVAL
END
TABLE FILE SQLOUT
HEADING
"&VNAME"
"Run date: <+0>&DATEtrMDYY <+0>"
"&VDESC"
PRINT
*
END


Version 7.6.11
Webfocus installed in AIX 5.3,
desktop PC: Windows-XP based
Output: Excel, HTML, PDF
 
Posts: 83 | Registered: October 19, 2007Report This Post
Virtuoso
posted Hide Post
We have some reports which use LONG database fields from Oracle. We have them defined as TX fields in the master file desription.

For example:

FIELD=LONGDESC ,ALIAS=LONGDESC ,TX250 ,TX ,MISSING=ON , TITLE='Description' ,DESC='Description',$

We need to do some additional processing to the contents of the fields so we also create a HOLD file using these fields. Using the TX250 format gets all the contents of the LONG database field into the HOLD file but breaks it into multiple lines with a max of 250 characters per line. We then use -READ to massage the data.

This is the only way we were able to use DM to do -READs on the data. 4000 characters was just way too long.

I hope this helps.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Gold member
posted Hide Post
Hi Mickey,
I have changed my master file desc as TX250 and -READ variable format is A4000 (Tried TX250 but gave me some error as UNRECOGNIZED FORMAT OF AMPER VARIABLE IN -READ: -READ HOLD1 OPEN
&VSQL.TX250.).While keeping the VSQL variable as A4000 I am able to retrive only the first line of the query.
Current master file setting is as below,
FIELD=REP_SQL ,REP_SQL,
TX250 ,TX ,MISSING=OFF,$

New sample code as below,

TABLE FILE REPORT_TABLE
PRINT
REP_SQL
WHERE ID=1
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
-READ HOLD1 OPEN &VSQL.A4000.
-TYPE VSQL &VSQL
-EXIT


Version 7.6.11
Webfocus installed in AIX 5.3,
desktop PC: Windows-XP based
Output: Excel, HTML, PDF
 
Posts: 83 | Registered: October 19, 2007Report This Post
Guru
posted Hide Post
quote:
FORMAT OF AMPER VARIABLE IN -READ: -READ HOLD1 OPEN
&VSQL.TX250.).


Johney,

Try changing your variable to

 &VSQL.A250. 


when you use the TX field.

Fernando


Prod WF 8.1.04, QA WF 8.2.03, Dev WF 8.2.03
 
Posts: 278 | Registered: October 10, 2006Report This Post
Virtuoso
posted Hide Post
Johney,

What exactly do you want as the final output for the value of REP_SQL?

In order to get ALL parts of the REP_SQL from the HOLD1 file, you need to use A250 for the format of your variable and then use a loop around the -READ statement to read each line of the hold file until there are no more lines in the HOLD1 file. This is because using TX250 will break the REP_SQL value into multiple lines in the HOLD1 file. Edit the HOLD1 file and you will see what I mean.

Try this:

TABLE FILE REPORT_TABLE
PRINT
REP_SQL
WHERE ID=1
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN

ENGINE SQLORA SET DEFAULT_CONNECTION myconn
SQL SQLORA PREPARE SQLOUT FOR
-BEGLOOP
-READ &VSQL.A250.
-IF (&IORETURN NE 0) GOTO ENDLOOP;
&VSQL
-GOTO BEGLOOP
-ENDLOOP

END
TABLE FILE SQLOUT
HEADING
"&VNAME"
"Run date: <+0>&DATEtrMDYY <+0>"
"&VDESC"
PRINT
*
END


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Gold member
posted Hide Post
Thanks Fernando and Mickey . I tried your method of changing the format to A250 but it was not giving me the full text.Then I tried following but still I have the formating issues.

I have changed my master entry as ,

FIELD=REP_SQL, ALIAS=REP_SQL, USAGE=A3968, ACTUAL=A4000V, MISSING=ON, $

FEX Code as below,

TABLE FILE REPORT_TABLE
PRINT
REP_SQL
WHERE ID=1
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
-READ HOLD1 &VSQL.A3968.
-TYPE &VSQL
-EXIT
ENGINE SQLORA SET DEFAULT_CONNECTION myconn
SQL SQLORA
&VSQL;
TABLE FILE SQLOUT
PRINT
*
END

It is working for me if the REP_SQL was stored in the table after removing newline and tab characters.

Any suggestions ?


Version 7.6.11
Webfocus installed in AIX 5.3,
desktop PC: Windows-XP based
Output: Excel, HTML, PDF
 
Posts: 83 | Registered: October 19, 2007Report This Post
Virtuoso
posted Hide Post
Johney,
You might want to check the contents of the hold1 file afer it gets created.
So, after you did:
TABLE FILE REPORT_TABLE
PRINT 
REP_SQL
WHERE ID=1
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN

Do a !cat hold1.ftm to see what's in the hold file.
My guess is that everything you heed is in this file.
If that's the case I wonder if the following code would work:
TABLE FILE REPORT_TABLE
PRINT 
REP_SQL
WHERE ID=1
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
ENGINE SQLORA SET DEFAULT_CONNECTION myconn
SQL SQLORA
-INCLUDE hold1.ftm
;
TABLE FILE SQLOUT
PRINT
*
END

And, if you have other LONG or TX fields in the final request, make sure there is only 1 such field in the request and also make sure it is the very last field in the resulting output. Reason for this is that a hold file may containno more than 1 tx-field in the output and is has to be the last (rightmost) column. And the result of the sql query is always a hold file (the sqlout file).


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Gold member
posted Hide Post
Hi GamP,

I made it my master file defenition as below,

FIELD=REP_SQL, ALIAS=REP_SQL, USAGE=TX256, ACTUAL=TX, $

And now I am able to print the entire SQLs (I tried upto 19000 characters ,and obviosly it is more than that of my requirement.Thanks a lot)

There is one %$ in the include result , which gives me an error,
ORA-00911: invalid character : Erroneous character: % SQLPREPARE error.

Could you please help me to correct this also ?

Thanks a lot....!!!


Version 7.6.11
Webfocus installed in AIX 5.3,
desktop PC: Windows-XP based
Output: Excel, HTML, PDF
 
Posts: 83 | Registered: October 19, 2007Report This Post
Virtuoso
posted Hide Post
I'm curious as to why using TX256, TX worked for you as opposed to TX250, TX. Either one should have given you all the values.

In order to eliminate the one record with the %$ value, you could use the LOOP with the -READ and do an -IF test on the record to decide whether or not to include it in the SQL statements. We use a different method to parse the HOLD file but we have to deal with the %$ as well. We simply exclude the record that has this since it appears on a line by itself.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Gold member
posted Hide Post
Hi Mickey,
The master file defenition I used is the default when I created a synonym against the database table with a LONG datatype.

My only issue now is to remove the unwanted %$ when using the -INCLUDE ftm statements to process the SQL regest.

Any suggestions ?


Version 7.6.11
Webfocus installed in AIX 5.3,
desktop PC: Windows-XP based
Output: Excel, HTML, PDF
 
Posts: 83 | Registered: October 19, 2007Report This Post
Virtuoso
posted Hide Post
You cannot exclude that line by simply using the -INCLUDE of the HOLD file. You need to read through the HOLD file either with a Dialogue Manager loop or use TABLE FILE to process the HOLD file.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Gold member
posted Hide Post
Thanks everybody ,

I achieved what I was looking for .I am sharing it with you.

My requirement was to store an SQL query which can grow upto 15000 and execute this SQLs using sqlpassthru.

My database column is still LONG and I changed my master file defenition only, as ACTUAL=15000 , USAGE=15000

Now I used the same steps which I described earlier

TABLE FILE REPORT_TABLE
PRINT
REP_SQL
WHERE ID=1
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
ENGINE SQLORA SET DEFAULT_CONNECTION myconn
SQL SQLORA
-INCLUDE hold1.ftm
;
TABLE FILE SQLOUT
PRINT
*
END

It is working for me now ... Thanks a lot once again to all whoever involved in the discussion...

Regards,
Johney


Version 7.6.11
Webfocus installed in AIX 5.3,
desktop PC: Windows-XP based
Output: Excel, HTML, PDF
 
Posts: 83 | Registered: October 19, 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     Handling of LONG datatype in Webfocus

Copyright © 1996-2020 Information Builders