My first "try to do a weekly interesting post" (and serious obviously): Java for read and write to blob

From: Juan Cachito Reyes Pacheco <jreyes_at_dazasoftware.com>
Date: Thu, 4 Mar 2004 12:46:10 -0400
Message-ID: <000d01c40208$35ffffa0$2501a8c0@dazasoftware.com>

Hi what do you think about this, this is a idea about the "try to do one weekly..."
Maybe you could prefer to give more explanation, I think this is enough for the point.
Personally I want to learn tips and don't have problems sharing my own tips.

My experience readin and writing blobs in Oracle 9.2 windows 2000 Using utl_file to write there is a bug in windows, it add an additional character to one character, when it writes. Aditionally you have to define a directory. I tried to do all in java, but I had a problem becaues to read you had to give a connection, so I did it read using utl_file, adn write using java.

Here is the package to read (utl_file), write (java) and read (java witout a connection I got after, still not implemented)

As you see I didn't went to ask tom and copied it from there, it is what I (searched, copied from asktom), but implemented and tested, so you have here sometihng
working in a database)
FUNCTION LeeArchivo( cFile VARCHAR2, cDirectory VARCHAR2, bArchivo IN OUT BLOB) RETURN VARCHAR2 IS b_blob BLOB;

b_bfile BFILE;

cPath VARCHAR2(150);

cReturn VARCHAR2(2000);

BEGIN cReturn := 'Open File, debe darse GRANT READ/WRITE ON DIRECTORY TO DAZ.';

b_bfile := BFILENAME( cDirectory , cFile );

cReturn := 'Creating temporary.';

DBMS_LOB.CREATETEMPORARY(b_blob,TRUE);

cReturn := 'Opening lobs.';

DBMS_LOB.OPEN(b_blob,DBMS_LOB.LOB_READWRITE);

DBMS_LOB.OPEN(b_bfile, DBMS_LOB.LOB_READONLY);

cReturn := 'Loading file.';

DBMS_LOB.LOADFROMFILE (b_blob,b_bfile, DBMS_LOB.GETLENGTH( b_bfile ));

cReturn := 'Close.';

DBMS_LOB.CLOSE(b_bfile);

DBMS_LOB.CLOSE(b_blob);

bArchivo :=b_blob;

cReturn := 'Free temporary.';

DBMS_LOB.FREETEMPORARY(b_blob);

RETURN 'T'; EXCEPTION WHEN OTHERS THEN RETURN 'F-'||cReturn || SQLERRM;

END; CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "WriteBLOB" AS import java.lang.*;

import java.io.*;

import java.sql.*;

import oracle.sql.*;

public class WriteBLOB

{

public static void do_export(BLOB p_blob, String p_file) throws Exception

{

// create file output stream

File l_file = new File(p_file);

FileOutputStream l_out = new FileOutputStream(l_file);

// get an input stream from the blob

InputStream l_in = p_blob.getBinaryStream();

// get buffer size from blob and use this to create buffer for stream

int l_size = p_blob.getBufferSize();

byte[] l_buffer = new byte[l_size];

int l_length = -1;

// write the blob data to the output stream

while ((l_length = l_in.read(l_buffer)) != -1)

{

l_out.write(l_buffer, 0, l_length);

l_out.flush();

}

// close the streams

l_in.close();

l_out.close();

}

};

/

• write using java (got from ask tom)

connect sys as sysdba;

grant javauserpriv to scott;

begin
  dbms_java.grant_permission('SCOTT',
'java.io.FilePermission','c:\temp\blob.txt', 'read'); end;
/

connect scott/tiger;

create table blob_test
(
  short_desc varchar2(32),
  the_blob blob,
  constraint blob_test_pk primary key (short_desc) );

create or replace and compile java source named "importBLOB" as

import java.lang.*;
import java.io.*;
import java.sql.*;

import oracle.sql.*;
import oracle.jdbc.driver.*;

public class importBLOB
{
  public static void do_import(String p_file) throws Exception   {
    PreparedStatement l_pstmt = null;
    BLOB l_blob = null;

    // get a connection using Oracle's internal server-side jdbc driver     Connection l_conn = new OracleDriver().defaultConnection();

    // turn off autocommit - required for this example but something I do anyway

    l_conn.setAutoCommit(false);

    // put an 'empty' row in the table
    l_pstmt = l_conn.prepareStatement("insert into blob_test(short_desc, the_blob) values (?, empty_blob())");

    l_pstmt.setString(1, "TestBlob");

    l_pstmt.execute();

    l_conn.commit();

    // select the 'empty' row from the table for update     // this requires autocommit to be false     l_pstmt = l_conn.prepareStatement("select short_desc, the_blob from blob_test where short_desc = ? for update");

    l_pstmt.setString(1, "TestBlob");

    ResultSet l_rs = l_pstmt.executeQuery();

    // get the blob from the resultset
    if (l_rs.next())
    {
      l_blob = (BLOB) l_rs.getBlob(2);
    }

    // open the file to load into the blob     File l_inputFile = new File(p_file);     FileInputStream l_inputStream = new FileInputStream(l_inputFile);

    // get output stream from the blob
    OutputStream l_outputStream = l_blob.getBinaryOutputStream();

    // create a buffer of the appropriate size     int l_chunkSize = l_blob.getChunkSize();

    byte[] l_buffer = new byte[l_chunkSize];

    // read the file into the blob
    int l_readLength = -1;

    while ((l_readLength = l_inputStream.read(l_buffer)) != -1)     {
      l_outputStream.write(l_buffer, 0, l_readLength);     }

    // close the streams
    l_outputStream.close();
    l_inputStream.close();
  }
};
/

create or replace procedure importBLOB (p_file in varchar2) as language java name 'importBLOB.do_import(java.lang.String)';
/

• a small test of the above begin importBLOB('c:\temp\blob.txt'); end;
  /
  

  ---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Mar 04 2004 - 11:15:05 CST