Focal Point
[SOLVED] -READFILE Variable Not Reading the Value

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/5177001196

February 11, 2019, 03:31 PM
Winnie
[SOLVED] -READFILE Variable Not Reading the Value
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
February 11, 2019, 03:35 PM
BabakNYC
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
February 11, 2019, 03:37 PM
MartinY
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
February 11, 2019, 04:08 PM
Winnie
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
February 11, 2019, 05:38 PM
FP Mod Chuck
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
February 12, 2019, 08:41 AM
MartinY
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
February 12, 2019, 08:56 AM
MattC
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
February 12, 2019, 09:03 AM
MartinY
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
February 12, 2019, 09:15 AM
MattC
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
February 12, 2019, 09:51 AM
MartinY
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
February 12, 2019, 10:48 AM
Winnie
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
February 12, 2019, 11:16 AM
MartinY
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
February 12, 2019, 12:08 PM
Winnie
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
February 12, 2019, 01:01 PM
Frans
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.
February 12, 2019, 01:09 PM
Winnie
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
February 12, 2019, 01:31 PM
MartinY
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