|
View previous topic :: View next topic
|
| Author |
Message |
Tone
New User
Joined: 01 Oct 2008
Posts: 12
Location: India
|
|
|
|
Hi all,
Requirement:
I need to transfer a picture (.bmp file) across MQ to DB2 tables in mainframe. For this the binaryvalue of the picture will be read using a cobol program from MQ and stored as a BLOB in DB2 table. The DB2 table has signature-BLOB field and ROW ID.
How this is working:
• The signature field that is of datatype BLOB, just contains the information of the field themselves , say whether it is a BLOB or CLOB.
•The BLOB field in this table (base table) is acting just like a pointer, ie. The actual data is in another table called as auxillary table.
•So inorder to uniquely connect each base table records to auxilary table we have some sort of address stored in ROWID datatype field.
How can we enter a record into the base table containing the BLOB datatype?
Considering the fact that base table consists only a pointer to actual data, if I have the binary value of a picture file, which field will get that in the base table?
As such we won't be able to directly edit the auxillary table.
Please Help! |
|
| Back to top |
|
 |
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008
Posts: 107
Location: New York
|
|
|
|
Hi
Define a Working Storage variable of size the same as the Blob data and you can use this variable in the INSERT query.
For example, the table column is defined as below
| Code: |
IN_BLOB BLOB(2097152) NOT NULL.
|
Declare a Working Stroage variable.
| Code: |
WS-IN-BLOB USAGE IS SQL TYPE IS BLOB(2097152).
|
After compiling this program, if we refer the Compile listing of this program, the above declared variable would be split as given below
| Code: |
01 WS-IN-BLOB.
02 WS-IN-BLOB-LENGTH PIC S9(9) COMP.
02 WS-IN-BLOB-DATA.
49 FILLER PIC X(32767).
--
--
--
--
49 FILLER PIC X(64).
|
So once we get the input to the COBOL program
Find out the length of the data, move the length to WS-IN-BLOB-LENGTH and move the data to the WS-IN-BLOB-DATA. Use the variable WS-IN-BLOB directly in the INSERT query against the BLOB Column.
| Code: |
INSERT INTO <TABLE NAME>
(IN_BLOB)
VALUES
(:WS-IN-BLOB)
|
Hope this helps.. |
|
| Back to top |
|
|
anand_smail
New User
.jpg)
Joined: 05 Jun 2005
Posts: 13
|
|
|
|
Do you have a sample program, in my case i have BLOB data in a file with multiple rows. eg: my file have 15 lines of each and length of file is 32000.
Please help. |
|
| Back to top |
|
|
Ajay Baghel
Active User
.jpg)
Joined: 25 Apr 2007
Posts: 206
Location: Bangalore
|
|
|
|
| this info helps. |
|
| Back to top |
|
|
Mr Swann
New User
Joined: 09 Jul 2020
Posts: 6
Location: France
|
|
|
|
| Suresh Ponnusamy wrote: |
Hi
Find out the length of the data, move the length to WS-IN-BLOB-LENGTH and move the data to the WS-IN-BLOB-DATA. Use the variable WS-IN-BLOB directly in the INSERT query against the BLOB Column.
| Code: |
INSERT INTO <TABLE NAME>
(IN_BLOB)
VALUES
(:WS-IN-BLOB)
|
Hope this helps.. |
Thank you @Suresh Ponnusamy
it was very helpfull for me !
I was facing various issues, but all solved by now.
My program is reading an unload from a DB2 of a table including BLOB collumns
and will insert them into another DB2
The issues
the DCLGEN does no match to the unload files ==> I have written a record description
( if anyone as a better solution ...)
the DCLGEN is not correct for the Insert ====>I have written a record description
the DCLGEN
| Code: |
EXEC SQL DECLARE MYBLOB TABLE
(
CODREF CHAR(20) NOT NULL,
FLGPREBLOB1 CHAR(1) NOT NULL,
BLOB1 BLOB(20480) NOT NULL,
FLGPREBLOB2 CHAR(1) NOT NULL,
BLOB2 BLOB(102480) NOT NULL
)
END-EXEC
|
and the cobol part
| Code: |
01 DCLMYBLOB.
10 CODREF PIC X(20) .
10 FLGPREBLOB1 PIC X(1).
10 BLOB1 USAGE SQL TYPE IS BLOB LOCATOR.
10 FLGPREBLOB2 PIC X(1).)
10 BLOB2 USAGE SQL TYPE IS BLOB-LOCATOR.
|
the record defining the unload file is
| Code: |
01 UNL-MYBLOB.
10 CODREF PIC X(20) .
10 FLGPREBLOB1 PIC X(1) .
10 BLOB1 .
49 BLOB1-LENGTH PIC S9(9) COMP-5.
49 BLOB1-DATA PIC X(5625).
10 FLGPREBLOB2 PIC X(1).
10 BLOB2 .
49 BLOB2-LENGTH PIC S9(9) COMP-5.
49 BLOB2-DATA PIC X(9000).
|
for the figures 5625 and 9000, I have looked into the unload file ...
the record for the insert is
| Code: |
01 INS-MYBLOB.
10 CODREF PIC X(20) .
10 FLGPREBLOB1 PIC X(1) .
10 BLOB1 USAGE IS SQL
TYPE is BLOB(20480).
10 FLGPREBLOB2 PIC X(1).
10 BLOB2 USAGE IS SQL
TYPE is BLOB(102400)
|
the figures form the size of BLOBS are comming from the DCLGEN |
|
| Back to top |
|
|
Mr Swann
New User
Joined: 09 Jul 2020
Posts: 6
Location: France
|
|
|
|
So I read the input FILE
| Code: |
MOVE CORRESPONDING UNL-MYBLOB
to INS-MY-BLOB |
and I can do the INSERT
| Code: |
EXEC SQL
INSERT INTO MYBLOB
(
CODREF ,
FLGPREBLOB1,
BLOB1 ,
FLGPREBLOB2 ,
BLOB2
)
VALUES
(
:INS-MY-BLOB.CODREF ,
:INS-MY-BLOB.FLGPREBLOB1,
:INS-MY-BLOB.BLOB1 ,
:INS-MY-BLOB.FLGPREBLOB2 ,
:INS-MY-BLOB.BLOB2
) |
and it works well ..
Regards |
|
| Back to top |
|
|
|
|
