Récupération de ParameterMetaData via useFmtOnly

  • Article

Télécharger le pilote JDBC

Le pilote Microsoft JDBC pour SQL Server propose un autre moyen d’interroger les métadonnées des paramètres à partir du serveur, useFmtOnly. Cette fonctionnalité a été introduite pour la première fois dans la version 7.4 du pilote, et est requise comme solution de contournement pour les problèmes connus dans sp_describe_undeclared_parameters.

Le pilote utilise principalement la procédure stockée sp_describe_undeclared_parameters pour interroger les métadonnées de paramètre. Cette procédure est l’approche recommandée pour la récupération des métadonnées de paramètre dans la plupart des circonstances. Toutefois, l’exécution de la procédure stockée échoue actuellement dans les cas d’utilisation suivants :

  • Par rapport à des colonnes Always Encrypted
  • Par rapport aux tables temporaires et aux variables de table
  • Par rapport aux vues

La solution proposée pour ces cas d’utilisation consiste à analyser la requête SQL de l’utilisateur pour les paramètres et les cibles de la table, puis à exécuter une requête SELECT avec FMTONLY activé. L’extrait de code suivant permet de visualiser la fonctionnalité.

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

Activation/désactivation de la fonctionnalité

La fonctionnalité useFmtOnly est désactivée par défaut. Les utilisateurs peuvent activer cette fonctionnalité par le biais de la chaîne de connexion en spécifiant useFmtOnly=true. Par exemple : jdbc:sqlserver://<server>:<port>;encrypt=true;databaseName=<databaseName>;user=<user>;password=<password>;useFmtOnly=true;.

La fonctionnalité est également disponible avec 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 fonctionnalité est aussi disponible au niveau de l’instruction. Les utilisateurs peuvent activer ou désactiver la fonctionnalité par le biais de PreparedStatement.setUseFmtOnly(boolean).

Notes

Le pilote donne la priorité à la propriété de niveau instruction par rapport à celle de niveau connexion.

Utilisation de la fonctionnalité

Une fois activé, le pilote commence à utiliser la nouvelle fonctionnalité en interne au lieu de sp_describe_undeclared_parameters lors de l’interrogation des métadonnées de paramètre. Aucune autre action n’est nécessaire de la part de l’utilisateur 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'
    }
}

Notes

La fonctionnalité prend uniquement en charge les requêtes SELECT/INSERT/UPDATE/DELETE. Les requêtes doivent commencer par l’un des 4 mots clés pris en charge ou une expression de table commune suivie de l’une des requêtes prises en charge. Les paramètres dans les expressions de table communes ne sont pas pris en charge.

Problèmes connus

Il existe actuellement des problèmes avec la fonctionnalité, liés à des lacunes dans la logique d’analyse SQL. Ces problèmes pourraient être résolus dans une prochaine mise à jour de la fonctionnalité et sont décrits ci-dessous, avec des suggestions de solutions de contournement.

R. Utilisation d’un alias ’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 > ?;

B. Nom de colonne ambigu lorsque les tables ont des noms de colonnes partagés

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 à partir d’une sous-requête avec des paramètres


CREATE TABLE Foo(c1 int)

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

--Workaround: N/A

D. Sous-requêtes dans une clause 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 = ?;

Voir aussi

Définition des propriétés de connexion