/**********************************************************************
* FILENAME :    UsingInsertWithParameterArrays.c
*
* DESCRIPTION :
* 		Illustrates inserting records into a table where the data is taken
* 		from parameter arrays.
*
* ODBC USAGE :
*		SQLSetStmtAttr() 	- SQL_ATTR_PARAM_BIND_TYPE
*					  SQL_ATTR_PARAMSET_SIZE
*					  SQL_ATTR_PARAM_STATUS_PTR
*					  SQL_ATTR_PARAMS_PROCESSED_PTR
*		SQLBindParameter()  - to bind the 4 INSERT parametoers
*		SQLExecDirect()	- to execute a INSERT statement
*	 	Print Param Status Array
*/

#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>

#include "util.c"

/*
 * Uses TestTBL1,
 *
 *      PersonID int NOT NULL IDENTITY(1,1),
 *      FirstName varchar(255) NOT NULL,
 *      LastName varchar(255),
 *      Address varchar(255),
 *      City varchar(255)
*/

#define PARAM_ARRAY_SIZE 10

#define FIRSTNAME_LEN 256
#define LASTNAME_LEN 256
#define ADDRESS_LEN 256
#define CITY_LEN 256

int main () {

    SQLHENV   henv  = SQL_NULL_HENV;   // Environment
    SQLHDBC   hdbc  = SQL_NULL_HDBC;   // Connection handle
    SQLHSTMT  hstmt = SQL_NULL_HSTMT;  // Statement handle
    SQLRETURN retcode;

    // Select statement to return person details from TestTBL2
    SQLCHAR     *stmt = "INSERT INTO TestTBL2 "
                        "(FirstName, LastName, Address, City) "
                        "VALUES (?,?,?,?)";

    // Arrays of data
    SQLCHAR     ArrFirstNames[PARAM_ARRAY_SIZE][2] 	=
                            {"a","b","c","d","e","f","g","h","i","j"};
	SQLCHAR	    ArrLastNames[PARAM_ARRAY_SIZE][2] 	=
                            {"a","b","c","d","e","f","g","h","i","j"};
	SQLCHAR		ArrAddress[PARAM_ARRAY_SIZE][2] 	=
                            {"a","b","c","d","e","f","g","h","i","j"};
	SQLCHAR		ArrCity[PARAM_ARRAY_SIZE][2] 		=
                            {"a","b","c","d","e","f","g","h","i","j"};

    // Column binding variables
    SQLCHAR     FirstName [FIRSTNAME_LEN];
    SQLLEN      FirstName_l;
    SQLCHAR     LastName [LASTNAME_LEN];
    SQLLEN      LastName_l;
    SQLCHAR     Address [ADDRESS_LEN];
    SQLLEN      Address_l;
    SQLCHAR     City [CITY_LEN];
    SQLLEN      City_l;

    int i;
    char *     tmp;
    char       strPersonID[11];

    SQLUSMALLINT ParamStatusArray[PARAM_ARRAY_SIZE];
    SQLLEN       ParamsProcessed=0;

    // Allocate the ODBC environment and save handle.
    retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
                henv, SQL_HANDLE_ENV);

    // Notify ODBC that this is an ODBC 3.0 app.
    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 ODBC connection handle and connect.
    retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
                hdbc, SQL_HANDLE_DBC);

    // Connect to DATASOURCE
    retcode=SQLDriverConnect(hdbc, NULL, "DSN=SQLSRV2014;",
                             SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
    CHECK_ERROR(retcode, "SQLDriverConnect(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);

    // Set parameter set size, status array and params processed pointers
    retcode = SQLSetStmtAttr (hstmt, SQL_ATTR_PARAM_BIND_TYPE,
                              SQL_PARAM_BIND_BY_COLUMN, 0);
    retcode = SQLSetStmtAttr (hstmt, SQL_ATTR_PARAMSET_SIZE,
                              (SQLPOINTER) PARAM_ARRAY_SIZE, 0);
    retcode = SQLSetStmtAttr (hstmt, SQL_ATTR_PARAM_STATUS_PTR,
                              ParamStatusArray, PARAM_ARRAY_SIZE);
    retcode = SQLSetStmtAttr (hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR,
                              &ParamsProcessed, 0);

    // Bind array values of parameters 1-4
    retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
                               SQL_VARCHAR, 2, 0, ArrFirstNames, 2, NULL);
    retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
                               SQL_VARCHAR, 2, 0, ArrLastNames, 2, NULL);
    retcode = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR,
                               SQL_VARCHAR, 2, 0, ArrAddress, 2, NULL);
    retcode = SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR,
                               SQL_VARCHAR, 2, 0, ArrCity, 2, NULL);

    // execute stmt
    retcode = SQLExecDirect (hstmt, stmt, SQL_NTS);
    CHECK_ERROR(retcode, "SQLExecDirect()", hstmt, SQL_HANDLE_STMT);

    //dumpDescriptors ("HSTMT", hstmt, 'Y', 'Y', 1);

	// Check to see which sets of parameters were inserted successfully.
	printf ("\nParameter Status Array :\n");
	for (i = 0; i < ParamsProcessed; i++) {
		printf(" Parameter Set ");
		switch (ParamStatusArray[i]) {
			case SQL_PARAM_SUCCESS:
			case SQL_PARAM_SUCCESS_WITH_INFO:
				printf("%d Success\n", i);
				break;
			case SQL_PARAM_ERROR:
				printf("%d Error\n", i);
				break;
			case SQL_PARAM_UNUSED:
				printf("%d Not processed\n", i);
				break;
			case SQL_PARAM_DIAG_UNAVAILABLE:
				printf("%d Status Unknown\n", i);
				break;
		}
	}

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.