/**********************************************************************
* FILENAME : TVPAuthorsAndBooks.c
*
* DESCRIPTION :
* Example uses stored procedure and table-valued parameters to
* insert records into an Authors table and a Books table. The
* records in the books table are linked back to the Authors table
* by using the primary key of the authors table as a foreign key
* in the books table. As the authors are unique, this creates a
* one to many relationship between the authors table records and
* the books table records.
*
* ODBC USAGE:
* Uses stored procedure - InsertAuthorsBooks(), two tables -
* authorsDBTable and booksDBTable and two table-valued parameters
* booksTVPTable and authorsTVPTable. The authorsDBTable holds
* author details and the booksDBTable holds book details. Just
* one author with a list of books is added each time therefore
* in this example there is only 1 record in the authorsTVPTable
* parameter but there can be many in the booksDBTable parameter,
* each time the stored procedure is executed. The tables are
* linked through the primary and foreign keys relating to the
* identity field in the authors table. All the above are dropped
* and recreated when the program is run.
*
* SQLAllocHandle(), SQLExecDirect(), SQLFreeHandle(),
* SQLSetEnvAttr(), SQLConnect(), SQLSetConnectAttr(),
* SQLBindParameter(), SQLFreeStmt(), SQLFreeHandle()
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <sqlncli.h>
#include <string.h>
#include "util.c"
#define FIELD_LEN 32
#define MAX_ARRAY_SIZE 20
//
// Author table data
// Although we reserve MAX_ARRAY_SIZE elements, we only use the
// first one because we add one author at a time with their books.
//
SQLCHAR strFirstName [MAX_ARRAY_SIZE][FIELD_LEN];
SQLCHAR strLastName [MAX_ARRAY_SIZE][FIELD_LEN];
// First and last name lengths
SQLLEN lFirstName [MAX_ARRAY_SIZE];
SQLLEN lLastName [MAX_ARRAY_SIZE];
//
// Book table data
//
SQLCHAR strTitle [MAX_ARRAY_SIZE][FIELD_LEN];
SQLCHAR strGenre [MAX_ARRAY_SIZE][FIELD_LEN];
SQLCHAR strISBN [MAX_ARRAY_SIZE][FIELD_LEN];
// title, genre and isbn lengths
SQLLEN lTitle [MAX_ARRAY_SIZE];
SQLLEN lGenre [MAX_ARRAY_SIZE];
SQLLEN lISBN [MAX_ARRAY_SIZE];
// Displays the authors/books tables (with the bounds
// of a 20 row array) before exiting.
SQLRETURN displayAuthorsAndBooks (SQLHDBC hdbc) {
SQLRETURN retcode;
SQLHSTMT hstmt = SQL_NULL_HSTMT;
// Used to read authors back
typedef struct tagAuthorsStruct {
SQLUINTEGER author_id;
SQLLEN author_idLen;
SQLCHAR lastName[FIELD_LEN];
SQLLEN lastNameLen;
SQLCHAR firstName[FIELD_LEN];
SQLLEN firstNameLen;
} authorsStruct;
authorsStruct authorsArray[MAX_ARRAY_SIZE];
SQLLEN rowsFetched = 0;
// Used to read books back one at a time
SQLCHAR title[FIELD_LEN];
SQLLEN titleLen;
SQLCHAR genre[FIELD_LEN];
SQLLEN genreLen;
SQLCHAR ISBN[FIELD_LEN];
SQLLEN ISBNLen;
SQLSMALLINT columns;
// Books select is based on foreign key author_id
char *selectBooks = "SELECT title, genre, ISBN "
"FROM booksDBTable WHERE author_id=%i";
char stmt[128];
int i;
// Allocate handle for reading from authors table
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle(AUTHORS)",
hstmt, SQL_HANDLE_STMT);
// Read as many as possible upto MAX_ARRAY_SIZE, ignore rest if any more
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE,
(SQLPOINTER)sizeof(authorsStruct), 0);
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE,
(SQLPOINTER)MAX_ARRAY_SIZE, 0);
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR,
&rowsFetched,0);
// Bind author_ids, last and first names to array buffers
retcode = SQLBindCol(hstmt, 1, SQL_C_ULONG, &authorsArray[0].author_id,
sizeof(authorsArray[0].author_id),
&authorsArray[0].author_idLen);
retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR, authorsArray[0].lastName,
sizeof(authorsArray[0].lastName),
&authorsArray[0].lastNameLen);
retcode = SQLBindCol(hstmt, 3, SQL_C_CHAR, authorsArray[0].firstName,
sizeof(authorsArray[0].firstName),
&authorsArray[0].firstNameLen);
// Select authors
retcode = SQLExecDirect(hstmt,
(SQLCHAR*)"SELECT author_id, lastName, FirstName "
"FROM authorsDBTable", SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect(SELECT AUTHORS)",
hstmt, SQL_HANDLE_STMT);
retcode = SQLFetch(hstmt);
CHECK_ERROR(retcode, "SQLFetch(FETCH AUTHORS)", hstmt, SQL_HANDLE_STMT);
// Free handle and start with books using data from authors
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle(BOOKS)",
hstmt, SQL_HANDLE_STMT);
// Bind params for title, genre and ISBN
retcode = SQLBindCol(hstmt, 1, SQL_C_CHAR, title,
sizeof(title), &titleLen);
CHECK_ERROR(retcode, "SQLBindCol(1 BOOKS)",
hstmt, SQL_HANDLE_STMT);
retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR, genre,
sizeof(genre), &genreLen);
CHECK_ERROR(retcode, "SQLBindCol(2 BOOKS)",
hstmt, SQL_HANDLE_STMT);
retcode = SQLBindCol(hstmt, 3, SQL_C_CHAR, ISBN,
sizeof(ISBN), &ISBNLen);
CHECK_ERROR(retcode, "SQLBindCol(3 BOOKS)",
hstmt, SQL_HANDLE_STMT);
printf ("\nNo of Authors : %i", (int) rowsFetched);
for (i=0; i<rowsFetched;i++) {
printf ("\n\nAuthor ID : %i", (int) authorsArray[i].author_id);
printf ("\nFirst Name : %s", authorsArray[i].firstName);
printf ("\nLast Name : %s", authorsArray[i].lastName);
sprintf (stmt, selectBooks, authorsArray[i].author_id);
retcode = SQLExecDirect(hstmt, (SQLCHAR*)stmt, SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect(SELECT BOOKS)",
hstmt, SQL_HANDLE_STMT);
do {
retcode=SQLNumResultCols(hstmt, &columns);
CHECK_ERROR(retcode, "SQLNumResultCols()",
hstmt, SQL_HANDLE_STMT);
if (columns > 0) {
while (SQLFetch(hstmt) != SQL_NO_DATA) {
printf ("\n - %.10s, %.10s, %.10s ", title, genre, ISBN);
}
}
} while (SQLMoreResults(hstmt) == SQL_SUCCESS);
}
exit:
if (hstmt != SQL_NULL_HSTMT)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
return SQL_SUCCESS;
}
//
// SetupTVPTest is used to create and/or drop the stored procedure,
// TVP table typeS and DB Table used in the test.
//
// Stored Procedure - InsertAuthorsBooks()
// Table-Valued Params - authorsTVPTable and booksTVPTable
// DB Tables - authorsDBTable and booksDBTable
//
SQLRETURN SetupTVPTest (SQLHDBC hdbc, char doCreate, char doDrop) {
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
SQLRETURN retcode = SQL_SUCCESS; // Return status
//
// DROP Everything
//
// Drop Stored Procedure InsertAuthorsBooks()
SQLCHAR strDropAuthorsBooksSP [] =
"IF EXISTS (SELECT * FROM dbo.sysobjects "
"WHERE id = object_id(N'dbo.InsertAuthorsBooks') "
"AND OBJECTPROPERTY(id, N'IsProcedure') = 1) "
"DROP PROCEDURE dbo.InsertAuthorsBooks";
// Drop TVPs booksTVPTable and authorsTVPTable
SQLCHAR strDropTVPBooksType[] =
"IF EXISTS (SELECT * FROM sys.types "
"WHERE is_table_type = 1 AND name = 'booksTVPTable') "
"DROP TYPE dbo.booksTVPTable";
SQLCHAR strDropTVPAuthorsType[] =
"IF EXISTS (SELECT * FROM sys.types "
"WHERE is_table_type = 1 AND name = 'authorsTVPTable') "
"DROP TYPE dbo.authorsTVPTable";
// Drop DB Tables booksDBTable and authorsDBTable
SQLCHAR strDropDBBooksTable [] =
"IF EXISTS (SELECT * FROM dbo.sysobjects "
"WHERE id = object_id(N'dbo.booksDBTable') "
"AND OBJECTPROPERTY(id, N'IsUserTable') = 1) "
"DROP TABLE dbo.booksDBTable";
SQLCHAR strDropDBAuthorsTable [] =
"IF EXISTS (SELECT * FROM dbo.sysobjects "
"WHERE id = object_id(N'dbo.authorsDBTable') "
"AND OBJECTPROPERTY(id, N'IsUserTable') = 1) "
"DROP TABLE dbo.authorsDBTable";
//
// CREATE Everything
//
// Create DB Authors Table
SQLCHAR strCreateDBAuthorsTable [] =
"CREATE TABLE authorsDBTable ("
"author_id int identity(1,1),"
"lastName varchar(32),"
"firstName varchar(32)"
"CONSTRAINT PK_authorsDBTable "
"PRIMARY KEY CLUSTERED(author_id)"
")";
// Create DB Books Table
SQLCHAR strCreateDBBooksTable [] =
"CREATE TABLE booksDBTable ("
"bookDetail_id int identity(1,1),"
"author_id int,"
"title varchar(32),"
"genre varchar(32),"
"ISBN varchar(32)"
"CONSTRAINT PK_booksDBTable "
"PRIMARY KEY CLUSTERED(bookDetail_id) "
"CONSTRAINT FK_booksDBTable_authorID "
"FOREIGN KEY(author_id) "
"REFERENCES dbo.authorsDBTable(author_id) "
")";
// Create TVP Authors Table Data Type
SQLCHAR strCreateTVPAuthorsType [] =
"CREATE TYPE authorsTVPTable AS TABLE ( "
"lastName varchar(32),"
"firstName varchar(32)"
")";
// Create TVP Books Table Data Type
SQLCHAR strCreateTVPBooksType [] =
"CREATE TYPE booksTVPTable AS TABLE ( "
"title varchar(32),"
"genre varchar(32),"
"ISBN varchar(32)"
")";
// Create the stored procedure which takes the authors TVP
// and the books TVP as parameters. Assumes we get one author
// and a list of books each call.
// NOTE: Table-value Params must be read only.
SQLCHAR strCreateAuthorsBooksSP [] =
"CREATE PROCEDURE dbo.InsertAuthorsBooks ("
"@myAuthorsTVPTable authorsTVPTable READONLY,"
"@myBooksTVPTable booksTVPTable READONLY"
") "
"AS "
"BEGIN "
"SET NOCOUNT ON;"
//-- Declare local variables
"DECLARE @myAuthorID int = 0;"
"DECLARE @LN varchar(32);"
"DECLARE @FN varchar(32);"
//-- See if author already exists and save identifier (author_id)
//-- if found
"SELECT @myAuthorID = author_id, @LN = lastName, @FN = firstName "
"FROM dbo.authorsDBTable "
"WHERE lastName IN (SELECT lastName FROM @myAuthorsTVPTable) "
"AND firstName IN (SELECT firstName FROM @myAuthorsTVPTable);"
//-- Does author already exist?
"IF @myAuthorID = 0 "
"BEGIN "
//-- Author doesn't exist, insert new author from author
//-- TVP table and all books from books TVP table
"INSERT INTO dbo.authorsDBTable "
"SELECT lastName, firstName FROM @myAuthorsTVPTable;"
"SET @myAuthorID = @@IDENTITY;"
"INSERT INTO dbo.booksDBTable "
"SELECT @myAuthorID, title, genre, ISBN "
"FROM @myBooksTVPTable;"
"END "
"ELSE "
"BEGIN "
//-- Author exists, use existing author_id and insert
//-- all books records from books TVP table
"INSERT INTO dbo.booksDBTable "
"SELECT @myAuthorID, title, genre, ISBN "
"FROM @myBooksTVPTable;"
"END "
"SET NOCOUNT OFF;"
"END";
// Allocate a statement handle
retcode=SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
if (doDrop=='Y') {
// Execute Drop Procedure
retcode = SQLExecDirect (hstmt, strDropAuthorsBooksSP, SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect(DROPPROC)",
hstmt, SQL_HANDLE_STMT);
// Execute Drop TVP Types
retcode = SQLExecDirect (hstmt, strDropTVPBooksType, SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect(DROPBOOKSTYPE)",
hstmt, SQL_HANDLE_STMT);
retcode = SQLExecDirect (hstmt, strDropTVPAuthorsType, SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect(DROPAUTHORSTYPE)",
hstmt, SQL_HANDLE_STMT);
// Execute DROP DB Tables
retcode = SQLExecDirect (hstmt, strDropDBBooksTable, SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect(DROPBOOKSTABLE)",
hstmt, SQL_HANDLE_STMT);
retcode = SQLExecDirect (hstmt, strDropDBAuthorsTable, SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect(DROPAUTHORSTABLE)",
hstmt, SQL_HANDLE_STMT);
}
if (doCreate=='Y')
// Execute Create DB Tables
retcode = SQLExecDirect (hstmt, strCreateDBAuthorsTable, SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect(CREATEAUTHORSTABLE)",
hstmt, SQL_HANDLE_STMT);
retcode = SQLExecDirect (hstmt, strCreateDBBooksTable, SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect(CREATEBOOKSTABLE)",
hstmt, SQL_HANDLE_STMT);
// Execute Create TVP Types
retcode = SQLExecDirect (hstmt, strCreateTVPAuthorsType, SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect(CREATEAUTHORSTYPE)",
hstmt, SQL_HANDLE_STMT);
retcode = SQLExecDirect (hstmt, strCreateTVPBooksType, SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect(CREATEBOOKSTYPE)",
hstmt, SQL_HANDLE_STMT);
// Execute Create Procedure
retcode = SQLExecDirect (hstmt, strCreateAuthorsBooksSP, 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;
}
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, ai, bi, numAuthors=0, numBooks=0;
// Used in SQLBindParameter() to indicate table data type name`
SQLCHAR *TVPAuthorsTable = (SQLCHAR *) "authorsTVPTable";
SQLCHAR *TVPBooksTable = (SQLCHAR *) "booksTVPTable";
// Used in SQLBindParameter() to indicate no of rows
SQLLEN lTVPAuthorRows; // Will only be 1
SQLLEN lTVPBookRows; // can be many
// 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, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
// Setup stored procedure, Table-Value Parameter data types and database
// Tables.
// Second parameter (Y or N) indicates whether the above are to be created
// Third parameter (Y or N) 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 two parameters, both Table-Valued Parameters (TVP)
// one for an author table, on for author's books table .
//
// Authors TVP
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
TVPAuthorsTable, // 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.
&lTVPAuthorRows); // For a TVP this is the number of rows
// actually available.
CHECK_ERROR(retcode, "SQLBindParameter(P1)", hstmt, SQL_HANDLE_STMT);
// Authors books TVP
retcode = SQLBindParameter(
hstmt, // Statement handle
2, // 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
TVPBooksTable, // 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.
&lTVPBookRows); // For a TVP this is the number of rows
// actually available.
CHECK_ERROR(retcode, "SQLBindParameter(P2)", hstmt, SQL_HANDLE_STMT);
//
// Now we need to bind a separate set of parameters for each TVP.
// To do this we set the focus on each TVP in turn and bind the
// parameters for it.
//
// First set focus on TVP 1
retcode = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS,
(SQLPOINTER) 1, SQL_IS_INTEGER);
CHECK_ERROR(retcode, "SQLSetStmtAttr(FOCUS P1)", hstmt, SQL_HANDLE_STMT);
// Col 1 - Bind start of LastName array
retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR,
FIELD_LEN, 0, &strLastName[0],
FIELD_LEN, &lLastName[0]);
CHECK_ERROR(retcode, "SQLBindParameter(P1 1)", hstmt, SQL_HANDLE_STMT);
// Col 2 - Bind start of FirstName array
retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR,
FIELD_LEN, 0, &strFirstName[0],
FIELD_LEN, &lFirstName[0]);
CHECK_ERROR(retcode, "SQLBindParameter(P1 2)", hstmt, SQL_HANDLE_STMT);
// Next, set focus on TVP 2
retcode = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS,
(SQLPOINTER) 2, SQL_IS_INTEGER);
CHECK_ERROR(retcode, "SQLSetStmtAttr(FOCUS P2)", hstmt, SQL_HANDLE_STMT);
// Col 1 - Bind start of Title array
retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR,
FIELD_LEN, 0, &strTitle[0],
FIELD_LEN, &lTitle[0]);
CHECK_ERROR(retcode, "SQLBindParameter(P2 1)", hstmt, SQL_HANDLE_STMT);
// Col 2 - Bind start of Genre array
retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR,
FIELD_LEN, 0, &strGenre[0],
FIELD_LEN, &lGenre[0]);
CHECK_ERROR(retcode, "SQLBindParameter(P2 2)", hstmt, SQL_HANDLE_STMT);
// Col 3 - Bind start of ISBN array
retcode = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR,
FIELD_LEN, 0, &strISBN[0],
FIELD_LEN, &lISBN[0]);
CHECK_ERROR(retcode, "SQLBindParameter(P2 3)", hstmt, SQL_HANDLE_STMT);
//
// 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);
CHECK_ERROR(retcode, "SQLSetStmtAttr(FOCUS P0)", hstmt, SQL_HANDLE_STMT);
//
// We assemble 1 author and many books each time we go round the loop
//
ai=0; // Not really used as we only have 1 author at a time
bi=0;
// Get data from user
while (1) {
// Prompt for author first and last names
printf ("\nAuthor : ");
getStr ("\nFirstName ", strFirstName[ai], FIELD_LEN, 'N');
lFirstName[ai]=strlen(strFirstName[ai]);
if (lFirstName[ai]==0) break;
getStr ("LastName ", strLastName[ai], FIELD_LEN, 'N');
lLastName[ai]=strlen(strLastName[ai]);
// Get books to store against author
printf ("\nBooks : ");
while (1 && bi<MAX_ARRAY_SIZE) {
getStr ("\nTitle ", strTitle[bi], FIELD_LEN, 'N');
lTitle[bi]=strlen(strTitle[bi]);
if (lTitle[bi]==0) break;
getStr ("Genre ", strGenre[bi], FIELD_LEN, 'N');
lGenre[bi]=strlen(strGenre[bi]);
getStr ("ISBN ", strISBN[bi], FIELD_LEN, 'N');
lISBN[bi]=strlen(strISBN[bi]);
bi++;
}
// Insert author and books. The stored procedure checks whether the
// author already exists and if the author does exist, only the
// books are added. Otherwise a new author in the TVP authors
// table is added to the authors DB table and the books TVP table data
// is transferred to the books DB take. In either case the books are
// written with the foreign key identity field being set to author
// primary key value.
lTVPAuthorRows=ai+1;
lTVPBookRows=bi;
retcode = SQLExecDirect(hstmt,
"{CALL InsertAuthorsBooks (?, ?)}", SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect(InsertAuthorsBooks)",
hstmt, SQL_HANDLE_STMT);
ai=0; // Not used as we only have 1 author at a time
bi=0; // reset otherwise all the previous books will be re-added
}
// Clear statement handle
retcode = SQLFreeStmt(hstmt, SQL_UNBIND);
retcode = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
// Display authors and books tables
retcode = displayAuthorsAndBooks (hdbc);
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;
}
Further information
