VFP9 copying records from one MySQL database to another MySQL database

[vincestevens]

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

 

[Mike Lewis]

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

 

[Mike Lewis]

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:

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

 

[vincestevens]

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....

 

[Mike Lewis]

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

 

[Olaf Doschke]

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

 

[vincestevens]

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

 

[Olaf Doschke]

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

 

[Olaf Doschke]

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

 

[Mike Lewis]

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

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 [tt]FOR[/tt] clause with [tt]APPEND FROM[/tt] or a [tt]WHERE[/tt] clause with [tt]INSERT .. SELECT.
[/tt]
In all cases, you should finish the operation by doing a [tt]TABLEUPDATE()[/tt] on the target view. That will actually commit the data to the database.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

But using [tt]APPEND FROM[/tt] or [tt]INSERT INTO[/tt] 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, [tt]SCATTER / GATHER[/tt] (see Olaf's recent post). You would still need the [tt]TABLEUPDATE()[/tt].

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

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