Hi All,

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
PRINT *
ON TABLE HOLD AS SQLOUT1
END
-RUN


Error:
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?

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)
BEGIN
SELECT COUNT(*) INTO param1 FROM pdib.pd_job;
END

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 "?"
END

Now you can retrieve data from your procedure:
TABLE FILE BASEAPP/MYSQL_PROC_MYPROC1
PRINT
OUTPUT.PARAM1
-*ON TABLE HOLD ...
END

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
parameter types.
(FOC1405) SQL PREPARR ERROR.


What does this error mean?

Jeff,

Please the 'User' that is on the connection for MySQL adapter. Does it have enough privileges?

1)
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.

2)
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.

Jeff,

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
comments.

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!
Tamra Colangelo
Focal Point Moderator
Information Builders