Working with Binary Large Objects (BLOBs) Updated for Powerbuilder 8.0
When do I want to use Powerscript's BLOB datatype?
There are several reason's why you would want to use the BLOB datatype.
What datatype on my DBMS can work as BLOB in Powerscript?
It's best to consult your database documentation, as well as the Appendix of your PowerBuilder database interface guide to determine this. Listed below are some supported databases and the corresponding datatypes.
I. OLE columns - DataWindows only:
Before creating on OLE column in a datawindow you must meet a few requirements:
1. The table that will contain the OLE column must:
2. In the PowerBuilder development environment, if you are using SQLServer you must set AUTOCOMMIT to TRUE (set the AUTOCOMMIT option to in Preferences to 1).
3. In the Datawindow Painter create a datawindow. The column previously defined to store the binary (BLOB) data must not be included in the data source.
4. Add an OLE column by going to the Insert/Control/OLE Database Blob
Clients Class: This is the name of the class that is used if your application is referenced by the OLE server. Here it is the default class of 'Datawindow'
Client Name: This is the name OLE servers will use when referring to your application. Here we have left the default name of 'untitled'
Table: This specifies the table that contains the blob column. In this case the table is 'rb_blob'.
Large Binary/Text Columns: This selection is the column that contains the actual blob. In this case the 'rb_blob' column is selected to store the OLE object.
Key Clause: This specifies the WHERE criteria that allows PowerBuilder to select and update the correct row. This will default to the primary key of the table. In this case, id is the primary key and the select and update statement will have a where clause of "... where id = :id"
File Template: Specify a file name, if you wish to start each object based on a common template. In our example, the sound bit storage, we are starting with an empty file each time, but if you were storing Word documents for example you might want to always start out with a specific .DOC file.
OLE Class: Description (Only Class is used) : If you are not using a file template, you will need to specify the class of application that you wish to use . In this case, since we are using Word Document as our OLE Class.
Client Name Expression: This expression will evaluate to a unique string for each row in the blob column. This allows OLE to reference each blob row as a unique object. This case uses a series of unique expressions created by adding the unique blobkey onto the string blob'. It is common to use the primary key field in this expression.
Test your OLE column in the Design Preview mode. Double-clicking on the control will start the server application, either with a new document , if you specified the server by object class, or with a copy of the template, if you specified a template file. In order to make the OLE column more visible, it is often a good idea to place a graphic object behind it, or to give it visible border.
When you have actually stored data in the column, the control will be represented on the data window by an icon appropriate to the server application you have assigned to that OLE column.
Updating an OLE Column
Using the dwOLEActivate Function
The format of this function is:
datawindowcontrol.dwOLEActivate ( row, column, verb )
datawindowcontrol: The name of the DataWindow control or child DataWindow from which you want to activate on-line linking and embedding (OLE).
row: A long identifying the row location of the OLE object
column: The column location of the OLE object. It is best to use a string containing the column name. Using the example in figure 1, this would be "sound_blob"
verb: Usually 0, but the verb is dependent on the OLE server
dw_sound.dwOLEActivate(5, "sound_blob", 1)
This will activate OLE for the OLE Column "dw_sound" in row five. We are passing the verb 1, which means Edit for this OLE Server, so we will activate the link for edit.
dw_sound.dwOLEActivate(2, 4, 0)
This will activate OLE for row two, column four (which better be an OLE column!), passing the verb 0. In this case 0 represents the action "Play" so this command will play the .WAV stored in row two, column four.
A Note on Verbs:
Which will show you the Registration Tree, from which you can see the verbs and their associated numeric values.
Note: The Registration Database is essential to many Windows operations. Consequently, you should never manually edit the database, unless it is absolutely necessary. Should editing be necessary you should be very careful in the process.
A Note to Sybase Users
III. Accessing Information Stored in a BLOB column without using
Reading Data Into A Blob Column
/*This Script Reads In A File Exceeding 32766 Bytes In Size. The Same Script Can Be Used To Read In Both Text And Bitmaps.*/
//Find out how long the file specified in sle_filename.text is
//Assign Open for read, and assign a filenumber to the specified
//Determine how many FileReads will be necessary to read all of
the file ...
//read the file ...
if len(lb_tot_b) > 1000000 then
total_blob = total_blob + lb_tot_b
UPDATEBLOB rb_blob SET rb_blob2 = :total_blob
commit using sqlca;
//If it was a picture...
Now that you have the BLOB stored in a blob variable, you may write it to the database. If you are not going to use OLE, the you will need to make use of the SELECTBLOB, UPDATEBLOB functionality provided by PowerScript. In order to use these functions you must meet a couple of conditions:
1. Must have blob column defined in whatever your local equivalent is
(i.e., Long Binary or Long Varchar in AdaptiveServerAnywhere, Image in
SQL Serer, long raw in Oracle, etc..)
For the purposes of our example let's say we have a simple lookup table called "blob_table" composed of a key field, of type integer, called "blob_id" and a blob column called "blob_col". We have defined the blob column as long binary (for AdaptiveServerAnywhere) and set it to allow null values. Before we can update the blob to the database, we must insert the key value for the row we wish to update. In this case, let's say we have added a new row to the table with blob_col set to NULL and the blob_id set to 5. Having all of that the syntax needed to update the blob to the database is:
UPDATEBLOB rb_blob SET rb_blob2 = :total_blob
The WHERE clause could also use a host variable, rather than having a hard-coded value.
The complement of UPDATEBLOB is SELECTBLOB. Using SELECTBLOB is exactly like using a regular SELECT INTO statement, except that you are dealing with Large Objects. For example:
//Assuming the existence of a blob variable lb_blob_var
You can now manipulate the blob variable through Powerscript as you choose.
N.B.: While you can use UPDATEBLOB to update a number of BLOB rows to the same value, but you CAN NOT use the SELECTBLOB function to return more than one row. You must set up your WHERE clause such that the SELECTBLOB only returns ONE row. The SELECTBLOB is a singleton select.
When you have a Blob variable defined in PowerScript you can of course write to a file, in much the same way that you can read information from a file into a blob variable.
IV. Other Blob Functions