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