Home arrow static arrow Java Programming [Archive] - Problem writing a BLOB
Warning: Creating default object from empty value in /www/htdocs/w008deb8/wiki/components/com_staticxt/staticxt.php on line 51
Java Programming [Archive] - Problem writing a BLOB
This topic has 14 replies on 1 page.

Posts:181
Registered: 9/11/01
Problem writing a BLOB  
May 27, 2004 6:08 AM



 
Hi,

- I have some problems to write a file in a BLOB field of an Oracle 9i database.
I don't want to use oracle.sql.BLOB class because I want my application to accept all kinds of database, so I want to use java.sql.Blob class.
1/ First I create a record in the database with an empty value for the BLOB field
2/ Then I retrieve the record just created
3/ I want to write in the BLOB field
There is the code for the third part, which occurs an exception :
java.sql.Blob myBlob = (java.sql.Blob) rs.getBlob(1);File f = new File("c:\\myFile.txt");FileInputStream fis = new FileInputStream(f);OutputStream os = myBlob.setBinaryStream(0);int length=-1;	while((length=fis.read())!=-1) {	   os.write(length);}

The exception occurs on the line : "OutputStream os = myBlob.setBinaryStream(0);"
There is the exception :
java.sql.SQLException: Fonction non prise en charge
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.dbaccess.DBError.throwUnsupportedFeatureSqlException(DBError.java:689)
at oracle.sql.BLOB.setBinaryStream(BLOB.java:1007)
...
Any idea ?

- another question I have.
When I retrieve my Blob from the database, I'm using the getBinaryStream() of the Blob class. This method returns always an OracleBlobInputStream, is there any way to retrieve a "standard" java object, and not a specific oracle object ?

Thanks in advance for your help,
Steve
 

Posts:8,813
Registered: 10/4/00
Re: Problem writing a BLOB  
May 27, 2004 6:13 AM (reply 1 of 14)



 
In English, the error is
ORA-17023 Unsupported Feature

I would suspect that means the driver doesn't support blobs. Maybe find one that does support them?
 

Posts:181
Registered: 9/11/01
Re: Problem writing a BLOB  
May 27, 2004 6:17 AM (reply 2 of 14)



 
Thx for your answer.
Driver is the last one from Oracle 9i, and it supports BLOB : classes12.jar
Other idea ?
 

