Wednesday, December 10, 2008

Reading and writing a blob in chunks

At least with Oracle using the DBMS_LOB package. The issue with using SELECTBLOB and UPDATEBLOB is that you have to store the entire blob in memory. That could create an issue if you're trying to process extremely large amounts of data. Fortunately, there are options to pass the data in smaller, more bite size chunks.



So, let's create a package that uses DBMS_LOB to read and write a database blob in 32,000 character chunks. Note that we will be passing data in for write in a LONG RAW rather than a blob. I wasn't able to get Oracle to use a blob that PB was passing in, although PB can use a BLOB that Oracle passes out.

Package

CREATE OR REPLACE PACKAGE pkg_blob IS
PROCEDURE NEW(p_id NUMBER);
PROCEDURE OPEN(p_id NUMBER);
FUNCTION READ(p_data OUT BLOB) RETURN NUMBER;
FUNCTION WRITE(p_data LONG RAW) RETURN NUMBER;
END pkg_blob;
/
CREATE OR REPLACE PACKAGE BODY pkg_blob IS
src_lob BLOB;
c_amt CONSTANT BINARY_INTEGER := 32000; -- No larger than 32,512 because that's all PB can send
pos INTEGER;
PROCEDURE NEW(p_id NUMBER) IS
BEGIN
DELETE FROM blob_test
WHERE id = p_id;
INSERT INTO blob_test
(id,
data)
VALUES
(p_id,
rawtohex(' '));
COMMIT;
END;
PROCEDURE OPEN(p_id NUMBER) IS
BEGIN
SELECT data
INTO src_lob
FROM blob_test
WHERE id = p_id
FOR UPDATE;
--reset pos whenever we reselect the blob
pos := 1;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000,
SQLERRM);
END;
FUNCTION READ(p_data OUT BLOB) RETURN NUMBER IS
v_amt NUMBER;
BEGIN
v_amt := c_amt;
dbms_lob.READ(src_lob,
v_amt,
pos,
p_data);
pos := pos + v_amt;
RETURN v_amt;
EXCEPTION
WHEN no_data_found THEN
RETURN 0;
WHEN OTHERS THEN
raise_application_error(-20000,
SQLERRM);
END READ;
FUNCTION WRITE(p_data LONG RAW) RETURN NUMBER IS
v_amt NUMBER;
BEGIN
v_amt := LENGTH ( p_data ) / 2 ;
DBMS_LOB.WRITE ( src_lob, v_amt, pos, p_data ) ;
pos := pos + v_amt;
RETURN v_amt;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000,
SQLERRM);
END WRITE;
END pkg_blob;
/


Then we declare some RPCFUNC calls on a user object of type transaction:

RPCFUNC delcarations

subroutine NEW(double P_ID) RPCFUNC ALIAS FOR "PKG_BLOB.NEW"
subroutine OPEN(double P_ID) RPCFUNC ALIAS FOR "PKG_BLOB.OPEN"
function double READ( ref blob data ) RPCFUNC ALIAS FOR "PKG_BLOB.READ"
function double WRITE( blob data ) RPCFUNC ALIAS FOR "PKG_BLOB.WRITE"


Writing the blob to the database

int li_file, li_rc
long ll_size, ll_totalsize
blob lb_data
string ls_pathname, ls_filename

lb_data = Blob ( Space ( il_buflen ) )

li_rc = GetFileOpenName ( "Open File", ls_pathname, ls_filename )
IF li_rc 1 THEN Return

li_file = FileOpen ( ls_pathname, StreamMode!, Read! )

SQLCA.of_connect()
SQLCA.new( 1 )
SQLCA.open( 1 )

ll_size = FileReadEx ( li_file, lb_data, il_buflen )
DO WHILE ll_size > 0
ll_totalsize += ll_size
li_rc = SQLCA.write( lb_data )
st_1.text = "Bytes Read: " + String ( ll_totalsize )
ll_size = FileReadEx ( li_file, lb_data, il_buflen )
LOOP

