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] SQLOUT: SQL STORED PROCEDURE
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] SQLOUT: SQL STORED PROCEDURE
 Login/Join
 
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, 2014Reply With QuoteReport This Post
Master
posted Hide Post
quote:
SQL SQLMSS EX spname
'&P1'
, '&P2'
, '&P3'
, '&mP4'
, '&P5'
, '&P6'
;



Use END after above statement.
 
Posts: 542 | Location: Dearborn, MI | Registered: June 03, 2009Reply With QuoteReport This Post
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, 2014Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
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, 2014Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
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, 2014Reply With QuoteReport This Post
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, 2014Reply With QuoteReport This Post
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: 405 | Location: India | Registered: June 13, 2013Reply With QuoteReport This Post
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, 2014Reply With QuoteReport This Post
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: 2282 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

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

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.