Utilisation des paramètres table

Télécharger le pilote JDBC

Les paramètres table fournissent un moyen simple de marshaler plusieurs lignes de données d’une application cliente vers SQL Server sans avoir recours à plusieurs allers-retours ou à une logique spéciale côté serveur pour traiter les données. Vous pouvez utiliser des paramètres table pour encapsuler des lignes de données dans une application cliente et envoyer les données au serveur dans une commande paramétrable unique. Les lignes de données entrantes sont stockées dans une variable de table que vous pouvez ensuite utiliser à l’aide de Transact-SQL.

Les valeurs de colonne dans les paramètres table sont accessibles à l’aide d’instructions Transact-SQL SELECT standard. Les paramètres table sont fortement typés et leur structure est validée automatiquement. La taille des paramètres table est limitée uniquement par la mémoire du serveur.

Pour plus d’informations sur les paramètres table, consultez les ressources suivantes.

Transmettre plusieurs lignes dans les versions précédentes de SQL Server

Avant l’introduction des paramètres table dans SQL Server 2008, les options permettant de passer plusieurs lignes de données à une procédure stockée ou à une commande SQL paramétrée étaient limitées. Un développeur peut choisir parmi les options suivantes pour transmettre plusieurs lignes au serveur :

• Utilisez une série de paramètres individuels pour représenter les valeurs dans plusieurs colonnes et lignes de données. La quantité de données transmissibles selon cette méthode est limitée par le nombre de paramètres autorisés. Les procédures SQL Server peuvent contenir jusqu’à 2 100 paramètres. Une logique côté serveur est requise pour assembler ces différentes valeurs dans une variable de table ou une table temporaire à des fins de traitement.

• Regroupez plusieurs valeurs de données dans des chaînes délimitées ou des documents XML, puis transmettez ces valeurs de texte à une procédure ou une instruction. Il faut pour cela que la procédure ou l’instruction inclue la logique nécessaire pour valider les structures de données et dissocier les valeurs.

• Créez une série d’instructions SQL distinctes pour les modifications de données qui affectent plusieurs lignes. Ces modifications peuvent être envoyées individuellement au serveur ou regroupées par lots. Toutefois, même lorsqu’elles sont soumises dans des lots contenant plusieurs instructions, chacune des instructions est exécutée séparément sur le serveur.

• Utilisez le programme utilitaire bcp ou l’objet SQLServerBulkCopy pour charger de nombreuses lignes de données dans une table. Bien que cette technique soit très efficace, elle ne prend pas en charge le traitement côté serveur, sauf si les données sont chargées dans une table temporaire ou une variable de table.

Créer des types de paramètres table

Les paramètres table sont basés sur des structures de table fortement typées et définies à l’aide d’instructions Transact-SQL CREATE TYPE. Vous devez créer un type de table et définir la structure dans SQL Server avant de pouvoir utiliser les paramètres table dans vos applications clientes. Pour plus d’informations sur la création des types de table, consultez Types de table définis par l’utilisateur dans la Documentation en ligne de SQL Server.

CREATE TYPE dbo.CategoryTableType AS TABLE  
    ( CategoryID int, CategoryName nvarchar(50) )  

Après avoir créé un type table, vous pouvez déclarer des paramètres table basés sur ce type. Le fragment Transact-SQL suivant montre comment déclarer un paramètre table dans une définition de procédure stockée. Le mot clé READONLY est nécessaire pour déclarer un paramètre table.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)  

Modifier des données avec des paramètres table (Transact-SQL)

Les paramètres table peuvent être utilisés dans des modifications de données par jeux qui affectent plusieurs lignes avec une seule instruction. Par exemple, vous pouvez sélectionner toutes les lignes d’un paramètre table et les insérer dans une table de base de données, ou créer une instruction de mise à jour en joignant un paramètre table à la table à mettre à jour.

L’instruction Transact-SQL UPDATE suivante montre comment utiliser un paramètre table en le joignant à la table Categories. Si vous utilisez un paramètre table avec un JOIN dans une clause FROM, vous devez également créer un alias pour celui-ci (ici, le paramètre table a pour alias « ec ») :

UPDATE dbo.Categories  
    SET Categories.CategoryName = ec.CategoryName  
    FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec  
    ON dbo.Categories.CategoryID = ec.CategoryID;  

Cet exemple Transact-SQL montre comment sélectionner des lignes d’un paramètre table pour effectuer une insertion (INSERT) dans une opération basée sur un jeu unique.

INSERT INTO dbo.Categories (CategoryID, CategoryName)  
    SELECT nc.CategoryID, nc.CategoryName FROM @tvpNewCategories AS nc;  

Limitations des paramètres table

Les paramètres table présentent plusieurs limitations :