FileClose ( li_file )
SQLCA.of_commit()
SQLCA.of_disconnect()


Reading the blob back from the database

int li_file, li_rc
long ll_size, ll_totalsize
blob lb_data
string ls_pathname, ls_filename

lb_data = Blob ( Space ( il_buflen ) )

li_rc = GetFileSaveName ( "Save File", ls_pathname, ls_filename )
IF li_rc 1 THEN Return

li_file = FileOpen ( ls_pathname, StreamMode!, Write!, LockReadWrite!, Replace! )
SQLCA.of_connect()
SQLCA.open( 1 )
ll_size = SQLCA.read( lb_data )
DO WHILE ll_size > 0
ll_totalsize += ll_size
FileWriteEx ( li_file, lb_data )
st_1.Text = "Bytes Written: " + String ( ll_totalsize )
ll_size = SQLCA.read( lb_data )
LOOP
FileClose ( li_file )
SQLCA.of_disconnect()

14 comments:

J Buell said...

Bruce, are your setting the value of the instance var il_buflen to 32000?

brucearmstrong said...

Yes. Guess I forgot to include that in the sample. It has to match whatever you set the value to in the package.

J Buell said...

Thanks Bruce for the quick reply. This post was outstanding and extremely helpful. I searched your blog but couldn't find anything on the transaction object you are using. What are the new(i), open(i) and write() methods doing? Do you extend the PFC n_tr object?

brucearmstrong said...

The Open, New, Read and Write methods in the package are declared as local external functions in the transaction object. If you then use that class for SQLCA, those become methods of SQLCA. As a result, I'm calling those stored procedures through those methods.

J Buell said...

ah, D'Oh. Got it. Thanks again.

kucluk said...

i want to write and read image into sybase sql anywhere but always failure, can you explaine script in pb 9 how to solve that problems?

thanks anyway

Johnn said...

Hello,

This way to read and write blobs is very interesting.

Do you know a way to read a blob that contains text and put its content to a rich text box or similar control?

Thank you very much for your help...

brucearmstrong said...

What format is the text in? You can always just do a Paste or PasteRTF on the edit control if it's text or RTF. Or an InsertDocument if it's another format (DOC, HTML).

I still use the RichTextControl rather than the rich text edit control in the DataWindow because of the size limitations on the field in the latter.

deny said...

i have confused using the code. i new in power builder. can you give the sample project(pbt and pbl) of this blob.
thanks.

Alfredo said...

Mi problema es el siguiente, quiero leer la huella dactilar y guardar la informacion en un la BD para su futura identificacion.

Utilizo SQL Server 2005, estoy programando en Power Builder 11.2.

Ya tengo resuelto la extracion de informacion de la huella y la comparacion entre 2 lecturas, mi problema es guardar esa informacion en la BD.

La informacion de la huella la tengo en un arreglo Blob de 10000 y ese es el que ocupo guardar en la BD y despues obtenerlo para realizar la comparacion.

En este ejemplo que muestran esta para Oracle y una variable Blob, y lo que ocupo es hacerlo con un arreglo de Blob y en SQL Server 2005.

Gracias de antemano

brucearmstrong said...

Unfortunately, the technique I'm showing is very specific to Oracle. It really is only to address limiting the memory impact of handling very large blobs. That doesn't appear to be an issue in your case.

Byron Salas said...

Mr Armstrong,

I liked your "Reading and writing a blob in chunks" article, i have a question.
If i have in a database a blob that contains a text file (txt extension), how is possible read it and inserted in a Rich text control?

Thanks and regards

Unknown said...

Bruce, Have you every converted this code to work with APPEON? I tried to use it the way it is and it does not work. I like that it chucks up the data so I can display what has been read back to the user. I have to deal with 400+ meg files. (Using Oracle 12c, PB12.6 and APPEON 2015)

Regards,
Ron

Bruce Armstrong said...

Sorry, I haven't. You might want to post over on Appeon's forums and see if someone there has:

https://community.appeon.com/home