The COPY_DATA feature allows the user to copy data from an SQL table to a DAT file and vice versa.
Flex2SQL has two options that allows us to copy the data from a DAT to a SQL and vice versa, but our customers prefer a single command approach.
The interaction between the DataFlex application and the DataFlex Runtime has some overhead which causes a performance slow down. In this case, less application interaction should bring better performance, which means that using a single command to support the COPY_DATA feature should be the best approach.
How does it work?
COPY_DATA is a single macro command which can be used to copy data from an SQL Table to a DAT file and vice versa. The command opens the source and destination file, copies the data and closes them. When the source file is an SQL Table, you can pass an argument to the command which can be used to restrict the table rows that will be migrated to the DAT file.
The COPY_DATA definition is based on a regular DataFlex command named Copy_DB. Since this is a single command, which takes data copy without requiring any special interaction with the end-user, it is useful for the command process to be monitored.
This is possible through a CallBack object which will be used by the driver to interact with the user to show for example the data copy progress and error messages. These are the messages SQLFlex sends back to the application during the COPY_DATA execution:
-> DF_MESSAGE_PROGRESS_TITLE : This message is sent when SQLFlex is starting the Copy
Data process.
-> DF_MESSAGE_HEADING_1 : This message is sent when SQLflex is about to copy the data
for source to
-> DF_MESSAGE_ERROR : This message is sent when there is an error during the data
migration
-> DF_MESSAGE_PROGRESS_STATUS : This message is sent every 10 % of the data has been
migrated, with the following format "<number of records saved>,<total number of records>"
In addition to monitoring the data copy progress, the user should be able to cancel the process through the CallBack object as well.
Syntax:
Copy_Data {source-table} To {dest-table} [Callback {callback-object}] ;
[Constrain {SQL-expression}]
Where:
• {source-table}: Is a name and path of the database table to be copied.
• {dest-table}: Is a name and path that the database table is copied to.
• {callback-object}: Is a handle to an object that will receive the Callback message during
the operation.
• {SQL-expression}: Is the SQL expression that will restrict the rows that will be copied
from a SQL Table to a DAT file. This option will be applied when {source-table} is a SQL
Table only.
Examples:
1. The following code will copy the data from a table named DIARY into a DAT file named DIARY and only the rows with JobCode = '02-78CC' will be migrated.
Copy_Data “sql_drv:diary” To “diary” Callback 0 Constrain “JobCode = '02-78CC'”
2. The following code will copy the data from a table named CREW into a DAT file named CREW2 and only the rows with JobCode = '02-78CC' will be migrated. This code has a Callback object which will be used to interact with the driver during the migration.
Integer giCallBackObj
Object CallBackObj Is A Array
Move Self To giCallBackObj
Function Callback String sText Integer iLogicalID Returns Integer
If (iLogicalID = DF_MESSAGE_ERROR) Begin
::
<handle Error Message>
::
// Tell operation to Stop
// Function_Return DFTRUE
End
Else If (iLogicalID >= DF_MESSAGE_PROGRESS_STATUS) Begin
::
<Handle Data Migration Progress>
::
// Tell operation to Continue
// Function_Return DFFALSE
End
Function_Return DFFALSE
End_Function
End_Object
Copy_Data "sql_drv:crew" To "crew2" Callback (CallBackObj(Self)) Constrain "JobCode = '02-78CC'"
3. The following code will copy the all data from a DAT file named TIMECARD to a SQL table named TIMECARD.
Copy_Data “timecard” To “sql_drv:timecard” Callback 0
4. The following code is a very simple program that will migrate some records from a SQL Table to a DAT file and vice versa.
use mertech.inc
//
// Simple Object with a Callback function
//
Integer giCallBackObj
Object CallBackObj Is A Array
Move current_object To giCallBackObj
Function Callback String sText Integer iLogicalID Returns Integer
Integer iWorking ret# herr# liPos liLen liCount liTotal liPercent
String lsValue
if (iLogicalID = DF_MESSAGE_PROGRESS_TITLE) Begin
// This message is sent when SQLFlex is starting the Copy Data process
showln "Title " sText
showln
End
else if (iLogicalID = DF_MESSAGE_HEADING_1) Begin
// This message is sent when SQLflex is about to copy the data for source to destination
// and when the operation is done
showln "Sub-Title " sText
showln
End
else If (iLogicalID = DF_MESSAGE_ERROR) Begin
// This message is sent when there is an error during the migration
showln "Error " sText
function_return dftrue // Function_return dftrue means you want the migration stop
End
Else If (iLogicalID >= DF_MESSAGE_PROGRESS_STATUS) Begin
// This message is sent every 10 % of the data has been migrated
showln "Progress " sText
End
Function_Return dffalse
End_Function
End_Object
login "nachbar" "marcelo" "nachbar" "sql_drv"
open "customer" as customer
zerofile customer
close customer
//
// All records Customer table that has STATE = 'FL', will be copied to Customer.DAT file
copy_data "sql_drv:customer" to "customer" callback giCallBackObj constrain "STATE = 'FL'"
showln "done"
inkey pageend
open "salesp.int" as salesp
zerofile salesp
close salesp
showln
showln
//
// All records For Salesp.DAT will be copied to Salesp Table
copy_data "salesp" to "sql_drv:salesp" callback giCallBackObj
showln "done"
inkey pageend
Mertech: Mar 29, 2024 8:53:00 AM
Mertech