Using Send/Receive Record to Recover, Replace or Update
By Kent Wilbur, Manager of Internal Databases, ACI
Technical Note 99-1
Technical Notes for Technical Notes for 99-01-January 1999
Introduction
This technical note describes using Send/Receive Record to effectively move data between two databases.
Why would you ever need to move data?
There are several reasons to abandon one database data file and move all the data to a new data file.
Abandoning an old structure because is has become outdated and obsolete.
You are selling your database to a client and only some of the tables are appropriate to deliver to the new client.
Your database has become corrupted beyond repair. Nothing seems to work and you want to salvage what you can.
You are upgrading to a different version of 4D. Your data file does not convert without errors, no matter what you try.
In very large data files, converting from v3 to v6 may be faster by exporting and importing the data than by just letting v6 convert the data.
Using Send/Receive Record
When all you need to do is dump all the data from one database to another, the solution is very simple. If you need to selectively send data from one database to another, then you must modify the sending routines to get only the desired records or parts of records.
Limitations
The only limit to Send/Receive Record is that the database table structure must be the same on both ends. That is, each field must be of the same type and length in both the sending and receiving table.
Caution: I do not recommend exporting on one platform and importing on another, i.e. export on Windows and import on a Macintosh. Although this usually works from Macintosh to Windows, I have had problems going from Windows to Macintosh. Therefore, work on the one platform, then convert your data to the other.
Advantages
It works on all field types, pictures, BLOBs, and even subtables. It even works between v3 exports and v6 imports.
Pitfalls
The major problem with this technique has to do with sequence numbers. If you are using the 4D Sequence number command or the #N in the default values on forms, there is a problem.
The way the sequence number inside 4D works is that each time a record is saved, or the sequence number is called for (in a multi-user database), 4D updates the sequence number internal counter. Therefore, if over the years you have saved 30,000 records in a file, the next sequence number will be 30,001. If you have deleted records over the years and only have 10,000 records in your database, the next sequence number in your new database, once the data is imported, will be 10,0001. You already have that number in your database. Eventually, you will have a real problem with logical data corruption. If you have checked the Unique checkbox on your indexed field, you will not be able to add a new record the instant you encounter the first non-deleted record that has a value over 10,000.
You should rewrite your database to generate your own sequence numbers. For details, see Technical Note 98-35, "Replacing the Sequence Number Command."
The only other problem is that when receiving records, sometimes an extra record is created at the end of the receiving routine. Just delete this extra record if it occurs.
The Send Record procedure (v3 version)
` Switch to READ ONLY in order to avoid locking records
READ ONLY([Table 1])
If (False)
` Procedure/Method: E_SendRecords
` ACI US Technical Note
` Using Send/Receive Record
` Created By: Kent Wilbur
` January 1999
` Purpose: This routine creates a completely generic method for sending records
` out of the database for import into another database.
` The primary purpose is for the conversion of V3 data to V6 but it could be used
` for other purposes as well.
` Flags for insider
<>fGeneric:=True
<>fSendReceiv:=True
<>fKWilbur:=True
End if
` Declare arrays for selection and clear out any old values
ARRAY STRING(1;asSelected;0) ` Array for holding the selection marker
ARRAY STRING(15;asFileName;0) ` Array for holding the table names
` Declare local variables
C_LONGINT($i) ` Loop counter
C_LONGINT($LNumFiles) ` Number of files in the database
C_LONGINT($LNumRecs) ` Loop counter for records being exported
C_LONGINT($LRecordNum) ` Loop counter for records being exported
C_POINTER($pFile) ` Pointer to the file
C_STRING(4;$sFileSuffix) ` File suffix for windows machines
$LNumFiles:=Count files
` Declare arrays for selection
ARRAY STRING(1;asSelected;$LNumFiles)
ARRAY STRING(15;asFileName;$LNumFiles)
` Read the file names
For ($i;1;$LNumFiles)
asFileName{$i}:=Filename($i)
End for
` Select the files to be sent
sPurpose:="Send"
NewWindow (200;310;0;1)
DIALOG([zDialogs];"GEN_SelectFiles")
CLOSE WINDOW
If (GEN_fIsWindows ) ` Is this a Windows machine
$sFileSuffix:=".txt"
Else
$sFileSuffix:=""
End if
If (OK=1)
NewWindow (200;30;6;1)
For ($i;1;$LNumFiles)
If (asSelected{$i}="X")
MESSAGE("Sending "+asFileName{$i}+Char(13)) `Simple interface for the users
$pFile:=File($i)
ALL RECORDS($pFile»)
SET CHANNEL(10;asFileName{$i}+" Data"+$sFileSuffix) ` Open data file "Filename Data{.txt}"
$LNumRecs:=Records in file($pFile»)
For ($LRecordNum;1;$LNumRecs)
If ($LRecordNum%100=0) ` Only update message every 100 records this takes time
GOTO XY(2;1)
MESSAGE("Record "+String($i)+" of "+String($LNumRecs))
End if
SEND RECORD($pFile»)
NEXT RECORD($pFile»)
End for
SET CHANNEL(11) ` Close this data file
End if
End for
CLOSE WINDOW
End if
` End of procedure/method
A brief word about the routines. NewWindow simply opens a window based upon the width, height, screen position (0=Centered, 6=upper 1/3), and window type parameters. GEN_fIsWindows returns True if this is running on a Windows machine. It uses the PLATFORM PROPERTIES command to accomplish this.
The only differences between a v3 version and a v6 version are the commands Count files vs. Count tables, Filename vs. Table name and that in v6 the Open window command returns a Window ID. Additionally, the v6 version has some items that create a more pleasant user interface.
The GEN_SelectFiles dialog is a simple one that enables users to select the appropriate files to be sent.
Version 3 Version 6
Note that the v6 interface is a little more elegant. It uses Picture Resource #805, which is not available in v3. It also does not need Select All or Clear All buttons. In v6 you can detect function keys being held down. The functionality of those buttons can be made available by inserting the following code behind the array:
If (False)
` Method: Flip_asSelected(pointer)
` ACI US Technical Note
` Using Send/Receive Record
` Created By: Kent Wilbur
` January 1999
` Purpose: Marks or unmarks a chosen item as appropriate
` Flags for insider
<>fGeneric:=True
<>fSendReceiv:=True
<>fKWilbur:=True
End if
` Declare parameters
C_POINTER($1;$pElement) ` Element to change
` Declare local variables
C_LONGINT($i)
` Reassign for readability
$pElement:=$1
If (Macintosh command down) ` Ctrl key on Windows
` Turn them all on or off
If (afSelected{$pElement->})
For ($i;1;Size of array(agSelected))
agSelected{$i}:=gUnCheckMark
afSelected{$i}:=False
End for
Else
For ($i;1;Size of array(agSelected))
agSelected{$i}:=gCheckMark
afSelected{$i}:=True
End for
End if
Else
If (afSelected{$pElement->})
agSelected{$pElement->}:=gUnCheckMark
afSelected{$pElement->}:=False
Else
agSelected{$pElement->}:=gCheckMark
afSelected{$pElement->}:=True
End if
End if
agSelected:=0
` End of method
The Receive Record method (v6 version)
If(False) ` Method: E_ReceiveRecords ` ACI US Technical Note ` Using Send/Receive Record ` Created By: Kent Wilbur ` January 1999 ` Purpose: This routine creates a completely generic method for receiving records ` into the database which have been exported from another database. ` The primary purpose is for the conversion of V3 data to V6 but it could be used ` for other purposes as well. ` Flags for insider <>fGeneric:=True <>fVersion_6x0:=True <>fSendReceiv:=True <>fKWilbur:=True End if ` Declare arrays for selection and clear out any old values ARRAY BOOLEAN(afSelected;0) ` Array for holding the selection marker ARRAY PICTURE(agSelected;0) ` Array for displaying the selection marker ARRAY STRING(31;asTableName;0) ` Array for holding the table names ` Get the picture resource GET PICTURE RESOURCE(805;gCheckMark) ` Get the checkmark picture resource 4D uses ` in the Explorer window C_PICTURE(gUnCheckMark) ` Use an empty picture for the offset ` Declare local variables C_LONGINT($i;$j) ` Loop counters C_LONGINT($LFieldType) ` Type of the field - parameter for GET FIELD PROPERTIES C_LONGINT($LFieldLength) ` Length of the field - parameter for GET FIELD PROPERTIES C_LONGINT($LNumberOfFields) ` Number of Tables in the database C_LONGINT($LNumberOfTables) ` Number of Tables in the database C_LONGINT($LRecordNumber) ` Loop counter for records being exported C_LONGINT($LWindowID) ` Window ID C_POINTER($pField) ` Pointer to an Indexed field C_POINTER($pTable) ` Pointer to the Table C_STRING(4;$sFileSuffix) ` Table suffix for windows machines C_TIME($hStartTime) C_TIME($hElapsedTime) ARRAY BOOLEAN($fFieldIndexed;0) $LNumberOfTables:=Count tables ` Declare arrays for selection ARRAY BOOLEAN(afSelected;$LNumberOfTables) ARRAY PICTURE(agSelected;$LNumberOfTables) ARRAY STRING(31;asTableName;$LNumberOfTables) ` Read the Table names For ($i;1;$LNumberOfTables) asTableName{$i}:=Table name($i) End for sPurpose:="Receive" $LWindowID:=WIN_LNewWindow (200;290;<>WIN_LCentered;Modal dialog box) DIALOG([zDialogs];"GEN_SelectTables") CLOSE WINDOW If (GEN_fIsWindows ) $sFileSuffix:=".txt" Else $sFileSuffix:="" End if If (OK=1) $hStartTime:=Current time $LWindowID:=WIN_LNewWindow (200;30;<>WIN_LUpper;Modal dialog box) For ($i;1;$LNumberOfTables) If (afSelected{$i}) $LRecordNumber:=0 MESSAGE("Receiving "+asTableName{$i}+Char(Carriage return)) `Simple interface for the users $pTable:=Table($i) ` Delete any default records created ` This may not be appropriate for all uses ALL RECORDS($pTable->) DELETE SELECTION($pTable->) ` Now lets see what is indexed ` Let's temporarily turn it off to improve receiving speed $LNumberOfFields:=Count fields($pTable) ARRAY BOOLEAN($fFieldIndexed;$LNumberOfFields) For ($j;1;$LNumberOfFields) GET FIELD PROPERTIES($i;$j;$LFieldType;$LFieldLength;$fFieldIndexed{$j}) If ($fFieldIndexed{$j}) $pField:=Field($i;$j) SET INDEX($pField->;False) End if End for SET CHANNEL(10;asTableName{$i}+" Data"+$sFileSuffix) While (OK=1) $LRecordNumber:=$LRecordNumber+1 If ($LRecordNumber%25=0) ` Only update every 25 records this takes time GOTO XY(2;1) MESSAGE("Record "+String($LRecordNumber)) End if RECEIVE RECORD($pTable->) SAVE RECORD($pTable->) End while UNLOAD RECORD($pTable->) SET CHANNEL(11) ` Lets turn back on the indexes MESSAGE("Indexing "+asTableName{$i}+Char(Carriage return)) For ($j;1;$LNumberOfFields) If ($fFieldIndexed{$j}) $pField:=Field($i;$j) SET INDEX($pField->;True) End if End for End if End for ` End of tables CLOSE WINDOW ` Display how long this took for later planning if appropriate $hElapsedTime:=Current time-$hStartTime ALERT("The current time is "+String(Current time;HH MM AM PM)+ Char(Carriage return)+"Total Elapsed time was "+String($hElapsedTime;Hour Min)) End if ` End of method
Note that the beginning of the loop to receive records finds and turns off all indexes for the table being imported. It turns the indexes back on when the records for that table are completed. This allows the actual receiving of records to not be hindered by trying to rebuild the index after each record is received. Since indexing takes place in its own process, turning the indexes on after all records have been received allows 4D to begin building the indexes on one table while another table is being received. The impact of this depends on the speed of your hard disk and CPU and the available memory. My experiments have shown that the overall task, complete with indexes rebuilt, is faster this way, even though the time indicated in the ALERT may actually be slower.
The routines called by this method are written differently between v3 and v6. The WIN_LNewWindow routine uses the Open window routines described in Technical Note 97-29, "Using Form Sizing Options." The GEN_fIsWindows routine has been rewritten to use v6 techniques. In both cases, the v3 code would have worked without conversion to v6 techniques.
Summary
This technical note demonstrates a simple, reliable way to move data between like databases using Send/Receive Record. It is particularly helpful when converting large data files from v3 to v6. Using the traditional method of just letting v6 convert the data, it sometimes takes five to six attempts to get a completely clean conversion. This method works the first time every time.
See Also
Technical Note 97-29, "Using Form Sizing Options," Kent Wilbur
Technical Note 98-35, "Replacing the Sequence Number Command," Kent Wilbur