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] Sql Store Proc as Master in Maintain

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Sql Store Proc as Master in Maintain
 Login/Join
 
Gold member
posted
Good morning,

I am trying to create a new .mnt with a sql stored proc as the master. I used the WebFOCUS server to create the synonym. The stored proc has four parameters. Sample prompts me for the parameters and returns the data as expected. I have written a .fex, which returns the expected output based on the values input for the parameters. I have a very simple .mnt I have created. Without the INFER statement, deploy creates or updates the .fcm for my .mnt. If I include the INFER statement, it does not. With and without the INFER statement, my code appears to deploy. I receive no errors and the deployment details indicate it was compiled. I have searched and not found anything to help me. Any assistance would be appreciated.

This app is using WebFOCUS 7703 and SQLServer 2012 or 2014 running in 2008 compatibility mode.

I have included all code and results.

Stored proc
CREATE PROCEDURE [dbo].[usp_OffEdCourseAttendSelect]
@FacilityCode char(4),
@CourseCode char(4),
@SessionNumber int,
@AttendanceMonthYear datetime
AS
BEGIN

SELECT --[OffenderEdCourseAttendanceID],
[LOCATION],
[COURSE],
[SESSION_NO],
[CLASS_MMYY]
FROM DCSCOURS05
WHERE LOCATION = @FacilityCode
AND COURSE = @CourseCode
AND SESSION_NO = @SessionNumber
AND CLASS_MMYY = @AttendanceMonthYear
END
GO


.ACX File
SEGNAME=INPUT, CONNECTION=CORR, STPNAME=dbo.usp_OffEdCourseAttendSelect, $
SEGNAME=OUTPUT, STPRESORDER=0, $
SEGNAME=ANSWERSET1, STPRESORDER=1, $

.MAS File
FILENAME=USP_OFFEDCOURSEATTENDSELECT, SUFFIX=SQLMSS , $
SEGMENT=INPUT, SEGTYPE=S0, $
FIELDNAME=@FACILITYCODE, ALIAS=P0001, USAGE=A4, ACTUAL=A4,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@COURSECODE, ALIAS=P0002, USAGE=A4, ACTUAL=A4,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@SESSIONNUMBER, ALIAS=P0003, USAGE=I11, ACTUAL=I4,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@ATTENDANCEMONTHYEAR, ALIAS=P0004, USAGE=MDYY, ACTUAL=DATE,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
SEGMENT=OUTPUT, SEGTYPE=S0, PARENT=INPUT, $
FIELDNAME=@RETURN_VALUE, ALIAS=P0000, USAGE=I11, ACTUAL=I4, $
SEGMENT=ANSWERSET1, SEGTYPE=S0, PARENT=INPUT, $
FIELDNAME=LOCATION, ALIAS=LOCATION, USAGE=A4, ACTUAL=A4, $
FIELDNAME=COURSE, ALIAS=COURSE, USAGE=A4, ACTUAL=A4, $
FIELDNAME=SESSION_NO, ALIAS=SESSION_NO, USAGE=I11, ACTUAL=I4, $
FIELDNAME=CLASS_MMYY, ALIAS=CLASS_MMYY, USAGE=MDYY, ACTUAL=DATE, $


Sample Data for BASEAPP/USP_OFFEDCOURSEATTENDSELECT


LOCATION COURSE SESSION_NO CLASS_MMYY
WMC AGED 11 03/01/2017
WMC AGED 11 03/01/2017


-* File StoreProcAsMaster.fex
TABLE FILE USP_OFFEDCOURSEATTENDSELECT
BY LOCATION
BY COURSE
BY SESSION_NO
BY CLASS_MMYY
WHERE ( @FACILITYCODE EQ 'WMC' ) AND ( @COURSECODE EQ 'AGED' ) AND ( @SESSIONNUMBER EQ 11 ) AND ( @ATTENDANCEMONTHYEAR EQ '03012017' );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

.fex output
LOCATION COURSE SESSION_NO CLASS_MMYY
WMC AGED 11 03/01/2017

.mnt
MAINTAIN FILE USP_OFFEDCOURSEATTENDSELECT

$$Declarations

Case Top
-*INFER usp_offedcourseattendselect.ANSWERSET1.LOCATION INTO StkAttendance;

COMPUTE
FacilityCode/A4 = 'WMC';
CourseCode/A4 = 'AGED';
SessionNumber/I11 = 10;
AttendanceMonthYear/MDYY = 03012017;

