Universal Copy

Copying records is a fairly common operation for line-of-business applications built with My Visual Database. Usually, this is done using a form in edit mode and a trick with the value of the dbGeneralTableID property of the button responsible for saving data.

Another way to get a copy of a table record is to use a SQL query.

INSETR INTO cycle (code,type,description) SELECT code,type,description FROM cycle WHERE id=1Code language: SQL (Structured Query Language) (sql)

This example makes a copy of the record with ID=1 for the cycle table, which has three fields: code, type, and description.

Is it possible to copy a record without knowing the structure of the table? Can! To do this, we need a temporary table (this technology is supported in the SQLite and MySQL DBMS).

function DB_CopyRecord( ATableName: string; AID: integer ):integer;
// universal function for copying a record
begin
   // create a temporary table with a structure identical to the copied record
   SQLExecute('CREATE TEMPORARY TABLE tmp AS SELECT * FROM ['+ATableName+'] WHERE id = '+IntToStr(AID));
   // reset the value of the ID field
   SQLExecute('UPDATE tmp SET id = NULL');
   // insert record back
   SQLExecute('INSERT INTO ['+ATableName+'] SELECT * FROM tmp');
   // get the ID of the new entry
   Result := Last_Insert_ID();
   // delete temporary table
   SQLExecute('DROP TABLE tmp');
end;
Code language: Delphi (delphi)

The procedure input is the name of the table and the identifier of the record to be copied. The output is the ID of the copy.

At the database level, record uniqueness in My Visual Database projects is supported only for the required ID field, so the command on line 7 is to set it to NULL. This is necessary so that when you insert a copy (line 9), the value of the ID field is automatically set by the DBMS itself.

An example of using a procedure in which the field with the name is modified on the copy.

var
  tmpIDOrder: integer;
  tmpIDNewOrder: integer;
begin
  ...
  tmpIDNewOrder := DB_CopyRecord( 'order', tmpIDOrder ); // copy order
  SQLExecute('UPDATE [order] SET name = (SELECT name FROM [order] WHERE id = '+IntToStr(tmpIDNewOrder)+') || " (копия)" WHERE id = '+IntToStr(tmpIDNewOrder));
Code language: JavaScript (javascript)

Note

A feature of the SQLQuery command is the locking of the SQLite database, so if you decide to use the DB_CopyRecord() procedure to copy multiple records, then make sure that you do not call it inside the loop that uses the dataset opened by the SQLQuery command. This requirement is because the DROP TABLE command requires full access to the database.

You can get around this limitation by replacing the while loop and SQLQuery with a for loop and SQLExecute. Since you only need the record IDs for copying, you can extract and convert to an array. Below is an example code for copying order details:

var
  tmpID: integer;
  tmpIDOrder: integer;
  tmpListID: array of string;
  i: integer;

  tmpSQL: string;
begin
  ...
  tmpSQL := 'SELECT GROUP_CONCAT(id) FROM orderPosition WHERE id_order = '+IntToStr(tmpIDOrder);
  tmpListID := SplitString(SQLExecute(tmpSQL),',');
  for i:=0 to length(tmpListID) - 1 do
  begin
    tmpID := DB_CopyRecord( 'orderPosition', StrToInt( tmpListID[i] ) ) );
    ...
  end;

  ...
end;Code language: Delphi (delphi)