Storing archives in a Progress database

tsmit

New Member
In the application we are building at the moment we need to store large parts of infomation. I.e large flat ASCI-files,
sometimes up to 700 KB each. So, to reduce the size of these files before we store it, we archived it using winzip or ARJ and stored it in a characterfield in our database. But after restoring it and trying to unzip the thing the archive is corrupted. When we compare the original archive with the one that has been stored in the database it seems that some values (NULL and Low) are transformed in spaces.
Does someone has a clue to our problem.

Thanks in advance.

Ted Smit
HI-Systems
The Netherlands
tsmit@hi-systems.nl
 

phirst

New Member
Hi Ted,
You would be better off using a RAW field to store your zipped file. A character field is not designed to stock binary data, this is why the nulls are converted to spaces.
Bye,
phirst.
 

tsmit

New Member
How to store binairy data in a raw db-var

Hallo Phirst,

Thanks for your reply. But your answer rises a new question to me. And that is, how do I do that storing binairy data in a raw variable. Bit by bit or ???
Do you have a example course for me where something similair happens.

Thanks

Ted Smit
 

Chris Kelleher

Administrator
Staff member
Ted-

The problem is that Progress uses the NULL character as an internal field seperator, so you can't store a NULL value in a normal database field. There is a great Progress knowledgebase entry on storing BLOB (binary large objects) inside of a Progress database here: http://www.progress.com/services/support/cgi-bin/techweb-kbase.cgi/webkb.html?kbid=17093

Code:
INTRODUCTION:
=============
This knowledgebase entry describes how to store and retrieve binary
data in a Progress database.

This information only applies to Progress versions 8.2A and higher.


WHY YOU NEED TO KNOW THIS:
==========================
You need to know this if you have the need to store and retrieve
binary data from a Progress database (obviously).


CODE ASSUMPTIONS:
=================
In the following code examples I am making the following
assumptions:

-  Only bitmaps (*.BMP) will be stored in the database.

-  The bitmaps will be broken out into 16K (16,384 byte) chunks.

-  A bitmap will be added to the database by clicking on a push
   button and selecting a bitmap from the standard MS-Windows
   file open dialog box.

-  A bitmap will be retrieved and displayed by clicking on a row in
   a browse widget (i.e. the VALUE-CHANGED event).

-  There are only three (3) widgets on the window.  They are as
   follows:

   1)  A push button which is used to add a bitmap to the database.

   2)  A browse widget which displays a list of available bitmaps
       which are stored in the database and by which the user can
       select an image to be retrieved and displayed.

   3)  An image widget which is used to display the retrieved bitmaps.

-  The widgets are named as follows:

   1)  PBN-AddBitmap        /* push button used to add bitmap    */

   2)  BRW-AvailableBitmaps /* browse listing available bitmaps  */

   3)  IMG-Display          /* image widget that displays bitmap */


DATABASE STRUCTURE:
===================
The database is made up of the following fields:

-  ImageName      CHARACTER, FORMAT "X(60)"
-  ImageSequence  INTEGER
-  ImageData      RAW

I also have an index defined containing ImageName and ImageSequence
as fields in the index.  The index is defined as unique and primary.


CODE TO STORE A BITMAP IN THE DATABASE:
=======================================
ON CHOOSE OF PBN-AddBitmap IN FRAME {&FRAME-NAME}
DO:
    DEFINE VARIABLE chrFileName  AS CHARACTER NO-UNDO.
    DEFINE VARIABLE logResult    AS LOGICAL   NO-UNDO.
    DEFINE VARIABLE rawImageData AS RAW       NO-UNDO.
    DEFINE VARIABLE intSequence  AS INTEGER   NO-UNDO INITIAL 1.

    SYSTEM-DIALOG GET-FILE chrFileName
        TITLE "Please Select a Bitmap to Add to the Database"
        FILTERS "Bitmap Files (*.bmp)" "*.bmp"
        MUST-EXIST RETURN-TO-START-DIR UPDATE logResult.

    IF logResult = False THEN
        RETURN NO-APPLY.

    ASSIGN logResult            = SESSION:SET-WAIT-STATE("GENERAL")
           LENGTH(rawImageData) = 16384.

    INPUT FROM VALUE(chrFileName) BINARY NO-MAP NO-CONVERT.

    REPEAT:
        IMPORT UNFORMATTED rawImageData.
        CREATE IMAGES.
        ASSIGN IMAGES.ImageName     = chrFileName
               IMAGES.ImageSequence = intSequence
               IMAGES.ImageData     = rawImageData
               intSequence          = intSequence + 1.
    END.

    INPUT CLOSE.

    ASSIGN logResult = SESSION:SET-WAIT-STATE("").

    OPEN QUERY BRW-AvailableBitmaps
        FOR EACH IMAGES
            WHERE IMAGES.IMAGE-SEQUENCE = 1 NO-LOCK.
END.


CODE TO RETRIEVE A BITMAP FROM THE DATABASE:
============================================
ON VALUE-CHANGED OF BRW-AvailableBitmaps IN FRAME {&FRAME-NAME}
DO:
    DEFINE VARIABLE chrImageName AS CHARACTER NO-UNDO.
    DEFINE VARIABLE logResult    AS LOGICAL   NO-UNDO.

    ASSIGN logResult    = SESSION:SET-WAIT-STATE("GENERAL")
           chrImageName = IMAGES.ImageName.

    OUTPUT TO C:\TEMPBLOB.BMP NO-MAP NO-CONVERT BINARY.

    FOR EACH IMAGE NO-LOCK WHERE IMAGE.ImageName = chrImageName:
        PUT CONTROL IMAGE.ImageData.
    END.

    OUTPUT CLOSE.

    ASSIGN logResult = SESSION:SET-WAIT-STATE("")
           logResult = IMG-Display:LOAD-IMAGE("")
           logResult = IMG-Display:LOAD-IMAGE("C:\TEMPBLOB.BMP").
END.

Hope this helps,
-Chris
 

phirst

New Member
Hello again,
I forgot to say that the RAW data-type is limited to 32K, so, above this size you will have to cut the file up.
This is trick shown in the KB entry (progresstalk reply).
Cheers,
phirst.
 

heindhoven

New Member
Hello,

I've been searching for long times for finding this sort of information. I'm trying to get peoples pictures out of Progress into Microsoft SQL.
I managed to insert the information in SQL tables, but as stated in this discussion, its broken into 16k chunks.
So now i have a table with, for each user picture, several lines with varbinary data, 16k per line. I also have the line numbers. So i know which lines belongs to which users and what is the order of the lines.
Is there any way i can concatenate the lines to 1 varbinary value? I tried a "simple" concatenation query, which just puts the values together (per user picture) in the right order, but it doesn't work. It seems each line has some kind of header (and footer ?) information, which repeatys on each line. So probably, when a picture consist of for example, 7 lines, i have to remove the "header" information from lines 2 -7, but keep it on the first line.
Anyhow, to make my question clear: is there a way to get pictures from Progress to Microsoft SQL?

p.s: the link http://www.progress.com/services/sup...tml?kbid=17093 is not working anymore.

Regards, Hennie
 

TomBascom

Curmudgeon
The technique described is 10+ years old and dates from the dark ages. Digging it up is useful for dealing with legacy conversions but if this code is still running what you really want to do is to upgrade to a modern and supported version of Progress, like OpenEdge 10.2, which supports LOBs. Then you can store your image data in a single field. You would also have vastly improved SQL-92 support.
 
Top