Solved

writing Oracle 9 BLOB (JDBC)

Posted on 2004-03-29
5
6,067 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
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Format Number Field 10 39
levels for reporting 5 51
Create index on View 27 52
Calculating percentages per course - Oracle Query 3 28
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now