UseFmtOnly を使用した ParameterMetaData の取得Retrieving ParameterMetaData via useFmtOnly

ダウンロードJDBC Driver のダウンロードDownloadDownload JDBC Driver

Microsoft JDBC Driver for SQL Server には、サーバーからパラメーターのメタデータをクエリする別の方法として、useFmtOnly があります。The Microsoft JDBC Driver for SQL Server includes an alternative way to query Parameter Metadata from the server, useFmtOnly. この機能は、ドライバーのバージョン 7.4 で初めて導入されたものであり、sp_describe_undeclared_parameters の既知の問題に対する回避策として必要です。This feature was first introduced in version 7.4 of the driver, and is required as a workaround for known issues in sp_describe_undeclared_parameters.

ドライバーは、主にストアド プロシージャ sp_describe_undeclared_parameters を使用してパラメーターのメタデータのクエリを実行します。これは、ほとんどの状況でパラメーター メタデータの取得に推奨される方法です。The driver primarily uses the stored procedure sp_describe_undeclared_parameters to query Parameter Metadata, as this is the recommended approach for Parameter Metadata retrieval under most circumstances. ただし、現在、ストアド プロシージャの実行は次のユース ケースでは失敗します。However, executing the stored procedure currently fails under the following use cases:

  • Always Encrypted 列に対してAgainst Always Encrypted columns

  • 一時テーブルとテーブル変数に対してAgainst temporary tables and table variables

  • ビューに対してAgainst views

これらのユース ケースに対する提案ソリューションは、ユーザーの SQL クエリでパラメーターとテーブル ターゲットを解析し、FMTONLY が有効になっている SELECT クエリを実行することです。The proposed solution for these use cases is to parse the user's SQL query for parameters and table targets, then execute a SELECT query with FMTONLY enabled. 次のスニペットを使用すると、機能を視覚化できます。The following snippet will help visualize the feature.

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

機能のオン/オフの切り替えTurning the feature on/off

useFmtOnly 機能は既定ではオフになっています。The feature useFmtOnly is off by default. ユーザーは、useFmtOnly=true を指定すると、接続文字列を使用してこの機能を有効にできますUsers can enable this feature through the connection string by specifying useFmtOnly=true. (例: jdbc:sqlserver://<server>:<port>;databaseName=<databaseName>;user=<user>;password=<password>;useFmtOnly=true;)。For example: jdbc:sqlserver://<server>:<port>;databaseName=<databaseName>;user=<user>;password=<password>;useFmtOnly=true;.

または、SQLServerDataSource から機能を使用することができます。Alternatively, the feature is available through 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
}

この機能は、ステートメント レベルでも使用できます。The feature is also available on the Statement level. ユーザーは、PreparedStatement.setUseFmtOnly(boolean) を使用して機能のオン/オフを切り替えることができます。Users can turn the feature on/off through PreparedStatement.setUseFmtOnly(boolean).

注意

ドライバーでは、ステートメント レベル プロパティが "接続レベル" プロパティより優先されます。The driver will prioritize the Statement level property over the Connection level property.

機能の使用Using the feature

有効にすると、ドライバーでは、パラメーターのメタデータのクエリを実行するときに、sp_describe_undeclared_parameters ではなく、新しい機能の使用を内部的に開始します。Once enabled, the driver will internally start using the new feature instead of sp_describe_undeclared_parameters when querying Parameter Metadata. エンド ユーザーがこれ以上操作を行う必要はありません。There is no further action necessary from the end user.

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 クエリのみサポートされます。The feature only supports SELECT/INSERT/UPDATE/DELETE queries. クエリは、サポートされている 4 つのキーワードのいずれか、または共通テーブル式で始まり、サポートされているいずれかのクエリがその後ろに続く必要があります。Queries should start with one of the 4 supported key words or a Common Table Expression followed by one of the supported queries. 共通テーブル式内のパラメーターはサポートされていません。Parameters within Common Table Expressions are not supported.

既知の問題Known issues

現在、機能には、SQL の解析ロジックの欠陥が原因である問題がいくつかあります。There are currently some issues with the feature, which are caused by deficiencies in SQL parsing logic. これらの問題は、機能の今後の更新プログラムで解決される可能性があります。回避策と合わせて以下に説明します。These issues may be addressed in a future update to the feature, and are documented below along with workaround suggestions.

A.A. '前方宣言' のエイリアスを使用しているUsing a 'forward declared' alias

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.B. テーブルに共有列名がある場合、列名があいまいであるAmbiguous Column Name when tables have shared column names

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.C. パラメーターを設定したサブクエリからの SELECTSELECT from a subquery with parameters


CREATE TABLE Foo(c1 int)

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

--Workaround: N/A

D.D. SET 句でのサブクエリSubqueries in a SET clause

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 = ?;

参照See also

接続プロパティの設定Setting the connection properties