[SOLVED] Provide default value (username) for stored procedure synonym

How are you calling the synonym ?

You should be able to supply a WHERE clause.

e.g.
TABLE FILE {sp master}
...
WHERE @USERNAME EQ '&IBIMR_user'
END

Waz...

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006

Ignored post by Waz posted

Member

posted

I can pass the username in a report no problem.
The problem I am having is that when I add filters in the report that are dynamic parameters to use in a page filter these parameters are not populated and my assumption is that the retrieval (follows) cannot get these values without the input parameter.

WHERE SP_SPONSORED_PROJECTS.DIM_INPUT.FH_DIVISION_NAM EQ &FH_DIVISION_NAM.(OR(FIND SP_SPONSORED_PROJECTS.DIM_INPUT.FH_DIVISION_NAM IN FMSBI/SP_SPONSORED_PROJECTS |FORMAT=A30,SORT=ASCENDING)).FH_DIVISION_NAM:.;

This is why I want the synonym to know who the user is.

I also tried creating a reporting object that passes the IBIMR_user as a WHERE clause, then built a report on the RO. The report works but same problem with trying to create dynamic filters, the values are not populated, only the All option appears.

This message has been edited. Last edited by: Tim Easley,

Posts: 29 | Registered: July 17, 2007

Ignored post by Tim Easley posted

Member

posted

Some progress, I edited the INPUT segment in the .mas file to change the ACCESS_PROPERTY value from NEED_VALUE to INTERNAL, then added a mandatory filter. (below)

When I run my page now, all dynamic filter list boxes are populated and work correctly

SEGMENT=INPUT, SEGTYPE=S0, $
FIELDNAME=@USERNAME, ALIAS=P0001, USAGE=A30, ACTUAL=A30,
MISSING=ON, ACCESS_PROPERTY=(INTERNAL), $
FILTER FILTER1=@USERNAME EQ 'teasley';
TITLE='INPUT,Filter', MANDATORY=YES, $

So the next hurdle is that WFRS does not know &IBIMR_user (that's why I had to hard-code my user ID above).
Since security is set to "Trusted: Pass WebFOCUS User ID and their Groups" I'm hoping WFRS might have my user ID in a variable but haven't yet found any documentation on this.

This message has been edited. Last edited by: Tim Easley,

Posts: 29 | Registered: July 17, 2007

Ignored post by Tim Easley posted

Tom Flynn

Expert

posted

Tim,
This may work for you.
On the Adminitration Console under Configuration, Custom Settings, add this:
<set>IBIC_user(pass)
<set>IBIMR_user(pass)

&IBMR_user will be available for all procedures...

hth

Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe

Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006

Ignored post by Tom Flynn posted

Member

posted

Update: in edasprof.prf I created a global variable on WFRS with my username:
-SET &&WFRSUSER = GET_TOKEN(&FOCSECUSER, '\', 2);
And it works on the server:
-TYPE WFRSUSER = &&WFRSUSER
WFRSUSER = teasley

However, when I replace the hard-coded username in the input filter with this variable I still get no results:

SEGMENT=INPUT, SEGTYPE=S0, $
FIELDNAME=@USERNAME, ALIAS=P0001, USAGE=A30, ACTUAL=A30,
MISSING=ON, ACCESS_PROPERTY=(INTERNAL), $
FILTER FILTER1=@USERNAME EQ '&&WFRSUSER.EVAL' ;
TITLE='INPUT,Filter', MANDATORY=YES, $

I read the documentation on using parameters in the .acx but it only refers to using parameters for connection and table name, nothing about passing a parameter used by the SP.

Posts: 29 | Registered: July 17, 2007

Ignored post by Tim Easley posted

Member

posted

Thanks Tom, when I took over this job I noticed the custom vars were set as follows:

IBIC_user = &IBIC_user (pass)
IBIMR_user = &IBIMR_user (pass)

I tried changing as you suggested but IBIMR_user is still not available in WFRS. So I added a new var to edasprof based on FOCSECUSER and it seems to work as a variable now (ie -TYPE) but not in the synonym.

Posts: 29 | Registered: July 17, 2007

Ignored post by Tim Easley posted

Tom Flynn

Expert

posted

Tim,
IBIMR_user is "ONLY" available to procedures run out of MRE/Content.
Never used in a synonym, never needed to, so, IBI needs to help/assist you there.
IBIC_user is for WFRS...

Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe

Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006

Ignored post by Tom Flynn posted

Member

posted

The prize goes to Marcy Miller for solving this problem.

Global variables have to be declared (VARIABLE NAME line below) in synonyms before they can be used.

Note, in this example &&WFRSUSER is created in EDASPROD.PRF and is the FOCSECUSER value with the domain name stripped off.

Thanks Marcy!

FILENAME=SP_SPONSORED_PROJECTS, SUFFIX=SQLMSS , $
VARIABLE NAME=&&WFRSUSER, $
SEGMENT=INPUT, SEGTYPE=S0, $
FIELDNAME=@USERNAME, ALIAS=P0001, USAGE=A30, ACTUAL=A30,
MISSING=ON, ACCESS_PROPERTY=(INTERNAL), $
FILTER FILTER1=@USERNAME EQ '&&WFRSUSER';
TITLE='INPUT,Filter', MANDATORY=YES, $

Posts: 29 | Registered: July 17, 2007

Ignored post by Tim Easley posted

Michael L. Meagher

Member

posted

Excellent!

In FOCUS since 1980

Posts: 11 | Location: Lynnwood, WA | Registered: November 02, 2007

Ignored post by Michael L. Meagher posted