• Il n’est pas possible de transmettre des paramètres table à des fonctions définies par l’utilisateur.

• Les paramètres table ne peuvent être indexés que pour prendre en charge les contraintes UNIQUE et PRIMARY KEY. SQL Server ne gère pas les statistiques de paramètres table.

• Les paramètres table sont en lecture seule dans le code Transact-SQL. Vous ne pouvez pas mettre à jour les valeurs de colonne dans les lignes d’un paramètre table, ni insérer ou supprimer des lignes. Pour modifier les données transmises à une procédure stockée ou à une instruction paramétrable dans un paramètre table, il faut les insérer dans une table temporaire ou une variable de table.

• Vous ne pouvez pas utiliser les instructions ALTER TABLE pour modifier la conception des paramètres table.

• Il est possible de transmettre en continu des objets volumineux dans un paramètre table.

Configuration d'un paramètre table

À compter de la version 6.0 de Microsoft JDBC Driver pour SQL Server, les paramètres table sont pris en charge avec une instruction paramétrable ou une procédure stockée paramétrable. Ils peuvent être remplis à partir d’une SQLServerDataTable, d’un ResultSet ou d’une implémentation fournie par l’utilisateur de l’interface ISQLServerDataRecord. Lorsque vous définissez un paramètre table pour une requête préparée, vous devez spécifier un nom de type correspondant au nom d’un type compatible déjà créé sur le serveur.

Les deux fragments de code suivants montrent comment configurer un paramètre table avec une SQLServerPreparedStatement et une SQLServerCallableStatement pour insérer des données. Ici, sourceTVPObject peut être une SQLServerDataTable, un ResultSet ou un objet ISQLServerDataRecord. Les exemples supposent que la connexion est un objet Connection actif.

// Using table-valued parameter with a SQLServerPreparedStatement.  
SQLServerPreparedStatement pStmt =
    (SQLServerPreparedStatement) connection.prepareStatement("INSERT INTO dbo.Categories SELECT * FROM ?");  
pStmt.setStructured(1, "dbo.CategoryTableType", sourceTVPObject);  
pStmt.execute();  

// Using table-valued parameter with a SQLServerCallableStatement.  
SQLServerCallableStatement pStmt =
    (SQLServerCallableStatement) connection.prepareCall("exec usp_InsertCategories ?");
pStmt.setStructured(1, "dbo.CategoryTableType", sourceTVPObject);;  
pStmt.execute();  

Transmettre un paramètre table comme objet SQLServerDataTable

À compter de la version 6.0 de Microsoft JDBC Driver pour SQL Server, la classe SQLServerDataTable représente une table en mémoire de données relationnelles. Cet exemple montre comment construire un paramètre table à partir de données en mémoire à l’aide de l’objet SQLServerDataTable. Le code crée d’abord un objet SQLServerDataTable, définit son schéma et remplit la table avec des données. Le code configure ensuite une SQLServerPreparedStatement qui transmet cette table de données sous forme de paramètre table à SQL Server.

/* Assumes connection is an active Connection object. */

// Create an in-memory data table.  
SQLServerDataTable sourceDataTable = new SQLServerDataTable();
  
// Define metadata for the data table.  
sourceDataTable.addColumnMetadata("CategoryID" ,java.sql.Types.INTEGER);
sourceDataTable.addColumnMetadata("CategoryName" ,java.sql.Types.NVARCHAR);
  
// Populate the data table.  
sourceDataTable.addRow(1, "CategoryNameValue1");
sourceDataTable.addRow(2, "CategoryNameValue2");
  
// Pass the data table as a table-valued parameter using a prepared statement.  
SQLServerPreparedStatement pStmt =
        (SQLServerPreparedStatement) connection.prepareStatement(  
            "INSERT INTO dbo.Categories SELECT * FROM ?;");  
pStmt.setStructured(1, "dbo.CategoryTableType", sourceDataTable);  
pStmt.execute();  

Cet exemple est semblable au précédent. La seule différence est qu’il définit le nom TVP sur SQLServerDataTable à la place de la conversion en cas sur PreparedStatement dans un SQLServerPreparedStatement pour utiliser la méthode setStructured.

/* Assumes connection is an active Connection object. */

// Create an in-memory data table.
SQLServerDataTable sourceDataTable = new SQLServerDataTable();
sourceDataTable.setTvpName("dbo.CategoryTableType");

// Define metadata for the data table.
sourceDataTable.addColumnMetadata("CategoryID" ,java.sql.Types.INTEGER);
sourceDataTable.addColumnMetadata("CategoryName" ,java.sql.Types.NVARCHAR);

// Populate the data table.
sourceDataTable.addRow(1, "CategoryNameValue1");
sourceDataTable.addRow(2, "CategoryNameValue2");

