LiveCode Forums

Hi Everyone,

I need to copy a MySQL database table from one database to another database.

Because LiveCode's revExecuteSQL command uses a database ID number and allows just ONE database ID number, as in the following syntax:
revExecuteSQL DatabaseID, "QueryString"

I cannot see any way to copy a table from one database to another. This is easy in other languages where the database NAMES (instead of ID numbers) are used, e.g.,
INSERT INTO DatabaseName2.TableName2 SELECT * from DatabaseName1.TableName1

Is there any way to do this in a revExecuteSQL command?

Thanks,

Steve Alessi

Hi Steve,
you can do it. The database name in revOpenDatabase is just to connect to a database, so you don't need to query "USE ...", however you have access to all databases with your credentials.
I used your query and it works!!!!

Hi MaxV,

I’m still a little uncertain about what syntax worked for you.

Let’s say I have two databases:
“database1” with ID number 1
and
“database2” with ID number 2,

and the table name is the same, “tablename”, in each database, I try this:

revExecuteSQL 1 , “INSERT INTO database2.tablename SELECT * FROM database1.tablename”

and this (where only the database number is different)

revExecuteSQL 2 , “INSERT INTO database2.tablename SELECT * FROM database1.tablename”

In either case it does not work, because it does not recognize the existence of one of the two databases.

What syntax did you use?

Thanks much!

Steve Alessi

Steve,
You have ONE MySQL server.
This server has TWO databases.
You log in the server with revOpenDatabase: The variable connID containg the connection to the MySQL server and already sent this query: USE databasename1 ;
That's all.
Now you can do your queries as you wish and they work. I used all these this: All with the same connID.
I attached also my stack as example.

Hi again MaxV,

Thanks very much. Yes, that works great for me, when both databases are on the server. But sometimes I'm working with databases on different servers. I guess in that situation I'll have to use export and import with phpMyAdmin.

Thanks very much for your help.

Sincerely,

Steve Alessi

Are these going to be routines that you run on a regular basis (or semi-regular), or random things that change frequently?

If you have livecode server running on your db server, it is possible to have a short bash script that you can launch with Livecode that could ssh into your other server and do your database stuff. You could even add variables to launching the bash script:

put shell("./scripts/dbclone database1 database2") into tClone

Livecode Server running on your database server can add a lot of flexibility to sending commands to the server from an app (and is easier to secure). It takes a bit of dinkering around, but once you have it set up it is surprisingly easy to connect with.

Hi Ghettocottage!

The "same server" copying, which MaxV helped me with, is a regular thing, for doing backups. Copying to a different server is an occasional thing, when setting up a new user who has a different server. I will try your solution for that.

Thanks,

Steve Alessi

are you copying contents of a database, or just the structure when you are setting up a new user?

Structure and content.

Steve

Well on different databases the best solution is the mysqldump binary: replace the IPserver with the IPserver1 of the first server.

then

Terrific ... thanks much.

Sincerely,

Steve Alessi