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] -READFILE Variable Not Reading the Value

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] -READFILE Variable Not Reading the Value
 Login/Join
 
Gold member
posted
Good afternoon,

I am trying to read do a -READFILE on a value of a column from a stored procedure. I would like that value passed in to a variable so I can pass it in to another stored procedure to create actual report.

Here's my code and I created a simple report.

ENGINE SQLSYB SET DEFAULT_CONNECTION SYBASE


SQL SQLSYB
EX shop..GetTMTPrevMaintCode;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS PREVMAINT
END

TABLE FILE PREVMAINT
PRINT prevMaintList
ON TABLE HOLD AS COMPHOLD FORMAT BINARY
END
-RUN
-READFILE COMPHOLD
-TYPE prevMaintList IS &PMCode

SQL SQLSYB
EX shop..GetTMTPrevMaintCodeTest '&PMCode';
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS PREVMAINTTEST
END

TABLE FILE PREVMAINTTEST
PRINT prevMaintList
ON TABLE PCHOLD FORMAT HTML
END

I'm not sure what else I'm missing. When I run the .fex file, it prompts me to enter a value on &PMCode and I shouldn't have to. When I enter a value, it doesn't do anything.

Please advise and thanks for your help.

Winnie

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


Winnie

Webfocus 7.7.3
 
Posts: 83 | Registered: July 16, 2008Report This Post
Virtuoso
posted Hide Post
Add a -SET &ECHO=ALL; to the beginning of the code and show what the resolved variables look like.

Also, remove FORMAT BINARY from the HOLD line. &Variables are text.

Does -TYPE show the right value?


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Virtuoso
posted Hide Post
Should be
TABLE FILE PREVMAINT
PRINT prevMaintList
ON TABLE HOLD AS COMPHOLD FORMAT BINARY
END
-RUN
-READFILE COMPHOLD
-RUN
-TYPE prevMaintList IS &prevMaintList

Without any AS name, the variables have the name of the field.
So, since you did : "PRINT prevMaintList" the variable name is "prevMaintList"

To use "&PMCode" as your variable name, you should do the following
TABLE FILE PREVMAINT
PRINT prevMaintList AS 'PMCode'
ON TABLE HOLD AS COMPHOLD FORMAT BINARY
END
-RUN
-READFILE COMPHOLD
-RUN
-DEFAULTH &PMCode = ''
-TYPE PMCode IS &PMCode


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Gold member
posted Hide Post
Thanks so much for the quick responses BabakNYC and MartinY.

I changed my code to the one below and the result is still the same.
I know there's a row returned when I run my stored procedure in SQL. I am not sure why it prompts me to enter a value in order to run the second procedure.

ENGINE SQLSYB SET DEFAULT_CONNECTION SYBASE

-SET &ECHO=ALL;

SQL SQLSYB
EX shop..GetTMTPrevMaintCode;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS PREVMAINT
END

TABLE FILE PREVMAINT
PRINT prevMaintList
ON TABLE HOLD AS COMPHOLD
END
-RUN
-READFILE COMPHOLD
-RUN
-TYPE prevMaintList IS &prevMaintList


SQL SQLSYB
EX shop..GetTMTPrevMaintCodeTest '&prevMaintList';
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS PREVMAINTTEST
END

TABLE FILE PREVMAINTTEST
PRINT prevMaintList
ON TABLE PCHOLD FORMAT HTML
END

Btw, I also tried using the 2nd option that MartinY suggested with the AS name.

Thanks,
Winnie


Winnie

Webfocus 7.7.3
 
Posts: 83 | Registered: July 16, 2008Report This Post
Virtuoso
posted Hide Post
Winnie

The & variable is trying to be resolved because of the -TYPE statement. Put this line at the beginning of the fex and try again.


-DEFAULTH &prevMaintList = '';


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Virtuoso
posted Hide Post
quote:

Winnie
The & variable is trying to be resolved because of the -TYPE statement.
Put this line at the beginning of the fex and try again.
-DEFAULTH &prevMaintList = '';

