Sybase Business Intelligence Solutions - Database Management, Data Warehousing Software, Mobile Enterprise Applications and Messaging
Sybase Brand Color Bar

Search for    in all of
view all search results right arrow
Support > Technical Documents > Document Types > Technote > Working with Binary Large Objects (BLOBs) in Power...  
RSS Feed
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. For Example:
· You need to store OLE objects in your database
· You need to store large binary objects in your database
· You are storing text larger than that easily handled by normal functions
· You need to access a variable type that PowerBuilder does not support. For example, in SQLBase the longvarchar datatype is not a supported datatype, but can be manipulated through use of blob functions.

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.

DBMS                            Datatypes
ASA (SQLAnyWhere)    long binary, long varchar
Oracle                             longraw
SQL Server                     Image, Text
Informix                           Text
MDI Gateway DB/2        Long varchar
SQLBase                        longvarchar
AllBase                           longvarchar
DB2/2                            N/A

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:
a) have a column defined as the local data type which maps onto the Binary Large Object (i.e., Long Binary in ASA, Image in SQL Server, Long Raw in Oracle, etc...)
b) be defined to allow nulls.
c) have a primary key

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 menu.
You will be presented with the Database Binary/Text Large Object window, which is shown in figure 1


Figure 1

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
OLE columns should never be updated from anywhere but within the datawindow. OLE objects are stored along with a "wrapper" or object container that describes their class and provides information on the server which originated them, and this "wrapper" must be reserved when the field is updated. This functionality is built into the datawindow and the OLE column object, but if you attempt to update the Blob from somewhere else you will destroy the wrapper around the data, leaving it useless with respect to OLE.

Using the dwOLEActivate Function
If you wish to activate an OLE column programmatically i.e., without requiring the user to double- click on the column you can make use of the dwOLEActivate. Also, if you wish to activate an OLE column using a 'verb' different from the default you will need to use this 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
Let assume for the moment that you have created a datawindow, called "dw_sound", which has an OLE column called "sound_blob" which is used to contain .WAV files with Microsoft's Quick Recorder as the OLE Server. Some sample commands might be:

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:
What each verb does and which number maps to that verb vary with each OLE server. See the documentation provided with your OLE server to determine this. To see a list of verbs for your server you can consult the Registration Database, using Microsoft's REGEDIT.EXE, which is provided with windows. To do so, run this command line:


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
While Sybase will handle OLE columns well, there is a limitation with blobs mapping onto the text datatype. Because of the way Sybase's string functions work, you are limited to a 4K field size when working with the Text type as a blob field. The work around for this is to make the field an OLE field, as the low-level functions which deal with the SQL Server in OLE columns do not have this 4K limit.

III. Accessing Information Stored in a BLOB column without using OLE
PowerScript provides you with a number of methods for dealing with Binary Long Objects without using Object Linking and Embedding. Here are a few examples of these methods, and a short explanation of what they do:

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.*/

integer li_filenum, li_loops, li_counter
long ll_filelen, ll_bytes_read, ll_new_pos
blob lb_our_blob, lb_tot_b, blob_temp, total_blob, blank_blob

//Find out how long the file specified in sle_filename.text is
ll_filelen = FileLength(sle_filename3.text)

//Assign Open for read, and assign a filenumber to the specified file
li_filenum = FileOpen(sle_filename3.text,STREAMMODE!,READ!,LOCKREAD!)

//Determine how many FileReads will be necessary to read all of the file ...
//You can't read in more than 32K at a time
IF ll_filelen > 32765 THEN
    IF Mod(ll_filelen,32765) = 0 THEN
        li_loops = ll_filelen/32765
        li_loops = (ll_filelen/32765) + 1
    END IF
    li_loops = 1

//read the file ...
FOR li_counter = 1 to li_loops
    ll_bytes_read = FileRead(li_filenum,blob_temp)
    lb_tot_b = lb_tot_b + blob_temp
    ll_new_pos = ll_new_pos + ll_bytes_read

    if len(lb_tot_b) > 1000000 then
        total_blob = total_blob + lb_tot_b
        lb_tot_b = blank_blob
    end if

total_blob = total_blob + lb_tot_b


UPDATEBLOB rb_blob SET rb_blob2 = :total_blob
WHERE id = 5
USING sqlca ;
If sqlca.sqlcode <> 0 then
messagebox("connect error",sqlca.sqlerrtext)
end if

commit using sqlca;
If sqlca.sqlcode <> 0 then
messagebox("connect error",sqlca.sqlerrtext)
end if

//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..)
2. Must have a unique key that identifies each blob row
3. Must have allowed the blob column to contain nulls
4. Must already have entered a row for the blob you are about to write, with it's primary key.
5. SQL SERVER users must set AUTOCOMMIT to TRUE before doing any BLOB functions. (It may be set back to false after the blob function is finished, but none of the blob function will work on these databases if they are attempting to do transaction processing.)

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
WHERE id = 5
USING sqlca ;
If sqlca.sqlcode <> 0 then
messagebox("connect error",sqlca.sqlerrtext)
end if

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
blob total_blob

  INTO  :total_blob
  FROM rb_blob
  WHERE id = 5
  USING sqlca ;


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
Three other functions which may be useful when working with Blobs are the functions Blob(), BlobEdit() and BlobMid(). These functions are explained in the PowerBuilder, Powerscript reference documents.


Related Links

Last Revised: Jun 27, 2001
Product: PowerBuilder
Hardware Platform: Windows x86
Technical Topics: Database Admin
Business or Technical: Technical
Content Id: 44008
Infotype: Technote

© Copyright 2014, Sybase Inc. - v 7.6 Home / Contact Us / Help / Jobs / Legal / Privacy / Code of Ethics