f2s_copy_data¶

Applies to: Oracle, MS SQL, MySql/MariaDB, PostgreSQL Drivers

Syntax

f2s_copy_data {from} to {destination} [CALLBACK {function}] [CONSTRAIN {variable}]

Description This command is used to copy data from a DAT file to an SQL table. Both the source and the destination files are opened, the data is copied and then the files are closed. This is usually used along with f2s_convert_dat_structure, which creates the structure and then this command will insert the data. If needed, this command can also work backwards, copying data from an SQL table to a DAT file. This is an alternative to using the Flex2SQL Migration Tool and can improve performance when used in the right situations.

Note that in order for this process to work properly the table being copied can not be opened. Because of this, a manual login must take place, using something like LOGIN. Since this only copies the data, f2s_convert_dat_structure can be used to create the table structure.

Because f2s_copy_data requires no user interaction, an optional callback function can be passed for tracing purposes.

By passing the constant “CALLBACK” followed by the object name, a function can be used to set up messages for tracking and error handling. Below is more information regarding this callback object.

This function should follow this pattern:

Function Callback String sText Integer iStatusID Returns Integer

sText - Message corresponding to the current status ID

iStatusID - Integer value mapping to the current status constant: DF_MESSAGE_PROGRESS_TITLE Sent when SQLFlex starts the process

DF_MESSAGE_HEADING_1 Sent when SQLFlex is about to copy the source data

DF_MESSAGE_ERROR Sent when an error occurs

DF_MESSAGE_PROGRESS_STATUS Sent each time an additional 10% of the data is copied

NOTE: Returning false each time the function is called will continue copying process. Returning true will end it.

Another optional parameter that can be used is a constrain on the data being copied. This is done by passing the constant “CONSTRAIN” followed by an SQL constrain statement. When passing a constrain but not a callback object, pass 0 for the callback before passsing the constrain.

Example

         String sPhysicalFileName sDriver sNewRootName
         Integer iFileNumber iNumDrivers iDriver

         // filenumber of the dataflex data file
         Move 52 to iFileNumber
         Get_Attribute DF_NUMBER_DRIVERS to iNumDrivers
         For iDriver from 1 to iNumDrivers
             Get_Attribute DF_DRIVER_NAME of iDriver to sDriver
             If ((lowercase(sDriver)) contains "f2s") Break
         Loop
         Get_Attribute DF_FILE_ROOT_NAME of iFileNumber to sPhysicalFileName
         Move (Trim((Lowercase(sDriver))) + ":" +(Trim(sPhysicalFileName))) to sNewRootName

         // create structure in SQL without copying the data
         f2s_convert_dat_structure iFileNumber

         // set the root name of the filelist entry to include the f2s driver
         Set_Attribute DF_FILE_ROOT_NAME of iFileNumber to sNewRootName

         //copy the data from the dataflex file "addresses" to the SQL Table "addresses" without using a callback
         f2s_copy_data "addresses" to sNewRootName Callback 0

Example using a constraint

f2s_copy_data "f2s_ms:diary" To "diary" Callback 0 Constrain "JobCode = '02-78CC'"

Example of copying data from a table named CREW into a Dataflex 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
                        //Do some code
                        //<handle Error Message>
                        // Tell operation to Stop
                        Function_Return DFTRUE
                End
                Else If (iLogicalID >= DF_MESSAGE_PROGRESS_STATUS) Begin
                        //Do some code
                        // Tell operation to Continue
                        Function_Return DFFALSE
                End

                Function_Return DFFALSE
        End_Function
End_Object

f2s_copy_data "f2s_ms:crew" To "crew2" Callback (CallBackObj(Self)) Constrain "JobCode = '02-78CC'"

Example of copying all data from a Dataflex DAT file named TIMECARD into a SQL table named TIMECARD.

f2s_copy_data "timecard" To "f2s_ms:timecard" Callback 0

Example of copying records from a SQL Table to a DAT file and vice versa

Integer giCallBackObj

Object CallBackObj Is A Array
        Move current_object To giCallBackObj

        Function Callback String sText Integer iLogicalID Returns Integer
                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 "SERVERNAME" "username" "password" "f2s_ms"
open "customer" as customer
zerofile customer
close customer


// Copy all records from the Customer table that where the STATE = 'FL' into Customer Dataflex .DAT file
f2s_copy_data "f2s_ms:customer" to "customer" callback giCallBackObj constrain "STATE = 'FL'"

showln "done"

open "salesp.int" as salesp
zerofile salesp
close salesp

showln
showln
inkey windowindex

Example that Copies all records from the Dataflex file “Salesp.DAT” into the Salesp Table

f2s_copy_data "salesp" to "f2s_ms:salesp" callback giCallBackObj

showln "done"
inkey windowindex

Related Commands

• f2s_convert_dat_full

• f2s_convert_dat_structure

Replaces: COPY_DATA