Abrufen von ParameterMetaData über useFmtOnly
Der Microsoft JDBC-Treiber für SQL Server bietet eine alternative Möglichkeit zum Abfragen von Parametermetadaten vom Server: useFmtOnly. Diese Funktion wurde erstmals in Version 7.4 des Treibers eingeführt und ist als Problemumgehung für bekannte Probleme in sp_describe_undeclared_parameters erforderlich.
Der Treiber verwendet hauptsächlich die gespeicherte Prozedur sp_describe_undeclared_parameters, um Parametermetadaten abzufragen. In den meisten Fällen wird diese Prozedur zum Abrufen von Parametermetadaten empfohlen. Bei folgenden Anwendungsfällen wird die Ausführung der gespeicherten Prozedur jedoch aktuell mit einem Fehler abgebrochen:
- Bei Always Encrypted-Spalten
- Für temporäre Tabellen und Tabellenvariablen
- Bei Ansichten
In diesen Fällen sollte die SQL-Abfrage des Benutzers auf Parameter und Tabellenziele analysiert und anschließend eine SELECT-Abfrage mit aktiviertem FMTONLY ausgeführt werden. Der folgende Ausschnitt veranschaulicht diese Funktion:
--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;
Aktivieren/Deaktivieren der Funktion
Die Funktion useFmtOnly ist standardmäßig deaktiviert. Benutzer können sie über die Verbindungszeichenfolge aktivieren, indem sie useFmtOnly=true angeben. Beispiel: jdbc:sqlserver://<server>:<port>;encrypt=true;databaseName=<databaseName>;user=<user>;password=<password>;useFmtOnly=true;.
Die Funktion ist auch über SQLServerDataSource verfügbar.
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
}
Die Funktion ist auch auf Anweisungsebene verfügbar. Benutzer können sie über PreparedStatement.setUseFmtOnly(boolean) aktivieren/deaktivieren.
Hinweis
Der Treiber priorisiert die Eigenschaft auf Anweisungsebene gegenüber der Eigenschaft auf Verbindungsebene.
Verwenden der Funktion
Wenn die Funktion aktiviert ist, verwendet der Treiber sie intern beim Abfragen von Parametermetadaten anstelle von sp_describe_undeclared_parameters. Der Endbenutzer muss keine weiteren Schritte ausführen.
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'
}
}
Hinweis
Die Funktion unterstützt ausschließlich SELECT/INSERT/UPDATE/DELETE-Abfragen. Abfragen sollten mit einem der vier unterstützten Schlüsselwörter oder einem allgemeinen Tabellenausdruck beginnen, auf das bzw. den eine der unterstützten Abfragen folgt. Parameter innerhalb von allgemeine Tabellenausdrücken werden nicht unterstützt.
Bekannte Probleme
Es gibt derzeit einige Probleme mit dieser Funktion, die auf Lücken in der SQL-Analyselogik zurückzuführen sind. Diese Probleme werden möglicherweise in einem zukünftigen Update der Funktion behoben und sind im Folgenden mit entsprechenden Vorschlägen für Problemumgehungen dokumentiert.
A. Verwenden eines „vorwärts deklarierten“ 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. Mehrdeutiger Spaltenname, wenn Tabellen gemeinsame Spaltennamen aufweisen
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 aus einer Unterabfrage mit Parametern
CREATE TABLE Foo(c1 int)
SELECT * FROM (SELECT * FROM Foo WHERE c1 = ?) WHERE c1 = ?; --Incorrect syntax near '?'
--Workaround: N/A
D: Unterabfragen in einer SET-Klausel
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 = ?;