Driver version or jar name

6.2.2.jre8. Also happens in 6.3.5 and 6.2.0.jre8. It does NOT happen with 6.1.0.jre8 so it seems like a regression.

SQL Server version

2014

Client operating system

Mac and Windows. Does not matter.

Java/JVM version

1.8.0_45-b14

Table schema

create table [contentdata] (
[id] varchar(255) not null,
[dtype] varchar(255) not null,
[name] varchar(255),
[type] varchar(255),
[size] numeric(19,0),
[data] varbinary(max),
primary key ([id])
);

The data column is the important one.

Problem description

This seems to be an issue with 6.2 and later driver.

Attempting to read from the binary stream of a blob for a varbinary column with more than 40-60 kb of content throws SQL exception: "The TDS protocol stream is not valid". Trying blob.length() first also throws the same exception.

Expected behavior and actual behavior

Should be able to read from the binary stream. Worked ok in v 6.1.

Repro code

This is the simplest plain vanilla jdbc code that illustrates the issue. The main point is that the blob's stream is no longer valid after closing the resultset when using v6.2 and later of the mssql jdbc driver whereas in v6.1 the stream is still valid and can be read from after resultset/statement close. From my understanding, the "A Blob object is valid for the duration of the transaction in which is was created".

Code:

package com.gauss.forms.web;

import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JdbcBlobTester {

public static void main(String[] args) {
	// 
	Connection connection = null;  
    Statement statement = null;   
    ResultSet rs = null;  
	try {
		Class cls = Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		
		Driver driver = (Driver)cls.newInstance();
		System.out.println("Driver version: " + driver.getMajorVersion() + "." + driver.getMinorVersion());
		
		String connectionString = "some connection string;";
		
		connection = DriverManager.getConnection(connectionString, "user", "password");  
		connection.setAutoCommit(false);
		
		String selectSql = "SELECT ...";  
        statement = connection.createStatement();  
        rs = statement.executeQuery(selectSql);  

        // Print results from select statement  
        rs.next();  

        //System.out.println(rs.getString(1));
        Blob b = rs.getBlob("data");
        
        InputStream is = b.getBinaryStream();
        System.out.println("First byte: "+is.read());
        
        rs.close();
        rs = null;
        System.out.println("rs closed");
        statement.close();
        statement = null;
        System.out.println("Statement closed");
        System.out.println("Second byte: "+is.read());  // for driver v 6.2 and later, exception here
            
	}
	catch (ClassNotFoundException cnfe) {
		System.out.println("ClassNotFoundException");
	}
	catch (Exception e) {  
        e.printStackTrace();  
    }  
    finally {  
        // Close the connections after the data has been handled.  
        if (rs != null) try { rs.close(); } catch(Exception e) {}  
        if (statement != null) try { statement.close(); } catch(Exception e) {}  
        if (connection != null) try { connection.close(); } catch(Exception e) {}  
    } 	}

}

This is the output when running using driver v6.2.2.jre8:

Driver version: 6.2
First byte: 37
rs closed
Statement closed
Nov 30, 2017 9:07:13 AM com.microsoft.sqlserver.jdbc.TDSReader throwInvalidTDS
SEVERE: ConnectionID:1 ClientConnectionId: fb9d035c-e266-4fd2-b108-8547683257ce got unexpected value in TDS response at offset:3808
java.io.IOException: The TDS protocol stream is not valid.
at com.microsoft.sqlserver.jdbc.PLPInputStream.readBytes(PLPInputStream.java:304)
at com.microsoft.sqlserver.jdbc.PLPInputStream.read(PLPInputStream.java:223)
at com.gauss.forms.web.JdbcBlobTester.main(JdbcBlobTester.java:48)

When running using driver v6.1.0.jre8:

Driver version: 6.1
First byte: 37
rs closed
Statement closed
Second byte: 80