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.

New TIBCO Community Coming Soon
In early summer, TIBCO plans to launch a new community—with a new user experience, enhanced search, and expanded capabilities for member engagement with answers and discussions! In advance of that, the current myibi community will be retired on April 30. We will continue to provide updates here on both the retirement of myibi and the new community launch.

What You Need to Know about Our New Community
We value the wealth of knowledge and engagement shared by community members and hope the new community will continue cultivating networking, knowledge sharing, and discussion.

During the transition period, from April 20th until the new community is launched this summer, myibi users should access the TIBCO WebFOCUS page to engage.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SQLOUT: SQL STORED PROCEDURE

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] SQLOUT: SQL STORED PROCEDURE
 Login/Join
 
BM
Platinum Member
posted
HI,
Trying SQL stored procedure with parameters.
I get popup window "File Download", "WFServlet.ibfs".
Not sure what that is.

-******

ENGINE SQLMSS SET DEFAULT_CONNECTION &severname

SQL SQLMSS SET NOCOUNT ON
SQL SQLMSS SET ANSI_WARNINGS OFF


SQL SQLMSS EX spname
'&P1'
, '&P2'
, '&P3'
, '&mP4'
, '&P5'
, '&P6'
;

-RUN


TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS TT
END

TABLE FILE TT
PRINT *
END

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


WebFOCUS 8202M
 
Posts: 167 | Location: Montreal | Registered: September 23, 2014Report This Post
Ram Prasad E
Master
posted Hide Post
quote:
SQL SQLMSS EX spname
'&P1'
, '&P2'
, '&P3'
, '&mP4'
, '&P5'
, '&P6'
;



Use END after above statement.


WebFOCUS 8.1.05
Windows
http://ibiwebfocus.wordpress.com
https://www.facebook.com/groups/ibi.webfocus/
 
Posts: 542 | Location: Dearborn, MI | Registered: June 03, 2009Report This Post
BM
Platinum Member
posted Hide Post
Thanks but still the same issue.
I tried a different stored proc. without any parameter and it gives me

(FOC1400) SQLCODE IS 229 (HEX: 000000E5) XOPEN: S0005
: (229) [S0005] The EXECUTE permission was denied on the object 'uspname'
(FOC1405) SQL PREPARE ERROR.

This message has been edited. Last edited by: BM,
 
Posts: 167 | Location: Montreal | Registered: September 23, 2014Report This Post
njsden
Virtuoso
posted Hide Post
Well, that is a different error which points to execution permissions on the database side.

Please make sure that whichever DB account you are using in your adapter connection from WebFOCUS can indeed EXECUTE the stored procedure.

It would also help if you posted actual code/error messages. I mean, your code seems to attempt execution of a stored procedure called "spname", yet the error message complains about permissions on "uspname". Which one is it? It is really not relevant to us but does cause confusion.

Check for proper DB permissions and you should be fine.


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
BM
Platinum Member
posted Hide Post
Hi,
Thanks.. sorry for the confusion. I didn't want to put the real stored proc. name and that's when I mistyped.
I was able to solve the security issue by granting access to stored proc.
But now I am back to my original issue which is the poping up of File download window asking me to download WFSevlet.ibfs.
It gives me 3 options. "Find", "Save", "Cancel".
Whichever option I choose, it gives me a message "Waiting for Response".


WebFOCUS 8202M
 
Posts: 167 | Location: Montreal | Registered: September 23, 2014Report This Post
njsden
Virtuoso
posted Hide Post
Try something like this and let us know what you get:

ENGINE SQLMSS SET DEFAULT_CONNECTION &severname

SQL SQLMSS SET NOCOUNT ON 
SQL SQLMSS SET ANSI_WARNINGS OFF


SQL SQLMSS
EX spname '&P1', '&P2', '&P3', '&P4', '&P5', '&P6'
;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS HREPORT
END
-RUN

TABLE FILE HREPORT
PRINT *
ON TABLE SET HTMLCSS ON
ON TABLE PCHOLD FORMAT HTML
END
-RUN


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
jvb
Gold member
posted Hide Post
I used global temporary table. Here the stored proc updates the GTT and which i use the GTT as normal database table.
SQL SQLORA CALL stored_proc('&PARAM1.EVAL','&PARAM2.EVAL',&PARAM3.EVAL,'&FDT.EVAL','&PARAM4.EVAL', '&PARAM5', '&PARAM6') ;
END
-RUN
-***The stored proc is called and updates at Global temp table(GLOBAL_TEMP_TABLE) which is further used as simple SQL passthrough
SQL SQLORA PREPARE SQLOUT FOR
SELECT * FROM GLOBAL_TEMP_TABLE
END

TABLE FILE SQLOUT
PRINT *
END

hope this helps.


WF 8.1.04,Infoassist,Oracle, Excel, PDF,HTML.
 
Posts: 82 | Registered: January 06, 2014Report This Post
BM
Platinum Member
posted Hide Post
tried still the same result.

quote:
Originally posted by njsden:
Try something like this and let us know what you get:

ENGINE SQLMSS SET DEFAULT_CONNECTION &severname

SQL SQLMSS SET NOCOUNT ON 
SQL SQLMSS SET ANSI_WARNINGS OFF


SQL SQLMSS
EX spname '&P1', '&P2', '&P3', '&P4', '&P5', '&P6'
;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS HREPORT
END
-RUN

TABLE FILE HREPORT
PRINT *
ON TABLE SET HTMLCSS ON
ON TABLE PCHOLD FORMAT HTML
END
-RUN


WebFOCUS 8202M
 
Posts: 167 | Location: Montreal | Registered: September 23, 2014Report This Post
Rifaz
Guru
posted Hide Post
As far as I remember, Stored procedures must adhere to IBI rules. Just validate once, your SP adhered according to IBI. Please surf techsupport.


-Rifaz

WebFOCUS 7.7.x and 8.x
 
Posts: 406 | Location: India | Registered: June 13, 2013Report This Post
BM
Platinum Member
posted Hide Post
Hi all,
after a few tries I was able to resolve the issue.

Here is my code.


-***Code**
ENGINE SQLMSS SET DEFAULT_CONNECTION &servername
SQL SQLMSS PREPARE SQLOUT FOR
EXEC [dbname].[dbo].[spname] &p1
END
TABLE FILE SQLOUT
PRINT *
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END


WebFOCUS 8202M
 
Posts: 167 | Location: Montreal | Registered: September 23, 2014Report This Post
MartinY
Virtuoso
posted Hide Post
Edit your first post and add [SOLVED] at the beginning of the subject.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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] SQLOUT: SQL STORED PROCEDURE

Copyright © 1996-2020 Information Builders