×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

VFP9 copying records from one MySQL database to another MySQL database

VFP9 copying records from one MySQL database to another MySQL database

VFP9 copying records from one MySQL database to another MySQL database

(OP)
I have a VFP 9 application that connects to a MYSql archive database, I also have another database that is an exact copy of the archive database. What is the best way to copy variuous records from tables within the archive database into the other database? Any thoughts and suggestions would really help. Many thanks

RE: VFP9 copying records from one MySQL database to another MySQL database

You say you already have a way of connecting to your archive database. Whatever that method is, I suggest you use the same method for the new database. For example, if you currently use SQLCONNECT() or SQLSTRINGCONNECT() to get a connection handle, use that same function to point to the new database and get a new connection handle to it. If you use remote views, set up a new view (it can be in the same DBC as the existing one) to point to the relevant table in the new database.

If you would care to give us some details about how you connect at present, we can talk you through the steps.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: VFP9 copying records from one MySQL database to another MySQL database

Alternatively, you can do the whole thing within MySQL, without the overhead of transferring all the data to/from VFP.

I'll assume you know how to get a connection to your server, and how to send a SQL command via SQLEXEC(). If so, send the followong two commands:

CODE -->

CREATE TABLE new_db.new_table LIKE archive_db.existing_table;
 
INSERT new_db.new_table SELECT * FROM archive_db.existing_table; 

Obvously, you will need to substitute the actual database and table names. And if the new table already exists, skip the first of the above commands.

Note that I haven't tested the above. It's just to give you the general idea.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: VFP9 copying records from one MySQL database to another MySQL database

(OP)
Hi, Thanks for the replies... I am familiar with connecrting/inserting etc.... Im really after a faster/easier way of copying the data... The records will come from a number of tables and some of the records are quite large. It will need to be done via VFP as the client is using an 'archive' system to look at old client records, and I need to give them the facilty to copy the various records onto their 'Live' system.
It seems that I'll have to do it using reading the archive, then inserting each field into the live table....

RE: VFP9 copying records from one MySQL database to another MySQL database

When you say the client is using an archive system to "look at" the records, do you mean that the user will actually be looking at the data on the screen in order to decide which records to copy? If so, then yes, you will have to bring the data into VFP.

But if you mean that you want to copy only those records which meet some criterion, then it's simply a matter of adding a WHERE clause to the INSERT ... SELECT.

If you do need to bring the data into VFP, one way to do that would be to two create remote views. One would point to the archive table; the other to the new table. Create a simple form, with Next, Previous, First, Last buttons; bind it to the data in the first view. Let the user click a button to copy the currently-viewed record to the second view (which would have to be updatable, of course). Finally, commit the data from the second view back to the remote database.

That's just one way of doing it. There are other approaches as well.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: VFP9 copying records from one MySQL database to another MySQL database

It's still not clear what you want, even contradictory. You say both databases are exact copies, then what do you want to copy at all? Or is it just the goal to have exact copies? Or was that just referring to the schema of the databases?

The easiest way to populate the data the same would be backup&restore, of course.

I think you need to explain in a bit more detail what needs to be copied from where to where with which criteria and user interaction or not.

If the databases are not on the same server and each connection can only work on one of the databases you of course would go through copies, in the simplest case have updatable remote views, use one for the source data and let it populate with the data to copy over, use the remote view to the destination database with WHERE 1=0 to have it empty. Append the data form source db cursor into destinatoion db cursor and then save that via Tableupdate. In the simplest case when the destination database is empty. If not it gets messier, as just appending data only creates new records, not updates already existing.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: VFP9 copying records from one MySQL database to another MySQL database

(OP)
Thanks for the replies... Yes, its a VFP 9 application that allows the user to interogate (among other things) clients.. the structure of both databases is identical, but has differing data, hence the need for the user to go through the data and select which client would need to be copied to the other database. Ive got to the point of having two remote views (one for each database)... when you say copy the data, which way would you recommend, obviously cant APPEND FROM... so I wonder what method you would recommend to copy n number of records from one remote view to another? Many thanks

