12 Replies Latest reply: Jun 7, 2013 10:18 PM by rp0428 RSS

    Can't pass object implementing Clob interface to PreparedStatement.setClob

    994207

      So as we know, the .setClob() method expects a Clob interface passed to it. I do exactly that, but I get a ClassCastException, because the Oracle driver internally makes the assumption that it's an Oracle Clob instead of a java.sql.Clob. What is the point of an interface if we can't use it for its purpose?

      That's a major bug!

        • 1. Re: Can't pass object implementing Clob interface to PreparedStatement.setClob
          gimbal2
          Possibly your code is just wrong though, I can hardly believe that the regular JDBC API interface of the driver has such a bug and that YOU are the one to find it among the thousands of people and hundreds of thousands of applications using the driver. Perhaps you're using a driver specific feature/method.
          • 2. Re: Can't pass object implementing Clob interface to PreparedStatement.setClob
            rp0428
            Welcome to the forum!

            When you post a JDBC related question provide your Java version and platform, the JDBC driver name and version and the full database version and name.
            >
            So as we know, the .setClob() method expects a Clob interface passed to it. I do exactly that, but I get a ClassCastException, because the Oracle driver internally makes the assumption that it's an Oracle Clob instead of a java.sql.Clob. What is the point of an interface if we can't use it for its purpose?
            >
            All you have said is the equivalent of "my car won't go; why won't my car go".

            If you want help with code you need to post the code. We can't see your machine, your code, the exception stack trace and don't know what your code is trying to do, how it is doing it or how you are executing it.
            • 3. Re: Can't pass object implementing Clob interface to PreparedStatement.setClob
              Joe Weinstein-Oracle
              I have seen this sort of problem with the Oracle driver before, where the API for a call says it
              takes a java.sql.XXX but in fact the driver call assumes and relies on the object being a specific
              oracle implementation of that java.sql.XXX, in fact sometimes internally casting it to a concrete
              Oracle object.
              What sort of Clob are you passing in? Is it from another JDBC connection? From another DBMS?
              From another JDBC driver? In the cases I have dealt with, WebLogic wraps all JDBC objects to
              provide safety and control in a multithreaded and pooled environment, and these wrappers
              dynamically project any standard or vendor-specific interface and methods that the wrapped
              object implements, so the wrapper object can be used identically to the original object, but
              they still can't be cast to the concrete class of the object they wrap...
              Joe
              • 4. Re: Can't pass object implementing Clob interface to PreparedStatement.setClob
                994207
                Yes, the Clob that I'm passing is a custom implementation which only implements the interfaces that are required by java.sql.Clob. Most of them are dummy implementations.

                Joe, what do you mean by this?

                >
                and these wrappers dynamically project any standard or vendor-specific interface and methods that the wrapped object implements
                >

                A wrapper "projects"? Can you elaborate on your solution? The only immediate solution that I can think of, is to open an extra connection to the OracleDB and keep it opened
                for creating Clob objects exclusively, converting between my SerialClob (see below) and the oracle Clob manually each time I pass it to any Oracle driver.


                here is the MANIFEST.MF of the Oracle driver that I'm using, I don't remember where I got it from but it is a fairly recent one. By the way, this bug sticks out so much to me because the SQLServer JDBC driver does not(!) have this problem. I can pass the interface object to it there and everything works perfectly.

                >
                Manifest-Version: 1.0
                Ant-Version: Apache Ant 1.6.5
                Created-By: 1.5.0_24-rev-b08 (Sun Microsystems Inc.)
                Implementation-Vendor: Oracle Corporation
                Implementation-Title: JDBC
                Implementation-Version: 11.2.0.2.0
                Repository-Id: JAVAVM_11.2.0.2.0_LINUX_100812.1
                Specification-Vendor: Sun Microsystems Inc.
                Specification-Title: JDBC
                Specification-Version: 4.0
                Main-Class: oracle.jdbc.OracleDriver
                sealed: true
                >

                This is the code for the Clob implementation: (I'm fairly certain the code itself does not matter, obviously the Oracle driver expects some kind of Oracle object, therefore it doesn't matter what the Clob interface implementation looks like)
                // VJDBC - Virtual JDBC
                // Written by Michael Link
                // Website: http://vjdbc.sourceforge.net
                
                package de.simplicit.vjdbc.serial;
                
                import java.io.*;
                import java.nio.CharBuffer;
                import java.sql.Clob;
                import java.sql.SQLException;
                
                import de.simplicit.vjdbc.util.SQLExceptionHelper;
                
                public class SerialClob implements Clob, Externalizable {
                     private static final long serialVersionUID = 3904682695287452212L;
                
                     protected char[] _data;
                
                     public SerialClob()
                     {
                     }
                
                     public SerialClob(Clob other) throws SQLException
                     {
                          try
                          {
                               StringWriter sw = new StringWriter();
                               Reader rd = other.getCharacterStream();
                               char[] buff = new char[1024];
                               int len;
                               while ((len = rd.read(buff)) > 0)
                               {
                                    sw.write(buff, 0, len);
                               }
                               _data = sw.toString().toCharArray();
                               other.free();
                          } catch (IOException e)
                          {
                               throw new SQLException("Can't retrieve contents of Clob",
                                         e.toString());
                          }
                     }
                
                     public SerialClob(Reader rd) throws SQLException
                     {
                          try
                          {
                               init(rd);
                          } catch (IOException e)
                          {
                               throw new SQLException("Can't retrieve contents of Clob",
                                         e.toString());
                          }
                     }
                
                     public SerialClob(Reader rd, long length) throws SQLException
                     {
                          try
                          {
                               init(rd, length);
                          } catch (IOException e)
                          {
                               throw new SQLException("Can't retrieve contents of Clob",
                                         e.toString());
                          }
                     }
                
                     public void init(Reader rd) throws IOException
                     {
                          StringWriter sw = new StringWriter();
                          char[] buff = new char[1024];
                          int len;
                          while ((len = rd.read(buff)) > 0)
                          {
                               sw.write(buff, 0, len);
                          }
                          _data = sw.toString().toCharArray();
                     }
                
                     public void init(Reader rd, long length) throws IOException
                     {
                          StringWriter sw = new StringWriter();
                          char[] buff = new char[1024];
                          int len;
                          long toRead = length;
                          while (toRead > 0
                                    && (len = rd.read(
                                              buff, 0, (int) (toRead > 1024 ? 1024 : toRead))) > 0)
                          {
                               sw.write(buff, 0, len);
                               toRead -= len;
                          }
                          _data = sw.toString().toCharArray();
                     }
                
                     public void writeExternal(ObjectOutput out) throws IOException
                     {
                          out.writeObject(_data);
                     }
                
                     public void readExternal(ObjectInput in) throws IOException,
                               ClassNotFoundException
                     {
                          _data = (char[]) in.readObject();
                     }
                
                     public long length() throws SQLException
                     {
                          return _data.length;
                     }
                
                     public String getSubString(long pos, int length) throws SQLException
                     {
                          if (pos <= Integer.MAX_VALUE)
                          {
                               return new String(_data, (int) pos - 1, length);
                          }
                          // very slow but gets around problems with the pos being represented
                          // as long instead of an int in most java.io and other byte copying
                          // APIs
                          CharArrayWriter writer = new CharArrayWriter(length);
                          for (long i = 0; i < length; ++i)
                          {
                               writer.write(_data[(int) (pos + i)]);
                          }
                          return writer.toString();
                     }
                
                     public Reader getCharacterStream() throws SQLException
                     {
                          return new StringReader(new String(_data));
                     }
                
                     public InputStream getAsciiStream() throws SQLException
                     {
                          try
                          {
                               return new ByteArrayInputStream(new String(_data).getBytes("US-ASCII"));
                          } catch (UnsupportedEncodingException e)
                          {
                               throw SQLExceptionHelper.wrap(e);
                          }
                     }
                
                     public long position(String searchstr, long start) throws SQLException
                     {
                          throw new UnsupportedOperationException("SerialClob.position");
                     }
                
                     public long position(Clob searchstr, long start) throws SQLException
                     {
                          StringReader r = (StringReader) searchstr.getCharacterStream();
                          CharBuffer temp = CharBuffer.allocate(3000);// ...obviously I need some way to have this work for any size
                          try
                          {
                               int what = r.read(temp);
                          } catch (IOException e)
                          {
                               // TODO Auto-generated catch block
                               e.printStackTrace();
                          }
                          return -1;
                          //throw new UnsupportedOperationException("SerialClob.position");
                     }
                
                     public int setString(long pos, String second) throws SQLException
                     {
                          String end = null;
                          if (_data != null)
                          {
                               String first = new String(_data, 0, (int) pos);
                               end = first.concat(second);
                          }
                          else 
                          {
                               end = second;
                          }
                          
                          _data = end.toCharArray();
                          return end.length();
                          //throw new UnsupportedOperationException("SerialClob.setString");
                     }
                
                     public int setString(long pos, String str, int offset, int len)
                               throws SQLException
                     {
                          throw new UnsupportedOperationException("SerialClob.setString");
                     }
                
                     public OutputStream setAsciiStream(long pos) throws SQLException
                     {
                          throw new UnsupportedOperationException("SerialClob.setAsciiStream");
                     }
                
                     public Writer setCharacterStream(long pos) throws SQLException
                     {
                          throw new UnsupportedOperationException("SerialClob.setCharacterStream");
                     }
                
                     public void truncate(long len) throws SQLException
                     {
                          throw new UnsupportedOperationException("SerialClob.truncate");
                     }
                
                     /* start JDBC4 support */
                     public Reader getCharacterStream(long pos, long length) throws SQLException
                     {
                          if (pos <= Integer.MAX_VALUE && length <= Integer.MAX_VALUE)
                          {
                               return new CharArrayReader(_data, (int) pos, (int) length);
                          }
                          // very slow but gets around problems with the pos being represented
                          // as long instead of an int in most java.io and other byte copying
                          // APIs
                          CharArrayWriter writer = new CharArrayWriter((int) length);
                          for (long i = 0; i < length; ++i)
                          {
                               writer.write(_data[(int) (pos + i)]);
                          }
                          return new CharArrayReader(writer.toCharArray());
                     }
                
                     public void free() throws SQLException
                     {
                          _data = null;
                     }
                     /* end JDBC4 support */
                }
                Edited by: 991204 on 04.06.2013 00:25
                • 5. Re: Can't pass object implementing Clob interface to PreparedStatement.setClob
                  Joe Weinstein-Oracle
                  You are right that the code doesn't matter. The fact is that if the driver internally casts the
                  argument passed in, to be a concrete driver-level object, that is always going to fail with
                  anything you implement yourself. I don't have a solution for you, and it is interesting that
                  other driver vendors, talking to other DBMSes, can take generic Lobs as arguments to
                  their calls. I am not fluent or current in knowledge of the Oracle driver code, so I can't
                  say when/if this driver behavior will ever be relaxed. You could file an official bug and see
                  what they say...
                  • 6. Re: Can't pass object implementing Clob interface to PreparedStatement.setClob
                    rp0428
                    >
                    Yes, the Clob that I'm passing is a custom implementation which only implements the interfaces that are required by java.sql.Clob. Most of them are dummy implementations.

                    Joe Weinstein wrote:
                    I have seen this sort of problem with the Oracle driver before, where the API for a call says it
                    takes a java.sql.XXX but in fact the driver call assumes and relies on the object being a specific
                    oracle implementation of that java.sql.XXX, in fact sometimes internally casting it to a concrete
                    Oracle object.
                    Thanks for posting that code. It would have helped if you had posted your code that is doing the 'setting' of the instance.

                    There are two similar 'set' methods in the Oracle driver. One is 'setClob' and takes an instance that implements the java.sql.Clob interface. The other is 'setCLOB' and takes an instance of an oracle.sql.CLOB class.

                    As Joe suspected I confirmed that the 'setClob' method in the Oracle driver does, indeed, perform a cast to CLOB and then calls the 'setCLOB' method. Of course that will fail if the instance is not an oracle.sql.CLOB class instance.

                    So you make a point, in one sense, that if a method declares that it takes an interface then you might think it should work properly for any valid implementation of that interface.

                    However, the Java API does contain language that specifies that there is more involved than that.

                    Here is the description of the java.sql.Clob API
                    http://docs.oracle.com/javase/6/docs/api/java/sql/Clob.html
                    >
                    public interface Clob

                    The mapping in the JavaTM programming language for the SQL CLOB type. An SQL CLOB is a built-in type that stores a Character Large Object as a column value in a row of a database table. By default drivers implement a Clob object using an SQL locator(CLOB), which means that a Clob object contains a logical pointer to the SQL CLOB data rather than the data itself. A Clob object is valid for the duration of the transaction in which it was created.

                    The Clob interface provides methods for getting the length of an SQL CLOB (Character Large Object) value, for materializing a CLOB value on the client, and for searching for a substring or CLOB object within a CLOB value. Methods in the interfaces ResultSet, CallableStatement, and PreparedStatement, such as getClob and setClob allow a programmer to access an SQL CLOB value. In addition, this interface has methods for updating a CLOB value.

                    All methods on the Clob interface must be fully implemented if the JDBC driver supports the data type.
                    >
                    Note the first sentence - 'The mapping . . for the SQL CLOB type'.

                    Then the Java API for the 'setClob' method of the PreparedStatement
                    http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html#setClob(int, java.sql.Clob)
                    >
                    setClob
                    void setClob(int parameterIndex,
                    Clob x)
                    throws SQLExceptionSets the designated parameter to the given java.sql.Clob object. The driver converts this to an SQL CLOB value when it sends it to the database.

                    Parameters:
                    parameterIndex - the first parameter is 1, the second is 2, ...
                    x - a Clob object that maps an SQL CLOB value
                    >
                    Note the sentence - 'The driver converts this to an SQL CLOB value when it sends it to the database.

                    So the 'Clob' interface must be MAPPED to an appropriate SQL CLOB value by the driver.

                    Since you are using an Oracle driver a 'Clob' is getting mapped to an oracle.sql.CLOB. You are getting that exception because you did not provide the Oracle driver with an instance of a class that maps to oracle.sql.CLOB.

                    So if you write you own implementation of the java.sq.Clob interface, SerialClob, it is ONLY going to work for a driver that can map a 'SerialClob' instance to a SQL CLOB value.

                    Namely - you need to write your own JDBC driver and have it perform that mapping.

                    The 'Clob' interface is just an 'interface'. It is only meant to be implemented by code that implements a JDBC Driver. And it is the driver implementation that is responsible for implementing the appropriate 'mapping.

                    Since you used an Oracle driver it will attempt to do just what the API above said - 'convert it to a SQL CLOB value'; in this case cast it to an oracle.sql.CLOB; which, of course, fails.

                    So the nut of the problem is that the Oracle API implicitly requires that you pass an instance of oracle.sql.CLOB or a class that extends oracle.sql.CLOB
                    • 7. Re: Can't pass object implementing Clob interface to PreparedStatement.setClob
                      994207
                      Exending oracle.sql.CLOB does not seem to be possible. I tried and after realizing that an internal data field had to be set, I still get this error:
                      "ORA-22275: invalid LOB locator specified" on PreparedStatement.execute(). Clearly the oracle.sql.CLOB itself has some kind of handle into the database already and the rest of the Oracle code relies on that handle to exist.
                      • 8. Re: Can't pass object implementing Clob interface to PreparedStatement.setClob
                        gimbal2
                        991204 wrote:
                        Exending oracle.sql.CLOB does not seem to be possible. I tried and after realizing that an internal data field had to be set, I still get this error:
                        "ORA-22275: invalid LOB locator specified" on PreparedStatement.execute(). Clearly the oracle.sql.CLOB itself has some kind of handle into the database already and the rest of the Oracle code relies on that handle to exist.
                        Correct (about the handle, and probably the rest too)!
                        • 9. Re: Can't pass object implementing Clob interface to PreparedStatement.setClob
                          rp0428
                          >
                          Exending oracle.sql.CLOB does not seem to be possible. I tried and after realizing that an internal data field had to be set, I still get this error:
                          "ORA-22275: invalid LOB locator specified" on PreparedStatement.execute(). Clearly the oracle.sql.CLOB itself has some kind of handle into the database already and the rest of the Oracle code relies on that handle to exist.
                          >
                          Of course you can extend oracle.sql.CLOB the same way you extend any other class.

                          But a valid instance of CLOB needs to be a CLOB Locator. And you get those from the database using the Oracle driver. But the Oracle driver is only going to return a CLOB instance and you won't be able to cast that to an instance of your class.

                          So it is not clear what you hope to accomplish by extending CLOB but any extension will only be useful in code that you write to pass TO the Oracle driver but won't be useful to deal with anything you get FROM the driver. And since the internals of the Oracle classes are proprietary and not published you are likely to have nothing but problems using any of the class internals properly.

                          See the JDBC Developer's Guide for examples of how to work with LOBs
                          http://docs.oracle.com/cd/B28359_01/java.111/b31224/toc.htm
                          • 10. Re: Can't pass object implementing Clob interface to PreparedStatement.setClob
                            dsurber
                            No JDBC vendor that I am aware of supports arbitrary implementations of Clob, Blob, NClob, RowId, etc. For example an Oracle CLOB pretty much is a locator; it doesn't contain one; it is one. There is nothing in java.sql.Clob that allows an implementation to store or access a locator. Without a locator you don't have an Oracle CLOB. There is no way around this. Passing an instance of an arbitrary implementation of Clob to Oracle JDBC is never going to work because there is no locator. Same is true for every other driver to the best of my knowledge.

                            The problem highlights a weakness in the Java language. This is a moderately well known issue and is certainly no surprise to the members of the JDBC Expert Group. Early Java had no way to specify that objects passed as arguments to one method must be the results of calling another method implemented by the same library. Generics might allow this to be specified now but they didn't exist in 1995. This is a consistent requirement throughout JDBC.

                            Douglas
                            • 11. Re: Can't pass object implementing Clob interface to PreparedStatement.setClob
                              dsurber
                              Extending oracle.sql.CLOB is not supported. Yes I know it should be final in that case. It's not. What can I say other than it is not supported and don't do that. It might work. If it doesn't support will laugh at you. Same for all the other classes in oracle.sql.

                              Douglas
                              • 12. Re: Can't pass object implementing Clob interface to PreparedStatement.setClob
                                rp0428
                                >
                                Extending oracle.sql.CLOB is not supported. Yes I know it should be final in that case. It's not. What can I say other than it is not supported and don't do that. It might work. If it doesn't support will laugh at you. Same for all the other classes in oracle.sql.
                                >
                                I agree; I don't how it could possibly be useful since anyone doing that couldn't possible intrepret or use the actual internal object. They wouldn't even know what that object is or anything about its structure. And it you don't have access to that information you can't manipulate it so what would be the point.

                                OP never did mention why they even wanted to do that or we might have been able to find a better solution.