Reading and Writing blobs in chunks to Oracle Database.

Ron Calder's picture
1
0
-1

Has anyone converted Bruce Armrstrong's code for reading and writing in chunks to an Oracle database to work with APPEON?   I have the need to read files larger than 100meg and store it in a database.  The average file size is 350meg.  I have it working in PB 12.6 & Oracle 12c but it does not work when deployed to APPEON WEB Server.  Below is the link to Bruce's code.

http://www.brucearmstrong.org/2008/12/reading-and-writing-blob-in-chunks.html

Thank you in advance,

Ron

Question Tags: 

Answers

Appeon Support Team answered Reading and Writing blobs in chunks to Oracle Database.

1
0
-1

Hi Ron,

The data type 'blob' can only be used in SELECTBLOB and UPDATEBLOB SQL statements. But it is not working as parameter of the function read/write in your Oracle package.

For more details please refer to the help documentation:

https://www.appeon.com/support/documents/appeon_online_help/2015/features_help_for_appeon_web/ch07s01.html

 

Thanks,

Appeon Support Team

Ron Calder's picture

I have the upload side working?  See Package and Package Body along with PB/Appeon Upload Code that works? It is only the download side that not does not work.

--DB Package
CREATE OR REPLACE PACKAGE pkg_blob IS
  PROCEDURE NEWB(p_quote_id NUMBER, p_file_no number);
  PROCEDURE OPENB(p_quote_id NUMBER, p_file_no number);
  FUNCTION READB(p_data out blob) RETURN NUMBER;
  FUNCTION WRITEB(p_data IN blob) RETURN NUMBER;
END pkg_blob;
--DB Body
CREATE OR REPLACE PACKAGE BODY pkg_blob IS
  src_lob BLOB;
  c_amt CONSTANT BINARY_INTEGER := 32765; -- No larger than 32,512 because that's all PB can send
  pos number; --INTEGER;
 PROCEDURE NEWB(p_quote_id NUMBER, p_file_no number) IS
  BEGIN
    DELETE FROM quote_files
     WHERE qa_quote_id = p_quote_id
       and qa_file_number = p_file_no;
     
    INSERT INTO quote_files
      (qa_quote_id, qa_file_number, qa_file)
    VALUES
      (p_quote_id, p_file_no, rawtohex(' '));
    COMMIT;
  END;
  PROCEDURE OPENB(p_quote_id NUMBER, p_file_no number) IS
  BEGIN
    SELECT qa_file
      INTO src_lob
      FROM quote_files
     WHERE qa_quote_id = p_quote_id
       and qa_file_number = p_file_no
       FOR UPDATE;
    --reset pos whenever we reselect the blob
    pos := 1;
  EXCEPTION
    WHEN OTHERS THEN
      raise_application_error(-20000, SQLERRM);
  END;
  FUNCTION READB(p_data out blob) RETURN NUMBER IS
    v_amt integer; --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 READB;
  FUNCTION WRITEB(p_data in blob) 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 WRITEB;
END pkg_blob;
//Upload File PB Code working with APPEOPN!
int          li_file, li_rc
int ll_size
longlong ll_totalsize
blob       lb_data
string     ls_pathname, ls_filename
longlong il_buflen = 32765
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!,  LockRead!  )

               
   SQLCA.newblob( 1, 1 )  // (QA_Quote_id, QA_File_number) Change to vairable data once tested.
   SQLCA.openblob( 1, 1 )   // (QA_Quote_id, QA_File_number)  Change to vairable data once tested.
    ll_size =               FileRead ( li_file, lb_data)

             
  DO WHILE ll_size > 0
      ll_totalsize += ll_size
      li_rc = SQLCA.writeblob( lb_data )
      yield()
      em_fl.text = String ( ll_totalsize )
      ll_size = FileRead ( li_file, lb_data) //, il_buflen )
  LOOP
     
FileClose ( li_file )
commit;
Ron Calder's picture

I have the upload side working?  See Package and Package Body along with PB/Appeon Upload Code that works? It is only the download side that not does not work.

--DB Package
CREATE OR REPLACE PACKAGE pkg_blob IS
  PROCEDURE NEWB(p_quote_id NUMBER, p_file_no number);
  PROCEDURE OPENB(p_quote_id NUMBER, p_file_no number);
  FUNCTION READB(p_data out blob) RETURN NUMBER;
  FUNCTION WRITEB(p_data IN blob) RETURN NUMBER;
END pkg_blob;
--DB Body
CREATE OR REPLACE PACKAGE BODY pkg_blob IS
  src_lob BLOB;
  c_amt CONSTANT BINARY_INTEGER := 32765; -- No larger than 32,512 because that's all PB can send
  pos number; --INTEGER;
 PROCEDURE NEWB(p_quote_id NUMBER, p_file_no number) IS
  BEGIN
    DELETE FROM quote_files
     WHERE qa_quote_id = p_quote_id
       and qa_file_number = p_file_no;
     
    INSERT INTO quote_files
      (qa_quote_id, qa_file_number, qa_file)
    VALUES
      (p_quote_id, p_file_no, rawtohex(' '));
    COMMIT;
  END;
  PROCEDURE OPENB(p_quote_id NUMBER, p_file_no number) IS
  BEGIN
    SELECT qa_file
      INTO src_lob
      FROM quote_files
     WHERE qa_quote_id = p_quote_id
       and qa_file_number = p_file_no
       FOR UPDATE;
    --reset pos whenever we reselect the blob
    pos := 1;
  EXCEPTION
    WHEN OTHERS THEN
      raise_application_error(-20000, SQLERRM);
  END;
  FUNCTION READB(p_data out blob) RETURN NUMBER IS
    v_amt integer; --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 READB;
  FUNCTION WRITEB(p_data in blob) 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 WRITEB;
END pkg_blob;
//Upload File PB Code working with APPEOPN!
int          li_file, li_rc
int ll_size
longlong ll_totalsize
blob       lb_data
string     ls_pathname, ls_filename
longlong il_buflen = 32765
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!,  LockRead!  )

               
   SQLCA.newblob( 1, 1 )  // (QA_Quote_id, QA_File_number) Change to vairable data once tested.
   SQLCA.openblob( 1, 1 )   // (QA_Quote_id, QA_File_number)  Change to vairable data once tested.
    ll_size =               FileRead ( li_file, lb_data)

             
  DO WHILE ll_size > 0
      ll_totalsize += ll_size
      li_rc = SQLCA.writeblob( lb_data )
      yield()
      em_fl.text = String ( ll_totalsize )
      ll_size = FileRead ( li_file, lb_data) //, il_buflen )
  LOOP
     
FileClose ( li_file )
commit;