I'm new to using stored procedures in WebFOCUS. I've searched through all of the posts I could find on the forum and have not found a solution.
The stored proc I'm trying to run has no input parameters and one output parameter. It seems so simple, but it does not work. The error message is stating that the SP cannot be found.
ENGINE SQLMYSQL SET DEFAULT_CONNECTION armsdev
SQLMYSQL PREPARE SQLOUT FOR
EX armsdev.GetCustomerFunctionalGroups &Status;
TABLE FILE SQLOUT
ON TABLE HOLD AS SQLOUT1
Cannot include resource specified armsdev.GetCustomerFunctionalGroups
Can anyone help?This message has been edited. Last edited by: Tamra,
Can you try to create master file that SP and try that it is working or not?
WebFOCUS 8105, Dev Studio 8105, Windows 7, ALL Outputs
You can try creating a synonym for your stored procedure and then use the TABLE FILE syntax.
Let's say you have a procedure myproc1 as:
CREATE PROCEDURE myproc1(OUT param1 INT)
SELECT COUNT(*) INTO param1 FROM pdib.pd_job;
Create a synonym for the procedure using GUI or use a focexec syntax:
CREATE SYNONYM baseapp/mysql_proc_myproc1 FOR 'myproc1' DBMS SQLMYSQL AT CONPDIB STOREDPROCEDURE PARMS "?"
Now you can retrieve data from your procedure:
TABLE FILE BASEAPP/MYSQL_PROC_MYPROC1
-*ON TABLE HOLD ...
Thanks for the replies.
When I try to create a synonym for the stored proc, I get the following error:
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFFF) XOPEN: S1000
:0 [S1000] User does not have access to metadata required to determine
stored procedure parameter types. If rights cannot be granted, configure
connection with "noAccessToProcedureBodies=true" to have driver generate
parameters that represent INOUT string irregardless of the actual
(FOC1405) SQL PREPARR ERROR.
What does this error mean?
Please the 'User' that is on the connection for MySQL adapter. Does it have enough privileges?
One of the reasons for this error could be that a 'User' that is defined on your connection for MySQL adapter is (most probably) not the same that a 'User' used to logon to MySQL database when the MySQL procedure was created. Or, this user does not have privileges to access the Procedure.
Check the properties of the Logon ID that used to connect to your WebFOCUS server. What is the list of privileges?
Also, I found similar situation when the solution was to add on MySQL:
GRANT SELECT ON mysql.proc TO ....
The (1) option I mentioned above.
The requirements of using a stored procedure for synonym
creation, although the following doesn't explain the exact error you are getting you may
want to adjust it to comply with this:
A Synonym candidate can be any file that contains one (and only one) valid SQL
Query and does not contain end-of-statement delimiters (";" or "/") and
More information to help you out can be found Technical Content Library
Enter Stored procedure in the search criteria - several links will appear.
For Using the Adapter for MySQL Scroll to find....
WebFOCUS Reporting Server
> Adapter Administration
> Using the Adapter for MySQL
> Managing MySQL Metadata
The IBI Technical Support Center - " Search Techncial Content" is another place to find information.
Thank you for participating in the Focal Point Forum!
Focal Point Moderator
WebFOCUS 8x - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5
|Powered by Social Strata|