Scénarios d'utilisation des paramètres table ODBC

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Cette rubrique présente les principaux scénarios utilisateur dans lesquels des paramètres table sont utilisés avec ODBC :

• Paramètre table avec mémoires tampons multilignes entièrement liées (envoyer des données en tant que paramètre table avec toutes les valeurs en mémoire)

• Paramètre table avec diffusion de lignes en continu (envoyer des données en tant que paramètre table à l'aide de données en cours d'exécution)

• Récupération des métadonnées de paramètre table du catalogue système

• Récupération des métadonnées de paramètre table pour une instruction préparée

Paramètre table avec mémoires tampons multilignes entièrement liées (envoyer des données en tant que paramètre table avec toutes les valeurs en mémoire)

Lorsqu'elles sont utilisées avec des mémoires tampons multilignes entièrement liées, toutes les valeurs de paramètres sont disponibles en mémoire. Ce comportement est classique d'une transaction OLTP par exemple, dans laquelle les paramètres table peuvent être insérés dans une procédure stockée unique. Sans paramètres table, il serait nécessaire de générer dynamiquement un lot à instructions multiples complexe ou d'effectuer plusieurs appels au serveur.

Le paramètre table lui-même est lié à l’aide de SQLBindParameter avec les autres paramètres. Une fois tous les paramètres liés, l’application définit l’attribut focus de paramètre, SQL_SOPT_SS_PARAM_FOCUS, sur chaque paramètre table et appelle SQLBindParameter pour les colonnes du paramètre table.

Le type de serveur d’un paramètre table est un nouveau type spécifique SQL Server, SQL_SS_TABLE. Le type C de liaison pour SQL_SS_TABLE doit toujours être SQL_C_DEFAULT. Aucune donnée n'est transférée pour le paramètre lié au paramètre table ; il est utilisé pour passer les métadonnées de table et contrôler comment passer des données dans les colonnes qui constituent le paramètre table.

La longueur du paramètre table est définie sur le nombre de lignes qui sont envoyées au serveur. Le paramètre ColumnSize de SQLBindParameter pour un paramètre table spécifie le nombre maximal de lignes pouvant être envoyées ; il s’agit de la taille du tableau des mémoires tampons de colonne. ParameterValuePtr est la mémoire tampon de paramètres ; pour un paramètre table dans SQLBindParameter, ParameterValuePtr et bufferLength associé sont utilisés pour transmettre le nom de type du paramètre table si nécessaire. Le nom de type n'est pas requis pour les appels de procédure stockée, mais il est requis pour les instructions SQL.

Quand un nom de type de paramètre table est spécifié sur un appel à SQLBindParameter, il doit toujours être spécifié sous la forme d’une valeur Unicode, même dans les applications qui sont générées en tant qu’applications ANSI. Lorsque vous spécifiez un nom de type de paramètre table à l’aide de SQLSetDescField, vous pouvez utiliser un littéral conforme à la façon dont l’application est générée. Le Gestionnaire de pilotes ODBC effectuera toute conversion Unicode requise.

Les métadonnées des paramètres table et des colonnes de paramètres table peuvent être manipulées individuellement et explicitement à l’aide de SQLGetDescRec, SQLSetDescRec, SQLGetDescRec, SQLGetDescField et SQLSetDescField. Toutefois, la surcharge de SQLBindParameter est généralement plus pratique et ne nécessite pas d’accès descripteur explicite dans la plupart des cas. Cette approche est cohérente avec la définition de SQLBindParameter pour d’autres types de données, sauf que pour un paramètre table, les champs de descripteurs affectés sont légèrement différents.

Une application utilise parfois un paramètre table avec Dynamic SQL et le nom de type du paramètre table doit être fourni. Si c’est le cas et que le paramètre table n’est pas défini dans le schéma par défaut actuel pour la connexion, SQL_CA_SS_SCHEMA_NAME doit être défini à l’aide de SQLSetDescField. Étant donné que les définitions de type de table et les paramètres table doivent se trouver dans la même base de données, SQL_CA_SS_CATALOG_NAME ne doit pas être défini si l’application utilise des paramètres table. Sinon, SQLSetDescField signale une erreur.

