Partilhar via

Usos de parâmetros ODBC com valor de tabela

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure PDW (Sistema de Plataforma de Análise) do Azure Synapse Analytics

Este tópico discute os principais cenários de usuário para o uso de parâmetros com valor de tabela com ODBC:

  • Parâmetro com valor de tabela com buffers de várias linhas totalmente associados (Enviar dados como um RVP com todos os valores na memória)

  • Parâmetro com valor de tabela com streaming de linhas (Enviar dados como TVP usando dados em execução)

  • Recuperando metadados do parâmetro com valor de tabela do catálogo do sistema

  • Recuperando metadados do parâmetro com valor de tabela para uma instrução preparada

Parâmetro com valor de tabela com buffers de várias linhas totalmente associados (Enviar dados como um RVP com todos os valores na memória)

Quando usados com buffers de várias linhas totalmente associados, todos os valores de parâmetro ficam disponíveis na memória. Por exemplo, isto é típico de uma transação de OLTP na qual os parâmetros com valor de tabela podem ser empacotados em um único procedimento armazenado. Sem parâmetros com valor de tabela, isso envolveria gerar um lote de várias instruções complexas dinamicamente ou fazer várias chamadas para o servidor.

O próprio parâmetro com valor de tabela é associado usando SQLBindParameter junto com os outros parâmetros. Depois que todos os parâmetros tiverem sido associados, o aplicativo definirá o atributo de foco do parâmetro, SQL_SOPT_SS_PARAM_FOCUS, em cada parâmetro com valor de tabela e chamará SQLBindParameter para as colunas do parâmetro com valor de tabela.

O tipo de servidor para um parâmetro com valor de tabela é um novo tipo específico do SQL Server, SQL_SS_TABLE. O tipo de C que associa para SQL_SS_TABLE sempre deve ser SQL_C_DEFAULT. Nenhum dado é transferido para o parâmetro associado ao parâmetro com valor de tabela. Ele é usado para transmitir metadados de tabela e controlar a maneira de transmitir dados nas colunas constituintes do parâmetro com valor de tabela.

O comprimento do parâmetro com valor tabela é definido como o número de linhas que são enviadas ao servidor. O parâmetro ColumnSize de SQLBindParameter para um parâmetro com valor de tabela especifica o número máximo de linhas que podem ser enviadas; esse é o tamanho da matriz dos buffers de coluna. ParameterValuePtr é o buffer de parâmetro; para um parâmetro com valor de tabela em SQLBindParameter, ParameterValuePtr e seu BufferLength associado são usados para passar o nome do tipo do parâmetro com valor de tabela quando necessário. O nome de tipo não é necessário para chamadas de procedimento armazenado, mas é necessário para instruções SQL.

Quando um nome de tipo de parâmetro com valor de tabela é especificado em uma chamada para SQLBindParameter, ele sempre deve ser especificado como um valor Unicode, mesmo em aplicativos criados como aplicativos ANSI. Ao especificar um nome de tipo de parâmetro com valor de tabela usando SQLSetDescField, você pode usar um literal que esteja em conformidade com a maneira como o aplicativo é criado. O Gerenciador do Driver ODBC executará todas as conversões de Unicode necessárias.

Os metadados para parâmetros com valor de tabela e colunas de parâmetro com valor de tabela podem ser manipulados individual e explicitamente usando SQLGetDescRec, SQLSetDescRec, SQLGetDescField e SQLSetDescField. No entanto, sobrecarregar SQLBindParameter geralmente é mais conveniente e não requer acesso explícito ao descritor na maioria dos casos. Essa abordagem é consistente com a definição de SQLBindParameter para outros tipos de dados, exceto que, para um parâmetro com valor de tabela, os campos de descritor afetados são ligeiramente diferentes.

Às vezes, um aplicativo usa um parâmetro com valor de tabela com SQL dinâmico e é necessário fornecer o nome do tipo do parâmetro com valor de tabela. Se esse for o caso e o parâmetro com valor de tabela não estiver definido no esquema padrão atual para a conexão, SQL_CA_SS_SCHEMA_NAME deverá ser definido usando SQLSetDescField. Como as definições de tipo de tabela e os parâmetros com valor de tabela devem estar no mesmo banco de dados, SQL_CA_SS_CATALOG_NAME não devem ser definidos se o aplicativo usar parâmetros com valor de tabela. Caso contrário, SQLSetDescField relatará um erro.

