如何将用户定义的表类型传递给Java的MSSQL请求 [英] How to pass userdefined table type to MSSQL request from Java

问题描述

给定一个简单的存储过程，其中一个表有一个整数列作为参数：

Given a simple stored procedure which has a table with a single column of integers as argument:

CREATE PROCEDURE [dbo].[table_sel]
@tbl INT32Table READONLY
AS
BEGIN
SET NOCOUNT ON;
SELECT value FROM @tbl
END

如何执行此存储过程来自Java？从C＃开始，这就是我想要的：

How do I execute this stored procedure from Java? From C# this does what I want:

SqlConnection sqlconn;
System.Data.DataTable tbl = new System.Data.DataTable("INT32Table", "dbo");
tbl.Columns.Add("value", typeof(int));
tbl.Rows.Add(2);
tbl.Rows.Add(3);
tbl.Rows.Add(5);
tbl.Rows.Add(7);
tbl.Rows.Add(11);
using (SqlCommand command = new SqlCommand("table_sel"))
{
    command.Connection = sqlconn;
    command.Parameters.AddWithValue("@tbl", tbl);
    command.CommandType = System.Data.CommandType.StoredProcedure;
    SqlDataReader reader = command.ExecuteReader();

    //do stuff
}

推荐答案

你不能使用JDBC传递TVP。正如他们2年前提到的那样，Microsoft JDBC团队仍在努力解决这个问题。请参阅链接：

You Cannt pass TVP's using JDBC. The Microsoft JDBC team is still working on this as they mentioned 2 years ago. Refer the Link :

通过在过程体中传递XML参数和OPENXML（）来尝试一些替代解决方案，而不是使用TVP 。

Try some alternate solution instead of using TVP's by passing XML parameter and with OPENXML() in procedure body.

这篇关于如何将用户定义的表类型传递给Java的MSSQL请求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！