Scénarios d'utilisation des paramètres table ODBC
S’applique à :
SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics 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 , ainsi que les autres paramètres. Une fois que tous les paramètres ont été liés, l’application définit l’attribut de 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 qui peuvent être envoyées ; il s’agit de la taille du tableau des mémoires tampons de colonnes. ParameterValuePtr est la mémoire tampon de paramètre ; pour un paramètre table dans SQLBindParameter, ParameterValuePtr et son BufferLength associé sont utilisés pour passer le nom de type du paramètre table lorsque 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.
Lorsqu’un nom de type de paramètre table est spécifié sur un appel à SQLBindParameter, il doit toujours être spécifié en tant que valeur Unicode, même dans les applications 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, SQLGetDescField et SQLSetDescField. Toutefois, la surcharge de SQLBindParameter est généralement plus pratique et ne nécessite pas d’accès de 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 descripteur 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. S’il s’agit du cas et que le paramètre table n’est pas défini dans le schéma par défaut actuel de 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 de ce scénario se trouve dans la procédure demo_fixed_TVP_binding d’utilisation des paramètres table (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 au niveau de l’exécution, autrement dit, en retournant SQL_NEED_DATA. Lorsque le pilote est prêt à accepter des données pour un paramètre table, SQLParamData retourne la valeur de ParameterValuePtr dans SQLBindParameter.
Une application utilise SQLPutData pour un paramètre table pour indiquer la disponibilité des données pour les colonnes constituantes de paramètre table. Lorsque SQLPutData est appelé pour un paramètre table, DataPtr doit toujours être null et StrLen_or_Ind doit être soit 0, soit un nombre 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’est pas 0, le pilote traite les colonnes constituantes des paramètres table de la même façon 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 spécifier des données lors de son exécution via sa mémoire tampon de longueur/indicateur. Les valeurs de colonne de paramètre table peuvent être passées par des appels répétés à SQLPutData comme d’habitude lorsqu’une valeur de caractère ou binaire doit être passée 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é sera 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 réel suivant. Lorsque SQLPutData transmet une valeur d’indicateur supérieure ou égale à 1, le pilote traite les colonnes et lignes de paramètres table de manière séquentielle jusqu’à ce qu’elle 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 constituantes de paramètre table et le contenu de la mémoire tampon d’indicateur pour la ligne ou les lignes suivantes à transmettre au serveur.
L’exemple de code de ce scénario se trouve dans la routine demo_variable_TVP_binding d’utilisation des paramètres table (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 de paramètre table, DATA_TYPE est retournée 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 est défini et SS_TYPE_SCHEMA_NAME retourne le nom du schéma où le type de table du paramètre table est défini. Conformément à la spécification ODBC, SS_TYPE_CATALOG_NAME et SS_TYPE_SCHEMA_NAME apparaissent avant toutes les colonnes spécifiques du 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 des tables SQLTables pour déterminer les noms des types de tables de la même façon que 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 d’autres fonctions de catalogue qui prennent un nom de table en tant que paramètre doivent interpréter le nom de la table comme 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 de la même façon que pour les tables persistantes, mais doit d’abord définir SQL_SOPT_SS_NAME_SCOPE pour indiquer qu’elle fonctionne avec des types de tables plutôt que des tables réelles. SqlPrimaryKeys peut également être utilisé avec des types de tables, à nouveau à l’aide de SQL_SOPT_SS_NAME_SCOPE.
L’exemple de code de ce scénario se trouve dans la routine demo_metadata_from_catalog_APIs d’utilisation des paramètres table (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 pour les 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 définis par l’utilisateur CLR. 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 pour un paramètre table dans ce scénario, également, car SQLDescribeParam ne retourne pas de métadonnées pour les colonnes d’une colonne de paramètre table.
L’exemple de code pour ce cas d’usage se trouve dans la routine demo_metadata_from_prepared_statement dans Use Table-Valued Parameters (ODBC) (Use Table-Valued Parameters).