Connect to remote host from within MySql stored procedure

Question

Possible Duplicate:
can I access a remote mysql server/db from a stored procedure?

I'm a bit new to MySQL. I want to create a stored routine that accepts a parameter and returns a dataset to a Read-Only user.

Within the stored routine, however, I want to establish a new connection (using different credentials) to a remote MySQL host and read the required values based on the input parameter.

I imagine it would go like this:

CREATE PROCEDURE procReturnsData(parameter1)
BEGIN
     <connect to remote MySQL server using new credentials>
     <Select col1, col2 from remoteServer.table1 Where col3 = parameter1 >
     <close connection>
END

What's the best way to achieve this?

Thanks.

http://stackoverflow.com/questions/4323225/can-i-access-a-remote-mysql-server-db-from-a-stored-procedure — Haim Evgi, Sep 07 '11 at 05:56

score 3 · Accepted Answer · edited Sep 06 '12 at 09:54

3

Using federated tables, you can create a remote table.

Unfortunately, I don't think it works with SSH without creating a tunnel at the OS level first.

edited Sep 06 '12 at 09:54

mmdemirbas

9,060
5
45
53

answered Sep 07 '11 at 06:27

Doug Kress

3,537
1
13
19

This appears to be the preferred way. – colonel_px Sep 08 '11 at 08:21

Connect to remote host from within MySql stored procedure

1 Answers1