• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 902
  • Last Modified:

VFP 9 & MySQL - Copy Record Same Table

Hi all. I am using VFP 9 to connect to a mysql db via odbc using sqlselect. All is working fine, I can read/write to the db and all tables. But I have a question.

I have encountered several situations where it would be nice to be able to copy the data from an existing record to a new record in the same db/table.

So, for example, say I have a record (example record_id = 100), and I want to copy all the data for this record to a new record (record_id = 101), how would I do that?


  • 2
3 Solutions
This is probably better to do at the server side, e.g.

INSERT INTO YourTable (record_id, colName2, colName3, colName4, ...)
   SELECT 101, colName2, colName3, colName4, ... FROM YourTable WHERE record_id = 100

If the record_id column is autoincremented then you cannot list it in the column list.
Of course, you may pass above command to MySQL server from VFP by sqlexec() function call.

More info about INSERT ... SELECT: http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
Olaf DoschkeSoftware DeveloperCommented:
If you'd use updatable remote views or an updatable cursoradapter, you'd be able to do all data manipulation the foxpro way inside foxpro cursors.

Eg in this case you create an updatable view of the table, parameterised to query query a certain ID, here 100, then would have that record in an updatable view cursor. You can do an INSERT similar to the one pcelba recommended but on the vfp view cursor instead of the mysql table, or you SCATTER NAME loRecord of the view record 100, creating a record object and then INSERT INTO viewcrusor FROM NAME loRecord to insert that object as a new record into the same updatable view cursor and then do TableUpdate() to write it back to the mysql database.

Once you have data in an updatable foxpro remote cursor, you can do ynything you want, anyhow you're used to do it in foxpro. This would only be a disadvantage, if you'd need to read in much data to copy into new records. As MySQL is a real server, you can make advantage of that, by just sending the sql command, which is executed on the server side. That can be a huge advantage in processing time.

On the other side, if you're displaying a record in your frontend anyway, and that process of copying is done manually by the user, working in vfp cursors is the way to go.

Bye, Olaf.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now