Utilisation de paramètres de type 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 de type table sont fortement typés et leur structure est validée automatiquement. La taille des paramètres de type table est limitée uniquement par la mémoire du serveur.

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

Passage de plusieurs lignes avec les versions antérieures 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éation de types de paramètres de table

Les paramètres de table sont basés sur des structures de table fortement typées, 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)  

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

Les paramètres de type table peuvent être utilisés dans des modifications de données ensemblistes qui affectent plusieurs lignes en exécutant 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 depuis un paramètre de table afin d’effectuer une opération INSERT en une seule opération ensembliste.

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

Limitations des paramètres de type table

Les paramètres de type table comportent plusieurs limites :

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

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

• Les paramètres de type 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 définition des paramètres de type table.

• Vous pouvez transmettre en flux des objets volumineux dans un paramètre tabulaire.

Configuration d’un paramètre de type 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. Les paramètres table peuvent être renseignés depuis un SQLServerDataTable, un ResultSet ou une implémentation de l’interface ISQLServerDataRecord fournie par l’utilisateur. 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();  

Transmission d’un paramètre de type table sous la forme d’un 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 du TVP sur SQLServerDataTable au lieu de s’appuyer sur la conversion de type de PreparedStatement en 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();

Transmission d’un paramètre à valeur de table en tant qu’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();  

Transmission d’un paramètre de type table en tant qu’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 de type 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 destinée à être utilisée avec des paramètres de type 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