/**********************************************************************
* 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;
}

See Also


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.