Hibernate and BlobStreams causing "The TDS protocol stream is not valid" exception

[Kinchkun]

Driver version or jar name

6.1.6.jre8-preview and higher

hibernate: 5.2.13.Final'

SQL Server version

MSSQL Server 2012

Client operating system

MacOs and Windows

Java/JVM version

Java 1.8.0 r144

Problem description

It is not possible to get mssql-jdbc, hibernate and blob streaming to work.

We store and retrieve large data (mostly images) from the database. Loading the data completely into memory will cause a Out of Memory Exception.

Using Blob and getBinaryStream will the cause Exception "The TDS protocol stream is not valid". This behavior is reproducible since 6.1.6.jre8-preview. The version 6.1.5.jre8-preview works, but the blob is hold completely into memory causing the mentioned OoM-Exceptions.

I think the issue is related to: #567 and #16

As far as I understand, a workaround would be not to close the resultset. However, these are handled by hibernate.

Expected behavior and actual behavior

I would assume, that I can read the blob stream to the very end. Instead reading the stream will throws exception.

Repro code

We have an entity with just a blob and an id.

@Entity
@Table (
        name = "blobs"
)
@NamedQueries({
        @NamedQuery(name = "findById", query = "SELECT b FROM BlobEntity b WHERE b.id like :id"),
})

public class BlobEntity {
    @Id
    @GeneratedValue(strategy= GenerationType.AUTO)
    private long id;

    @NotNull
    @Column
    private Blob blob;

    public Blob getBlob(){
        return blob;
    }

    public BlobEntity setBlob(Blob blob) {
        this.blob = blob;
        return this;
    }

    public long getId() {
        return id;
    }
}

Now, retrieving the blob and writing the stream to file will cause the exception:

Transaction tx;

try (Session session = sessionFactory.openSession()) {
  tx = session.beginTransaction();

  BlobStoreEntity result = (BlobStoreEntity) session.getNamedQuery("findById")
                    .setParameter("id", 11).uniqueResult();

  tx.commit();
           
  Files.copy(
    result.getBlob().getBinaryStream(), 
    Paths.get("/tmp/somepath"));
} catch (Exception e) {
  e.printStackTrace(); // The TDS protocol stream is not valid" exception
}

[rene-ye]

Hi @Kinchkun, we are aware of this issue and a fix is undergoing testing. The changes proposed may be breaking and we have decided to not release it until we are fully confident of its stability. If you would like to test the changes proposed, you can grab a copy here, and follow PR #595.

[Kinchkun]

Hi @rene-ye,

thanks for your fast reply! I already discovered your branch. But based on your description I assumed you would read the content of the blob-stream into a byte[], which would caused a OoM-Exception in our case. But maybe I misread the fillBlobs function. I will try out our branch as soon as possible.

[rene-ye]

Hi @Kinchkun, streaming a blob from the database is only possible while the resultset (RS) is open, the blob itself does not maintain a connection to the database and therefore does not maintain its own stream. As per JDBC specs, the blob created from the RS should still be available after the RS closes, which is why we recently implemented functionalities to fill the blob into a byte[]. Freeing the blob with Blob.free() when you finish reading it will prevent the blob from being populated after the stream is closed. Please note that moving the cursor of the RS, e.g. by getting another blob, will also populate the blob as the active stream will close.

Edit: I am not incredibly familiar with Hibernate, but I believe moving tx.commit() after the filecopy block and freeing the blob after copying should solve your problems if using the proposed blob fixes.

[rene-ye]

PR #595 has been merged and can be expected in 6.5.0. Closing issue.

[Lonzak]

@cheenamalhotra I have a question: I don't see a final release of 6.5. Did all the changes of 6.5.X went into version 7.0?