And better to put
-DEFAULTH &prevMaintList = ''


AFTER the -READFILE such as
-READFILE COMPHOLD
-RUN
-DEFAULTH &prevMaintList = ''
-TYPE prevMaintList IS &prevMaintList

This to avoid the error FOC2906


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Guru
posted Hide Post
Don't you have to -SET the variable? I am not seeing that after the -READFILE.

See below, this works and it reads out the first model in the CAR file.


-SET &ECHO = 'ALL';

TABLE FILE CAR
PRINT *
ON TABLE HOLD AS TEST
END
-RUN

-DEFAULTH &MODEL = '';
-READFILE TEST
-SET &MODEL = &MODEL;
-TYPE &MODEL

  


WebFOCUS 8.1.05
 
Posts: 496 | Registered: January 04, 2008Report This Post
Virtuoso
posted Hide Post
quote:
Don't you have to -SET the variable? I am not seeing that after the -READFILE.

No.

-READFILE do creates variables automatically using the field's name (or the AS name when specified)


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Guru
posted Hide Post
Good to know! I have always specified. Maybe what's coming out of the SQL isn't named or cased the way Winnie is expecting.


WebFOCUS 8.1.05
 
Posts: 496 | Registered: January 04, 2008Report This Post
Virtuoso
posted Hide Post
TABLE FILE PREVMAINT
PRINT prevMaintList
ON TABLE HOLD AS COMPHOLD 
END
-RUN

The above assign the name of the field in the hold file so, not anymore related to the SQL out from the step prior to this one

But doing a -SET after a -READFILE may be useful in this situation (as per example)
TABLE FILE CAR
BY COUNTRY AS 'CNTRY'
BY CAR
ON TABLE HOLD AS EXTCNTRY
END
-RUN
-SET &NBCNTRY = &LINES;

-REPEAT LOOP FOR &I FROM 1 TO &NBCNTRY STEP 1
-READFILE EXTCNTRY
-SET &COUNTRY&I.EVAL = TRIM_(BOTH, ' ', &CNTRY);
-SET &CAR&I.EVAL = TRIM_(BOTH, ' ', &CAR);
-TYPE COUNTRY&I.EVAL = -->&COUNTRY&I.EVAL<--, CAR&I.EVAL = -->&CAR&I.EVAL<--
-LOOP


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Gold member
posted Hide Post
Thanks, guys! I really appreciate your input. I got that Readfile part to work and it now shows my variable. Smiler

The resultset for &prevMaintList shows as expected.

("000-008","000-009")

Now I'm getting error below passing the &prevMaintList to my second procedure.I tried to switch the double quotes and single quotes around to see if that helps and even hard-coded the parameter with the above result, but no luck. Anyone knows what this error means?

My code is:
SQL SQLSYB
EX shop..GetTMTPrevMaintCodeTest '&prevMaintList';
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS PREVMAINTTEST
END

(FOC1400) SQLCODE IS 207 (HEX: 000000CF) XOPEN: 42S22
(FOC1500) : Invalid column name '000-009'.
(FOC1500) :
(FOC1500) :
(FOC1500) :
(FOC1500) :
(FOC1500) :
(FOC1500) :
(FOC1500) :

I found this article in Focal Point but not sure what it means. http://forums.informationbuild...1057331/m/5671000661

Thanks much!


Winnie

Webfocus 7.7.3
 
Posts: 83 | Registered: July 16, 2008Report This Post
Virtuoso
posted Hide Post
quote:
EX shop..GetTMTPrevMaintCodeTest '&prevMaintList';

First, does the above run properly and as expected ?


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Gold member
posted Hide Post
MartinY, here's my complete error. The problem is the parameter getting passed in on the 2nd stored procedure. It may have issues with special characters or something or maybe white space?

I tried to harcode the exact result from the variable and also passed in the &prevMaintList variable...same error. If I run the stored proc in SQL with same parameter, it runs just fine. App Studio doesn't seem to like the string I pass in.

