Restore a Corrupted Test using Firebird

Occasionally, users may receive an "access violation error" or a "stream read error" when attempting to load a previously-recorded test. This is a very intermittent bug that we haven't been able to 100% nail down the cause of. We suspect that the Encrypt Data Records parameter in Configure System > Program > Database may be involved, so disabling that may prevent this from happening in the future.

To restore your test, use the following directions. Note: if your system is connected to a network, you can contact Diagnosys and set up a time for us to remote in to your system and perform the restoration for you.

• Step one is to back up your database. Make sure your backup is in a secure location. You can do this by opening the software, pressing Database Center, then Backup. Ideally backups are located on an external drive/directory. (Important note - We will be using square brackets in these instructions to indicate where a value must be supplied. IGNORE THESE BRACKETS. If you see them in formulas, omit them when supplying your own values.)

• Step two - navigate to the patient whose test is giving you an access violation or stream read error. Note down the date and time of the test, as well as the patient name. Select the test and press Delete. You'll be warned that this is a permanent change (it isn't) - confirm you wish to delete the test, and close the software.

• Step three - navigate to C:\Multifocal\Firebird and Utilities. (If you are using an earlier version of the software, it will be in the C:\Espion V5\firebird directory). Doubleclick on IBManager.exe to open it. (If this is the first time you're opening this, and it should be, it'll likely pop up with a question about how you want to view things. Keep everything as the default and press Okay.)

• Step four - up at the upper left, click the Database menu, then click Register Host. Under the password field, type masterkey but keep everything else as is. Press Next. Press Finish. Up at the upper left, you should see a little grey icon of a computer appear, next to the word localhost. Doubleclick on the computer to connect to localhost. The computer icon should become colored and a Users subfolder should appear.

• Step five - click the Database menu again, then click Register Database. Press Next. Press Next. Up at the top, where it says Database Name, click on the Browse icon and navigate to your database (if you're not sure, you can find out the name and location of your database by reopening the Espion software and pressing Database Center. This will list the name and location of your database). Once you've done this, press Finish. Now under localhost you should see a little grey can that says [DATABASENAME] on localhost. Doubleclick on [DATABASENAME] and the grey can will turn gold and a bunch of folders will show up. Much like this:

• Step six - So the first thing we're going to do is find your deleted test. At the top of the page, click Tools and then New SQL Editor. In the Edit box on the right, copy this string:

SELECT * from AUDIT where Table_name='TESTS' and Reason_modified='DELETED RECORD';

Once you've done that, press Execute Query. It's in the middle column, the very top link.

This will return a list of all of the tests you have ever deleted. The ENTERED_DATE column reflects the date that test was deleted so bear that in mind. Once you've figured out which the right test is (the ENTERED_DATE will be five minutes ago), take note of the RECORD_NO of that test. (You may need to scroll to the right in order to see the RECORD_NO column - these images have been rearranged). Got it? Moving on.

• Step seven - Once we have the test's record number, we're going to make sure we're restoring the OLDEST test information we have (remember, the most recent version is corrupt!). You're looking at a tabbed list of results. Up at the top of this third column, click on the Edit tab. Select what's (still) there, and delete it. Replace it with this:

SELECT * from TESTS_HISTORY where TEST_NO=[record number you just got-- remember to delete these square brackets];

and press Execute Query again.

This provides you with a life history of that test's revision info. Generally, the lowest THID_NO (test history id number, by default the far-left column) is the oldest. You'll also notice, if you scroll to the right and look at the TEST_DATA column, the most recent version of the test will have a blank page icon and older versions will have filled-out pages as icons. Grab the THID_NO of the newest filled-out page and we'll move on.

• Step eight - this is where we reverse the deletion. Go back to the Edit tab, delete what's in there, then copy this in:

Insert into Tests(test_no,subject_no,Protocol_no,Unique_ID,test_date,examining_doctor,comment,test_data,test_flags,report_data,Last_updated,normal,username,reason_modified,serial_no,creation_date)Select tests_history.test_no,tests_history.subject_no,tests_history.Protocol_no,tests_history.Unique_ID,tests_history.test_date,tests_history.examining_doctor,tests_history.comment,tests_history.test_data,tests_history.test_flags,tests_history.report_data,tests_history.last_updated,tests_history.normal,tests_history.username,tests_history.reason_modified,tests_history.serial_no,tests_history.creation_date from tests_history where THID_NO=[THID_NO YOU JUST GOT];

(yeah. we know).

Press Execute Query. If the box at the bottom says Query OK, (x ms) then the code works. If you get an error, it was probably a copy/transcription error - these are really long lines. You can find a list of all the SQL commands attached to this article.

Down at the bottom of the middle column is a link that says Commit transaction with a little green checkbox next to it. Press that, and it will insert the deleted test back into the database.

When you open the software, the test should be restored into the database and you should be able to load it without issue. If you need any help, please contact support@diagnosysllc.com or call +1 (978) 458-1600 (US) or +44 (0) 1223 520 699 (UK)