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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] MySQL Stored Procedures
 Login/Join
 
Jeff Weatherly
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, 2016Report This Post
Avinash
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, 2013Report This Post
Marina
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: 37 | Registered: February 13, 2007Report This Post
Jeff Weatherly
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, 2016Report This Post
Marina
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: 37 | Registered: February 13, 2007Report This Post
Marina
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: 37 | Registered: February 13, 2007Report This Post
Tamra
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, 2009Report 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     [CLOSED] MySQL Stored Procedures

Copyright © 1996-2020 Information Builders