TVP varchar(max) fails with ISQLServerDataRecord, works with SQLServerDataTable

[dangets]

Driver version

7.1.1.jre10-preview AND 6.2.2.jre8 (assuming all other versions too)

SQL Server version

Microsoft SQL Server 2014 (SP2-CU11) (KB4077063) - 12.0.5579.0 (X64)
Feb 21 2018 12:19:47
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.3 (Build 9600: )

Client Operating System

Ubuntu 18.04

JAVA/JVM version

10.0.2+13-1ubuntu0.18.04.2

Table schema

create type data.DgTestType AS table (
  fieldA VARCHAR(MAX) NULL
)
grant exec on type :: data.DgTestType to public

create procedure data.SelFromType @tbl data.DgTestType READONLY
AS
BEGIN
  SELECT * FROM @tbl
end

Problem description

Error thrown when trying to use TVP, with string column > 4000 characters. Using SQLServerDataTable works, but ISQLServerDataRecord fails. The code snippet below shows the usages. This relates to #250

Error message is The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Table-valued parameter 0 (""), row 1, column 1: Data type 0xE7 has an invalid data length or metadata length.

Reproduction code

Code is in Kotlin, but is nearly identical to java version.

fun main(args: Array<String>) {
    val ds = devCustCustodyData()

    val paramString = "abcd".repeat(1001)

    val table = SQLServerDataTable()
    table.addColumnMetadata("fieldA", Types.LONGVARCHAR)
    table.addRow(paramString)
    table.tvpName = "data.DgTestType"

    ds.connection.use { conn ->
        conn.prepareStatement("exec data.SelFromType ?").use { pstmt ->
            pstmt.unwrap(SQLServerPreparedStatement::class.java).use { msStmt ->
                msStmt.setStructured(1, "data.DgTestType", MyRow(listOf(paramString)))  // <---- ISQLServerDataRecord fails
                //msStmt.setStructured(1, "data.DgTestType", table)  // <---- SQLServerDataTable works
                msStmt.executeQuery().use { rs ->
                    while (rs.next())
                        println(rs.getString(1).length)
                }
            }
        }
    }

}

class MyRow(values: List<String>) : ISQLServerDataRecord {
    private val iterator = values.iterator()
    private var current: String = ""

    override fun next(): Boolean {
        if (iterator.hasNext()) {
            current = iterator.next()
            return true
        }
        return false
    }

    override fun getRowData(): Array<Any> {
        return arrayOf(current)
    }

    override fun getColumnMetaData(i: Int): SQLServerMetaData {
        if (i == 1)
            return SQLServerMetaData("fieldA", Types.LONGVARCHAR)
        throw IllegalArgumentException("unexpected column index $i")
    }

    override fun getColumnCount(): Int = 1
}

fun devCustCustodyData(): HikariDataSource {
    val windowsUrl = """jdbc:sqlserver://dev-cust-db;database=CustodyData;integratedSecurity=true"""
    val linuxUrl = """jdbc:sqlserver://dev-cust-db;database=CustodyData;integratedSecurity=true;authenticationScheme=JavaKerberos"""
    val url = linuxUrl

    val config = HikariConfig()
    config.dataSourceClassName = "com.microsoft.sqlserver.jdbc.SQLServerDataSource"
    config.addDataSourceProperty("url", url)
    return HikariDataSource(config)
}

[lilgreenbird]

hi @dangets,

Thank you for providing the repro code we will look into this and update you on our findings.

[dangets]

For sure! I can rewrite in Java if it would be more helpful.

[lilgreenbird]

hi @dangets

I was able to make this work by specifying the precision of the column in SQLServerMetaData in getColumnMetaData(). In this case that is the length of the string in the row you are adding. In the case of SQLServerDataTable, the addRow() method fills in the precision for SQLServerMetaData when adding the string, so getColumnMetaData() needs to do the same when constructing the SQLServerMetaData. This is because the driver needs to know the length/precision of the data so it can send the appropriate header to the server.

Please see https://static.javadoc.io/com.microsoft.sqlserver/mssql-jdbc/6.1.6.jre8-preview/com/microsoft/sqlserver/jdbc/SQLServerMetaData.html constructor for SQLServerMetaData which also includes precision:

public SQLServerMetaData(String columnName,
                         int sqlType,
                         int precision,
                         int scale)

creates a new SQLServerMetaData

Parameters:
columnName - the name of the column
sqlType - the SQL type of the column
precision - the precision of the column
scale - the scale of the column

So in your example, if you change to:

return new SQLServerMetaData("fieldA", Types.LONGVARCHAR, getRowData().length , `0);

Then both methods would produce the same results.

Hope this helps, let me know if you have more questions.

[dangets]

The workaround does help, thank you. It still seems like this could/should be something handled by the driver code itself though, and would be more friendly & consistent with the SQLServerDataTable approach.

Otherwise, I would ask that this edge case be documented somewhere like https://docs.microsoft.com/en-us/sql/connect/jdbc/using-table-valued-parameters?view=sql-server-2017 or https://docs.microsoft.com/en-us/sql/connect/jdbc/using-advanced-data-types?view=sql-server-2017

[lilgreenbird]

hi @dangets,

I agree it's a bit non-intuitive, we will look into improving and/or documenting this case. Thank you for your patience.

[ulvii]

Hi @dangets ,
Thank you for bringing this issue to our attention.
We decided to add a new constructor to let the users specify the string length: SQLServerMetaData(String columnName, int sqlType, int length). This constructor should be used for string values of length greater than 4000.