/**********************************************************************
* FILENAME : TVPInsertRecs.c
*
* DESCRIPTION :
* Demonstrates a simple example of using a Table-Valued Parameter
* (TVP) to populate a table using a stored procedure with just one
* parameter. TVPs were introduced in SQL SERVER 2008. A TVP is a
* data type that can be used to hold records in a 'table' like
* manner but are held within a variable inside a program. The
* whole of the 'table' like parameter can then be passed as a
* single parameter to a stored procedure for processing. For
* example, TVPs can be usefully applied when performing bulk data
* operations where a whole set of rows can be passed as a single
* parameter to a stored procedure for subsequent inserting into
* a database table.
*
* ODBC USAGE :
*
* The TVP used here represents a table type with 4 columns:
* FirstName, LastName, Address and City.
*
* Prompts user for number of records to create
*
* Sets up the test by dropping and re-creating:-
* 1. Database Table (TestTVPTable1) - into which rows
* are written from rows in a TVP.
* 2. Stored Procedure (InsertFromTVP) - to select records
* from the TVP and insert them into TestTVPTable1.
* 3. TVP Table Variable (PersonTVP) - Table variable to
* hold records and passed as a single parameter to
* InsertFromTVP().
*
* Bind Paramaters:
* Binds parameter 1 as a TVP
*
* Bind TVP Parameters:
* Sets focus on parameter 1
* Bind arrays to each of the 4 TVP columns and lengths. i.e.
* for FirstName, LastName, Address and City
* Clears focus by setting focus to zero
*
* Load data into the TVP bound arrays:
* For the number of records requested
* prompts user for each of the four columns per row
* and save each set in the next row of the TVP bound buffers
* Execute the stores procedure to insert the records stored in the
* TVP bound array buffers. Note the stored procedure only takes
* one parameter, the TVP, but upto as many as MAX_ARRAY_SIZE records
* are written to the table.
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <sqlncli.h>
#include <string.h>
#include "util.c"
#define LASTNAME_LEN 255
#define FIRSTNAME_LEN 255
#define ADDRESS_LEN 255
#define CITY_LEN 255
#define MAX_ARRAY_SIZE 20
//
// SetupTVPTest is used to create and/or drop the stored procedure,
// TVP table type and DB Table used in the test.
//
SQLRETURN SetupTVPTest (SQLHDBC hdbc, char createEm, char dropEm) {
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
SQLRETURN retcode = SQL_SUCCESS; // Return status
// Drop statements
// Stored Procedure
SQLCHAR strDropTVPProc [] =
"IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = "
"object_id(N'dbo.InsertFromTVP') AND OBJECTPROPERTY"
"(id, N'IsProcedure') = 1) DROP PROCEDURE dbo.InsertFromTVP";
// TVP Table Type
SQLCHAR strDropTVPType[] =
"IF EXISTS (SELECT * FROM sys.types WHERE "
"is_table_type = 1 AND name = 'PersonTVP') "
"DROP TYPE dbo.PersonTVP";
// TVP Table (the physical one in the DB)
SQLCHAR strDropTVPTable [] =
"IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = "
"object_id(N'dbo.TestTVPTable1') AND OBJECTPROPERTY(id, N'IsUserTable')"
" = 1) DROP TABLE dbo.TestTVPTable1";
// Create the physical database table
SQLCHAR strCreateTVPTable [] =
"CREATE TABLE TestTVPTable1 ("
"PersonID int NOT NULL IDENTITY(1,1),"
"FirstName varchar(255),"
"LastName varchar(255),"
"Address varchar(255),"
"City varchar(255)"
")";
// Create the in-memory Table-Value Parameter Table
SQLCHAR strCreateTVPType [] =
"CREATE TYPE PersonTVP AS TABLE ( "
"vFirstName varchar(255),"
"vLastName varchar(255),"
"vAddress varchar(255),"
"vCity varchar(255)"
")";
// Create the stored procedure which takes one parameter,
// the persons Table-Value Parameter Table.
// NOTE: Table-value Params Must be read only.
SQLCHAR strCreateTVPProc [] =
"CREATE PROCEDURE InsertFromTVP (@Persons PersonTVP READONLY) "
"AS "
"INSERT INTO TestTVPTable1 (FirstName, LastName, Address, City) "
"SELECT vFirstName, vLastName, vAddress, vCity FROM @Persons";
// Allocate a statement handle
retcode=SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
if (dropEm=='Y') {
// Execute Drop Procedure
retcode = SQLExecDirect (hstmt, strDropTVPProc, SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect(DROPPROC)",
hstmt, SQL_HANDLE_STMT);
// Execute Drop Type
retcode = SQLExecDirect (hstmt, strDropTVPType, SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect(DROPTYPE)",
hstmt, SQL_HANDLE_STMT);
// Execute DROP Table
retcode = SQLExecDirect (hstmt, strDropTVPTable, SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect(DROPTABLE)",
hstmt, SQL_HANDLE_STMT);
}
if (createEm=='Y')
// Execute Create Table
retcode = SQLExecDirect (hstmt, strCreateTVPTable, SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect(CREATETABLE)",
hstmt, SQL_HANDLE_STMT);
// Execute Create Type
retcode = SQLExecDirect (hstmt, strCreateTVPType, SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect(CREATETYPE)",
hstmt, SQL_HANDLE_STMT);
// Execute Create Procedure
retcode = SQLExecDirect (hstmt, strCreateTVPProc, SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect(CREATEPROC)",
hstmt, SQL_HANDLE_STMT);
exit:
// Free statement handle
if (hstmt != SQL_NULL_HSTMT)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
return retcode;
}
//
// Arrays of column values
//
SQLCHAR strFirstName [MAX_ARRAY_SIZE][FIRSTNAME_LEN];
SQLCHAR strLastName[MAX_ARRAY_SIZE][LASTNAME_LEN];
SQLCHAR strAddress[MAX_ARRAY_SIZE][ADDRESS_LEN];
SQLCHAR strCity[MAX_ARRAY_SIZE][CITY_LEN];
//
// Arrays of column values lengths
//
SQLLEN lFirstName[MAX_ARRAY_SIZE];
SQLLEN lLastName[MAX_ARRAY_SIZE];
SQLLEN lAddress[MAX_ARRAY_SIZE];
SQLLEN lCity[MAX_ARRAY_SIZE];
int main () {
SQLHENV henv = SQL_NULL_HENV; // Environment
SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
SQLRETURN retcode; // Return status
int i, numRows=1;
// The name of the TVP table data type
SQLCHAR *TVPTableName = (SQLCHAR *) "PersonTVP";
SQLLEN lTVPRowsUsed;
// Get Number of records to insert
getInt ("Number of Recs", &numRows, 'N', 0);
if (numRows==0) goto exit;
if (numRows>MAX_ARRAY_SIZE) numRows=MAX_ARRAY_SIZE;
// Allocate environment
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
// Set ODBC Verion
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER*)SQL_OV_ODBC3, 0);
CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)",
henv, SQL_HANDLE_ENV);
// Allocate Connection
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
henv, SQL_HANDLE_DBC);
// Set Login Timeout
retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)",
hdbc, SQL_HANDLE_DBC);
// Connect to DSN
retcode = SQLConnect(hdbc, (SQLCHAR*) "SQLSRV2014", SQL_NTS,
(SQLCHAR*) NULL, 0, NULL, 0);
CHECK_ERROR(retcode, "SQLConnect(DSN:SQLSRV2014;)",
hdbc, SQL_HANDLE_DBC);
// Allocate Statement Handle
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
// Setup stored procedure, Table-Value Parameter data type and database
// Table.
// Second parameter indicates whether the above are to be created
// Third parameter indicates whether the above are to be dropped
retcode = SetupTVPTest (hdbc, 'Y', 'Y');
CHECK_ERROR(retcode, "SetupTVPTest()", hdbc, SQL_HANDLE_DBC);
//
// 1st step is bind all parameters in normal way.
// Here we have just one parameter, a Table-Valued Parameter (TVP)
// which requires some specific parameters.
//
retcode = SQLBindParameter(
hstmt, // Statement handle
1, // Parameter Number
SQL_PARAM_INPUT, // Input/Output Type (always INPUT for TVP)
SQL_C_DEFAULT, // C - Type (always this for a TVP)
SQL_SS_TABLE, // SQL Type (always this for a TVP)
MAX_ARRAY_SIZE, // For a TVP this is max rows we will use
0, // For a TVP this is always 0
TVPTableName, // For a TVP this is the type name of the
// TVP, and also a token returned by
// SQLParamData.
SQL_NTS, // For a TVP this is the length of the type
// name or SQL_NTS.
&lTVPRowsUsed); // For a TVP this is the number of rows
// actually available.
//
// Now we need to bind a separate set of parameters for each TVP.
// To do we set the focus on each TVP in turn, bind data to it in the same
// way as for rows in a table.
//
// First set focus on param 1.
retcode = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS,
(SQLPOINTER) 1, SQL_IS_INTEGER);
// Col 1 - Bind start of FirstName array
retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR,
FIRSTNAME_LEN, 0, &strFirstName[0],
FIRSTNAME_LEN, &lFirstName[0]);
// Col 2 - Bind start of LastName array
retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR,
LASTNAME_LEN, 0, &strLastName[0],
LASTNAME_LEN, &lLastName[0]);
// Col 3 - Bind start of Address array
retcode = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR, ADDRESS_LEN,
0, &strAddress[0],
ADDRESS_LEN, &lAddress[0]);
// Col 4 - Bind start of City array
retcode = SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR, CITY_LEN,
0, &strCity[0], CITY_LEN, &lCity[0]);
//
// If there were further TVPs we would set the focus to the next one,
// bind buffers to that, and so on on until all TVPs have bound buffers.
// As we have no more, reset param focus to zero.
//
retcode = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS,
(SQLPOINTER) 0, SQL_IS_INTEGER);
//
// The data for each row of the TVP is represented by the nth element of
// the arrays bound for columns FirstName, LastName, Address and City,
// held in strFirtstName[n], strLastName[n], strAddress[n], and strCity[n]
// Prompt for data, filling columns and rows until numRows reached.
//
for (i=0;i<numRows;i++) {
// Clear Buffers
memset (strFirstName[i], ' ', FIRSTNAME_LEN);
memset (strLastName[i], ' ', LASTNAME_LEN);
memset (strAddress[i], ' ', ADDRESS_LEN);
memset (strCity[i], ' ', CITY_LEN);
// Get data from user
printf ("\nRecord %i, out of %i\n\n", i+1, numRows);
getStr ("First Name", strFirstName[i], FIRSTNAME_LEN, 'N');
getStr ("Last Name", strLastName[i], LASTNAME_LEN, 'N');
getStr ("Address ", strAddress[i], ADDRESS_LEN, 'N');
getStr ("City ", strCity[i], CITY_LEN, 'N');
// Set lengths
lFirstName[i]=strlen(strFirstName[i]);
lLastName[i]=strlen(strLastName[i]);
lAddress[i]=strlen(strAddress[i]);
lCity[i]=strlen(strCity[i]);
}
//
// We now have arrays holding FirstNames, Lastnames, Addresses and Citys,
// and corresponding lengths for each.
//
// Set the StrLen_or_IndPtr value to the actual number of rows to write
// (i.e. in the buffers) and execute the stored procedure. Although just
// one parameter is passed, because it is a TVP, several rows are actually
// written to the table.
//
lTVPRowsUsed=numRows;
retcode = SQLExecDirect(hstmt, "{CALL InsertFromTVP(?)}", SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect(InsertFromTVP)",
hstmt, SQL_HANDLE_STMT);
exit:
printf ("\nComplete.\n");
// Free handles
// Statement
if (hstmt != SQL_NULL_HSTMT)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
// Connection
if (hdbc != SQL_NULL_HDBC) {
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
// Environment
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return 0;
}
See Also