Posts:3,055
Registered: 18/06/98
Re: Problem writing a BLOB  
May 27, 2004 6:19 AM (reply 3 of 14)



 
I've the same problem with the Oracle BLOB, but while calling stored procedures.
Adding to the injury, I had to use the pooled connection that the application server (iPlanet, JBoss) gave me when connecting to a DataSource, not the OracleConnection.
So I had to
- specialize my code to Oracle (I have to run stored procedures in Oracle databases - not very portable at all.. - , so I've not problems with portability at all
- get the "native" (aham, Oracle) connection from the pooled connection, use the Oracle classes, and work with the BLOB, not the java.sql.Blob.
There are two ways of using the Oracle BLOB in the otn.oracle.com articles, but only one of them worked for me when passing BLOBs as output parameters of stored procedures. Obviously I chose the wrong one, tested with very small blobs, and the solution choked with large blobs when the code was being homologated. Murphy's law in action.
 

Posts:181
Registered: 9/11/01
Re: Problem writing a BLOB  
May 27, 2004 6:24 AM (reply 4 of 14)



 
So it means beacause I'm using Oracle, I'm obliged to use oracle.sql.BLOB class ???
I don't think my customer 'll be very happy to hear that if he changes his database, we'll be obliged to come back to change the code :-)
Portability, where are you ??
 

Posts:8,813
Registered: 10/4/00
Re: Problem writing a BLOB  
May 27, 2004 6:27 AM (reply 5 of 14)



 
I believe the driver for 9.x is named classes12.zip

Look here
http://otn.oracle.com/software/tech/java/sqlj_jdbc/index.html
 

Posts:181
Registered: 9/11/01
Re: Problem writing a BLOB  
May 27, 2004 6:38 AM (reply 6 of 14)



 
I have downloaded the ojdbc14.jar (I'm using jdk1.4) from your url and it's same result...
 

Posts:1,125
Registered: 5/4/01
Re: Problem writing a BLOB  
May 27, 2004 6:43 AM (reply 7 of 14)



 
First off I believe you need setBinaryStream(1) as Oracle isnt zero-indexed.

Secondly, you're using an Oracle driver so will get the Oracle implementations. If you use a different driver you will get their imlpementation returned. You have to use the right driver for the database.

Ted.
 

Posts:181
Registered: 9/11/01
Re: Problem writing a BLOB  
May 27, 2004 6:48 AM (reply 8 of 14)



 
So you mean I'm obliged to use Oracle classes... So when should we use "standard" java class java.sql.Blob ??
 

Posts:96
Registered: 3/25/99
Re: Problem writing a BLOB  
May 27, 2004 6:48 AM (reply 9 of 14)



 
We had the same issue. It wasn't a driver issue, it was a BLOB insert issue. This article may help:
http://wamoz.com/JDBC_and_Oracle_LOB.asp
fitz
 

Posts:1,125
Registered: 5/4/01
Re: Problem writing a BLOB  
May 27, 2004 6:55 AM (reply 10 of 14)



 
So you mean I'm obliged to use Oracle classes... So
when should we use "standard" java class java.sql.Blob
??

No. That was the point of my last post. Java does not provide the classes, it provides the interfaces. It is down to the driver to provide the underlying implementation.

Have you considered using Blob.setBytes() ?

Ted.
 

Posts:181
Registered: 9/11/01
Re: Problem writing a BLOB  
May 27, 2004 7:04 AM (reply 11 of 14)



 
Blob.setBytes is working but size is limited. I was able to store a file until 52 Ko...
Ok so Java only provides interfaces, and not implementation, this mean I have to use oracle classes...
I just found too bad that there isn't a generic class that can be use for all type of database...
Thk for your help all !
Steve
 

Posts:1,125
Registered: 5/4/01
Re: Problem writing a BLOB  
May 27, 2004 7:05 AM (reply 12 of 14)



 
I've the same problem with the Oracle BLOB, but while
calling stored procedures.
Adding to the injury, I had to use the pooled
connection that the application server (iPlanet,
JBoss) gave me when connecting to a DataSource, not
the OracleConnection.
So I had to
- specialize my code to Oracle (I have to run stored

Make up your mind. You don't an OracleConnection but have tailored your code to Oracle???

procedures in Oracle databases - not very portable at

You can port stored procedures, granted only on decent ones.

all.. - , so I've not problems with portability at all
- get the "native" (aham, Oracle) connection from the
pooled connection, use the Oracle classes, and work
with the BLOB, not the java.sql.Blob.
There are two ways of using the Oracle BLOB in the
otn.oracle.com articles, but only one of them worked
for me when passing BLOBs as output parameters of
stored procedures. Obviously I chose the wrong one,
tested with very small blobs, and the solution choked
with large blobs when the code was being homologated.
Murphy's law in action.

Sounds like you may have come across the 4K issue. Wonder what your problem is....

Ted.
 

Posts:1,125
Registered: 5/4/01
Re: Problem writing a BLOB  
May 27, 2004 7:09 AM (reply 13 of 14)



 
Blob.setBytes is working but size is limited. I was
able to store a file until 52 Ko...

Shouldn't be, after all, whats the point of a large object that holds less than a regular object?

Ok so Java only provides interfaces, and not
implementation, this mean I have to use oracle
classes...
I just found too bad that there isn't a generic class

Sadly not. Too have a generic class would mean one person supporting all databases or all databases having the same interface (and I'm not referring to drivers)! MS and Oracle in bed together..... never happen.

that can be use for all type of database...
Thk for your help all !
Steve

Ted.
 

Posts:5
Registered: 4/26/99
Re: Problem writing a BLOB  
Jun 23, 2004 12:33 AM (reply 14 of 14)



 
Hello,
i would not get the OutputStream by calling the Method "OutputStream os = myBlob.setBinaryStream(0);".
I use the Method "blobDest.getBinaryOutputStream();"
Unfortunately, it's not possible to use the abstract JDBC driver interface to set a BLOB (unlimited size) into a oracle database, or rather i don't how to do it...
Here is the code which i use to insert a inputstream to a blob. Please note: In this example a string-type is used for the ID. This might be adapted in your solution....


public static void setBlob(Connection connection, InputStream inputStream, String tableName,
String columnName, String recordID) throws SQLException, IOException
{
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT ID, "columnName" FROM "tableName" WHERE ID='"recordID"' FOR UPDATE");
resultSet.next();
oracle.sql.BLOB blobDest = (oracle.sql.BLOB) ((OracleResultSet) resultSet).getBlob(2);
byte[] buffer = new byte[ blobDest.getBufferSize() ];
OutputStream outputStream = blobDest.getBinaryOutputStream();
int length = -1;
while ((length = inputStream.read(buffer)) != -1) {
outputStream.write(buffer,0,length);
outputStream.flush();
}
statement.close();
outputStream.close();
inputStream.close();
resultSet.close();
}


Hope it's help
best regards

sschoenig
 
This topic has 14 replies on 1 page.