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] Provide default value (username) for stored procedure synonym

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Provide default value (username) for stored procedure synonym
 Login/Join
 
Member
posted
I've created a synonym for an MS SQL stored procedure. The SP requires a username which I need to pass the &IBIMR_user value.
I've tried modifying the synonym to pass &IBIMR_user to the SP but no luck yet.

The synonym includes the following line in the INPUT segment.
SEGMENT=INPUT, SEGTYPE=S0, $
FIELDNAME=@USERNAME, ALIAS=P0001, USAGE=A30, ACTUAL=A30,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $

I tried adding the XDEFAULT parameter as follows but it still requires manually entering the username to request sample data.
SEGMENT=INPUT, SEGTYPE=S0, $
FIELDNAME=@USERNAME, ALIAS=P0001, USAGE=A30, ACTUAL=A30,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), XDEFAULT='&IBIMR_user.EVAL', $

I also tried adding the XDEFAULT parameter hard-coded with my username, still no luck.
SEGMENT=INPUT, SEGTYPE=S0, $
FIELDNAME=@USERNAME, ALIAS=P0001, USAGE=A30, ACTUAL=A30,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), XDEFAULT='teasley', $

Version 8.2.06

This message has been edited. Last edited by: FP Mod Chuck,
 
Posts: 29 | Registered: July 17, 2007Report This Post
Expert
posted Hide Post
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.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs: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, 2006Report This Post
Member
posted Hide Post
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, 2007Report This Post
Member
posted Hide Post
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, 2007Report This Post
Expert
posted Hide Post
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, 2006Report This Post
Member
posted Hide Post
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, 2007Report This Post
Member
posted Hide Post
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, 2007Report This Post
Expert
posted Hide Post
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, 2006Report This Post
Member
posted Hide Post
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, 2007Report This Post
Member
posted Hide Post
Excellent!


In FOCUS since 1980

 
Posts: 11 | Location: Lynnwood, WA | Registered: November 02, 2007Report 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] Provide default value (username) for stored procedure synonym

Copyright © 1996-2020 Information Builders