Inline Messages URL125 2019-02-12_10.59.58.007
SET GRAPHENGINE=GRAPH53
SET HTMLEMBEDIMG=ON
ENGINE SQLSYB SET DEFAULT_CONNECTION SYBASE
SQL SQLSYB
EX shop..GetTMTPrevMaintCode;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS PREVMAINT
END
TABLE FILE PREVMAINT
PRINT prevMaintList
ON TABLE HOLD AS COMPHOLD
END
-RUN
0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1
0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1
-READFILE COMPHOLD
-RUN
-DEFAULTH &prevMaintList = ''
-TYPE prevMaintList IS ("000-008","000-009")
prevMaintList IS ("000-008","000-009")
SQL SQLSYB
EX shop..GetTMTPrevMaintCodeTest '("000-008")';
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS PREVMAINTTEST
END
-*
-*SQL SQLSYB
-*EX shop..GetTMTPrevMaintCodeTest '&prevMaintList';
-*TABLE FILE SQLOUT
-*PRINT *
-*ON TABLE HOLD AS PREVMAINTTEST
-*END
TABLE FILE PREVMAINTTEST
PRINT *
ON TABLE PCHOLD FORMAT HTML
END
-*SET HOLDLIST = PRINTONLY
-*SET ASNAMES = ON
(FOC1400) SQLCODE IS 207 (HEX: 000000CF) XOPEN: 42S22
(FOC1500) : Invalid column name '000-008'.
(FOC1500) :
(FOC1500) :
(FOC1500) :
(FOC1500) :
(FOC1500) :
(FOC1500) :
(FOC1500) :
L (FOC1405) SQL PREPARE ERROR.
0 ERROR AT OR NEAR LINE 38 IN PROCEDURE preventativemaintenance_development
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: PREVMAINTTEST
(FOC009) Request failed validation, not executed.


Winnie

Webfocus 7.7.3
 
Posts: 83 | Registered: July 16, 2008Report This Post
Guru
posted Hide Post
I guess you need this:
  
SQL SQLSYB
EX shop..GetTMTPrevMaintCodeTest('000-008');
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS PREVMAINTTEST
END


This would this in your procedure:
  
SQL SQLSYB
EX shop..GetTMTPrevMaintCodeTest(&PMCode.QUOTEDSTRING);
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS PREVMAINTTEST
END


You can even call the procedure like this:
ENGINE SQLSYB
EX SAMPLE PARM1,PARM2,PARM3...;
TABLE ON TABLE PCHOLD
END

See https://infocenter.information...ource%2Ftopic447.htm (don't know if it will work in 7.7.3)


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
Gold member
posted Hide Post
Thanks everyone! I was able to figure out my issue. I just needed to use double quote on my variable instead of single quote the way I have it set up in my stored procedure.

ENGINE SQLSYB SET DEFAULT_CONNECTION SYBASE

-SET &ECHO=ALL;

SQL SQLSYB
EX shop..GetTMTPrevMaintCode;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS PREVMAINT
END

TABLE FILE PREVMAINT
PRINT prevMaintList
ON TABLE HOLD AS COMPHOLD
END
-RUN
-READFILE COMPHOLD
-RUN
-DEFAULTH &prevMaintList = ''
-TYPE prevMaintList IS &prevMaintList



SQL SQLSYB
EX shop..GetTMTPrevMaintCodeTest "&prevMaintList" ;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS PREVMAINTTEST
END

TABLE FILE PREVMAINTTEST
PRINT *
ON TABLE PCHOLD FORMAT HTML
END


Thanks so much for all the help!


Winnie

Webfocus 7.7.3
 
Posts: 83 | Registered: July 16, 2008Report This Post
Virtuoso
posted Hide Post
Edit your first post and add [SOLVED] at the beginning of the subject


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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] -READFILE Variable Not Reading the Value

Copyright © 1996-2020 Information Builders