Using table-valued parameters with SQL Server JDBC
<p>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 <a href="https://msdn.microsoft.com/en-us/library/mt651781(v=sql.110).aspx" rel="nofollow noreferrer">official documentation</a> as well as the more helpful example </p>

<p><a href="https://stackoverflow.com/questions/36575529/how-to-pass-table-valued-parameters-from-java-to-sql-server-stored-procedure">How to pass Table-Valued parameters from java to sql server stored procedure?</a></p>

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

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

<blockquote>
  <p>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</p>
</blockquote>

<p>Though I got an error when I tried this, and the type <em>is</em> in the dbo schema...</p>

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

<p>Here's my code:</p>

<pre><code>// JAVA
private static void tableParameters(DataSource ds)  throws SQLException {
    final String sql = "EXEC dbo.GetAccountsFromTable @accountIds=?";
    final List&lt;Integer&gt; 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
</code></pre>

<p>Any help or guidance is appreciated.  Thanks!</p>