copying between databases

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

copying between databases

Postby 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
Steve
 
Posts: 30
Joined: Fri Nov 07, 2008 10:46 pm
Location: Iowa, U.S.A.

Re: copying between databases

Postby 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: http://livecodeitalia.blogspot.it
To post code use this: http://tinyurl.com/ogp6d5w
MaxV
 
Posts: 1282
Joined: Tue May 28, 2013 2:20 pm
Location: Italy

Re: copying between databases

Postby 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
Steve
 
Posts: 30
Joined: Fri Nov 07, 2008 10:46 pm
Location: Iowa, U.S.A.

Re: copying between databases

Postby 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 14 times
Livecode Wiki: http://livecode.wikia.com
My blog: http://livecodeitalia.blogspot.it
To post code use this: http://tinyurl.com/ogp6d5w
MaxV
 
Posts: 1282
Joined: Tue May 28, 2013 2:20 pm
Location: Italy

Re: copying between databases

Postby 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
Steve
 
Posts: 30
Joined: Fri Nov 07, 2008 10:46 pm
Location: Iowa, U.S.A.

Re: copying between databases

Postby 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.
ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
 
Posts: 219
Joined: Tue Apr 10, 2012 9:18 am

Re: copying between databases

Postby 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
Steve
 
Posts: 30
Joined: Fri Nov 07, 2008 10:46 pm
Location: Iowa, U.S.A.

Re: copying between databases

Postby 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?
ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
 
Posts: 219
Joined: Tue Apr 10, 2012 9:18 am

Re: copying between databases

Postby Steve » Sat Aug 12, 2017 3:18 pm

Structure and content.

Steve
Steve Alessi
Associate Professor
The University of Iowa
Steve
 
Posts: 30
Joined: Fri Nov 07, 2008 10:46 pm
Location: Iowa, U.S.A.

Re: copying between databases

Postby 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: http://livecodeitalia.blogspot.it
To post code use this: http://tinyurl.com/ogp6d5w
MaxV
 
Posts: 1282
Joined: Tue May 28, 2013 2:20 pm
Location: Italy

Re: copying between databases

Postby Steve » Mon Aug 21, 2017 4:53 pm

Terrific ... thanks much.

Sincerely,

Steve Alessi
Steve Alessi
Associate Professor
The University of Iowa
Steve
 
Posts: 30
Joined: Fri Nov 07, 2008 10:46 pm
Location: Iowa, U.S.A.


Return to Databases

Who is online

Users browsing this forum: No registered users and 1 guest