O código de exemplo para esse cenário está no procedimento demo_fixed_TVP_binding em Usar ODBC (Parâmetros com Valor de Tabela).

Parâmetro com valor de tabela com streaming de linhas (Enviar dados como TVP usando dados em execução)

Neste cenário, o aplicativo fornece linhas ao driver conforme ele precisa e elas são transmitidas ao servidor. Isto evita ter a necessidade de armazenar em buffer todas as linhas na memória. Isto representa os cenários de inserção/atualização em massa. Parâmetros com valor de tabela fornecem um ponto de desempenho em algum local entre as matrizes do parâmetro e a cópia em massa. Isto é, parâmetros com valor de tabela são quase tão simples de programar quanto matrizes de parâmetros, mas eles fornecem mais flexibilidade no servidor.

O parâmetro com valor de tabela e suas colunas são associados conforme discutido na seção anterior, Parâmetro com valor de tabela com buffers de várias linhas totalmente associados, mas o indicador de comprimento do parâmetro com valor de tabela é definido como SQL_DATA_AT_EXEC. O driver responde a SQLExecute ou SQLExecuteDirect da maneira usual para parâmetros de dados em execução, ou seja, retornando SQL_NEED_DATA. Quando o driver estiver pronto para aceitar dados para um parâmetro com valor de tabela, SQLParamData retornará o valor de ParameterValuePtr em SQLBindParameter.

Um aplicativo usa SQLPutData para um parâmetro com valor de tabela para indicar a disponibilidade de dados para colunas constituintes de parâmetro com valor de tabela. Quando SQLPutData é chamado para um parâmetro com valor de tabela, DataPtr deve sempre ser nulo e StrLen_or_Ind deve ser 0 ou um número menor ou igual ao tamanho da matriz especificado para buffers de parâmetro com valor de tabela (o parâmetro ColumnSize de SQLBindParameter). 0 significa que não há mais linhas para o parâmetro com valor de tabela e que o driver continuará a processar o próximo parâmetro de procedimento real. Quando StrLen_or_Ind não for 0, o driver processará as colunas constituintes de parâmetro com valor de tabela da mesma forma que os parâmetros associados a parâmetros sem valor de tabela: cada coluna de parâmetro com valor de tabela pode especificar seu comprimento de dados real, SQL_NULL_DATA, ou pode especificar dados na execução por meio de seu buffer de comprimento/indicador. Os valores de coluna de parâmetro com valor de tabela podem ser passados por chamadas repetidas para SQLPutData como de costume quando um caractere ou valor binário deve ser passado em partes.

Quando todas as colunas de parâmetro com valor de tabela tiverem sido processadas, o driver retornará ao parâmetro com valor de tabela para processar outras linhas dos dados. Portanto, para parâmetros com valor de tabela de dados em execução, o driver não segue a verificação sequencial habitual de parâmetros associados. Um parâmetro associado com valor de tabela será sondado até que SQLPutData seja chamado com StrLen_Or_IndPtr igual a 0, momento em que o driver ignora colunas de parâmetro com valor de tabela e passa para o próximo parâmetro de procedimento armazenado real. Quando SQLPutData passa um valor de indicador maior ou igual a 1, o driver processa colunas e linhas de parâmetro com valor de tabela sequencialmente até ter valores para todas as linhas e colunas associadas. Então o driver retornará o parâmetro com valor de tabela. Entre receber o token para o parâmetro com valor de tabela de SQLParamData e chamar SQLPutData(hstmt, NULL, n) para um parâmetro com valor de tabela, o aplicativo deve definir dados de coluna constituinte de parâmetro com valor de tabela e conteúdo de buffer de indicador para a próxima linha ou linhas a serem passadas para o servidor.

O código de exemplo para esse cenário está na rotina demo_variable_TVP_binding em Usar ODBC (Parâmetros com Valor de Tabela).

Recuperando metadados do parâmetro com valor de tabela do catálogo do sistema

