Loading a File into Advantage Database
I switch back and forth between doing cool new stuff with the latest version of Delphi on web and mobile platforms and supporting legacy apps that have been running for a long time but the original programmer has long since left. The project I'm working on today falls into the latter category and the current challenge is storing a letter template file in an old Advantage Database. My update to the software has be put into an installer script and sent out to non-technical customers to update their existing installed databases.
After hunting around, I found the Advantage Developer Zone with resources, help files, and utilities (most of which didn't actually help that much with my existing Delphi 7 application). After pouring over the SQL documentation and with a bit of experimentation, I learned how to update a BLOB field in the database with a hex dump of the file.
First, I needed to convert the .rtf version of the letter template to hex. There are many ways to do this, both online tools and likely using your favorite text editor. I found a little command-line tool called bin2hex and soon had the hex version of the file ready.
(Using Microsoft Word to create a sample .rtf file for this article revealed a ridiculous amount of overhead for these simple files. My one line that said "Sample .rtf file" resulted in a 46K file and pulling that up in a text editor showed 227 lines! Needless to say, the code in the next paragraph is cut quite a bit short.)
The SQL statement to update the table takes a specific format but supports unlimited line lengths as BLOB fields can be arbitrarily long:
UPDATE LetterTemplates SET Letter91 = x'7B 5C 72 74 66 31 5C 61 6E 73 69 5C 64 65 66 66 30 5C 75 63 31 63 70 67 31 32 35 32 5C 64 65 66 74 61 62 37 32 30 7B 5C 66 6F 6E 74 74 62 6C 7B 5C 66'
The "x" signifies the long string that follows is a series of hexadecimal digits. The application uses the popular WPTools word processing component to manage letter editing and mail merging.
This worked pretty well to send out updates with InnoSetup spinning off a database update utility running these SQL scripts from temporary files.
Another day's work!