Derby incorrectly throws Exception when streaming to BLOB field

• Key: DERBY-562
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Major
• Assignee: Sunitha Kambhampati
• Reporter: Holger Rehn
• Component/s: JDBC
• Affects Version/s: 10.0.2.0, 10.0.2.1, 10.0.2.2, 10.1.1.0, 10.1.2.1, 10.2.1.6
• Fix Version/s: 10.1.2.1, 10.2.1.6

Description

Derby incorrectly throws an Exception when streaming to a BLOB in case the used InputStream actually could provide more data than I want to write to the BLOB field.

   PreparedStatement statement = connection.prepareStatement( "insert into FOO(ID,DATA) values(?, ?)" );
   statement.setLong( 1, someValue );
   statement.setBinaryStream( 2, someInputStream, amountOfData ); // amountOfData < amount of data readable from someInputStream
   statement.executeUpdate();

executeUpdate() throws an SQLException with detail message: "Input stream held less data than requested length.: java.io.IOException"

In my case this was first caused by writing an internal buffer (byte[]) to the data base through a ByteArrayInputStream while not limiting the ByteArrayInputStream to the useful data within the buffer but setting amountOfData to the number of useful bytes. So the 2 problems are:
1. the error text is definitly incorrect since I provide more data than neccessary, not less
2. in my opinion this shouldn't throw an exception at all (I checked against 4 other DBMS, all of them worked as intended)

Comments

[Holger Rehn]

Just in case that matters - I forgot to mention that I used Derby in embedded mode when getting this error.

[Rick Hillegas]

RawToBinaryFormatStream.checkSufficientData() requires that the InputStream not have any extra bytes in it. The comment in the code says that the JDBC 3.0 spec requires this behavior. I couldn't find this assertion in the 3.0 spec when I glanced at it quickly. However, the javadoc for java.sql.PreparedStatement.setBinaryStream() says that the length argument is "the number of bytes in the stream". I suppose someone could interpret this to mean "exactly the number of bytes in the stream".

I agree that the behavior of the other databases sounds better. I also agree that the error message is wrong. Holger, can you tell us what other DBMSes you tried?

Can someone cite chapter and verse for the JDBC 3.0 requirement claimed by checkSufficientData()?

[Daniel John Debrunner]

The comment actually says 'JDBC 3.0 (from tutorial book)', not from the spec.
Meaning
[TUTORIAL3] - JDBC API Tutorial and Reference, Third Edition. ISBN 0321173848 http://java.sun.com/developer/Books/jdbc/Fisher/index.html
(see http://db.apache.org/derby/papers/JDBCImplementation.html)

Chapter 25 - Page 664 - setBinaryStream of PreparedStatement

"length - ... Note that if the stream contains more or less bytes than are specified in length, an exception is thrown"

identical comments for setAsciiStream and setCharacterStream methods.

I'll provide an update for http://db.apache.org/derby/papers/JDBCImplementation.html

[Rick Hillegas]

You could kludge around this Derby behavior by wrapping your InputStream in a subclass of java.io.FilterInputStream which truncates input at the desired length.

[Holger Rehn]

Thank you for looking at the problem!

To answer Rick's question - I checked against Sybase Adaptive Server Anywhere, Sybase Adaptive Server Enterprise, Microsoft SQL Server and Hypersonic SQL - just because that are the data base systems I'm currently working with. And all of them read exactly as many bytes as set by the length argument, ignoring any additional data.

[Sunitha Kambhampati]

As Dan also pointed out in the earlier comment, Derby behavior is correct according to what the jdbc spec says ( in this case the tutorial book).

e.g. ps.setBinaryStream(pos,stream, streamLength)
Right now, even if you pass a stream with length greater than streamLength , derby throws an exception with the following message
"Input stream held less data than requested length"

I think the message must be changed to correctly say -
"Input stream did not have the exact amount of data as the requested length."

I will submit a patch for this.

[Sunitha Kambhampati]

This patch
- changes the error message thrown when the stream is either less or greater than the requested length to
'Input stream did not have exact amount of data as the requested length.'
- enhances the characterStreams.out test, to print out the nested sql exceptions to ensure that the proper error message is returned.
- updates to the master files.

Ran derbyall with no failures on jdk1.4.2/windows.

svn stat
M java\engine\org\apache\derby\impl\jdbc\RawToBinaryFormatStream.java
M java\engine\org\apache\derby\impl\jdbc\ReaderToUTF8Stream.java
M java\engine\org\apache\derby\iapi\reference\SQLState.java
M java\engine\org\apache\derby\loc\messages_en.properties
M java\testing\org\apache\derbyTesting\functionTests\tests\jdbcapi\characterStreams.java
M java\testing\org\apache\derbyTesting\functionTests\master\characterStreams.out
M java\testing\org\apache\derbyTesting\functionTests\master\resultsetStream.out

I verified that we are testing for these two error cases (ie stream has less or more data than requested length), for the following supported stream related api - setCharacterStream, setAsciiStream, setBinaryStream on PreparedStatement. Derby does not support setUnicodeStream api that is deprecated in jdbc 3.0.

Can someone please review it and commit it. Thanks.

I would like this patch to also be ported to 10.1. I'll submit the merge command once this gets committed on trunk.

[Sunitha Kambhampati]

I merged this fix to 10.1 codeline. All tests passed ok with jdk142/win2k.

The merge command is
svn merge -r 292829:292830 https://svn.apache.org/repos/asf/db/derby/code/trunk

I would like this to go in 10.1. I'd appreciate if someone could commit this. Thanks.

[Sunitha Kambhampati]

updating fixin information.

[Kathey Marsden]

Committed this to 10.1
Date: Thu Oct 6 16:52:27 2005
New Revision: 306964

URL: http://svn.apache.org/viewcvs?rev=306964&view=rev

[Sunitha Kambhampati]

Thanks Kathey for merging this fix to 10.1
The revision in the earlier comment is for derby504.

DERBY562 - New Revision 306965.
The url is: http://svn.apache.org/viewcvs.cgi?rev=306965&view=rev

Verified the fix on 10.1 codeline and trunk ( 10.2).

svn revision
10.1.1.2 - #306965
10.2.0.0(trunk) - 292830

[Sunitha Kambhampati]

Fix was verified and bug resolved, so closing the bug.

I think it would be good to add information about stream length when using the stream api's to the http://db.apache.org/derby/papers/JDBCImplementation.html. I'll address these clarifications to this paper along with some changes that I am working on for DERBY-500.