simultaneous multiple database access

[Steve]

MySQL allows you to copy a table from one database to another with this type of format:
CREATE TABLE database1.newtable LIKE database2.oldtable
INSERT database1.newtable SELECT * FROM database2.oldtable

The syntax database.table allows one to specify a table of a particular database, so you can use two different databases in a single command.

But LiveCode uses connection numbers to access databases, and only allows one connection number in a particular command (like the revExecuteSQL command).

So, how can one perform operations across databases, like copying a table from one database to another, which must be done in a single command?

Thanks,

Steve

[splash21]

Hi, Steve. If you grant the user that you connect with privileges for both databases, then you can access both DB's from 1 query.

[Steve]

Terrific! Can you give me a brief LiveCode example?

Thanks,

Steve Alessi

[splash21]

You can run something like the following providing you have the appropriate privileges;

Code: Select all

GRANT ALL ON otherDatabase.* TO 'user'@'localhost';

Check out the docs here...
http://dev.mysql.com/doc/refman/5.1/en/grant.html

[Steve]

Hi Splash21,

Sorry ... I was not clear in my question. I know how to grant permissions. What I don't know how to do is refer to two databases in one statement.

For example, in MySQL you can do this:
CREATE TABLE database1.newtable LIKE database2.oldtable -- which refers to two different databases
INSERT database1.newtable SELECT * FROM database2.oldtable -- which also refers to two different databases

But what would the equivalent syntax be in a LiveCode revExecuteSQL command? All the documentation and examples I can find demonstrate access to a single database, because only one connection number parameter is permitted.

Thanks again,

Steve Alessi

[splash21]

If you are connected to a database and want some info from 'database2' included in the results, you can ....

Code: Select all

SELECT users.name, colours.favouriteColour FROM users, database2.colours AS colours WHERE users.userId = colours.userId

...just specify the database name in the table alias

[Steve]

Hi Splash21,

Thanks very much! That worked for me inside a revExecuteSQL command. I guess I should have tried it, because it is standard mySQL syntax. But because the revExecuteSQL command refers to a database connection number, I assumed you could only operate on one database at a time. Apparently, the database connection number in LiveCode is somewhat "fake". You only have to give SOME exiting connection number, and your commands can then refer to ANY databases you have permission to access in your server area.

Thanks again,

Steve Alessi