We are facing some errors while trying to retrieve binary data using byte b = ResultSet.getBytes() on a PreparedStatement object. The problem happens from time to time & is consistently reproducable. We are using Oracle 9i thin drivers on a 8.1.7 database. The column type is LONG RAW. The errors are either: 1) Stream closed Exception 2) IOException : Protocol violation exception Many times there isn't any exception, but the data being returned is truncated. This has been verified by comparing the size of the data just before inserting to the database and just after retrieval. Has anyone else encountered this bug/issue? Any ideas on how this could be solved? We've tried using ResultSet.getBinaryStream() but to no avail. We haven't tried using BLOB's, as there is some concern about it's performance. Just as an fyi, the ResultSet/Statements are being closed properly.
Not something that I've encountered myself (I tend to avoid binary data in a database), but I think people have mentioned before that Oracle limits the size to (off the top of my head) 64k. If this is the case you might have to manage this manually. Dave
from Oracle FAQ's - "Writing into BLOBS is slower that inserting LONG RAWs ? It is true that inserting data in BLOBs with JDBC Thin is slower as it still uses the DBMS_LOB package. With JDBC OCI the inserts are faster as the native LOB APIs are used". Just stumbled accross this while searching Oracle, thought it might substantiate the concerns you had using BLOBs. For an explanation and complete examples on how to stream LONGs and LONG RAWS see this link Jamie
Joined: Jun 27, 2000
Thanks for your replies. LONG RAW column does support data upto 2GB. In any case my data is not very huge (just around 20 kb). That is one of the reasons too why I'm reluctant to use BLOB's as I think it would be overkill for such small data size. Anyways I think I've found a solution. I looked through the Oracle forums, and other folks have had similar problems getting binary data using getBytes() on a LONG RAW column. Basically the work-around is that if you are selecting multiple columns in a select query, you need to re-structure the select query as well as ResultSet retrievals so that the binary columns are retrieved first. Also we need to use getBinaryStream() instead of getBytes(). For eg: "select binary_data, id, name from my_table" InputStream is = ResultSet.getBinaryStream(1); //retrieve data fully from input stream is.close(); String id = ResultSet.getString(2); String name = ResultSet.getString(3); I tested the above approach and it seems to have resolved the issue.