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     [CLOSED] MySQL Stored Procedures
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] MySQL Stored Procedures
 Login/Join
 
Member
posted
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,


WebFOCUS 8.1.05M
 
Posts: 6 | Registered: June 01, 2016Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Can you try to create master file that SP and try that it is working or not?


Thanks!
@vi

WebFOCUS 8105, Dev Studio 8105, Windows 7, ALL Outputs
 
Posts: 103 | Registered: July 08, 2013Reply With QuoteReport This Post
Silver Member
posted Hide Post
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
 
Posts: 32 | Registered: February 13, 2007Reply With QuoteReport This Post
Member
posted Hide Post
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?


WebFOCUS 8.1.05M
 
Posts: 6 | Registered: June 01, 2016Reply With QuoteReport This Post
Silver Member
posted Hide Post
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?
 
Posts: 32 | Registered: February 13, 2007Reply With QuoteReport This Post
Silver Member
posted Hide Post
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.
 
Posts: 32 | Registered: February 13, 2007Reply With QuoteReport This Post
Guru
posted Hide Post
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


WebFOCUS 8x - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5
 
Posts: 487 | Location: Toronto | Registered: June 23, 2009Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] MySQL Stored Procedures

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