JDBC - varbinary(max) out parameter of stored procedure is truncated to 8000 bytes

Question

I'm using Spring Data JPA 1.10.2 with jTds 1.3.1 to call a stored procedure.

The stored procedure has an out parameter, @data of type varbinary(max). @data contains about ~10,000 bytes.

Here's the stored procedure signature:

ALTER procedure [User].[pTest]
      @data varbinary(max) out
      ,@name nvarchar(max) = null
      ,@date datetime
as
begin
.
.
.

My entity class is:

@Entity
@NamedStoredProcedureQuery(name = "User.getUser", procedureName = "User.pTest", parameters = {
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "data", type = byte[].class),  
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "name", type = String.class),
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "date", type = Date.class)
          })
@Data //lombok
public class User {


    // serves no purpose other than to meet
    // JPA requirement
    @Id
    private Long id;
}

The repository code is

public interface UserRepository  extends Repository<User, Long> {

    @Procedure("User.pTest")
    byte[] getUserSession(@Param("name") String name, 
            @Param("date") Date date
            );

}

My test code is as follows and when I run it I get the error:

@Test
public void testGettingApxSession() {
    Calendar cal = new GregorianCalendar(2016,6,5);
    byte[] b = userRepository.getUserSession("myName", cal.getTime());
}

When I log out @data using:

    StringBuilder sb = new StringBuilder();
    for (byte a : b) {
        sb.append(String.format("%02X ", a));
    }

I noticed that only 8,000 bytes are being returned. When I run the same stored procedure in SQL Server Management Studio, I notice it has ~10,000 bytes and ends as it should with hex code FFFF.

So, it appears my results are being truncated when running the stored procedure from my Java app.

How can I prevent this truncation? Is there a different data type that I should be specifying for varbinary(max) instead of byte[]?

Update

I've also tried Microsoft's JDBC driver (v 6.0.7728.100) and experience the same issue. My guess is that the JDBC drivers (I think) are taking the max to be 8000 based on that being the max number, n, you can specify in varbinary(n). However, the max capacity of varbinary is much greater than 8000 and is specified by varbinary(max). varbinary(max) can hold 2^31 - 1 bytes. See my question and other's answers here

Answer 1

Use Microsoft's JDBC driver and specify the output parameter type as a Blob:

@Entity
@NamedStoredProcedureQuery(name = "User.getUser", procedureName = "User.pTest", parameters = {
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "data", type = Blob.class),  
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "name", type = String.class),
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "date", type = Date.class)
          })

Note: This will not work with jTDS. It will still truncate anything > 8000 bytes.

If the Blob needs to be passed back to SQL Server, you must convert back to a byte array as follows:

byte[] bytes = blob.getBytes(1, (int) blob.length());