BTR2SQL 5.3 makes Btrieve LOBs SQL-accessible

Over the years, one specific portion of a Btrieve record has given some of our customers trouble. That's the variable portion, the bit that allows you to store up to 2GB of additional, unstructured data within a record using Large Objects (LOBs), in excess of the record's "fixed" 64K data limit.

A record's variable portion is extremely flexible. Customers often use it to store notes, pictures, or other media alongside the traditional information stored in a record's fixed portion. For example, if you maintain an employee database in Btrieve, you might include text-based information on an employee in the fixed portion of that employee's record, and then include a picture of that employee in the variable portion.

Recently, we updated BTR2SQL to allow our Microsoft SQL-based customers to easily migrate and access their Btrieve records' variable portions. To learn why we felt this feature was so important, and why it was so tricky to implement, read on.

Some LOB-based background

A long time ago, all Btrieve-using businesses had to manage their records' variable portions using chunk APIs. Chunk APIs are incredibly flexible, meaning that as long as you maintained an accurate map of your variable portion data, you could store and access nearly anything from within that portion. However, these chunk APIs didn't play nicely with the SQL-based access that many customers (including ours) began migrating toward as relational databases became more prevalent. While the data stored within the variable portion was still accessible, it was all lumped into one SQL column, no matter how many "distinct" pieces of data you'd defined.

As Btrieve was made compatible with SQL-style access, Pervasive added a new Btrieve data type, type 21, to allow developers to place SQL-supported guideposts within the fixed portion, mapping the variable data for SQL access. Developers would define the length of each LOB within their record's fixed portion, then SQL API-using apps would automatically know when and where to split the variable portion's data for correct data access. In this way, a number of SQL-database-using Btrieve customers finally received (renewed) access to their records' variable portions.

Unfortunately for us, adding LOB access to our own BTR to SQL database driver was a bit trickier. Because our driver replaces your existing lower-level Btrieve APIs (and not your SQL APIs), we've been migrating customers' variable portion data to SQL without splitting the defined pieces of that data into their own, easily accessible SQL columns. Due to the complexity involved in untangling this aspect of the migration, and due to the fact that workarounds existed, we waited a while before tackling this feature.

But in the last couple years, we've seen an uptick in demand for more robust LOB access, so we got to work on including LOB definitions within BTR2SQL's data migration. This year, we added multi-LOB support, as we call it, to BTR2SQL version 5.3, which is available for Microsoft SQL Server.

How LOB access works

Our solution also depends on data type 21 mapping. Prior to BTR2SQL 5.3, our data migration tool migrated all data contained within your records' variable portion as one SQL column, when transferring your data to your new SQL backend. Now, using your type 21 mapping, BTR2SQL splits variable portion data into separate SQL columns. Continuing our example from above, BTR2SQL might now see that you've defined an employee picture within a record's variable portion, using the record's Offset/Length value. When BTR2SQL moves that data over to your Microsoft SQL server, it now drops the picture within its own SQL column, instead of mashing it together with the rest of your variable portion data.

If you choose to use multi-LOB support, any undefined, unmapped data in records' variable portion will no longer be migrated. So if you implement multi-LOB support, you'll want to be sure to either define all your variable portion data or test to ensure your application can function without your undefined data.

After you migrate your data from Btrieve to SQL, you must access defined LOBs using new Btrieve-style APIs, such as BTR2SQL 5.3's B_INSERT_DELAYED, B_UPDATE_DELAYED, and B_COMMIT_INSERT. These APIs allow you to cache LOB data as your application loads it, so you can return, access, or update LOB data quickly, receiving a boost in performance.

Why LOB access matters

Normal Btrieve API calls can handle only about 64K of data at a time, due to those APIs' pre-existing limitations. However, in BTR2SQL 5.3, we've updated our Btrieve APIs so they can cache many packets of data (in this case, the many pieces that make up LOBs) in memory until an EndInsert command is received. Then, the API packages these packets together and sends them all at once, allowing speedier SQL LOB access. If your application includes a large number of LOBs, or a small number of large LOBs, you'll see a huge performance boost when you upgrade to BTR2SQL 5.3.

There are, of course, some limitations. First, you'll have to update your app's API calls, using our new version 5.3 APIs. Additionally, you'll have to keep any changes to your application's type 21 data mapping in sync in cases where you change the length of a piece of data defined in your records' variable portion. You've likely already been doing this, but it becomes even more important when you attempt to use this feature. To help you keep everything in sync, we've also created a new API, MdsCreateTriggers, that you can include if you will be modifying your data directly in SQL.

MdsCreateTriggers is to be called once per file or table during migration. When you perform a migration using the BTR2SQL Migration Utility, you can select a new option within the utility to have BTR2SQL perform MdsCreateTriggers calls for your data . If you perform the migration yourself, without using the migration utility, you must include these calls in your migration. Additionally, if you add tables in the future, using MdsAddTable or B_CREATE, you must also call the MdsCreateTriggers API as you add those tables.

There are also a number of other considerations you'll want to take into account as you enable this new feature. For example, you'll need to manage the memory involved in caching LOB data, which can run extremely tight in 32-bit Windows, and you'll need to set a reasonable limit on your LOB sizes. But all these technical details are things that you'll have to work out in only certain specific situations. For the most part, LOB access should now easily work as you'd expect after you migrate your Btrieve records to SQL with this feature enabled.

Conclusion

We're in the process of updating the BTR2SQL docs with more detailed information on how our LOB migration and access now works in conjunction with Microsoft SQL Server. That information should be available sometime within the next couple weeks. For now, when accessing your LOBs using BTR2SQL 5.3, refer to the SDK documentation included within the BTR2SQL 5.3 download, which contains information on how to use the LOB-related APIs we mentioned above. And if you have any questions, feel free to leave a comment below or contact us at support@mertechdata.com.