Quando um aplicativo chama SQLProcedureColumns para um procedimento que tem parâmetros de parâmetro com valor de tabela, DATA_TYPE é retornado como SQL_SS_TABLE e TYPE_NAME é o nome do tipo de tabela para o parâmetro com valor de tabela. Duas colunas adicionais são adicionadas ao conjunto de resultados retornado por SQLProcedureColumns: SS_TYPE_CATALOG_NAME retorna o nome do catálogo em que o tipo de tabela do parâmetro de valor de tabela é definido e SS_TYPE_SCHEMA_NAME retorna o nome do esquema em que o tipo de tabela do parâmetro de valor de tabela é definido. Em conformidade com a especificação ODBC, SS_TYPE_CATALOG_NAME e SS_TYPE_SCHEMA_NAME aparecem antes de todas as colunas específicas do driver que foram adicionadas em versões anteriores do SQL Server e depois de todas as colunas exigidas pelo próprio ODBC.

As colunas novas serão populadas não apenas para parâmetros com valor de tabela, mas também para parâmetros de tipo definido pelo usuário de CLR. As colunas existentes do esquema e do catálogo de parâmetros UDT ainda serão populadas, mas ter colunas de esquema e catálogo para tipos de dados que precisem delas simplificará o desenvolvimento do aplicativo no futuro. (Observe que as coleções de esquema XML são ligeiramente diferentes e não estão incluídas nessa alteração.)

Um aplicativo usa SQLTables para determinar os nomes dos tipos de tabela da mesma forma que faz para tabelas persistentes, tabelas do sistema e exibições. Um tipo de tabela novo, TABLE TYPE, é introduzido para permitir que um aplicativo para identifique tipos de tabela associados com parâmetros com valor de tabela. Tipos de tabela e tabelas regulares usam namespaces diferentes. Isto significa que você pode usar o mesmo nome para um tipo de tabela e uma tabela real. Para tratar isto, um novo atributo de instrução, SQL_SOPT_SS_NAME_SCOPE, foi introduzido. Esse atributo especifica se SQLTables e outras funções de catálogo que usam um nome de tabela como um parâmetro devem interpretar o nome da tabela como o nome de uma tabela real ou o nome de um tipo de tabela.

Um aplicativo usa SQLColumns para determinar as colunas de um tipo de tabela da mesma forma que faz para tabelas persistentes, mas primeiro deve definir SQL_SOPT_SS_NAME_SCOPE para indicar que está trabalhando com tipos de tabela em vez de tabelas reais. SQLPrimaryKeys também pode ser usado com tipos de tabela, novamente usando SQL_SOPT_SS_NAME_SCOPE.

O código de exemplo para esse cenário está na rotina demo_metadata_from_catalog_APIs em Usar ODBC (Parâmetros com Valor de Tabela).

Recuperando metadados do parâmetro com valor de tabela para uma instrução preparada

Nesse cenário, um aplicativo usa SQLNumParameters e SQLDescribeParam para recuperar metadados para parâmetros com valor de tabela.

O campo de IPD SQL_CA_SS_TYPE_NAME é usado para recuperar o nome de tipo para parâmetros com valor de tabela. Os campos IPD SQL_CA_SS_SCHEMA_NAME e SQL_CA_SS_CATALOG_NAME são usados para recuperar seu catálogo e esquema, respectivamente.

As definições de tipo de tabela e parâmetros com valor de tabela devem ocorrer no mesmo banco de dados. SQLSetDescField relatará um erro se um aplicativo definir SQL_CA_SS_CATALOG_NAME ao usar parâmetros com valor de tabela.

SQL_CA_SS_CATALOG_NAME e SQL_CA_SS_SCHEMA_NAME também podem ser usados para recuperar o catálogo e o esquema associados aos parâmetros de tipo definidos pelo usuário CLR. SQL_CA_SS_CATALOG_NAME e SQL_CA_SS_SCHEMA_NAME são alternativas aos atributos de esquema de catálogo específicos do tipo existente para tipos CLR UDT.

Um aplicativo usa SQLColumns para recuperar metadados de coluna para um parâmetro com valor de tabela nesse cenário também, pois SQLDescribeParam não retorna metadados para as colunas de uma coluna de parâmetro com valor de tabela.

O código de exemplo para esse caso de uso está na rotina demo_metadata_from_prepared_statement em Usar ODBC (Parâmetros com Valor de Tabela).

Confira também

Parâmetros com valor de tabela (ODBC)