Solved

writing Oracle 9 BLOB (JDBC)

Posted on 2004-03-29
5
6,079 Views
Last Modified: 2007-12-19
Hello all!

I'm having trouble with Oracle Blobs through java. I don't know in which forum I should ask this question, but here goes:

I have an Oracle 9i database and JDBC 9.2 driver version.
I am using java v1.4.

I'm connecting to my database using the following:

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        _connection = DriverManager.getConnection("jdbc:oracle:thin:@host:port:ut", user, passwd);


In my code, i'm trying to write a blob to my table and the code looks like this:

import java.sql.*;
import oracle.sql.BLOB;

...

String sql = "SELECT VALIDITY FROM RANGEEVENT WHERE IDEVENT="+idLREvent;
stmt = Database.getConnection().createStatement();
ResultSet rs = stmt.executeQuery(sql);
if (rs.next()) {
        Blob myblob = rs.getBlob(1);
        OutputStream os = ((BLOB)myblob).setBinaryStream(0);
        os.write(validity);
        os.close();
}


BUT i get this error:
java.sql.SQLException: Unsupported function


After debugging, I found that it is "((BLOB)myblob).setBinaryStream(0);" which is causing this problem.

I've been reading the Oracle JDBC doc and can't find what the problem could be. I tried using OCI driver for connection but then I get ORA-06401: NETCMN error...
0
Comment
Question by:mariec
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 

Author Comment

by:mariec
ID: 10703279
BTW i've just changed that line to:

OutputStream os = ((BLOB)myblob).getBinaryOutputStream();

and it still doesnt work
0
 
LVL 6

Expert Comment

by:musdu
ID: 10703432
Hi,

you should use DBMS_LOB package to write blob data to database.

regards.
0
 
LVL 7

Accepted Solution

by:
grim_toaster earned 230 total points
ID: 10703522
Couple of pointers for you:

1) You will need to set your connection to not automatically commit
2) You need to query for update
3) You will need to commit at the end (or rollback if there were errors)
4) Closing of database resources and io streams should be done in a finally block to ensure that they are always closed, even if errors occur

Here's a slightly modified version of your code to make it work in an Oracle specific way (but without the forcing closure of the resources as mentioned above).

            con = Database.getConnection();
            con.setAutoCommit(false);

            stmt = con.createStatement();
            rset = stmt.executeQuery("SELECT VALIDITY FROM RANGEEVENT WHERE IDEVENT=2 FOR UPDATE");

            if (rset.next()) {
                BLOB myblob = (BLOB) rset.getBlob(1);
                OutputStream os = myblob.getBinaryOutputStream();
                   
                os.write("hello I'm a goldfish...".getBytes());
                os.close();
            }

            con.commit();
0
 
LVL 23

Assisted Solution

by:seazodiac
seazodiac earned 20 total points
ID: 10707585
Hi, mariec,
in case you are still looking , here is a doc from Oracle metalink and it's for oracle8i but I find it's also applicable to oracle9i


Overview
--------
 
  The purpose of this article is to explain and demonstrate the use of  
  the Oracle JDBC extensions for CLOB and BLOB support.  It contains a  
  commented example of a JDBC program that manipulates CLOBs and BLOBs  
  in an Oracle database using the Oracle extensions to the JDBC drivers.  
 
  This article was taken from the 'LobExample.java' sample that comes with  
  the 8i release.  It is intended for use by programmers who are interested  
  in using these extensions to manipulate LOB data in an Oracle database.  
 
  The example is cursory in nature and is meant to be used as a template  
  to help create your own JDBC applications that need to manipulate LOBs.  
  It is not the responsibility of Oracle to recode the example to meet  
  your specific needs.  Since the code uses Oracle specific extensions to  
  JDBC, it is not portable to other vendors databases and JDBC drivers.
 
  This code was tested using classes111.zip, JDK 1.1.8, and Oracle 8.1.5.
  This article was written prior to the JDBC 2.0 specifiction and prior to  
  the existence of classes12.zip.  Refer to the LobExample.java on your  
  file system for a more current example that uses JDK 1.2 extentions.  
 
 
