Получение ParameterMetaData через useFmtOnly

  • Статья

Скачать драйвер JDBC

Драйвер Microsoft JDBC для SQL Server содержит альтернативный способ запроса метаданных параметров из сервера — useFmtOnly. Эта функция впервые появилась в драйвере версии 7.4 и является обязательной для решения известных проблем в sp_describe_undeclared_parameters.

Для запроса метаданных параметров драйвер в основном использует хранимую процедуру sp_describe_undeclared_parameters. Ее рекомендуется использовать в большинстве случаев. Однако выполнение хранимой процедуры завершается ошибкой в следующих случаях:

  • Для столбцов Always Encrypted
  • Для временных таблиц и табличных переменных
  • Для представлений

Предлагаемое решение для этих вариантов использования — анализ SQL-запроса пользователя для параметров и таблиц целевых объектов, а затем выполнение запроса SELECT с включенным FMTONLY. Следующий фрагмент кода поможет визуализировать эту функцию.

--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. Использование псевдонима "forward declared"

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

Б. Неоднозначное имя столбца, если у таблиц есть имена общих столбцов

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

В. ВЫБОР из вложенного запроса с параметрами


CREATE TABLE Foo(c1 int)

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

--Workaround: N/A

Г. Вложенные запросы в предложении 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 = ?;

См. также

Настройка свойств подключения