Site navigation
Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase, DB2, & more.
/**********************************************************************
* FILENAME : TVPCopyTable.c
*
* DESCRIPTION :
* This example copies one table to another and illustrates
* the use of a Table-Valued Parameter to pass several records
* as one parameter to a stored procedure to perform the insert.
*
* Records are read from the source table and saved into an
* array until a batch of records is assembled. The array in
* which the batch of records is saved is also used as a
* Table-Valued Parameter for the insert. It is suggested,
* table-valued parameters perform well for inserting upto
* 1000 rows but can only be used as READONLY input and DML
* operations such as INSERT, DELETE and UPDATE cannot be
* performed on them.
*
* The source and destination tables both have an identity field.
* To maintain consistency during the copy, the stored procedure
* performing the insert issues a SET IDENTITY_INSERT ON prior to
* the insert and a SET IDENTITY_INSERT OFF afterwards. This
* enables write access to the identity field and stops SQL
* Server from generating one automatically. This then allows the
* identity column values from the source records to be copied
* into the destination records and in so doing make an identical
* copy.
*
* ODBC USAGE :
* The TVP used here represents a table type with 5 columns:
* PersonID, FirstName, LastName, Address and City.
*
* Prompts user for batch size
*
* Sets up the test by dropping and re-creating:-
* 1. Database Table (TestTVPTable2) - 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 Parameters:
* Binds parameter 1 as a TVP
*
* Bind TVP Parameters:
* Sets focus on parameter 1
* Bind arrays to each of the 5 TVP columns and lengths. i.e.
* for PersonID, FirstName, LastName, Address and City
* Clears focus by setting focus to zero
*
* Prompts user for batch size.
* Loops until all records retrieved
* gets next record batch from source table using nextBatch()
* write batch via TVP (i.e. all records via 1 table type
* parameter)
*/
#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
#define MAX_BATCH_SIZE 5
// Row 0 across the arrays is used to store each record as it is
// fetched from the table. It is then moved into the part of the
// array which forms the Table-Valued Parameter (i.e. row 1 onwards);
SQLUINTEGER uiPersonID [MAX_ARRAY_SIZE+1];
SQLCHAR strFirstName [MAX_ARRAY_SIZE+1][FIRSTNAME_LEN];
SQLCHAR strLastName[MAX_ARRAY_SIZE+1][LASTNAME_LEN];
SQLCHAR strAddress[MAX_ARRAY_SIZE+1][ADDRESS_LEN];
SQLCHAR strCity[MAX_ARRAY_SIZE+1][CITY_LEN];
//
// Arrays of column values lengths. Same rule for row 0
//
SQLLEN lPersonID[MAX_ARRAY_SIZE+1];
SQLLEN lFirstName[MAX_ARRAY_SIZE+1];
SQLLEN lLastName[MAX_ARRAY_SIZE+1];
SQLLEN lAddress[MAX_ARRAY_SIZE+1];
SQLLEN lCity[MAX_ARRAY_SIZE+1];
SQLHSTMT hstmtSrc = SQL_NULL_HSTMT; // Statement handle for source table
//
// 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
// The stored procedure takes one parameter, the persons
// Table-Value Parameter Table.
// DROP 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";
// CREATE PROCEDURE
// Note: 1. READONLY is mandatory
// 2. Use of SET IDENTITY_INSERT allows identity
// column to have value inserted rather than
// autogenerated, making exact copy possible.
SQLCHAR strCreateTVPProc [] =
"CREATE PROCEDURE InsertFromTVP (@Persons PersonTVP READONLY) "
"AS "
"SET IDENTITY_INSERT TestTVPTable2 ON;"
"INSERT INTO TestTVPTable2 "
"(PersonID, FirstName, LastName, Address, City) "
"SELECT vPersonID, vFirstName, vLastName, vAddress, vCity "
"FROM @Persons;"
"SET IDENTITY_INSERT TestTVPTable2 OFF";
// The Table-Valued Parameter PersonTVP
// DROP TVP
SQLCHAR strDropTVPType[] =
"IF EXISTS (SELECT * FROM sys.types WHERE "
"is_table_type = 1 AND name = 'PersonTVP') "
"DROP TYPE dbo.PersonTVP";
// CREATE TVP
SQLCHAR strCreateTVPType [] =
"CREATE TYPE PersonTVP AS TABLE ( "
"vPersonID int,"
"vFirstName varchar(255),"
"vLastName varchar(255),"
"vAddress varchar(255),"
"vCity varchar(255)"
")";
// The physical database table into which records are copied
// DROP TABLE
SQLCHAR strDropTVPTable [] =
"IF EXISTS (SELECT * FROM dbo.sysobjects WHERE "
"id = object_id(N'dbo.TestTVPTable2') AND "
"OBJECTPROPERTY(id, N'IsUserTable') = 1) "
"DROP TABLE dbo.TestTVPTable2";
// CREATE TABLE
SQLCHAR strCreateTVPTable [] =
"CREATE TABLE TestTVPTable2 ("
"PersonID int NOT NULL IDENTITY(1,1),"
"FirstName varchar(255),"
"LastName varchar(255),"
"Address varchar(255),"
"City varchar(255)"
")";
// Allocate a statement handle
retcode=SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
// DROP sequence is : Procedure, Table Type Parameter, Table
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);
}
// CREATE sequence is : Table, Table Type Parameter, Procedure
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;
}
// Assume src table is TestTBL1, i.e with same layout as TestTDB2
int nextBatch (SQLHDBC hdbc, int lastID, int batchSize) {
SQLRETURN retcode=SQL_SUCCESS;
SQLLEN indicator, rowCount;
SQLSMALLINT columns;
int currRec=0;
//
// Use keyword TOP to restrict number of rows to the batch size.
// (differs from DB to DB. SQL SERVER/MSAccess uses TOP, MySQL
// uses LIMIT and ORACLE uses ROWNUM). Order by PersonID is
// important so each batch reads from where thw last one finished.
//
char *stmtStr="SELECT TOP %i * FROM TestTBL1 WHERE PersonID > %i "
"ORDER BY PersonID";
char stmt[128];
// First call?
if (hstmtSrc==SQL_NULL_HSTMT) {
lastID=0;
// Allocate a statement handle for selecting
// records from the source table TestTBL1
retcode=SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmtSrc);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmtSrc, SQL_HANDLE_STMT);
}
// Row 0 is used for records being fetched from the source table
// Row 1 upwards is where they are moved and where the TVP is
// mapped to.
retcode = SQLBindCol(hstmtSrc, 1, SQL_C_LONG, &uiPersonID[0],
0, &lPersonID[0]);
retcode = SQLBindCol(hstmtSrc, 2, SQL_C_CHAR, strFirstName[0],
FIRSTNAME_LEN, &lFirstName[0]);
retcode = SQLBindCol(hstmtSrc, 3, SQL_C_CHAR, strLastName[0],
LASTNAME_LEN, &lLastName[0]);
retcode = SQLBindCol(hstmtSrc, 4, SQL_C_CHAR, strAddress[0],
ADDRESS_LEN, &lAddress[0]);
retcode = SQLBindCol(hstmtSrc, 5, SQL_C_CHAR, strCity[0],
CITY_LEN, &lCity[0]);
sprintf (stmt, stmtStr, batchSize, lastID);
retcode = SQLExecDirect (hstmtSrc, stmt, SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect(SELECT TOP)",
hstmtSrc, SQL_HANDLE_STMT);
do {
// SQLNumResultCols() returns number of columns in result set.
// if non zero use SQLFetch until SQL_NO_DATA returned
retcode=SQLNumResultCols(hstmtSrc, &columns);
CHECK_ERROR(retcode, "SQLNumResultCols()",
hstmtSrc, SQL_HANDLE_STMT);
if (columns > 0) {
while (SQLFetch(hstmtSrc) != SQL_NO_DATA) {
currRec++;
uiPersonID[currRec]=uiPersonID[0];
strcpy (strFirstName[currRec], strFirstName[0]);
strcpy (strLastName[currRec], strLastName[0]);
strcpy (strAddress[currRec], strAddress[0]);
strcpy (strCity[currRec], strCity[0]);
}
}
} while (SQLMoreResults(hstmtSrc) == SQL_SUCCESS);
exit:
return currRec;
}
int main () {
SQLHENV henv = SQL_NULL_HENV; // Environment
SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
SQLHSTMT hstmtDst = SQL_NULL_HSTMT; // Statement handle for dest table
SQLRETURN retcode; // Return status
//
// batchSize is the number of rows stored in the TVP
// lastPersonID is a source record identity value and is used as the
// starting point for each batch by selecting records with identity
// greater than the last one we had. Works if select done uses ORDER BY
// identity field.
//
int i, batchSize, rowsFound, lastPersonID=0;
// The name of the TVP table data type
SQLCHAR *TVPTableName = (SQLCHAR *) "PersonTVP";
SQLLEN lTVPRowsUsed;
// Get number of records to read from
getInt ("Batch Size ", &batchSize, 'N', 0);
if (batchSize==0) goto exit;
if (batchSize>MAX_ARRAY_SIZE) batchSize=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 Version
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, &hstmtDst);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmtDst, 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)
// whish requires some specific parameters.
//
retcode = SQLBindParameter(
hstmtDst, // 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, specifically for the
// 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 the TVP, here it is param 1.
retcode = SQLSetStmtAttr(hstmtDst, SQL_SOPT_SS_PARAM_FOCUS,
(SQLPOINTER) 1, SQL_IS_INTEGER);
// Col 1 - Bind start of PersonID array
retcode = SQLBindParameter(hstmtDst, 1, SQL_PARAM_INPUT,
SQL_C_LONG, SQL_INTEGER, 0, 0,
&uiPersonID[1], 0, &lPersonID[1]);
// Col 2 - Bind start of FirstName array
retcode = SQLBindParameter(hstmtDst, 2, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR,
FIRSTNAME_LEN, 0, &strFirstName[1],
FIRSTNAME_LEN, &lFirstName[1]);
// Col 3 - Bind start of LastName array
retcode = SQLBindParameter(hstmtDst, 3, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR,
LASTNAME_LEN, 0, &strLastName[1],
LASTNAME_LEN, &lLastName[1]);
// Col 4 - Bind start of Address array
retcode = SQLBindParameter(hstmtDst, 4, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR,
ADDRESS_LEN, 0, &strAddress[1],
ADDRESS_LEN, &lAddress[1]);
// Col 5 - Bind start of City array
retcode = SQLBindParameter(hstmtDst, 5, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR,
CITY_LEN, 0, &strCity[1],
CITY_LEN, &lCity[1]);
//
// 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(hstmtDst, 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 batchSize reached.
// Data for the TVP starts in array posn 1 (0 is used for reading the
// records from the source table).
//
retcode=SQL_SUCCESS;
//
// While exits via a break when no more rows to copy
//
while (1) {
// Get next batch of records starting from last PersonID processed
rowsFound=nextBatch (hdbc, lastPersonID, batchSize);
//
// Display the batch returned and at the same time set the length
// variables that were bound in the SQLBindParameter() calls for the
// TVP.
//
for (i=1;i<=rowsFound;i++) {
// Person ID and length
printf ("\n%i, ", uiPersonID[i]);
lPersonID[i]=sizeof(uiPersonID[i]);
// FirstName and length
printf ("%.10s, ", strFirstName[i]);
lFirstName[i]=strlen(strFirstName[i]);
// LastName and length
printf ("%.10s, ", strLastName[i]);
lLastName[i]=strlen(strLastName[i]);
// Address and length
printf ("%.10s, ", strAddress[i]);
lAddress[i]=strlen(strAddress[i]);
// City and length
printf ("%.10s", strCity[i]);
lCity[i]=strlen(strCity[i]);
// Remember current person ID. The PersonIDs are selected in
// numeric ascending order (because of the ORDER BY in the select).
// By keeping hold of the last one in this batch the next batch
// starts at the next PersonID greater than this.
lastPersonID=uiPersonID[i];
}
//
// We now have arrays of PersonIDs, FirstNames, Lastnames, Addresses
// and Cities with corresponding lengths for each.
//
// Set the StrLen_or_IndPtr value (lTVPRowsUsed) to the actual number
// of rows to write (i.e. in the buffers) and execute the stored
// procedure. Note this might be less than the batch size if this batch
// is the last batch. Although just one parameter is passed, because
// it is a TVP, several rows (like a table) can be passed as one
// parameter (hence Table-Valued Parameter).
//
lTVPRowsUsed=rowsFound;
retcode = SQLExecDirect(hstmtDst, "{CALL InsertFromTVP(?)}", SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect(InsertFromTVP)",
hstmtDst, SQL_HANDLE_STMT);
// Got last batch? If so break out of the loop, otherwise continue
if (rowsFound<batchSize) break; else printf ("\n");
}
exit:
printf ("\nComplete.\n");
// Free handles
// Statement
if (hstmtSrc != SQL_NULL_HSTMT)
SQLFreeHandle(SQL_HANDLE_STMT, hstmtSrc);
if (hstmtDst != SQL_NULL_HSTMT)
SQLFreeHandle(SQL_HANDLE_STMT, hstmtDst);
// 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;
}
Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase, DB2, & more.