L’exemple de code pour ce scénario se trouve dans la procédure demo_fixed_TVP_bindingde l’article Utiliser des paramètres de Table-Valued (ODBC).

Paramètre table avec diffusion de lignes en continu (envoyer des données en tant que paramètre table à l'aide de données en cours d'exécution)

Dans ce scénario, l'application fournit des lignes au pilote quand il les lui demande et ces lignes sont transmises en continu au serveur. Ainsi, il n'est pas nécessaire que toutes les lignes soient mises en mémoire tampon. Ceci est représentatif des scénarios d'insertion/mise à jour en bloc. Les performances des paramètres table se situent entre les tableaux de paramètres et la copie en bloc. Autrement dit, les paramètres table sont pratiquement aussi faciles à programmer que les tableaux de paramètres, mais offrent une souplesse supérieure au niveau du serveur.

Le paramètre table et ses colonnes sont liés comme discuté dans la section précédente, Paramètre table avec mémoires tampons multilignes entièrement liées, mais l'indicateur de longueur du paramètre table lui-même est défini sur SQL_DATA_AT_EXEC. Le pilote répond à SQLExecute ou SQLExecuteDirect de la manière habituelle pour les paramètres de données à l’exécution, c’est-à-dire en retournant SQL_NEED_DATA. Lorsque le pilote est prêt à accepter des données pour un paramètre table, SQLParamData retourne la valeur ParameterValuePtr dans SQLBindParameter.

Une application utilise SQLPutData pour un paramètre table afin d’indiquer la disponibilité des données pour les colonnes constituant des paramètres table. Lorsque SQLPutData est appelé pour un paramètre table, DataPtr doit toujours avoir la valeur null et StrLen_or_Ind doit être égal ou inférieur ou égal à la taille de tableau spécifiée pour les mémoires tampons de paramètres table (paramètre ColumnSize de SQLBindParameter). 0 signifie qu'il n'y a plus de lignes pour le paramètre table et que le pilote passera au traitement du paramètre de procédure réel suivant. Lorsque StrLen_or_Ind n’a pas la valeur 0, le pilote traite les colonnes constituant les paramètres table de la même manière que les paramètres liés aux paramètres non table : chaque colonne de paramètre table peut spécifier sa longueur de données réelle, SQL_NULL_DATA, ou il peut spécifier des données au moment de l’exécution via sa mémoire tampon de longueur/indicateur. Les valeurs de colonne de paramètres table peuvent être transmises par des appels répétés à SQLPutData comme d’habitude lorsqu’un caractère ou une valeur binaire doit être transmis en morceaux.

Une fois toutes les colonnes de paramètre table traitées, le pilote revient au paramètre table pour traiter d'autres lignes de données de paramètre table. Par conséquent, pour les paramètres table de données en cours d'exécution, le pilote ne suit pas l'analyse séquentielle habituelle des paramètres liés. Un paramètre table lié est interrogé jusqu’à ce que SQLPutData soit appelé avec StrLen_Or_IndPtr égal à 0, auquel cas le pilote ignore les colonnes de paramètres table et passe au paramètre de procédure stockée suivant. Lorsque SQLPutData transmet une valeur d’indicateur supérieure ou égale à 1, le pilote traite séquentiellement les colonnes et les lignes de paramètres table jusqu’à ce qu’il ait des valeurs pour toutes les lignes et colonnes liées. Le pilote revient ensuite au paramètre table. Entre la réception du jeton pour le paramètre table à partir de SQLParamData et l’appel de SQLPutData(hstmt, NULL, n) pour un paramètre table, l’application doit définir les données de colonne et le contenu de la mémoire tampon d’indicateur pour la ou les lignes suivantes à passer au serveur.

L’exemple de code pour ce scénario figure dans la routine demo_variable_TVP_bindingdans Utiliser des paramètres de Table-Valued (ODBC).

Récupération des métadonnées de paramètre table du catalogue système

