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,

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?

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

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

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


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

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

  

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)

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.

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

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

(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!

quote:

EX shop..GetTMTPrevMaintCodeTest '&prevMaintList';

First, does the above run properly and as expected ?

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.

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)

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!

Edit your first post and add [SOLVED] at the beginning of the subject