Question How to read file with content from blob field?

Hello everyone,

I am using OpenEdge Release: 10.2B07 on Windows 7, 64-bit System. I have a Server with Linux (64-bit), on which a Progress database (the same Version of Progress) is running. For the last couple of days I have been trying without any success the following:
1) In the database there is a field of BLOB datatype with a dump of some other table in it; I copy the content of this field to a file on my Computer using COPY-LOB FROM OBJECT <field-name> TO FILE <file-name> NO-CONVERT;
2) I define a temp-table like the table, a dump of which I have saved in the BLOB-field (from step 1 );
3) I import the content of the created file (step 1 ) to the temp-table (step 2 ) using:
INPUT STREAM <stream-name> FROM VALUE(<file-name>) BINARY NO-ECHO.
REPEAT:
CREATE <temp-table name>.
IMPORT STREAM <stream-name> <temp-table name>.
END.
INPUT STREAM <stream-name> CLOSE.
4) I display the contents of the temp-table using:
FOR EACH <temp-table name> NO-LOCK:
DISPLAY <temp-table name> WITH 1 COLUMNS WIDTH 100.
END.
but here I get some coded data (probably in binary code) instead of the content that was dumped and saved in the BLOB-field (from step 1 ).
I thought that it might be because of different operating systems, but "binary", if I am not mistaken, should be platform independent. I also tried the same with a database running on Windows XP 32-bit, but I have not been successful neither, although I am not sure, whether "32-bit" could be the reason.

At the moment I do not have any idea, what I am doing wrong, so any help is appreciated!

How was the blob created?

Also, what does the file look like that you are dumping with COPY-LOB? Can you attach an example?

Hi, to create the blob following was done:
1) dump table to a file
OUTPUT STREAM <stream-name> TO VALUE(<file-name>) BINARY.
REPEAT PRESELECT EACH <table-name>:
FIND NEXT <table-name>.
EXPORT STREAM <stream-name> <table-name>.
END.
OUTPUT STREAM <stream-name> CLOSE.

2) copy file content to a variable of MEMPTR datatype
FILE-INFO:FILE-NAME = <file-name>.
SET-SIZE(<memptr-variable>) = FILE-INFO:FILE-SIZE.
COPY-LOB FROM FILE <file-name> TO OBJECT <memptr-variable> NO-CONVERT NO-ERROR.
3) copy <memptr-variable> to a BLOB
COPY-LOB FROM <memptr-variable> TO <blob-field> NO-ERROR.

Unfortunately, I cannot attach an example of dump file, because there can be some business relevant data.
I also tried to run all the process: dump table to a file, copy it to a blob field, copy blob field to another file, read from file to a temp-table - on my computer locally and it worked successfully.
Thanks for the quick answer!

Out of interest, why are you outputting as binary? Is it to do with different codepages?

Well, this code was programmed before me and I am just trying to use it. However, I think that this BINARY option is used because of different platforms (client vs, database), the codepage should be the same.

The binary thing is probably completely unnecessary. But no much you can do about it.

The problem is that my part begins with copying from blob to a file, so I do not have any influence on this BINARY option.

