Can I join a remote database table (separate server, username, password) using Editor?

Stacey1134

Hi. I am moving one of my tables to a remote server I have so our collaborators and movement partners may have access to it. I don't want them to have access to organization's server. At present my web application edits and uses that table mainly through editor, and other tables join to it regularly. But it is on my own server right now.

Can I do this in editor, like creating db1 and db2? in the config? Passwords and usernames ARE different, and I believe making them the same in this instance would leave my server vulnerable.

I am looking for ideas and suggestions.

Thanks in advance for any insight you can lend.

allan

Yes you can configure Editor's server-side code to use a remote database. As long as the remote server is listening for connections on the WAN (perhaps firewalled to your specific source IP) and the user is setup to allow remote access. You just need to specify the "host" field - normally it would be localhost but you can set it as you need.

Allan

Stacey1134

Okay, that part I get. Sorry I wasn't clear enough. Can I join a remote database table to one of my tables using editor? From old documentation, it seems like there was some issue with that in the past.

allan

Thanks for the clarification. Is it MySQL you are using or some other? It really depends on the SQL server and how accessible it makes it. MySQL has a Federated way of doing it, but that syntax isn't something that Editor supports.

Allan

Stacey1134

Darn, it is MySQL. I can do it without using dataTables of course, but some of the main modules that use that database I did with dataTables. Okay, thanks for the insight.

allan

If you need read only access, then you could readily use your non-Editor SELECT to get the data - just feed it back as JSON. I think the only real trouble would be if you need full read/write access to both the local and remote databases.

Your non-Editor SELECT - do you use MySQL's federation for that?

Allan

Stacey1134

Yes the remote server does have MySQL the Federated storage option enabled
Also thanks, non-Editor select gives me some ideas. Our collaborators and movement partners will only need read access. That is not the case with our own users however.

Got some ideas, may or may not work. BTW Thanks for your help with this!