Winform Show Form1;
EndCase

END

Deployment Details
---------------Scenario: Local Deploy (Development)---------------
Calling deploy extensions...
Compiling maintain procedures...
IBFS:/localhost/DEV/testcode/TestPopFromSql.mnt

********************************************************************************************
Deployment Status: 0 error(s), 0 warning(s)
********************************************************************************************


Thank you,
Deana

This message has been edited. Last edited by: Deana,


WebFOCUS 7.6.8 and 7.7.03; Windows Server 2003 R2 and Windows Server 2008 R2, respectively; Development environments - Windows Server 2003 R2 and Windows 7 Professional, respectively;
excel, html, pdf
 
Posts: 89 | Registered: March 19, 2011Report This Post
Master
posted Hide Post
Deana
The reason the Infer statement is yielding an error is that Maintain doesn't recognize the @fieldnames. If you just used Maintain against a Master containing:

SEGMENT=ANSWERSET1, SEGTYPE=S0, PARENT=INPUT, $
FIELDNAME=LOCATION, ALIAS=LOCATION, USAGE=A4, ACTUAL=A4, $
FIELDNAME=COURSE, ALIAS=COURSE, USAGE=A4, ACTUAL=A4, $
FIELDNAME=SESSION_NO, ALIAS=SESSION_NO, USAGE=I11, ACTUAL=I4, $
FIELDNAME=CLASS_MMYY, ALIAS=CLASS_MMYY, USAGE=MDYY, ACTUAL=DATE, $

You would be fine. In your example you are computing values for FacilityCode, CourseCode, SessionNumber and AttendanceMonthYear. However since you are including formats with the Computes, these are not the same as the database fields and Maintain can't write to them.

Can you just create a Master from the lowest segment and use Maintain to write to that?

If this isn't what you are looking for, please open a case so we can track it.
Thanks
mark
 
Posts: 663 | Location: New York | Registered: May 08, 2003Report This Post
Gold member
posted Hide Post
As of this morning, I have a work around to allow me to use a stored procedure as my data source using either TABLE or MAINTAIN. As Mark pointed out MAINTAIN does not recognize the @ generated in the original synonym. However, TABLE requires the use of the @ fields.

I have a stored proc, usp_getdata. I created the synonym for this stored procedure using the webfocus console which generated the following code.

.acx
SEGNAME=INPUT, CONNECTION=CONN, STPNAME=dbo.usp_getdata, $
SEGNAME=OUTPUT, STPRESORDER=0, $
SEGNAME=ANSWERSET1, STPRESORDER=1, $

.mas
FILENAME=USP_GETDATE, SUFFIX=SQLMSS, $
SEGMENT=INPUT, SEGTYPE=S0, $
FIELDNAME=@InputField1, ALIAS=P0001, USAGE=A4, ACTUAL=A4,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
SEGMENT=OUTPUT, SEGTYPE=S0, PARENT=INPUT, $
FIELDNAME=@RETURN_VALUE, ALIAS=P0000, USAGE=I11, ACTUAL=I4, $
SEGMENT=ANSWERSET1, SEGTYPE=S0, PARENT=INPUT, $
FIELDNAME=Field1Name, ALIAS=Field1Alias, USAGE=I11, ACTUAL=I4, $

I then copied these 2 files to usp_getdatamnt.acx and usp_getdatamnt.mas and made changes so the files look like this.

.acx
SEGNAME= ANSWERSET1, CONNECTION=CONN, STPNAME=dbo.usp_getdata, $

.mas
FILENAME=USP_GETDATE, SUFFIX=SQLMSS , $
SEGMENT=ANSWERSET1, SEGTYPE=S0, $
FIELDNAME=Field1Name, ALIAS=Field1Alias, USAGE=I11, ACTUAL=I4, $

I use usp_getdata whenever I need to use TABLE file and usp_getdatamnt for MAINTAIN.

This does create some additional overhead and maintenance, but appears to be working well now.

Thank you to Mark and Adrian. I appreciate your assistance.

Deana


WebFOCUS 7.6.8 and 7.7.03; Windows Server 2003 R2 and Windows Server 2008 R2, respectively; Development environments - Windows Server 2003 R2 and Windows 7 Professional, respectively;
excel, html, pdf
 
Posts: 89 | Registered: March 19, 2011Report 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] Sql Store Proc as Master in Maintain

Copyright © 1996-2020 Information Builders