Call Stored Procedure with Table-Valued Parameter from Java

How to pass a table-valued parameter to a stored procedure using jTDS?

Support for table-valued parameters (TVPs) is a fairly recent addition to Microsoft's JDBC Driver for SQL Server, and in fact, according to ...

System Requirements for the JDBC Driver

... none of the currently available JDBC drivers from Microsoft will work with JRE_6. You could try and find an ancient JDBC driver from Microsoft, but it wouldn't directly support TVPs anyway, so you might as well stick with jTDS.

Now jTDS doesn't directly support TVPs either, but you can accomplish your task by

1. creating a temporary table,
2. populating the temporary table with your data, and then
3. using an anonymous code block to populate a TVP from the temporary table and then call the stored procedure.

Example:

Given an existing reference table named [phonetic] ...

letter  word
------  -------
A       Alfa
B       Bravo
C       Charlie
...
Y       Yankee
Z       Zulu

... a user-defined table type ...

CREATE TYPE [dbo].[LetterListTableType] AS TABLE(
    [seq] [int] NOT NULL,
    [letter] [varchar](1) NOT NULL,
    PRIMARY KEY CLUSTERED 
(
    [seq] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)

... and a stored procedure that takes that table type as a parameter ...

CREATE PROCEDURE [dbo].[GetPhonetic] 
    @letters dbo.LetterListTableType READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SELECT l.seq, l.letter, p.word 
        FROM phonetic p INNER JOIN @letters l on p.letter = l.letter
        ORDER BY l.seq;
END

... the following Java code ...

// 0. create some test data
String[] letters = new String[] { "G", "o", "r", "d" };
//
// 1. create temporary table
Statement s = conn.createStatement();
s.executeUpdate("CREATE TABLE #tmp (seq INT IDENTITY PRIMARY KEY, letter VARCHAR(1))");
//
// 2. populate temporary table with letters to look up
PreparedStatement ps = conn.prepareStatement("INSERT INTO #tmp (letter) VALUES (?)");
for (String letter : letters) {
    ps.setString(1, letter);
    ps.addBatch();
}
ps.executeBatch();
//
// 3. use anonymous code block to pass table-valued parameter to stored procedure
String sql = 
          "SET NOCOUNT ON; "
        + "DECLARE @tvp dbo.LetterListTableType; "
        + "INSERT INTO @tvp (seq, letter) SELECT seq, letter FROM #tmp; "
        + "EXEC dbo.GetPhonetic @tvp; ";
ResultSet rs = s.executeQuery(sql);
while (rs.next()) {
    System.out.printf("%s -> %s%n", rs.getString("letter"), rs.getString("word"));
}

... produces

G -> Golf
o -> Oscar
r -> Romeo
d -> Delta

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

With the inputs provided by Mark Rotteveel I was able to do it. Thanks Mark, Sean thanks for your input as well. Here is the working code for any of you that may find it useful.

String jdbcurl = "jdbc:sqlserver://TestServer:1433;DatabaseName=Student";
connection = DriverManager.getConnection(jdbcurl,"username","password");

SQLServerDataTable stuTypeDT = new SQLServerDataTable(); 
stuTypeDT.addColumnMetadata("StudentId", java.sql.Types.NUMERIC);
stuTypeDT.addColumnMetadata("Name", java.sql.Types.VARCHAR);
stuTypeDT.addColumnMetadata("Department", java.sql.Types.VARCHAR);
stuTypeDT.addColumnMetadata("Address", java.sql.Types.VARCHAR);

stuTypeDT.addRow("1","Tom", "A", "123 Street");
stuTypeDT.addRow("2","Jery", "B", "456 Street");
stuTypeDT.addRow("3","Mac", "C", "Vancour");

String ececStoredProc = "EXEC InsertStudentInfo ?";
SQLServerPreparedStatement pStmt = (SQLServerPreparedStatement)connection.prepareStatement(ececStoredProc);
pStmt.setStructured(1, "dbo.StudentInfoType", stuTypeDT);
pStmt.execute();

Using table-valued parameters with SQL Server JDBC

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

Table-valued parameters into stored procedure using Hibernate

I was unable to find a solution for this problem as written. My work-around was to copy the entirety of the stored procedure into a string in Java. In the above example, that would mean that I replaced:

String fakeQueryStr = "call TestStringListType :list";

with

String fakeQueryStr = "SELECT * FROM :list";

In my actual code, this was undesirable, because the stored procedure was a significantly longer set of statements, but it does still work when wrapped in BEGIN and END within the string.

Call stored procedure passing table type argument

The table typed input parameter cannot be created outside of SAP HANA.
For client applications, one way to still use table typed parameters is to use temporary tables that have the same structure as the parameter table.

Your JAVA application would then first fill the temporary table and call the procedure in a second step

String storedProcedure = "{call UPDATE_GSTR("<temp_table_name>",?)}";

Passing a user-defined table type to a SQL Server stored procedure using JDBC

Yes, it is now possible. Version 6.0 of Microsoft's JDBC driver for SQL Server added support for table-valued parameters.

The following code sample shows how to

• use a SQLServerDataTable object to hold the table data to be passed, and
• call the SQLServerCallableStatement#setStructured method to pass that table to the stored procedure.

SQLServerDataTable sourceDataTable = new SQLServerDataTable();   
sourceDataTable.addColumnMetadata("SDate", java.sql.Types.DECIMAL);
sourceDataTable.addColumnMetadata("EDate", java.sql.Types.DECIMAL);
sourceDataTable.addColumnMetadata("PlantCode", java.sql.Types.NVARCHAR);
sourceDataTable.addColumnMetadata("LoadType", java.sql.Types.NCHAR);
sourceDataTable.addColumnMetadata("Asset", java.sql.Types.BIGINT);

// sample data
sourceDataTable.addRow(123, 234, "Plant1", "Type1", 123234);   
sourceDataTable.addRow(456, 789, "Plant2", "Type2", 456789);   

try (CallableStatement cs = conn.prepareCall("{CALL dbo.RegisterInitAssets (?)}")) {
    ((SQLServerCallableStatement) cs).setStructured(1, "dbo.INITVALS_MSG", sourceDataTable);
    boolean resultSetReturned = cs.execute();
    if (resultSetReturned) {
        try (ResultSet rs = cs.getResultSet()) {
            rs.next();
            System.out.println(rs.getInt(1));
        }
    }
}

For more details, see the following MSDN article:

Using Table-Valued Parameters

---

Related Topics