SQL Server | Read About Technology

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.

At the school where I work, student details are stored in a student administration system called SAS2000. This stores lots of information about each student like name, date of birth, year level, parents names, enrollment dates and their student ID number. The actual data is stored in a SQL Server database on one of our servers.

I wanted to create something that would be able to be hopefully modified by someone other that me in the future, so I chose to use vbscript, instead of my preferred language of Delphi for this task. At first I had code to query the Student table in the SQL Server database and then some functions to query Active Directory. This made the code balloon up in size and was getting to complicated.

Querying Active Directory with SQL Server

After a bit of research on the internet I found that you could use a feature of SQL Server to show active directory accounts as a table. This involved creating a connection to AD in the linked servers section of Management Studio and then creating a view called AD_Student with the code shown below. This uses the OLEDB Provider for Microsoft Directory Services.

SELECT sn, givenName, cn, sAMAccountName, title, employeeID, facsimileTelephoneNumber, 
            mobile, mail, telephoneNumber, displayName
FROM OPENQUERY(ADSI,'SELECT displayName, telephoneNumber, mail, mobile, 
    facsimileTelephoneNumber,employeeID ,title,sAMAccountName, cn,givenName,sn  
         FROM  ''LDAP://domain.local/OU=Students,DC=domain,DC=local'' WHERE objectClass =''User'' ')
         AS tblADSIORD

You obviously need to replace LDAP://domain.local/OU=Students,DC=domain,DC=local with what is relevant to your Active Directory domain.

Linking data from Active Directory with a SQL Server table

In SAS2000 each student gets an ID code that is unique to them. Each AD user account also gets a unique GUID. My first idea was to create a table that linked the Student ID Code from the SAS2000 student table to the account GUID in active directory. Unfortunately I wasn’t able to work out how to get the GUID with VBScript. My next idea was to alter the AD Schema. After some further reading however, I came across an existing field in Active Directory called EmployeeID. Obviously students aren’t employees, but it is a near enough match. So I went through all the students accounts and manually added their Student ID Code from the SAS2000 table to the EmployeeID field in Active Directory.

From this I was able to create another view that uses a “NOT IN” query that results in a list of students that are not in Active Directory Yet.

SELECT ID, Code, FirstName, MiddleName, LastName, PreferredName, PostNominal, NName, FormerName, TransferCode, FC2kSourceID, FC2kID, FC2kCode, Cases21ID, Cases21Code, AddressID, LeavingAddrID, NewAddrID, PhoneFamily, PhoneHome, PhoneMobile, Email, Email2, FamilyID, DOB, NDOB, Sex, CreatedWhen, UpdatedBy, UpdatedWhen, Comments, COB, PlaceOfBirth, Religion, House, Form, Distance, StudentType, HomeGroup, Year, Class, Rank, Youngest, Oldest, EntryYearLevel, StartYear, StartDate, PrevSchoolID, PrevLevel, PreEnrolment, Inactive, InactiveFrom, InactiveTo, InactiveReason, Vsn
FROM dbo.SAS_Student
WHERE (Code COLLATE DATABASE_DEFAULT NOT IN (SELECT StudentID FROM dbo.AD_Student))

This query shows any students that are enrolled, but don’t yet have a Active Directory user account. See Part 2 for actually creating the account in Active Directory.

Read About Technology

Tag Archives: SQL Server

Reading Bookmark Library System data files