/**********************************************************************
* FILENAME :    CallSPEditRecord.c
*
* DESCRIPTION :
*               Illustrates using a stored procedures to perform several
*               actions relating to updating a record into a table. It
*               shows how to set up input parameters and how to return a
*               status value from the procedure.
*
*               Calls a stored procedure as follows:
*               {? = CALL Edit_Record (?, ?, ?, ?, ?, ?)}
*
* ODBC USAGE :
*               Drops and recreates a procedure Edit_Record().
*               Confirms the procedures exist via SQLProcedures ()
*               Uses SQLBindParameter() to bind data to the 7
*               parameters used in the stored procedure.
*
*	            7 Parameters - 1   - Proc return value           - RETURNED
*	                           2   - Action - READ, UPDT         - INPUT
*	                           3   - Record ID                   - INPUT
*	                           4-7 - if action is READ
*	                               - old column values           - OUTPUT
*	                           4-7 - if action is UPDT
*	                               - new column values           - INPUT
*
*	            Illustrates SELECT, INSERT, UPDATE and DELETE actions  via
*	            a stored procedure Edit_Record
*	 			Uses SQLBindParameter() to bind the 7 parameters used with
*	 			the stored procedure Edit_Record() and calls SQLPrepare() to
*	 			make ready for execution.
*
*	 			Uses the notion of current Person ID as current record
*	 			(a value of -1 indicates none). Based on current record and
*	 			action, the program loops executing the stored procedure to
*	 			either Select a specific record, select all records, Update
*	 			or Delete the current record or Insert a new one.
*
*				Selects are either for a single record, or all records.
*				Following a single record select, the record becomes the
*				current record. Following an all records select, there is no
*				current record.
*	 			An insert does not rely on having a current record, but the
*	 			stored procedure returns the record id of the new record, which
*	 			becomes the current record.
*	 			Update relies on having a current record which is kept the same
*	 			during and after the update.
*	 			Delete relies on having a current record but after the delete,
*	 			there is no current record.
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>

#include "util.c"
#include "CallSPUtil.c"

#define SEL 0 // Action passed to Process_Record() to SELECT single record
#define UPD 1 // Action passed to Process_Record() to UPDATE a record
#define INS 2 // Action passed to Process_Record() to INSERT record
#define DEL 3 // Action passed to Process_Record() to DELETE a record
#define ALL 4 // Action passed to Process_Record() to SELECT ALL records

//
// Function to prompt user for action or record ID. currPID is the current
// record ID, which, if not available (i.e. currPID = -1) removes UPDATE and
// DELETE options (which are dependent on a current record ID) but allows
// SELECT, SELECT all and INSERT (which are not).
//
int getAction (int * currPID) {

    int action=SEL;
    char reply[5];

    printf ("\nNext Action: QUIT (Q)\n"
			"SELECT (Rec No), SELECT All (A), INSERT (I)");
    if (*currPID!=-1) {
        printf (", UPDATE (U), DELETE (D)");
    }
    printf (" ?\n  : ");

    reply[0] = ' ';
    fgets(reply, 5, stdin);

    action=SEL;
    if (atoi (reply)==0) {		// Number not entered
        switch (reply[0]) {
            case 'A':
            case 'a':
                action=ALL;		// SELECT all Records
                break;
            case 'I':
            case 'i':
                action=INS;		// INSERT new record
                break;
            case 'U':
            case 'u':
                action=UPD;		// UPDATE current record
                break;
            case 'D':
            case 'd':
                action=DEL;		// DELETE current record
                break;
            case 'Q':
            case 'q':
            default:
                *currPID=0;		// Quit application
                break;
        }
    } else {
        *currPID=(int) atoi (reply);
    }

    return action;
}

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

    // Declare call to stored proc with 7 parameter markers
    // Uses 7 Parameters - 1   - Proc return value   	- RETURNED
    //                     2   - Action - See below  	- INPUT
    //                     3   - RecordID             	- INPUT/OUTOUT
    //                     4-7 - if action is SELECT/SELECT ALL
    //                         - old column values   	- UNUSED
    //							  SELECT is via WHERE RecordID
    //							  SELECT ALL uses no WHERE
    //                     4-7 - if action is UPDATE or INSERT
    //                         - new column values      - INPUT
    //							  UPDATE/INSERT is via WHERE RecordID
    //		  	   4-7 - if action is DELETE    - UNUSED
    //		              				- Delete is via WHERE RecordID
    SQLCHAR * strCallSP   = "{? = CALL Edit_Record (?, ?, ?, ?, ?, ?)}";
    char * strProcName = "Edit_Record";
    char reply[5];

    SQLSMALLINT columns; 				// Number of columns in result-set
    int i, count;

    // Stored proc parameters, input/output variables and return variable.
    SQLCHAR     strFirstName[FIRSTNAME_LEN], strLastName[LASTNAME_LEN];
    SQLCHAR     strAddress[ADDRESS_LEN], strCity[CITY_LEN];
    SQLLEN      lFirstName, lLastName, lAddress, lCity;
    SQLINTEGER	retParam, action, pPersonID=-1, currPID=-1;
    SQLLEN      laction, lretParam;

    SQLLEN  indicator, rowCount;
    char *buf=malloc (255);

    // Allocate an environment handle
    retcode=SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    CHECK_ERROR(retcode, "SQLAllocHandle(ENV)", henv, SQL_HANDLE_ENV);

    // Set ODBC Version
    retcode=SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
                          (void *) SQL_OV_ODBC3, 0);
    CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)",
                henv, SQL_HANDLE_ENV);

    // Allocate a connection handle
    retcode=SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
                hdbc, SQL_HANDLE_DBC);

    // Connect to data source
    retcode=SQLDriverConnect(hdbc, NULL, "DSN=SQLSRV;", SQL_NTS, NULL,
                             0, NULL, SQL_DRIVER_COMPLETE);
    CHECK_ERROR(retcode, "SQLDriverConnect(SQLSRV)",
                hdbc, SQL_HANDLE_DBC);

    // Drop previous procedure called Edit_Record
    retcode = DropProcedure (hdbc, strProcName);

    // Create new procedure called Edit_Record
    retcode = CreateProcedure (hdbc, strProcName);

    // Use SQLPRocedures() to show Edit_Record it now available
    retcode = ListProcedure (hdbc, strProcName);

    // Allocate a statement handle
    retcode=SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
                hstmt, SQL_HANDLE_STMT);

    // Bind parameters. The order is the order in which they appear
    // 1st Parameter marker is stored procedure return parameter
    retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_LONG,
                               SQL_INTEGER, 0, 0, &retParam, 0, &lretParam);
    CHECK_ERROR(retcode, "SQLBindParameter(1)", hstmt, SQL_HANDLE_STMT);

    // 2nd Parameter marker is action, passed as INPUT
    retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG,
                               SQL_INTEGER, 0, 0, &action, 0, &laction);
    CHECK_ERROR(retcode, "SQLBindParameter(2)", hstmt, SQL_HANDLE_STMT);

    // 3rd Parameter marker is pPersonID, passed as INPUT/OUTPUT
    // Input for SELECT, UPDATE, and DELETE
    // Output for INSERT (returns record inserted)
    retcode = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT_OUTPUT, SQL_C_LONG,
                               SQL_INTEGER, 0, 0, &pPersonID, 0, NULL);
    CHECK_ERROR(retcode, "SQLBindParameter(3)", hstmt, SQL_HANDLE_STMT);

    // 4th Parameter marker is FirstName passed as INPUT
    // Input for INSERT and UPDATE
    retcode = SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR,
                               SQL_CHAR, FIRSTNAME_LEN, 0, strFirstName,
                               FIRSTNAME_LEN, &lFirstName);
    CHECK_ERROR(retcode, "SQLBindParameter(4)", hstmt, SQL_HANDLE_STMT);

    // 5th Parameter marker is LastName passed as INPUT
    // Input for INSERT and UPDATE
    retcode = SQLBindParameter(hstmt, 5, SQL_PARAM_INPUT, SQL_C_CHAR,
                               SQL_CHAR, LASTNAME_LEN, 0, strLastName,
                               LASTNAME_LEN, &lLastName);
    CHECK_ERROR(retcode, "SQLBindParameter(5)", hstmt, SQL_HANDLE_STMT);

    // 6th Parameter marker is Address passed as INPUT
    // Input for INSERT and UPDATE
    retcode = SQLBindParameter(hstmt, 6, SQL_PARAM_INPUT, SQL_C_CHAR,
                               SQL_CHAR, ADDRESS_LEN, 0, strAddress,
                               ADDRESS_LEN, &lAddress);
    CHECK_ERROR(retcode, "SQLBindParameter(6)", hstmt, SQL_HANDLE_STMT);

    // 7th Parameter marker is City passed as INPUT
    // Input for INSERT and UPDATE
    retcode = SQLBindParameter(hstmt, 7, SQL_PARAM_INPUT, SQL_C_CHAR,
                               SQL_CHAR, CITY_LEN, 0, strCity,
                               CITY_LEN, &lCity);
    CHECK_ERROR(retcode, "SQLBindParameter(7)", hstmt, SQL_HANDLE_STMT);


    // Prepare statement with procedure call to Edit_Record()
    retcode = SQLPrepare (hstmt, strCallSP, SQL_NTS);
    CHECK_ERROR(retcode, "SQLPrepare(SQL_HANDLE_STMT)",
                hstmt, SQL_HANDLE_STMT);

    // Get initial action. Returns:
    // 1. Record number to SELECT or 0 to quit
    // 2. 'A' to SELECT all records
    // 3. 'I' to INSERT new record
    action=getAction ((int *) &currPID);

    // Loop while we have a current record.
    while (currPID != 0) {

        pPersonID=currPID;
        if (action != SEL && action != ALL) {
    	    // Not selecting so action is either:
            // 			UPD - Update current record
            //          INS - INSERT new record
            //          DEL - DELETE current record

            if (action==UPD) {
                // Updating current record
    		// Prompt for replacment column values
                printf ("\nPersonID  : %i", pPersonID);
                printf ("\nFirstName : %.10s", strFirstName);
                getStr (" - ", strFirstName, FIRSTNAME_LEN, 'N');
                printf ("LastName  : %.10s", strLastName);
                getStr (" - ", strLastName, LASTNAME_LEN, 'N');
                printf ("Address   : %.10s", strAddress);
                getStr (" - ", strAddress, ADDRESS_LEN, 'N');
                printf ("City      : %.10s", strCity);
                getStr (" - ", strCity, CITY_LEN, 'N');
            }

            if (action==INS) {
    		// Inserting new record
    		// prompt for new record column value
                getStr ("\nFirstName ", strFirstName, FIRSTNAME_LEN, 'N');
                getStr ("LastName  ", strLastName, LASTNAME_LEN, 'N');
                getStr ("Address   ", strAddress, ADDRESS_LEN, 'N');
                getStr ("City      ", strCity, CITY_LEN, 'N');
            }

            if (action==UPD || action==INS) {
    		// If updating or insert, set SQLBindParamater()
    		// StrLen_or_IndPtr values
                lFirstName=strlen(strFirstName);
                lLastName=strlen(strLastName);
                lAddress=strlen(strAddress);
                lCity=strlen(strCity);
            } else {
    		// If deleting current record, prompt to confirm
                printf ("\nDelete Record %i, confirm Y/N ? : ", (int) currPID);
                reply[0] = ' ';
                fgets(reply, 3, stdin);
                if (reply[0]=='Y' || reply[0]=='y') {
                    action=DEL;
    		}
            }

    	    // Execute SP for Update, Insert or Delete
            retcode = SQLExecute (hstmt);
            CHECK_ERROR(retcode, "SQLExecute(SQL_HANDLE_STMT)",
                       hstmt, SQL_HANDLE_STMT);

            retcode = SQLMoreResults(hstmt);
	    if (retcode != SQL_NO_DATA) {
		CHECK_ERROR(retcode, "SQLMoreResults(SQL_HANDLE_STMT)",
                           hstmt, SQL_HANDLE_STMT);
		goto exit;
	    }

            if (action==INS) {
                currPID=pPersonID;
    	    }
            action=SEL;
        }

    	// Clear buffers
        memset (strFirstName, ' ', FIRSTNAME_LEN);
        memset (strLastName, ' ', LASTNAME_LEN);
        memset (strAddress, ' ', ADDRESS_LEN);
        memset (strCity, ' ', CITY_LEN);

    	// Reset buffer lengths
        lFirstName=0;
        lLastName=0;
        lAddress=0;
        lCity=0;

    	// Execute SP for SELECT or SELECT ALL
        retcode = SQLExecute (hstmt);
        CHECK_ERROR(retcode, "SQLExecute(SQL_HANDLE_STMT)",
                    hstmt, SQL_HANDLE_STMT);

    	// Loop until SQLMoreResults() says SQL_NO_DATA
        do {

    	    // Get number of columns in results set
            retcode=SQLNumResultCols(hstmt, &columns);
            CHECK_ERROR(retcode, "SQLNumResultCols()", hstmt, SQL_HANDLE_STMT);
            if (columns > 0) {
    			rowCount=0;
    		// Fetch records in results set
                while (SQLFetch(hstmt) != SQL_NO_DATA) {
            	    // Loop through the columns
    		    rowCount++;
            	    memset (buf, ' ', 255);
                    printf ("\n");

    		    // Get the column data
            	    for (i = 1; i <= columns; i++) {
            		retcode = SQLGetData(hstmt, i, SQL_C_CHAR,
                                             buf, 255, &indicator);
            		if (SQL_SUCCEEDED(retcode)) {
                            switch (i) {
                                case 1:
                                    pPersonID=atoi(buf);
                                break;
                                case 2:
                                    strcpy (strFirstName, buf);
                                break;

                                case 3:
                                    strcpy (strLastName, buf);
                                break;

                                case 4:
                                    strcpy (strAddress, buf);
                                break;

                                case 5:
                                    strcpy (strCity, buf);
                                break;
                            }
            		}
            	    }

    		    // Display column data
                    printf ("\nPersonID  : %i", pPersonID);
                    printf ("\nFirstName : %.20s", strFirstName);
                    printf ("\nLastName  : %.20s", strLastName);
                    printf ("\nAddress   : %.20s", strAddress);
                    printf ("\nCity      : %.20s", strCity);
                }
            }

            // Check we have a current person ID. If no records found or
    	    // action is ALL records, we don't have a current record so
    	    // set Person ID to -1 which will remove the INSERT and UPDATE
    	    // options from getAction().
            if (rowCount==0) {
    		if (action==SEL) {
                    printf ("\nNo Records Matching : %i", (int) pPersonID);
    		}
                // Reset current Person ID
                currPID=-1;
            }
            action=getAction ((int *) &currPID);
    	    if (currPID==0) goto exit;
        } while ((retcode=SQLMoreResults(hstmt)) == SQL_SUCCESS);
    }

exit:

    // Free temporary buffer used for formatting record data
    free (buf);

    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.