如何使用JTDS将表值参数传递给存储过程？

Question

            Connection con = ds.getConnection();
            CallableStatement call = con.prepareCall("{call getErrosSKF ?,?,?,?}");
            call.setString("agrupador", agrupador);
            call.setDate(2, new Date(dataInicio.getTimeInMillis()));
            call.setDate(3, new Date(dataFim.getTimeInMillis()));   

            SQLServerDataTable areas = new SQLServerDataTable();
            areas.addColumnMetadata("id",Types.INTEGER);
            for(Integer a : skfIdAreas) {
                areas.addRow(a);
            }
             // generate an exception
            ((SQLServerCallableStatement) call).setStructured(4, "erroSKFTable", areas);

Answer 1

对表值参数（TVPs）的支持是微软的SQL Server JDBC驱动程序最近增加的一项内容，事实上，根据...

JDBC驱动程序的系统要求

...微软目前可用的JDBC驱动程序都不能与JRE6一起工作。您可以尝试从Microsoft找到一个古老的JDBC驱动程序，但它无论如何都不直接支持TVPs，所以您还不如坚持使用JTDS。

null

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

...用户定义的表类型...

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)
)

...和将该表类型作为参数的存储过程...

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

...下面的Java代码...

// 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"));
}

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