Sunday 21 June 2009

A more sophisticated way of importing a database

The current Wessex Premier has a very simple way of checking a database to import - if it's not called "V3.mdb" it won't be imported. Now this works at a very simple level, but it leaves two problems

The first is that all the backed up files have to be called "V3.mdb" (rather than anything more convenient like say "ShopBackup_16_6_09.mdb"). You can get round this by renaming the file after exporting and back again before importing, but this is an awkward and fiddly way of dodging the issue.

The second problem is that although the file might be called "V3.mdb" the internal structure of the database (called the "schema") could have been changed and this would make it incompatible with the program.

So, is there a way of checking the schema of each table against a master schema-file?
There is, of course, otherwise I wouldn't be writing this!
This first thing to do is to extract the schema from the file to be imported. The crucial line of code is this -

Which writes the schema to a temporary (xml file) to compare against master xml schema.

Then the temporary file is then run (byte by byte) with the master file and each byte is compared, if they don't match then the file to be imported is rejected. The heart of the code is this -

MasterByte = MasterStream.ReadByte 'master table
CheckByte = CheckStream.ReadByte 'table to be imported
If MasterByte <> CheckByte Then
blnOk = False
Exit Do
End If
Loop While MasterByte <> -1

This is done for each of the four tables in the program's database, as long as the file-to-be-imported passes the test it can be imported (its name is changed to "V3" for internal use).
Obviously there is a lot more code to set up the above lines - checking that the files exist, that the user hasn't pressed "Cancel" as so on - but in testing I haven't yet come across any problems, so it looks like this feature will be in Wessex Premier II.

No comments:

Post a Comment