|By Bruce Armstrong||
|March 24, 2011 10:00 AM EDT||
If you're familiar with the Database Binary / Text Large Object column type in PowerBuilder Classic (see Figure 1), you know it's a way of storing blob data associated with an OLE Automation application (Paint, Microsoft Word, Microsoft Excel) and then displaying it as part of a DataWindow.
There were some limitations with the Database Binary / Text Large Object. It required the end user to have the application that was used to add the object to the database in order to view the data. It often didn't display the data well within the DataWindow. And it wrapped the data stored in the database with an OLE wrapper, making it difficult to deal with the data outside of the OLE Automation application used to store it.
To address those issues, a new column type, called the TableBlob column, has been added to the WPF DataWindow. Like the Database Binary / Text Large Object column, there is a separate dialog used to associate the column to a particular table and blob column in that table and specify the where clause (see Figure 2). However, with the TableBlob column, the only other thing you have to specify is the type of data that will be presented. Current choices are limited to Rich Text, Image, and XPS Document.
Let's see how it works with some images. I created the following table in SQL Anywhere to hold the data:
CREATE TABLE "dba"."images" (
"image" long binary DEFAULT NULL,
"id" integer DEFAULT autoincrement,
"pathname" varchar(128) DEFAULT NULL) ;
The pathname isn't necessary for the new column type to work; it was primarily added because of the way PowerBuilder interacts with tables that have autoincrement primary keys. I have to store something in the table when I originally create a record. It can't be the blob, because we'll be loading that after the record is created using an UPDATEBLOB statement. And it can't be the autoincrement column, because that's the column I want the database assigning and then PowerBuilder pulling back for the newly created row. I added the pathname to have some updateable field for the initial row insert.
To load images into that table, I created a DataWindow for it and then used the script in Listing 1 to allow the end user to load the image files. I then created a WPF DataWindow and dropped a TableBlob column onto it, providing these values in the dialog:
Large Binary/Text Columns: image
File Type: Image
Key Clause: id = :id
I created a WPF Window to host it, scripted up the connection to the database and the retrieve, and the result is shown in Figure 3 (provided you're a Braveheart fan).
One consequence of using the TableBlob column to render the data is that double-clicking on the image display won't do anything by default. You would have to add code to respond to that event. For a Database Binary / Text Large Object column, double-clicking on the data would cause PowerBuilder to launch the associated application and pass the data to it. You could also accomplish the same thing in code using the DataWindow OLEActivate method. I can think of one time when I found that even remotely useful, and most of the time I was trying to find a way to prevent that behavior. The TableBlob column gives me the capability to display the data more elegantly, and yet have much better control over when I want the user to be able to interact with the data outside of the application.
If you do want the user to be able to work with the data outside of your application, you could accomplish that by writing out the data to a local file (the inverse of Listing 1) and then passing that to the target application via command-line arguments (the lpParameters attribute of the SHELLEXECUTEINFO structure passed to the Windows API ShellExecuteEx command to launch the application) or through OLE Automation.
What gets a bit more complex would be updating the data if the user modified it. With the Database Binary / Text Large Object column, that happened pretty much automatically when the user closed the launched application (providing that your application and the window that launched the OLE application was still available). The OLE application returned the updated information back to the Database Binary / Text Large Object column and then you could decide what action to take regarding an update of the database.
One approach would be to have your window remember the timestamp on the file and check it before allowing the window to close to see if it's been updated. If you used the ShellExecuteEx method to launch the target application, you could also use the hProcess attribute of the SHELLEXECUTEINFO structure to monitor the target application to see when the user closes it and do your update check there. (You would need to set the SEE_MASK_NOCLOSEPROCESS flag on the fMask attribute to let ShellExecuteEx know you wanted the handle of the target application back.) Perhaps the best method might be to simply have the user manually upload the modified file, much the way it was originally loaded.
Updating the data, at least in my experience, is more the exception than the rule. The bottom line is that the TableBlob column gives us a much better way to display image and rich text data in our applications (I don't have much use for XPS). Right now it's limited to WPF applications, but Sybase has indicated that the TableBlob column type will be added to the DataWindow object for PowerBuilder Classic in a future release.