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!

score 3 · Accepted Answer · answered Dec 21 '16 at 14:49

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;
    }
}

score 1 · Answer 2 · answered Nov 02 '16 at 21:05

re: using PreparedStatement#setObject

I fooled around with it a bit and I could not get it to work either. Perhaps it was a feature in the 6.0 preview that got pulled from the final release.

re: using a SQLServerPreparedStatement object

I don't see why that would prevent using DBCP2 (or any other connection pool) per se, although it might have implications for DBCP2's poolPreparedStatements option (which is false by default). Still, if one wanted to create a "proper" PreparedStatement object and still needed to use setStructured then this worked fine when I tried it just now:

String sql = "EXEC dbo.GetAccountsFromTable ?";
try (
        Connection conn = DriverManager.getConnection(connectionUrl);
        PreparedStatement stmt = conn.prepareStatement(sql)) {
    SQLServerDataTable accounts = new SQLServerDataTable();
    accounts.addColumnMetadata("item", java.sql.Types.INTEGER);
    accounts.addRow(123);
    accounts.addRow(234);
    ((SQLServerPreparedStatement) stmt).setStructured(1, "dbo.IntegerTable", accounts);
    try (ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getInt(1));
        }
    }
} catch (Exception e) {
    e.printStackTrace(System.err);
}

However, given that we are calling a stored procedure it might be "good form" to use a CallableStatement object (with a quickie cast to SQLServerCallableStatement for the setStructured call) instead of a PreparedStatement object.

Using table-valued parameters with SQL Server JDBC

2 Answers2

Linked