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.
WinnieThis 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 = '';
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.
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
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
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