Reading data from a clob column not possible AFTER the result set has been closed. ("The TDS protocol stream is not valid")

[mheiss]

Driver version or jar name

6.2.1
6.5.1 (preview)

SQL Server version

SQL Server 2017

Client operating system

Windows 10 x64

Java/JVM version

JDK8u121 x64

Table schema

create table Test (
id numeric(18,0) not null,
data nvarchar(max)
);
prepareTestData.txt

Problem description

Attempting to create a character stream or an ascii stream of a clob column fails when the result set is already closed.

Expected behavior and actual behavior

Reading from a clob column should be possible even in case that the result set is already closed.

A similar issue has been reported for BLOB columns that has recently been fixed: "The TDS protocol stream is not valid" exception when streaming larger content as blob from varbinary(max) column

I assume this issue has the same root cause. The BLOB case is working again using driver version 6.5.1 and now only CLOB columns are broken.

Repro code

package com.wamas.platform.jdbc.tests;

import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JdbcClobTest {

    private final static String CONNECTION_STRING = "jdbc:sqlserver://<>";
    private final static String USER = "<>";
    private final static String PWD = "<>";

    public static void main(String[] args) throws Exception {
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            connection = DriverManager.getConnection(CONNECTION_STRING, USER, PWD);
            connection.setAutoCommit(false);

            // Select the desired data. 
            // See the attachment how to create the table and insert the data
            String selectSql = "SELECT data from Test where id= 1";
            statement = connection.createStatement();
            rs = statement.executeQuery(selectSql);
            rs.next();

            // Access the clob column
            Clob c = rs.getClob("data");
            System.out.println("Get clob column");

            // Free the result set
            rs.close();
            System.out.println("Rs closed");
            statement.close();
            System.out.println("Statement closed");

            // Attempt to read data
            Reader is = c.getCharacterStream();
            System.out.println("Reading data: " + is.read()); 
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
    }
}

Output from the sample provided above:

Get clob column
Rs closed
Statement closed
Apr 11, 2018 8:15:23 AM com.microsoft.sqlserver.jdbc.TDSReader throwInvalidTDS
SEVERE: ConnectionID:1 ClientConnectionId: b43c3187-0de1-48b1-81ca-5060b71d69eb got unexpected value in TDS response at offset:7992
Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The TDS protocol stream is not valid.
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2670)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2658)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.throwInvalidTDS(SQLServerConnection.java:2640)
	at com.microsoft.sqlserver.jdbc.TDSReader.throwInvalidTDS(IOBuffer.java:6414)
	at com.microsoft.sqlserver.jdbc.TDSReader.readBytes(IOBuffer.java:6715)
	at com.microsoft.sqlserver.jdbc.TDSReader.readWrappedBytes(IOBuffer.java:6735)
	at com.microsoft.sqlserver.jdbc.TDSReader.readInt(IOBuffer.java:6682)
	at com.microsoft.sqlserver.jdbc.TDSReader.readUnsignedInt(IOBuffer.java:6696)
	at com.microsoft.sqlserver.jdbc.PLPInputStream.readBytesInternal(PLPInputStream.java:326)
	at com.microsoft.sqlserver.jdbc.PLPInputStream.getBytes(PLPInputStream.java:139)
	at com.microsoft.sqlserver.jdbc.SQLServerClobBase.getStringFromStream(SQLServerClob.java:332)
	at com.microsoft.sqlserver.jdbc.SQLServerClobBase.getCharacterStream(SQLServerClob.java:237)
	at com.microsoft.sqlserver.jdbc.SQLServerClob.getCharacterStream(SQLServerClob.java:35)
	at com.wamas.platform.jdbc.tests.JdbcClobTest.main(JdbcClobTest.java:44)

[cheenamalhotra]

Hi @mheiss

Thanks for catching that, implementation's under way - we'll let you know soon!

[rene-ye]

Hi @mheiss, PR #682 has been created to address this issue. I will also attach jars below in case you'd like to test the changes for yourself.
Jars for testing.zip

[mheiss]

Hi @rene-ye, I can confirm that the fix is working as expected with the provided JARS. Thanks for the support :)

[rene-ye]

#682 has been merged. Closing the issue.