Partilhar via

Recuperação de ParameterMetaData por meio de useFmtOnly

Baixar driver JDBC

O Microsoft JDBC Driver para SQL Server inclui uma forma alternativa de consultar metadados de parâmetros do servidor, useFmtOnly. Esta funcionalidade foi introduzida pela primeira vez na versão 7.4 do driver e é necessária como solução alternativa para problemas conhecidos em sp_describe_undeclared_parameters.

O driver utiliza principalmente o procedimento sp_describe_undeclared_parameters armazenado para consultar Metadados de Parâmetros. Este procedimento é a abordagem recomendada para a recuperação de Metadados de Parâmetro na maioria das circunstâncias. No entanto, a execução do procedimento armazenado falha atualmente nos seguintes casos de uso:

  • Contra colunas Always Encrypted
  • Contra tabelas temporárias e variáveis de tabela
  • Opiniões contrárias

A solução proposta para estes casos de uso é analisar a consulta SQL do utilizador para parâmetros e alvos de tabela, e depois executar uma SELECT consulta com FMTONLY ativado. O seguinte excerto ajudará a visualizar a funcionalidade.

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

Ativar/desligar a funcionalidade

A funcionalidade useFmtOnly está desligada por defeito. Os utilizadores podem ativar esta funcionalidade através da cadeia de ligação especificando useFmtOnly=true. Por exemplo: jdbc:sqlserver://<server>:<port>;encrypt=true;databaseName=<databaseName>;user=<user>;password=<password>;useFmtOnly=true;.

A funcionalidade também está disponível através de 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
}

A funcionalidade também está disponível ao nível da Declaração. Os utilizadores podem ativar/desligar a funcionalidade através de PreparedStatement.setUseFmtOnly(boolean).

Observação

O driver irá priorizar a propriedade de nível de Instrução em detrimento da propriedade de nível de Conexão.

Utilização da funcionalidade

Uma vez ativado, o driver começará a utilizar internamente a nova funcionalidade em vez de sp_describe_undeclared_parameters ao consultar os Metadados de Parâmetros. Não é necessária nenhuma ação adicional por parte do utilizador final.

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'
    }
}

Observação

Esta funcionalidade suporta apenas SELECT/INSERT/UPDATE/DELETE consultas. As consultas devem começar com uma das 4 palavras-chave suportadas ou uma Expressão de Tabela Comum, seguida de uma das consultas suportadas. Parâmetros dentro das Expressões de Tabela Comum não são suportados.

Problemas conhecidos

Atualmente, existem alguns problemas com esta funcionalidade, que são causados por lacunas na lógica de análise SQL. Estes problemas podem ser resolvidos numa futura atualização da funcionalidade, e estão documentados abaixo juntamente com sugestões de soluções alternativas.

A. Uso de um alias 'previamente declarado'

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. Nome de Coluna ambíguo quando as tabelas partilham nomes de colunas

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 a partir de uma subconsulta com parâmetros


CREATE TABLE Foo(c1 int)

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

--Workaround: N/A

D. Subconsultas numa cláusula 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 = ?;

Consulte também

Definindo as propriedades da conexão

  • Last updated on