Related Documents
-----------------
 
  A more detailed description of the Oracle JDBC extensions and  
  additional JDBC information is contained in the "Oracle 8i JDBC  
  Developer's Guide and Reference", (A64685-01).
 
  For more information on Java Streams, refer to the "Core Java,  
  Volume II", (ISB-N-0-13-766965-8), Sun Microsystems Press (A prentice  
  Hall Title), by Hostmann and Cornell.
 
 
Notes
-----
   
  Only two steps that are necessary to read and write CLOB and BLOB  
  data in an Oracle database:
 
  1. Access the LOB locator.  The datatypes oracle.sql.BLOB and
     oracle.sql.CLOB are classes that encapsulate Oracle LOB
     locators and provide a streaming interface to the LOB data
     through the locator.
 
     A CLOB or BLOB locator can be retrieved from an OracleResultSet
     or an OracleCallableStatement.
 
  2. Use the appropriate getXXXStream() method provided by the LOB
     locator class.  This materializes the BLOB or CLOB as a  
     Java Stream.  The read() and write() methods provided by the  
     stream interface can then be used to access and manipulate the  
     LOB data.
 
 
  Oracle.sql.BLOB and oracle.sql.CLOB are datatypes that are supported  
  by the JDBC 2.0 standard.  As such, these datatypes will implement  
  java.sql.BLOB and java.sql.CLOB when the JDBC 2.0 standard is available.  
  This standard for JDBC is part of the JDK 1.2 release.
 
  Since the 8i release of the JDBC drivers is JDBC 1.22 and JDK 1.1.6  
  compliant, the oracle.sql.BLOB and oracle.sql.CLOB classes do not  
  implement java.sql.BLOB and java.sql.CLOB interfaces because they
  are not present in the classes.zip file that comes with the JDK 1.1.x.  
 
  To get around this and to offer support of the JDBC 2.0 features that
  pertain to LOB datatypes, Oracle has placed JDBC 2.0 compliant
  interfaces to LOB datatypes in the oracle.jdbc2 package.  This package
  can be found in classes111.zip.  
 
  The oracle.sql.BLOB and oracle.sql.CLOB datatypes implement the interfaces  
  oracle.jdbc2.Blob and oracle.jdbc2.Clob, respectively.  In a future  
  release of Oracle, LOB types will implement java.sql.* interfaces.
 
 
Example Code
------------
 
import java.sql.*;
import java.io.*;
import java.util.*;
 
// Importing the Oracle Jdbc driver package makes the code more readable
import oracle.jdbc.driver.*;
 
//needed for new CLOB and BLOB classes
import oracle.sql.*;
import oracle.jdbc2.*;
 
