?
Solved

How to access a remote stored procedure from local database?

Posted on 2014-07-16
3
Medium Priority
?
953 Views
Last Modified: 2014-07-19
Dear Experts,

I came across an issue pertaining to stored procedure in mysql database. I have two offices which both of them are running on mysql. I've created a store procedure named 'UPD_INVENTORY' on mysql server#1. Currently, I am working at an office that has mysql server#2. If I wish to use the 'UPD_INVENTORY' stored procedure on mysql server#1, is there a way that I can make a stored procedure call on mysql server#1 from mysql server#2? Does such syntax exists like this:
CALL server#1location.db_name.procedure_name ? If not what other options do I have besides using php? Will I able to use bash script in linux to accomplish such a task?

Thanks.
0
Comment
Question by:Kinderly Wade
  • 2
3 Comments
 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 40202308
You should be able to use FEDERATED tables; though I have never tried to use a stored procedure that way.  Once you have a connect with federated tables you should be able to access just as if the table is local. However you may find you pay a big performance penalty.

The manual description for federated tables is here:

http://dev.mysql.com/doc/refman/5.0/en/federated-use.html

Cd&
0
 

Author Comment

by:Kinderly Wade
ID: 40206032
Thanks COBOL for the feedback.

I've tried with FEDERATED tables.

Is it possible that I can make a call to a store procedure that's located on a remote mysql DB?
If possible, how can this be achieved?
0
 
LVL 53

Accepted Solution

by:
COBOLdinosaur earned 900 total points
ID: 40206423
Well if it won't let you run the stored procedure with a remote connection, then you will need to have a script on server #1 to run the stored procedure and return the data. Though I would have thought that the remote connection should be enough unless there is a security or permissions issue.

Cd&
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month16 days, 22 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question