| View previous topic :: View next topic |
| Author |
Message |
bauer
Intermediate
Joined: 10 Oct 2003
Posts: 315
Topics: 49
Location: Germany
|
 Posted: Thu May 19, 2011 3:23 pm Post subject: Acessing part of a BLOB column |
 |
|
Hi,
I have a DB2 z/OS table with a BLOB column. I like to access only some bytes of the BLOB column, containing some binary data.
Lets say: Get only bytes 100 to 130 from the BLOB Column for a specific row? How to code this with embedded SQL in a application program?
The redbook LOBs with DB2 for z/OS: Stronger and Faster SG24-7270-00 shows only some samples with CLOB and using POSSTR function.
How to add a value X to a BLOB Locator to skip the first bytes and read only bytes 100 to 130 for example?
Can anybody pls. provide me a hint for the correct syntax ?
regards,
bauer |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Thu May 19, 2011 3:50 pm Post subject: |
|
|
bauer,
How about HEX function with SUBSTR and cast?
Untested sql
| Code: |
SELECT CAST(SUBSTR(HEX(blob_col), 100, 30) AS DECIMAL)
FROM TABLE
|
Kolusu |
|
| Back to top |
|
|
bauer
Intermediate
Joined: 10 Oct 2003
Posts: 315
Topics: 49
Location: Germany
|
| Posted: Thu May 19, 2011 4:19 pm Post subject: |
|
|
kolusu,
thank you for your quick replay.
Your sql results with DSNT408I SQLCODE = -171, ERROR: THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 1
OF HEX IS INVALID.
Hex and BLOB columntype are not compatible. Well, i think i should do a little bit more userguide reading about locators ......
regards,
bauer |
|
| Back to top |
|
|
CZerfas
Intermediate
Joined: 31 Jan 2003
Posts: 211
Topics: 8
|
| Posted: Fri May 20, 2011 8:10 am Post subject: |
|
|
I think that hex values are not DECIMAL. Try CHARACTER instead.
regards
Christian |
|
| Back to top |
|
|
bauer
Intermediate
Joined: 10 Oct 2003
Posts: 315
Topics: 49
Location: Germany
|
| Posted: Mon May 23, 2011 2:50 am Post subject: Solution |
|
|
After reading the IBM manuals and some other samples: The solution using PL/1 program is as follows.
Assume we have table with key column BLKEY as BIN FIXED(31) and datacolumn BL2GB as BLOB(2 G), we want to read bytes 5 to 10, bytes 5 to 10 specify a valid, printable, string expression and BLKEY = 3 exists.
The procedure CheckSQLError just checks the SQL code and abends is SQLCODE not equals 0.
Advantage of this solution: Locators are used, so not the complete blob is transferred to the application program.
| Code: |
GetData:
PROC ;
DCL LengthTotal BIN FIXED(31) AUTO NOINIT ;
DCL DataTotal SQL TYPE IS BLOB_Locator ;
DCL DataPartial SQL TYPE IS BLOB (1 K);
DCL MyString CHAR(6) BASED(PTRADD(ADDR(DataPartial),4));
EXEC SQL SELECT LENGTH(BL2GB)
,BL2GB
INTO :LengthTotal
,:DataTotal
FROM MyTable
WHERE BLKEY = 3 ;
CALL CheckSQLError('Select');
EXEC SQL SET :DataPartial = SUBSTR(:DataTotal,5,6);
CALL CheckSQLError('Select');
PUT SKIP EDIT( 'TotalLength = ' || TRIM(CHAR(LengthTotal))
|| ', '
|| 'MyString = ' || MyString
)(A);
EXEC SQL FREE LOCATOR :DataTotal;
CALL CheckSQLError('FreeLocator');
END;
|
May be somebody else has similar question and this helps ....
regards,
bauer |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
