Using table-valued parameters with SQL Server JDBC

Question

Could anyone provide some guidance on how to use Table Value Parameters (TVP) with SQL Server JDBC? I am using the 6.0 version of the SQL Server driver provided by Microsoft, and I have reviewed the official documentation as well as the more helpful example

How to pass Table-Valued parameters from java to sql server stored procedure?

Both of the examples show getting a SQLServerPreparedStatement casted object from a Connection.prepareStatement call in order to call the setStructured method. Wouldn't this prevent usage of standard connection pools such as DBCP2?

I noticed a comment from the other Stack Overflow comment saying it might be possible to use the stmt.setObject method instead:

As an alternative to casting the PreparedStatement, you can pass the SQLServerDataTable instance to the PreparedStatement.setObject(int,Object) method. This worked for a TVP type that was defined in the dbo schema. – allenru Jul 15 at 19:18

Though I got an error when I tried this, and the type is in the dbo schema...

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException:  The Table-Valued Parameter must have a valid type name.

Here's my code:

// JAVA
private static void tableParameters(DataSource ds)  throws SQLException {
    final String sql = "EXEC dbo.GetAccountsFromTable @accountIds=?";
    final List<Integer> accountIds = generateIntegers(50, 1_000_000);

    try (Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql))
    {
        SQLServerDataTable accounts = new SQLServerDataTable();
        accounts.addColumnMetadata("item", java.sql.Types.INTEGER);
        for (Integer aid : accountIds)
            accounts.addRow(aid.toString());

        stmt.setObject(1, accounts);

        try (ResultSet rs = stmt.executeQuery())
        {
            while (rs.next()) {
                System.out.println(rs.getInt(1));
            }
        }
    }
}

-- TSQL
create type dbo.IntegerTable AS TABLE (item INT);

CREATE PROCEDURE dbo.GetAccountsFromTable(@accountIds dbo.IntegerTable READONLY)
AS
BEGIN
  IF OBJECT_ID('tempdb..#AccountIds') IS NOT NULL
    DROP TABLE #AccountIds

  CREATE TABLE #AccountIds (id INTEGER)

  INSERT INTO #AccountIds
  SELECT * FROM @accountIds

  SELECT * FROM #AccountIds
END

Any help or guidance is appreciated. Thanks!

Answer 1

Here's the route that I ended up using. DBCP2 has a DelegatingStatement.getInnermostDelegate method to get the PreparedStatement object created by the Microsoft driver. I'm not a huge fan of the hoops needed to jump through - even if error checking was added the code seems brittle. TVP is a SqlServer specific thing so maybe it's not so bad to use the required assumptions and casts.

private static int testTvp(DataSource ds, List<Integer> accountIds)  throws SQLException {
    final String sql = "EXEC dgTest.GetAccountsFromTvp @accountIds=?";

    try (Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql)) {
        DelegatingPreparedStatement dstmt = (DelegatingPreparedStatement)stmt;
        SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement)dstmt.getInnermostDelegate();

        SQLServerDataTable accounts = new SQLServerDataTable();
        accounts.addColumnMetadata("token", java.sql.Types.INTEGER);
        for (Integer aid : accountIds)
            accounts.addRow(aid);

        pstmt.setStructured(1, "dgTest.IntegerTable", accounts);

        //// NOTE: The below works for JTDS driver, official MS driver said no result sets were returned
        //try (ResultSet rs = pstmt.executeQuery()) {
        //  return sumInts(rs);
        //}

        if (pstmt.execute()) {
            try (ResultSet rs = pstmt.getResultSet()) {
                return sumInts(rs);
            }
        }
        return -1;
    }
}