copying between databases
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
copying between databases
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
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
Steve Alessi
Associate Professor
The University of Iowa
Associate Professor
The University of Iowa
Re: copying between databases
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!!!!
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!!!!
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
Re: copying between databases
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
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 Alessi
Associate Professor
The University of Iowa
Associate Professor
The University of Iowa
Re: copying between databases
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.
You have ONE MySQL server.
This server has TWO databases.
You log in the server with revOpenDatabase:
Code: Select all
revOpenDatabase("mysql", "www.example.com", "databaseName1", myUsr, myPass) into connID
That's all.
Now you can do your queries as you wish and they work. I used all these this:
Code: Select all
put "SELECT * FROM DatabaseName2.TableName1 ;" into tSQL
put revDataFromQuery(tab, return, connID, tSQL) into tData
Code: Select all
put "SELECT * FROM DatabaseName1.TableName1 ;" into tSQL
put revDataFromQuery(tab, return, connID, tSQL) into tData
Code: Select all
put "INSERT INTO DatabaseName2.TableName2 SELECT * from DatabaseName1.TableName1" into tSQL
revExecuteSQL connID, tSQL
I attached also my stack as example.
- Attachments
-
- Query tester.livecode.zip
- (894 Bytes) Downloaded 324 times
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
Re: copying between databases
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
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
Steve Alessi
Associate Professor
The University of Iowa
Associate Professor
The University of Iowa
-
- Livecode Opensource Backer
- Posts: 366
- Joined: Tue Apr 10, 2012 9:18 am
Re: copying between databases
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.
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.
Re: copying between databases
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
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
Steve Alessi
Associate Professor
The University of Iowa
Associate Professor
The University of Iowa
-
- Livecode Opensource Backer
- Posts: 366
- Joined: Tue Apr 10, 2012 9:18 am
Re: copying between databases
are you copying contents of a database, or just the structure when you are setting up a new user?
Re: copying between databases
Structure and content.
Steve
Steve
Steve Alessi
Associate Professor
The University of Iowa
Associate Professor
The University of Iowa
Re: copying between databases
Well on different databases the best solution is the mysqldump binary:
replace the IPserver with the IPserver1 of the first server.
then
Code: Select all
put shell("mysqldump --host=IPserver1 -u user -p password database_name tablename > dumpfilename.sql")
then
Code: Select all
put shell("mysql --host=IPserver2 -u user -p password database_name < dumpfilename.sql ")
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
Re: copying between databases
Terrific ... thanks much.
Sincerely,
Steve Alessi
Sincerely,
Steve Alessi
Steve Alessi
Associate Professor
The University of Iowa
Associate Professor
The University of Iowa