BLOB not found errors using Firebird and IBObjects

One of my applications suddenly started throwing “BLOB not found” errors apparently at random. After some hours spent googling I came up with a cause for the problem but no solution. A bit of fooling around came up with a workaround. A fix for IBObjects would be better, but this at least gets rid of the problem.

The Cause of the Problem

This gets a bit complicated, but I will do my best to explain the configuration that caused this to happen.

The application uses a TIB_Query, connected to a TIB_Transaction to feed data to a TDBGrid. The TDBGrid fetches the value of BLOB columns a necessary and forces the value to be displayed in the appropriate cell.

The TIB_Query is configured as follows:

CommitAction := caFetchAll;
FetchWholeRows := True;

This causes the query to fetch and cache all rows when the transaction is committed. FetchWholeRows causes the actual data for each column to be cached, rather than just the primary keys of the rows.

The TIB_Transaction has the IBObjects auto OAT advancement feature configured as follows:

TimeoutProps.AllowCheckOAT := 10;
TimeoutProps.Attempt := 5;
TimeoutProps.AttemptMaxRow := 1000;
TimeoutProps.AttemptRetry := 1;
TimeoutProps.AttemptTicks := 250;
TimeoutProps.ForceClosed := 0;

This causes the transaction to start fetching all rows and commit after 10 seconds.

This setup works just fine until such time as another user updates a BLOB that has been cached but not yet displayed in the grid. In this case, you will get a “BLOB not found” error when the user scrolls the grid in an attempt to display the modified BLOB. This is due the to fact that IBObjects only caches the BLOB ID and not the BLOB data. Since Firebird does not guarantee that BLOB IDs are the same before and after an update, IBObjects fails when it tries to fetch the BLOB data using the out-of-date BLOB ID.

The Workaround

If you are using Firebird 1.5 or later there is a workaround to this problem. Rather than returning the BLOB itself, use the SUBSTRING function to cast the BLOB to a VARCHAR and return the VARCHAR. Obviously, this will only work for you if you can be happy with some fixed length portion of the BLOB, but for grid display you probably don’t want the whole thing anyway. In general terms, what I did was this:

SELECT SUBSTRING(SOME_BLOB FROM 1 FOR 1000) AS SOME_BLOB
FROM SOME_TABLE

NOTE:

The above kludge no longer works  for Firebird 2.1.x.  In the new version of Firebird the SUBSTRING function returns a BLOB rather than a VARCHAR.  Luckily, Firebird 2.1.x allows you t0 CAST a BLOB to a VARCHAR.  So you can change the SELECT statement to this:

SELECT CAST(SUBSTRING(SOME_BLOB FROM 1 FOR 1000) AS VARCHAR(1000)) AS SOME_BLOB
FROM SOME_TABLE

This will work for both older and newer version of Firebird.

One Response to “BLOB not found errors using Firebird and IBObjects”

  1. payday advances Says:

    I am to a great extent impressed with the article I have just read. I wish the author of lnssoftware.dnsalias.net can continue to provide so much productive information and unforgettable experience to lnssoftware.dnsalias.net readers. There is not much to state except the following universal truth: Rummaging in an overgrown garden will always turn up a bouncy ball. I will be back.

Leave a Reply