The school I work for has a library system called Bookmark. At the start of the year, just before the students come back, I help the librarian update all the student details in the system. I do this by exporting out the details from SAS2000 and importing them into Bookmark. Of course, as it only occurs every 12 months, we forget how to do the process each time and have to find the documentation how to do it each time. Also throughout the year, new students come and others leave. My goal was to make this all easier to handle and make it so the student details would already be in the library system before they first went to borrow a book. What follows is part of my effort to make it easier to keep the library system up to date.

Reverse Engineering the File Format

Bookmark uses its own custom binary file format. It stores various details in different files making a kind of relational database. To help reverse engineer the file format, I used a hex editor. The borrower names are stored in a file called BORROWER.DAT. Some extra info about the borrower is stored in a file called BORRCMTS.DAT. In the hex editor, it shows the location of different pieces of data in the file. From this I was able to determine that each borrower had a record of 256 bytes, with a 256 byte header at the start of the file. Also for borrowers that have been deleted the relevant 256 byte section is simply zeroed out. The next step was to split each record up into the fields.

  TLibraryStud = record
    Name     : array[0..26] of AnsiChar;
    XX       : array[0..1] of AnsiChar;
    Group    : array[0..8] of AnsiChar;
    MaxCount : BYTE;
    xxx      : BYTE;
    Addr     : array[0..29] of AnsiChar;
    City     : array[0..19] of AnsiChar;
    postcode : array[0..5] of AnsiChar;
    Phone    : array[0..11] of AnsiChar;
    Group2   : array[0..1] of AnsiChar;
    Gender   : ansichar;
  end;

After a bit of guessing and trail and error I came up with the record above to hold the data.

Saving Borrower Details to Database

First I created a table called bookmark in a database in SQL Server. This table stored the same info as what was in the bookmark data file. Because the record id’s don’t change, I am able to simply delete all the data in the bookmark table and reload it from the bookmark files. I do this in a transaction, so that if anythings fails, I am able to rollback to the last complete successful import.
Below is the final code to make it work.

procedure TDataModule.LoadBorrowers(path:String);
var
  FromFStream : TFileStream;
  FromF2Stream: TFileStream;
  NumRead,numRead2, NumWritten: Integer;
  str : AnsiString;
  stud : TLibraryStud;
  studCom : TLibraryStCm;
  filename,filename2 : String;
  currentPos : Integer;
  lastPos : Integer;
  i : Integer;
begin
  filename  := IncludeTrailingPathDelimiter(path) +'BORROWER.DAT';
  filename2 := IncludeTrailingPathDelimiter(path) +'BORRCMTS.DAT';

  if(not (FileExists(filename) and FileExists(filename2))) then
    begin
      Exit;
    end;
    FromFStream := TFileStream.Create(filename,fmOpenRead or fmShareDenyNone);
    FromF2Stream := TFileStream.Create(filename2,fmOpenRead or fmShareDenyNone);
  begin
    begin
        lastPos := 0;
    i := 0;

      ADOConnection1.BeginTrans;
      ADOQuery1.SQL.Text := 'DELETE FROM Bookmark;';
      ADOQuery1.ExecSQL;
      ADOQuery1.SQL.Text := 'INSERT INTO Bookmark (tid,Name,Group1,Addr,City,Postcode,Phone,Group2,Gender,AdminID,maxCount) VALUES(:ID,:name,:group1,:addr,:city,:postcode,:phone,:group2,:gender,:adminID,:maxCount);';

    try
      repeat
        FromFStream.ReadBuffer(stud,128);
        FromF2Stream.ReadBuffer(studCom,64);
        if((length(Trim(stud.NAME))<>0) and (i>0)) then
         begin
          ADOQuery1.Parameters.ParamByName('ID').Value := i;
          ADOQuery1.Parameters.ParamByName('name').Value := Trim(stud.NAME);
          ADOQuery1.Parameters.ParamByName('group1').Value := StripNonAscii(Trim(stud.Group));
          ADOQuery1.Parameters.ParamByName('addr').Value := Trim(stud.Addr);
          ADOQuery1.Parameters.ParamByName('city').Value := Trim(stud.City);
          ADOQuery1.Parameters.ParamByName('postcode').Value := Trim(stud.postcode);
          ADOQuery1.Parameters.ParamByName('phone').Value := Trim(stud.Phone);
          ADOQuery1.Parameters.ParamByName('gender').Value := Trim(stud.Gender);
          ADOQuery1.Parameters.ParamByName('group2').Value := Trim(stud.Group2);
          ADOQuery1.Parameters.ParamByName('adminID').Value := StringReplace(Trim(studcom.barcode), '-', '/', [rfReplaceAll, rfIgnoreCase]);
          ADOQuery1.Parameters.ParamByName('maxCount').Value := stud.MaxCount;
          ADOQuery1.ExecSQL; 
         end;
        Inc(i);
      until (FromFStream.Position-FromFStream.Size=0);
    finally
      FreeAndNil(FromFStream);
      FreeAndNil(FromF2Stream);
      ADOConnection1.CommitTrans;
    end;
    end;
  end;
end;

Automating Import

To ensure the data is always up to date in the SQL Server database, as part of the backup process each night, this program is run with Windows Task Scheduler.

Going further

Bookmark has more files that can be imported in a similar way. Files for book records, loan data and book reviews are all there.