Using and Sorting Flat Files

An alternative to an array is a flat file. You can use a flat file when the required array size exceeds the available memory.

The code example in the previous section can be rewritten to use a file instead of an array. The advantage of using a file is that the program is not constrained by the amount of memory that is available. The disadvantage of using a file is that the program performs more input and output (I/O). However, it may still be faster than performing another SQL statement to retrieve the same data.

This program uses the UNIX/Linux sort utility to sort the file by name. This example can be extended to include other operating systems.

The following code example is rewritten to use the cust.dat file instead of the array:

Program ex24b.sqr
begin-program
  do main
end-program
begin-procedure main
!
! Open cust.dat
!
open 'cust.dat' as 1 for-writing record=80:vary
begin-select
state (,1)
city  (,7)
name  (,24)
phone (,55)
   position (+1)
   ! Put data in the file
   write 1 from &name:30 &state:2 &city:16 &phone:10
from customers
order by state
end-select
position (+2)
!
! Close cust.dat
close 1
! Sort cust.dat by name
!
call system using 'sort cust.dat > cust2.dat' #status
if #status <> 0
    display 'Error in sort'
    stop
end-if
!
! Print customers (which are now sorted by name)
!
open 'cust2.dat' as 1 for-reading record=80:vary
while 1  ! loop until break
   ! Get data from the file
   read 1 into $name:30 $state:2 $city:16 $phone:10
   if #end-file
      break   ! End of file reached
   end-if
   print $state (,1)
   print $city  (,7)
   print $name  (,24)
   print $phone (,55)
   position (+1)
end-while
!
! close cust2.dat
close 1
end-procedure ! main

The program starts by opening a cust.dat file:

open 'cust.dat' as 1 for-writing record=80:vary

The OPEN command opens the file for writing and assigns it file number 1. You can open as many as 12 files in one SQR program. The file is set to support records of varying lengths with a maximum of 80 bytes (characters). For this example, you can also use fixed-length records.

As the program selects records from the database and prints them, it writes them to cust.dat:

write 1 from &name:30 &state:2 &city:16 &phone:10

The WRITE command writes the four columns into file number 1, the currently open cust.dat. It writes the name first, which simplifies sorting the file by name. The program writes fixed-length fields. For example, &name:30 specifies that the name column uses exactly 30 characters. If the actual name is shorter, it is padded with blanks. When the program has finished writing data to the file, it closes the file by using the CLOSE command.

The file is sorted with the UNIX sort utility:

call system using 'sort cust.dat > cust2.dat' #status

The sort cust.dat > cust2.dat command is sent to the UNIX system. It invokes the UNIX sort command to sort cust.dat and direct the output to cust2.dat. The completion status is saved in #status; a status of 0 indicates success. Because name is at the beginning of each record, the file is sorted by name.

Next, open cust2.dat for reading. The following command reads one record from the file and places the first 30 characters in $name:

read 1 into $name:30 $state:2 $city:16 $phone:10

The next two characters are placed in $state, and so on. When the end of the file is encountered, the #end-file reserved variable is automatically set to 1 (true). The program checks for #end-file and breaks out of the loop when the end of the file is reached. Finally, the program closes the file by using the CLOSE command.