RE: VFP9 copying records from one MySQL database to another MySQL database

You can do append, if the source view has all the records you want to add, yes.

One way to copy over records easily in VFP9 is SCATTER NAMNE loRecord from the source remote view and INSERT INTO view FROM NAME loRecord in the other connection remoteview. With a record by record processing you have the best control. For updating existing clients with new info you would GATHER NAME loRecord positioned on the same customer record.

Finally update the target db by TABLEUPDATE() of the view with new and updated customer data, of course.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: VFP9 copying records from one MySQL database to another MySQL database

The other route possible would keep it at Mikes idea and can work, if your two databases can see each other serverside, MySQL has INSERT ON DUPLICATE KEY UPDATE, which could be used to merge data of customers from the source DB into the target DB. It's still the pĆ¼referred way to do this, knowing IDs to move will control what you read from prod into archive.

This, of course, is just theoretical quibbling as long as we don't know whether the two databases are on the same server and you have the option at all. You still didn't state that, but it seems likely the setup has a prod and archive DB on the same hosting or cloud, then moving data from database to database is the preferred method even if you fetch data for the interactive selection into the VFP client application.

It's inevitable you fetch data to display for the interactive choice, but that likely only needs some columns, not all data, surely not all detail data. Because of that still transporting data from DB to DB is the preferred method.

VFP has no merge statement or similar, so you'd always end up needing to program the merge logic with at least one insert and one update statement processing the two cases of new and changed data to go into archive remote view. I also know archives keeping a history, therefore only adding new data, even for data already in the archive the changed records will be inserted as new and be the more current version by an automatic version or timestamp column, then you can actually also just append or do a completely normal insert, the archive tables just need to drop the primary key constraint and/or have a new primary key column to accept records as many times as they are archived. That also has its pros.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: VFP9 copying records from one MySQL database to another MySQL database

Yes, you can use APPEND FROM to copy from the source view to the target. Or you can use INSERT <target view> SELECT * FROM <source view>.

If you want to copy all records from source to target, that's all you need. If you want to select records according to some criterion, use a FOR clause with APPEND FROM or a WHERE clause with INSERT .. SELECT.

In all cases, you should finish the operation by doing a TABLEUPDATE() on the target view. That will actually commit the data to the database.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: VFP9 copying records from one MySQL database to another MySQL database

But using APPEND FROM or INSERT INTO is only appropriate if you want the process to be automatic, that is, without user intervention. But in that case, you can use my original idea of doing the whole thing on the server (see the third post in this thread).

But didn't you say you wanted users to be able to view the records and decide which ones to copy across? If so, then you need a different approach. As I suggested earlier, you should create a form which is bound to the data in the source view. The form would have a Copy button which would let the user copy the record currently being viewed. You could do the actual copying of that record using, for example, SCATTER / GATHER (see Olaf's recent post). You would still need the TABLEUPDATE().

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: VFP9 copying records from one MySQL database to another MySQL database

Mike is correct, even when your choice remains doing the copy act on the VFP clientside VFP9 has the necessary SQL to insert and update multiple records via INSERT <target view> SELECT * FROM <source view> WHERE condition or INNER JOIN with a cursor of picked IDs of custmers, besides in a similar manner doing a correlated UPDATE of the targetview FROM the sourceview. We don't know what your level of SQL knowledge is, but VFP only lacked such statements before version 9, you can insert multiple records coming from a SELECT subquery and you can do correlated updates even for more VFP versions, that's not the problem. And before that, we had APPEND with FOR clause, too.

But obviously, even if half the way of the data is already done, uploading changes from client to host takes longer than just uploading the choices and doing the data move from server to server, even if this is a complex cloud and databases are in different zones. Your client/server connectoin will always be slower than local to local and all you need to "reupload" are IDs of the records to sync, you can put them on a temp table, for example.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close