Lorsqu’une application appelle SQLProcedureColumns pour une procédure qui a des paramètres table, DATA_TYPE est retourné en tant que SQL_SS_TABLE et TYPE_NAME est le nom du type de table pour le paramètre table. Deux colonnes supplémentaires sont ajoutées au jeu de résultats retourné par SQLProcedureColumns : SS_TYPE_CATALOG_NAME retourne le nom du catalogue où le type de table du paramètre table-value est défini, et SS_TYPE_SCHEMA_NAME retourne le nom du schéma où est défini le type de table du paramètre table-value. Conformément à la spécification ODBC, les SS_TYPE_CATALOG_NAME et les SS_TYPE_SCHEMA_NAME s’affichent devant toutes les colonnes spécifiques au pilote ajoutées dans les versions précédentes de SQL Server, et après toutes les colonnes mandatées par ODBC lui-même.

Les nouvelles colonnes seront remplies à la fois pour les paramètres table, mais aussi pour les paramètres du type CLR défini par l'utilisateur. Les colonnes de schéma et de catalogue existantes des paramètres définis par l'utilisateur continuent d'être remplies, mais le fait de disposer de colonnes de schéma et de catalogue communes pour les types de données qui en ont besoin simplifie le développement d'applications dans le futur. (Notez que les collections de schémas XML sont quelque peu différentes et ne sont pas incluses dans cette modification.)

Une application utilise SQLTables pour déterminer les noms des types de tables de la même façon qu’elle le fait pour les tables persistantes, les tables système et les vues. Un nouveau type de table, TABLE TYPE, est introduit pour permettre à une application d'identifier les types de tables associés aux paramètres table. Les types de tables et les tables standard utilisent des espaces de noms différents. Cela signifie que vous pouvez utiliser le même nom pour un type de table et pour une table réelle. À cet effet, un nouvel attribut d'instruction, SQL_SOPT_SS_NAME_SCOPE, a été introduit. Cet attribut spécifie si SQLTables et autres fonctions de catalogue qui prennent un nom de table en tant que paramètre doivent interpréter le nom de la table comme le nom d’une table réelle ou le nom d’un type de table.

Une application utilise SQLColumns pour déterminer les colonnes d’un type de table comme elle le fait pour les tables persistantes, mais doit d’abord définir SQL_SOPT_SS_NAME_SCOPE pour indiquer qu’elle utilise des types de table plutôt que des tables réelles. SQLPrimaryKeys peut également être utilisé avec des types de table, à l’aide de SQL_SOPT_SS_NAME_SCOPE.

L’exemple de code pour ce scénario figure dans la routine demo_metadata_from_catalog_APIsdans Utiliser des paramètres de Table-Valued (ODBC).

Récupération des métadonnées de paramètre table pour une instruction préparée

Dans ce scénario, une application utilise SQLNumParameters et SQLDescribeParam pour récupérer les métadonnées des paramètres table.

Le champ IPD SQL_CA_SS_TYPE_NAME est utilisé pour récupérer le nom du type du paramètre table. Les champs IPD SQL_CA_SS_SCHEMA_NAME et SQL_CA_SS_CATALOG_NAME sont utilisés pour récupérer respectivement son catalogue et son schéma.

Les définitions de types de tables et les paramètres table doivent se trouver dans la même base de données. SQLSetDescField signale une erreur si une application définit SQL_CA_SS_CATALOG_NAME lors de l’utilisation de paramètres table.

SQL_CA_SS_CATALOG_NAME et SQL_CA_SS_SCHEMA_NAME peuvent également être utilisés pour récupérer le catalogue et le schéma associés aux paramètres de type CLR définis par l’utilisateur. SQL_CA_SS_CATALOG_NAME et SQL_CA_SS_SCHEMA_NAME sont des alternatives aux attributs de schéma de catalogue spécifiques au type existant pour les types UDT CLR.

Une application utilise SQLColumns pour récupérer les métadonnées de colonne d’un paramètre table dans ce scénario, car SQLDescribeParam ne retourne pas de métadonnées pour les colonnes d’une colonne de paramètres table.

L’exemple de code pour ce cas d’usage figure dans la routine demo_metadata_from_prepared_statement dans Utiliser Table-Valued paramètres (ODBC).

Voir aussi

Paramètres table (ODBC)