Dynamically Setting a Default Printer on Win95 and NT (32 bit only)
| SUMMARY: | This document contains information on working with binary large objects (BLOBS). |
| Document ID: | 44008 | Last Revised: | 01/29/96 |
| Topic: | Database Admin | Document Type: | TechNote |
| Product: | PowerBuilder | Version: | 4; 3 |
| Platform: | PC | Operating System: | Not Operating System Specific |
Document:
Working with Binary Large Objects (BLOBs) (Pre-, PB 5.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.
ls_key = String variable containing the registry path to the printer information.
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
Watcom SQL
long binary, long varchar
Oracle longraw
Image, Text
Text
MDI Gateway DB/2
Long varchar
SQLBase
longvarchar
AllBase
longvarchar
DB2/2
N/A
I. OLE columns - DataWindows only:
(for further reference see the PowerBuilder 3.0 User's Guide pages 790 - 798)
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 Watcom, 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 Objects menu and selecting OLE Database Blob.You will be presented with the Database Binary/Text Large Object window.
Name: This specifies the name for the blob column, if you will need one. You don't need to name it, but if you do then you will be able to refer to it in scripts. This example is named 'sound_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 secifies the table that contains the blob column. In this case the table is 'soundblob'.
Large Binary/Text Columns: This selection is the column that contains the actual blob. In this case the 'soundblob' 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, blobkey is the primary key and the select and update statement will have a where clause of "... where blobkey =: blobkey"
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 recording soundbits, we set the class equal to SoundRec. You will be given the option of setting this to any of the classes defined in the Windows Registration Database. If your server is not listed, you will have to use the registration editor to add it. Manual editing of the registration database is not a trivial task, and is not recommended to the novice. See your windows manual for information on Regedit.
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. In our case the control will be represented by the cassette icon appropriate to the Microsoft Sound System .WAV server, the Quick Recorder.
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:
REGEDIT /V
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.*/
SetPointer(HOURGLASS!)// Let the user know that the script is doing something
int li_filenum, li_loops, li_counter long ll_filelen, ll_ll_bytes_read, ll_new_pos blob
lb_ourblob, lb_tot_b
ll_filelen = FileLength(sle_filename.text)
//Find out how long the file specified in sle_filename.text is
li_filenum = FileOpen(sle_filename.text,STREAMMODE!,READ!,LOCKREAD!)
//Assign Open for read, and assign a filenumber to the specified file
IF ll_filelen > 32766 THEN
IF Mod(ll_filelen,32766) = 0 THEN
li_loops = ll_filelen/32766
ELSE
li_loops = (ll_filelen/32766) + 1
END IF
ELSE
li_loops = 1
END IF
/*Determine how many FileReads will be necessary to read all of the file ...
You can't read in more than 32K at a time */
//read the file ...
FOR li_counter = 1 to li_loops
ll_bytes_read = FileRead(li_filenum,lb_our_blob )
lb_tot_b = lb_tot_b + lb_our_blob
ll_new_pos = ll_new_pos + ll_bytes_read
FileSeek(li_filenum,ll_new_pos,FROMBEGINNING!)
NEXT
FileClose(li_filenum)
//lb_tot_b now contains the contents of the file. You can do a SetPicture with
//it, if the file contained a Bitmap, or use the String function to convert it
//to text if it was a textual file.
//If it was a picture...
SetPicture(p_my_picture, lb_tot_b)
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 Watcom, Image in SQL Serer, long raw in Oracle, etc..)
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 Watcom) 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 373. Having all of that the syntax needed to update the blob to the database is:
UPDATEBLOB blob_table SET blob_col = :lb_tot_b WHERE blob_id = 373 USING SQLCA;
IF sqlca.sqlcode < > 0 then
messagebox("UPDATEBLOB failed", 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
SELECTBLOB blob_col INTO :lb_blob_var FROM blob_test WHERE blob_id = 423 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 on pages 10 -12 of the Function Reference provided with PowerBuilder V3.