// Pass the data table as a table-valued parameter using a prepared statement.
PreparedStatement pStmt =
        connection.prepareStatement(
            "INSERT INTO dbo.Categories SELECT * FROM ?;");
pStmt.setObject(1, sourceDataTable);
pStmt.execute();

Transmettre un paramètre table comme objet ResultSet

Cet exemple montre comment transmettre en continu des lignes de données d’un ResultSet à un paramètre table. Le code récupère d’abord des données d’une table source dans un objet SQLServerDataTable, définit son schéma et remplit la table avec des données. Le code configure ensuite une SQLServerPreparedStatement qui transmet cette table de données sous forme de paramètre table à SQL Server.

/* Assumes connection is an active Connection object. */

// Create the source ResultSet object. Here SourceCategories is a table defined with the same schema as Categories table.
ResultSet sourceResultSet = connection.createStatement().executeQuery("SELECT * FROM SourceCategories");  

// Pass the source result set as a table-valued parameter using a prepared statement.  
SQLServerPreparedStatement pStmt =
        (SQLServerPreparedStatement) connection.prepareStatement(  
                "INSERT INTO dbo.Categories SELECT * FROM ?;");  
pStmt.setStructured(1, "dbo.CategoryTableType", sourceResultSet);  
pStmt.execute();  

Transmettre un paramètre table comme objet ISQLServerDataRecord

À compter de la version 6.0 de Microsoft JDBC Driver pour SQL Server, une nouvelle interface ISQLServerDataRecord est disponible pour la transmission de données en continu (selon l’implémentation fournie par l’utilisateur) avec un paramètre table. L’exemple suivant montre comment implémenter l’interface ISQLServerDataRecord et la transmettre sous forme de paramètre table. Par souci de simplicité, l’exemple suivant transmet une seule ligne comportant des valeurs codées en dur au paramètre table. Dans l’idéal, l’utilisateur implémenterait cette interface pour transmettre en continu des lignes à partir de n’importe quelle source, par exemple à partir de fichiers texte.

class MyRecords implements ISQLServerDataRecord  
{  
    int currentRow = 0;  
    Object[] row = new Object[2];  
  
    MyRecords(){  
        // Constructor. This implementation has just one row.
        row[0] = new Integer(1);  
        row[1] = "categoryName1";  
    }  
  
    public int getColumnCount(){  
        // Return the total number of columns, for this example it is 2.  
        return 2;  
    }  
  
    public SQLServerMetaData getColumnMetaData(int columnIndex) {  
        // Return the column metadata.  
        if (1 == columnIndex)  
            return new SQLServerMetaData("CategoryID", java.sql.Types.INTEGER);  
        else  
            return new SQLServerMetaData("CategoryName", java.sql.Types.NVARCHAR);  
    }  
  
    public Object[] getRowData(){  
        // Return the columns in the current row as an array of objects. This implementation has just one row.  
        return row;
    }  
  
    public boolean next(){  
        // Move to the next row. This implementation has just one row, after processing the first row, return false.  
        currentRow++;  
        if (1 == currentRow)  
            return true;  
        else  
            return false;  
    }
}

// Following code demonstrates how to pass MyRecords object as a table-valued parameter.  
MyRecords sourceRecords = new MyRecords();  
SQLServerPreparedStatement pStmt =
        (SQLServerPreparedStatement) connection.prepareStatement(  
                "INSERT INTO dbo.Categories SELECT * FROM ?;");  
pStmt.setStructured(1, "dbo.CategoryTableType", sourceRecords);  
pStmt.execute();  

API des paramètres table pour le pilote JDBC

SQLServerMetaData

Cette classe représente les métadonnées d’une colonne. Elle est utilisée dans l’interface ISQLServerDataRecord pour transmettre des métadonnées de colonne au paramètre table. Elle comporte les méthodes suivantes :

SQLServerSortOrder

Enum définissant l’ordre de tri. Les valeurs possibles sont les suivantes : Ascending, Descending et Unspecified.

SQLServerDataTable

Cette classe représente une table de données en mémoire à utiliser avec des paramètres table. Elle comporte les méthodes suivantes :

SQLServerDataColumn

Cette classe représente une colonne de la table de données en mémoire représentée par SQLServerDataTable. Elle comporte les méthodes suivantes :

ISQLServerDataRecord

Cette classe représente une interface que les utilisateurs peuvent implémenter pour transmettre en continu des données à un paramètre table. Les méthodes de cette interface sont les suivantes :

SQLServerPreparedStatement

Les méthodes suivantes ont été ajoutées à cette classe pour permettre de transmettre des paramètres table.

SQLServerCallableStatement

Les méthodes suivantes ont été ajoutées à cette classe pour permettre de transmettre des paramètres table.

Voir aussi

Présentation du pilote JDBC