copying between databases

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
Steve
Posts: 30
Joined: Fri Nov 07, 2008 10:46 pm
Location: Iowa, U.S.A.
Contact:

copying between databases

Post by Steve » Thu Aug 10, 2017 2:40 am

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
Steve Alessi
Associate Professor
The University of Iowa

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: copying between databases

Post by MaxV » Thu Aug 10, 2017 11:29 am

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!!!! :D :D :D :D :D
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

Steve
Posts: 30
Joined: Fri Nov 07, 2008 10:46 pm
Location: Iowa, U.S.A.
Contact:

Re: copying between databases

Post by Steve » Thu Aug 10, 2017 5:02 pm

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 Alessi
Associate Professor
The University of Iowa

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: copying between databases

Post by MaxV » Fri Aug 11, 2017 10:02 am

Steve,
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
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:

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
All with the same connID.
I attached also my stack as example. :D
Attachments
Query tester.livecode.zip
(894 Bytes) Downloaded 268 times
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

Steve
Posts: 30
Joined: Fri Nov 07, 2008 10:46 pm
Location: Iowa, U.S.A.
Contact:

Re: copying between databases

Post by Steve » Fri Aug 11, 2017 4:41 pm

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
Steve Alessi
Associate Professor
The University of Iowa

ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 366
Joined: Tue Apr 10, 2012 9:18 am

Re: copying between databases

Post by ghettocottage » Fri Aug 11, 2017 6:25 pm

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.

Steve
Posts: 30
Joined: Fri Nov 07, 2008 10:46 pm
Location: Iowa, U.S.A.
Contact:

Re: copying between databases

Post by Steve » Fri Aug 11, 2017 8:50 pm

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
Steve Alessi
Associate Professor
The University of Iowa

ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 366
Joined: Tue Apr 10, 2012 9:18 am

Re: copying between databases

Post by ghettocottage » Fri Aug 11, 2017 9:12 pm

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

Steve
Posts: 30
Joined: Fri Nov 07, 2008 10:46 pm
Location: Iowa, U.S.A.
Contact:

Re: copying between databases

Post by Steve » Sat Aug 12, 2017 3:18 pm

Structure and content.

Steve
Steve Alessi
Associate Professor
The University of Iowa

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: copying between databases

Post by MaxV » Mon Aug 21, 2017 3:19 pm

Well on different databases the best solution is the mysqldump binary:

Code: Select all

put shell("mysqldump --host=IPserver1 -u  user  -p  password database_name tablename > dumpfilename.sql")
replace the IPserver with the IPserver1 of the first server.

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

Steve
Posts: 30
Joined: Fri Nov 07, 2008 10:46 pm
Location: Iowa, U.S.A.
Contact:

Re: copying between databases

Post by Steve » Mon Aug 21, 2017 4:53 pm

Terrific ... thanks much.

Sincerely,

Steve Alessi
Steve Alessi
Associate Professor
The University of Iowa

Post Reply

Return to “Databases”