Binary data is plattform dependent. While you can move binary data between plattforms, you will definately run into issues when using it on a different plattform than which it was created on. One of the reasons ( amongst others ) is the Endianess ( http://en.wikipedia.org/wiki/Endianness ).

Say, if the binary data in you blob field is created on the Linux machine then you won't be able to use it on your Windows machine and vice versa. On a wild guess I would say that the binary data in you blob field was created on the server - maybe by a batch process. If that's the case then you can only use ( re-construct it ) on the Linux plattform.

Heavy Regards, RealHeavyDude.

I see the problem with different platforms (Linux and Windows), but is there the same issue with different architectures (Windows 32-bit and 64-bit). When I copy blob from a database on a computer with Windows XP 32-bit to my computer with Windows 7 64-bit I have the same problem.

Binary might, or might not, be platform independent.

It just means that the data is not text. It is some sort of "machine readable" format. Sometimes that is a portable format -- like with an OpenEdge "binary dump". Sometimes not - like an executable image.

In your case I think it more likely that the issue is that the file was created with EXPORT and you are trying to read it with COPY-LOB. It would probably work just fine if you used COPY-LOB on both ends of the exchange.

Or if you use IMPORT to read what was EXPORTed:


"No-convert" is probably important ( NO-CONVERT specifies that no character conversions occur between the external file and memory. By default, the INPUT FROM statement converts characters from the -cpstream code page to the -cpinternal code page). The code above imports a memptr -- not a whole record. There are some important bits in the NOTES for IMPORT that talk about how importing BLOBS works:

• When importing records that contain a BLOB or CLOB field, the AVM uses the value stored in the BLOB or CLOB field of the exported record to determine whether or not the exported record has an associated object data file to import. If the BLOB or CLOB field in the exported record contains the Unknown value (?), the AVM stores the Unknown value (?) in the BLOB or CLOB field of the new or updated record. If the BLOB or CLOB field in the exported record contains a filename, the AVM imports the associated object data. If an updated record already has object data associated with it, the AVM deletes that object data before importing the new object data.
  
• The IMPORT statement reads large object data files from the directory specified as the input data source in the INPUT FROM statement, by default. You can use the LOB-DIR option on the INPUT FROM statement to specify the directory from which the IMPORT statement reads BLOB and CLOB data files.
  
  
• In the MEMPTR version of the IMPORT statement, the MEMPTR must be pre-allocated to the size needed for reading.
  
  
• When importing a CLOB or LONGCHAR variable, the AVM uses the code page information in the exported file header to determine the variable’s code page. However, the COPY-LOB statement is recommended for Unicode CLOB or LONGCHAR variables.

Hello Tom, thank you for your answer. Unfortunately, it does not help me. During the "dump process" both statements are used: first EXPORT (with options BINARY and NO-CONVERT), then COPY-LOB (from created file to a BLOB field with option NO-CONVERT). What I am using to get the dumped data back is first COPY-LOB (from BLOB field to a file with option NO-CONVERT), then INPUT FROM (file with options BINARY, NO-CONVERT and NO-ECHO) and IMPORT (to a temp-table, defined with option LIKE-SEQUENTIAL <table, which was dumped>). Besides in my case I am not trying to export records with blob field to a file and then import the same records from the file to a temp-table; I am trying to get dump data (records) from one BLOB field. My goal is to extract dump data from a BLOB field, I have no intent to change the dump process.
Sorry, if I expressed myself ambiguously before. I hope you can follow me now.

The code that you show above does not match the statements you make in your latest post.

You might get more useful answers if you show the code that you are actually using.

Hi, unfortunately, I do not see what in the code I posted above does not match with the statements I made. May be you were misled by the order of posted code pieces.
There are quasi two parts:
I Dump process
1) dump table to a file
OUTPUT STREAM <stream-name> TO VALUE(<file-name>) BINARY.
REPEAT PRESELECT EACH <table-name>:
FIND NEXT <table-name>.
EXPORT STREAM <stream-name> <table-name>.
END.
OUTPUT STREAM <stream-name> CLOSE.
2) copy file content to a variable of MEMPTR datatype
FILE-INFO:FILE-NAME = <file-name>.
SET-SIZE(<memptr-variable>) = FILE-INFO:FILE-SIZE.
COPY-LOB FROM FILE <file-name> TO OBJECT <memptr-variable> NO-CONVERT NO-ERROR.
3) copy <memptr-variable> to a BLOB
COPY-LOB FROM <memptr-variable> TO <blob-field> NO-ERROR.

II Extract dump data (this is where I get coded data)
1) In the database there is a field of BLOB datatype with a dump of some other table in it (described in part I);
I copy the content of this field to a file on my Computer using
COPY-LOB FROM OBJECT <field-name> TO FILE <file- name> NO-CONVERT;
2) I define a temp-table like the table, a dump of which I have saved in the BLOB-field (from step 1 );
3) I import the content of the created file (step 1 ) to the temp-table (step 2 ) using:
INPUT STREAM <stream-name> FROM VALUE(<file-name>) BINARY NO-ECHO.
REPEAT:
CREATE <temp-table name>.
IMPORT STREAM <stream-name> <temp-table name>.
END.
INPUT STREAM <stream-name> CLOSE.
4) I display the contents of the temp-table using:
FOR EACH <temp-table name> NO-LOCK:
DISPLAY <temp-table name> WITH 1 COLUMNS WIDTH 100.
END.

That is much clearer. Although it would be a whole lot easier to read if you used CODE tags.

I took a copy of the sports2000 database that I have laying around and added a BLOB field named myBLOB to a table I had previously inserted called "sysConfig".

Dump:


Load:


It works perfectly.

Is it possible that your dump session and your load session have different values for some of the -cp* startup parameters?

Is it possible that the MEMPTR/BLOB you are working with is larger than 1GB?

Is the field that the BLOB is being stored in a BLOB? Or is it CLOB?

Hi, there is one difference in the code you posted:
instead of
"output to value( "customer.d" )."
We use
"output to value( "customer.d" ) BINARY."
Answering your questions:
- my dump and load sessions use the same -cp* startup parameters;
- the MEMPTR/BLOB I am working with is not bigger then 1 Mb (at least for my testing);
- the BLOB object is being stored in a field of BLOB datatype.

Fixed it. Still works.

Was the BLOB created on a different machine to the one you're on now? What differences are there in Platform? OS, bitness, etc.

Yes, the BLOB was created on a different machine. I tried two variants:
- the BLOB created on Windows XP 32-bit;
- the BLOB created on Linux 64-bit.
On both is Progress 10.2B07 is installed, on both is the same -cp* startup parameter is used.
My computer has Windows 7 64-bit with Progress 10.2B07 and I use the same -cp* startup parameter. With both variants it did not work.