public class LobStreams  
{
  public static void main (String args [])
  throws Exception
  {  
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
 
    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    // <database> is either an entry in tnsnames.ora or a SQL*net name-value pair.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@<database>", "scott", "tiger");
 
    // Its faster when auto commit is off
    conn.setAutoCommit (false);
 
    // Create a Statement
    Statement stmt = conn.createStatement ();
 
    // Drop the basic_lob_table
    try
    {
      stmt.execute ("drop table basic_lob_table");
    }
    catch (SQLException e)
    {
      // An exception could be raised here if the table did not exist already
      // but we gleefully ignore it
    }
 
    // Create a table containing a BLOB and a CLOB
    stmt.execute ("create table basic_lob_table (x varchar2 (30), b blob," +
                  "c clob)");
    // Populate the table
    stmt.execute ("insert into basic_lob_table values ('one'," +
    "'010101010101010101010101010101', 'onetwothreefour')");
    stmt.execute ("insert into basic_lob_table values ('two'," +
    "'020202020202020202020202020202', 'twothreefourfivesix')");
    System.out.println ("Dumping lobs");
 
    // Select the lobs
    OracleResultSet rset = (OracleResultSet)stmt.executeQuery(
                                "select * from basic_lob_table");
    while (rset.next ())
    {
      // Get the lobs
      BLOB blob = ((OracleResultSet)rset).getBLOB (2);
      CLOB clob = ((OracleResultSet)rset).getCLOB (3);
 
      // Print the lob contents
      dumpBlob (conn, blob);
      dumpClob (conn, clob);
 
      // Change the lob contents
      fillClob (conn, clob, 2000);
      fillBlob (conn, blob, 4000);
    }
 
    System.out.println ("Dumping lobs again");
 
    rset = (OracleResultSet)stmt.executeQuery(
                      "select * from basic_lob_table");
    while (rset.next ())
    {
      // Get the lobs
      BLOB blob = ((OracleResultSet)rset).getBLOB (2);
      CLOB clob = ((OracleResultSet)rset).getCLOB (3);
 
      // Print the lobs contents
      dumpBlob (conn, blob);
      dumpClob (conn, clob);
    }
  }
 
  // Utility function to dump Clob contents
//  static void dumpClob (Connection conn, CLOB clob)
  static void dumpClob (Connection conn, Clob clob)
  throws Exception
  {
    // get character stream to retrieve clob data
    Reader instream = clob.getCharacterStream();
 
    // create temporary buffer for read
    char[] buffer = new char[10];
 
    // length of characters read
    int length = 0;
 
    // fetch data
    while ((length = instream.read(buffer)) != -1)
    {
      System.out.print("Read " + length + " chars: ");
      for (int i=0; i<length; i++)
        System.out.print(buffer[i]);
      System.out.println();
    }
     
    // Close input stream
    instream.close();
  }
 
  // Utility function to dump Blob contents
  static void dumpBlob (Connection conn, BLOB blob)
  throws Exception
  {
    // Get binary output stream to retrieve blob data
 
    InputStream instream = blob.getBinaryStream();
    // Create temporary buffer for read
    byte[] buffer = new byte[10];
    // length of bytes read
    int length = 0;
    // Fetch data
    while ((length = instream.read(buffer)) != -1)
    {
      System.out.print("Read " + length + " bytes: ");
      for (int i=0; i<length; i++)
        System.out.print(buffer[i]+" ");
      System.out.println();
    }
    // Close input stream
    instream.close();
  }
 
  // Utility function to put data in a Clob
  static void fillClob (Connection conn, CLOB clob, long length)
   throws Exception
  {
    Writer outstream = clob.getCharacterOutputStream();
    int i = 0;
    int chunk = 10;
    while (i < length)
    {
      outstream.write(i + "hello world", 0, chunk);
      i += chunk;
      if (length - i < chunk)
        chunk = (int) length - i;
    }
    outstream.close();
  }
 
  // Utility function to put data in a Blob
  static void fillBlob (Connection conn, BLOB blob, long length)
    throws Exception
  {
    OutputStream outstream = blob.getBinaryOutputStream();
    int i = 0;
    int chunk = 10;
    byte [] data = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
    while (i < length)
    {
      data [0] = (byte)i;
      outstream.write(data, 0, chunk);
      i += chunk;
      if (length - i < chunk)
        chunk = (int) length - i;
    }
    outstream.close();
  }
}
0
 

Author Comment

by:mariec
ID: 10711327
grim_toaster's hints helped out alot. Query for update helps as well as disabling autocommit.

Seazodiac, its an interesting article but since the JDBC 2.0 standard is available, the code must be changed a little. Thanks for the doc though.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
form builder not starting 3 72
Deleting Rows from an Oracle Database - Performance 19 76
sum of columns in a row in oracle 3 44
oracle sqlplus query delimiter 8 39
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question