Recupero di ParameterMetaData tramite useFmtOnly

Articolo
06/02/2023

Microsoft JDBC Driver per SQL Server include un modo alternativo per eseguire query sui metadati di parametro dal server, ovvero useFmtOnly. Questa funzionalità è stata introdotta per la prima volta nella versione 7.4 del driver ed è necessaria come soluzione alternativa per i problemi noti in sp_describe_undeclared_parameters.

Il driver usa principalmente la stored procedure sp_describe_undeclared_parameters per eseguire query sui metadati dei parametri. Questa procedura rappresenta l'approccio consigliato per i recupero dei metadati sui parametri nella maggior parte delle circostanze. Tuttavia, l'esecuzione della stored procedure attualmente non riesce nei casi d'uso seguenti:

Su colonne Always Encrypted
Su tabelle temporanee e variabili di tabella
Sulle viste

La soluzione suggerita per questi casi d'uso consiste nell'analizzare la query SQL dell'utente per ottenere parametri e destinazioni di tabella e quindi nell'eseguire una query SELECT con FMTONLY abilitato. Il frammento di codice seguente consente di visualizzare la funzionalità.

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

Attivazione/disattivazione della funzionalità

Per impostazione predefinita, la funzionalità useFmtOnly è disattivata. Gli utenti possono abilitarla tramite la stringa di connessione specificando useFmtOnly=true. Ad esempio: jdbc:sqlserver://<server>:<port>;encrypt=true;databaseName=<databaseName>;user=<user>;password=<password>;useFmtOnly=true;.

La funzionalità è disponibile anche tramite 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
}

La funzionalità è disponibile anche a livello di istruzione. Gli utenti possono attivare/disattivare la funzionalità tramite PreparedStatement.setUseFmtOnly(boolean).

Nota

Il driver darà la priorità alla proprietà a livello di istruzione rispetto a quella a livello di connessione.

Uso della funzionalità

Dopo che è stata abilitata, il driver verrà avviato internamente usando la nuova funzionalità anziché sp_describe_undeclared_parameters al momento di eseguire query sui metadati di parametro. Non sono necessarie altre azioni da parte dell'utente finale.

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

Nota

La funzionalità supporta solo query SELECT/INSERT/UPDATE/DELETE. Le query devono iniziare con una delle quattro parole chiave supportate o un'espressione di tabella comune seguita da una delle query supportate. I parametri all'interno delle espressioni di tabella comuni non sono supportati.

Problemi noti

Esistono attualmente alcuni problemi con la funzionalità causati da lacune nella logica di analisi di SQL. Questi problemi potranno essere risolti in un aggiornamento futuro della funzionalità e sono documentati di seguito insieme ai suggerimenti sulle soluzioni alternative.

R. Uso di un alias "dichiarato con prototipo"

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 di colonna ambiguo quando le tabelle hanno nomi di colonna condivisi

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 da una sottoquery con parametri


CREATE TABLE Foo(c1 int)

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

--Workaround: N/A

D. Sottoquery in una clausola 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 = ?;

Vedi anche

Impostazione delle proprietà delle connessioni

Condividi tramite