通过 useFmtOnly 检索 ParameterMetaData

  • 项目

下载 JDBC 驱动程序

The Microsoft JDBC Driver for SQL Server 提供从服务器查询参数元数据的另一种方法，即 useFmtOnly。 此功能是在驱动程序版本 7.4 中首次引入的，需要作为 sp_describe_undeclared_parameters 中已知问题的解决方法。

驱动程序主要使用存储过程 sp_describe_undeclared_parameters 来查询参数元数据。 在大多数情况下，此过程是用于检索参数元数据的推荐方法。 但是，在以下用例中，当前执行存储过程会失败：

  • 针对 Always Encrypted 列
  • 针对临时表和表变量
  • 针对视图

针对这些用例的建议解决方案是，分析用户的参数和表目标的 SQL 查询，然后在启用 FMTONLY 的情况下执行 SELECT 查询。 以下代码片段具体演示了此功能。

--create a normal table 'Foo' and a temporary table 'Bar'
CREATE TABLE Foo(c1 int);
CREATE TABLE #Bar(c1 int);

EXEC sp_describe_undeclared_parameters N'SELECT * FROM Foo WHERE c1 = @p0' --works fine
EXEC sp_describe_undeclared_parameters N'SELECT * FROM #Bar WHERE c1 = @p0' --fails with "Invalid object name '#Bar'"

SET FMTONLY ON;
SELECT c1 FROM Foo; --works
SET FMTONLY OFF;
SET FMTONLY ON;
SELECT c1 FROM #Bar; --works
SET FMTONLY OFF;

启用/禁用功能

默认情况下，useFmtOnly 功能处于禁用状态。 用户可以通过指定 useFmtOnly=true 在连接字符串中启用此功能。 例如：jdbc:sqlserver://<server>:<port>;encrypt=true;databaseName=<databaseName>;user=<user>;password=<password>;useFmtOnly=true;。

也可通过 SQLServerDataSource 提供该功能。

SQLServerDataSource ds = new SQLServerDataSource();
ds.setServerName(<server>);
ds.setPortNumber(<port>);
ds.setDatabaseName("<databaseName>");
ds.setUser("<user>");
ds.setPassword("<password>");
ds.setUseFmtOnly(true);
try (Connection c = ds.getConnection()) {
    // do work with connection
}

此功能也可在语句级别提供。 用户可以通过 PreparedStatement.setUseFmtOnly(boolean) 启用/禁用该功能。

备注

驱动程序将对“语句级别”属性和“连接级别”属性设置优先级。

使用功能

启用新功能后，驱动程序将在内部开始使用该功能，而不是在查询参数元数据时使用 sp_describe_undeclared_parameters。 最终用户不需要执行任何其他操作。

final String sql = "INSERT INTO #Bar VALUES (?)";
try (Connection c = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
    try (Statement s = c.createStatement()) {
        s.execute("CREATE TABLE #Bar(c1 int)");
    }
    try (PreparedStatement p1 = c.prepareStatement(sql); PreparedStatement p2 = c.prepareStatement(sql)) {
        ((SQLServerPreparedStatement) p1).setUseFmtOnly(true);
        ParameterMetaData pmd1 = p1.getParameterMetaData();
        System.out.println(pmd1.getParameterTypeName(1)); // prints int
        ParameterMetaData pmd2 = p2.getParameterMetaData(); // throws exception, Invalid object name '#Bar'
    }
}

备注

该功能仅支持 SELECT/INSERT/UPDATE/DELETE 查询。 查询应以 4 个支持的关键字之一或公用表表达式开头，后跟一个支持的查询。 不支持使用公用表表达式中的参数。

已知问题

此功能当前存在一些问题，这些问题是由 SQL 分析逻辑中的缺陷导致的。 这些问题可能会在以后对该功能的更新中得到解决，现已在下面列出，并提供了解决方法建议。

A. 使用“前向声明”别名

CREATE TABLE Foo(c1 int)

DELETE fooAlias FROM Foo fooAlias WHERE c1 > ?; --Invalid object name 'fooAlias'

--Workaround #1: Specify AS keyword
DELETE fooAlias FROM Foo AS fooAlias WHERE c1 > ?;
--Workaround #2: Use the table name
DELETE Foo FROM Foo fooAlias WHERE c1 > ?;

B. 当表具有共享的多个列名时，列名不明确

CREATE TABLE Foo(c1 int, c2 int, c3 int)
CREATE TABLE Bar(c1 int, c2 int, c3 int)

SELECT c1,c2 FROM Foo WHERE c3 IN (SELECT c3 FROM Bar WHERE c1 > ? and c2 < ? and c3 = ?); --Ambiguous Column Name

--Workaround: Use aliases
SELECT c1,c2 FROM Foo WHERE c3 IN (SELECT c3 FROM Bar b WHERE b.c1 = ? and b.c2 = ? and b.c3 = ?);

C. 从包含参数的子查询中 SELECT


CREATE TABLE Foo(c1 int)

SELECT * FROM (SELECT * FROM Foo WHERE c1 = ?) WHERE c1 = ?; --Incorrect syntax near '?'

--Workaround: N/A

D. SET 子句中存在子查询

CREATE TABLE Foo(c1 int)

UPDATE Foo SET c1 = (SELECT c1 FROM Foo) WHERE c1 = ?; --Incorrect syntax near ')'

--Workaround: Add a 'delimiting' condition
UPDATE Foo SET c1 = (SELECT c1 FROM Foo HAVING (HASH JOIN)) WHERE c1 = ?;

另请参阅

设置连接属性