Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Provide default value (username) for stored procedure synonym
Go
New
Search
Notify
Tools
Reply
  
[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: 27 | Registered: July 17, 2007Reply With QuoteReport 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.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6252 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport 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: 27 | Registered: July 17, 2007Reply With QuoteReport 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: 27 | Registered: July 17, 2007Reply With QuoteReport 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: 1961 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport 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: 27 | Registered: July 17, 2007Reply With QuoteReport 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: 27 | Registered: July 17, 2007Reply With QuoteReport 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: 1961 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport 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: 27 | Registered: July 17, 2007Reply With QuoteReport This Post
Member
posted Hide Post
Excellent!


In FOCUS since 1980

 
Posts: 11 | Location: Lynnwood, WA | Registered: November 02, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.