Pobieranie ParameterMetaData za pomocą useFmtOnly

pobierz sterownik JDBC

Sterownik JDBC firmy Microsoft dla programu SQL Server zawiera alternatywny sposób wykonywania zapytań o metadane parametrów z serwera, useFmtOnly. Ta funkcja została po raz pierwszy wprowadzona w wersji 7.4 sterownika i jest wymagana jako obejście znanych problemów w systemie sp_describe_undeclared_parameters.

Sterownik używa głównie procedury sp_describe_undeclared_parameters składowanej do wykonywania zapytań dotyczących metadanych parametrów. Ta procedura jest zalecaną metodą pobierania metadanych parametrów w większości przypadków. Wykonanie procedury składowanej obecnie kończy się niepowodzeniem w następujących scenariuszach użycia.

• Przeciwko kolumnom Always Encrypted
• Przeciwko tabelom tymczasowym i zmiennym tabelom
• Przeciw poglądom

Proponowanym rozwiązaniem dla tych przypadków użycia jest przeanalizowanie zapytania SQL użytkownika pod kątem parametrów i obiektów docelowych tabeli, a następnie wykonanie zapytania z włączoną obsługą SELECTFMTONLY . Poniższy fragment kodu pomoże zwizualizować funkcję.

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

Włączanie/wyłączanie funkcji

Funkcja useFmtOnly jest domyślnie wyłączona. Użytkownicy mogą włączyć tę funkcję za pomocą parametrów połączenia, określając wartość useFmtOnly=true. Na przykład: jdbc:sqlserver://<server>:<port>;encrypt=true;databaseName=<databaseName>;user=<user>;password=<password>;useFmtOnly=true;.

Funkcja jest również dostępna za pośrednictwem usługi 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
}

Funkcja jest również dostępna na poziomie instrukcji. Użytkownicy mogą włączać/wyłączać funkcję za pomocą funkcji PreparedStatement.setUseFmtOnly(boolean).

Uwaga / Notatka

Sterownik nada priorytet właściwości Poziom instrukcji przed właściwością Poziom połączenia.

Korzystanie z funkcji

Po włączeniu sterownik zacznie używać nowej funkcji zamiast sp_describe_undeclared_parameters podczas wykonywania zapytań dotyczących metadanych parametrów. Użytkownik końcowy nie musi podejmować dalszych działań.

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

Uwaga / Notatka

Funkcja obsługuje tylko zapytania SELECT/INSERT/UPDATE/DELETE. Zapytania powinny zaczynać się od jednego z 4 obsługiwanych słów kluczowych lub wspólnego wyrażenia tabeli , a następnie jednego z obsługiwanych zapytań. Parametry w typowych wyrażeniach tabeli nie są obsługiwane.

Znane problemy

Obecnie występują pewne problemy z funkcją, które są spowodowane lukami w logice analizowania SQL. Te problemy mogą zostać rozwiązane w przyszłej aktualizacji funkcji i zostały opisane poniżej wraz z sugestiami obejścia.

A. Używanie aliasu 'zadeklarowanego wcześniej'

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. Niejednoznaczna nazwa kolumny, gdy tabele mają nazwy kolumn udostępnionych

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. WYBIERANIE z podzapytania z parametrami

CREATE TABLE Foo(c1 int)

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

--Workaround: N/A

D. Podzapytania w klauzuli 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 = ?;

Zobacz także

Ustawianie właściwości połączenia