/**********************************************************************
* FILENAME :    CallSPUtil.c
*
* DESCRIPTION :
*               Utility functions for Stored Procedure exmples:
*               	DropProcedure () 	- Drop strored procedure
*               	CreateProcedure ()	- Create stored proecdure
*               	ListProcedure()		- Lookup and list stored procudure
*
*               Procedure referenced are:
*
*                   {? = CALL Insert_Record (?, ?, ?, ?, ?, ?)}
*                   {CALL SelectRecords()}
*                   {CALL Find_Record(?)}
*                   {CALL Multi_Result_Sets()}
*                   {? = CALL InOutRet_Params (?, ?)}
*                   {? = CALL Find_ByID (?, ?)}
*                   {? = CALL Find_ByName (?, ?)}
*                   {? = CALL Edit_Record (?, ?, ?, ?, ?, ?)}
*
*               See below for individual details.
*
* ODBC USAGE :
*           	DropProcedure()   - Calls SQLExecDirect() with statement that
*           			    checks procedure exist before dropping it.
*           	CreateProcedure() - Calls SQLExecDirect() to create a stored
*           			    procedure.
*           			    Insert_Record for use in these tests.
*           	List Procedure()  - Used to show one of the above procedures
*           			    is defined by lookup using SQLProcedures()
*           			    and SQLBindCol() for the columns returned.
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>

#define BUFF_SIZE 255
#define FIRSTNAME_LEN 256
#define LASTNAME_LEN 256
#define ADDRESS_LEN 256
#define CITY_LEN 256

//
// Create Stored procedure
// Procedure is called either Insert_Record or Select_Records
//
SQLRETURN CreateProcedure (SQLHDBC hdbc, char *ProcName) {

    SQLHSTMT hstmt = SQL_NULL_HSTMT;  	// Statement handle
    SQLRETURN retcode = SQL_SUCCESS;	// Return status

	SQLCHAR strCreateSP [1024];

	if (strcmp(ProcName, "Insert_Record") == 0) {
		// Stored Procedure: Insert_Record
		//
		// Called by: CallSPINsertRec.c
		//
		// Usage {? = CALL Insert_Record (?, ?, ?, ?, ?, ?)}
		//
		// Has 7 Parameters - 1   - Proc return value (Prov RETURN value)
		//		      2-5 - Table columns (INPUT)
		//                    6   - Matching records count (OUTPUT)
		//		      7   - Total records Count (OUTPUT)
		//
		// Creates Stored Proc 'Insert_Record'
		// Inserts record into TestTBL1 using params 2-5
		// Generates result set of entire table
		// Generates result set of records matching FirstName just inserted
		// Returns number of records in table
		// Returns number of records matching FirstName just inserted
		// Function returns 100
		//
		strcpy(strCreateSP, "CREATE PROCEDURE Insert_Record ("
		    "@pFirstName nvarchar(256), @pLastName varchar(256), "
		    "@pAddress varchar(256), @pCity varchar(256), "
		    "@MatchingRecs int OUTPUT, @TotalRecs int OUTPUT) AS "
		    "INSERT INTO TestTBL1 (FirstName, LastName, Address, City) "
		           "VALUES (@pFirstName, @pLastName, @pAddress, @pCity); "
		    "SELECT * FROM TestTBL1 ORDER BY PersonID; "
		    "SELECT * FROM TestTBL1 WHERE FirstName=@pFirstName; "
		    "SELECT @MatchingRecs=count(*) FROM TestTBL1 WHERE "
					"FirstName=@pFirstName; "
		    "SELECT @TotalRecs=count(*) FROM TestTBL1; "
		    "RETURN 100;");

	} else if (strcmp(ProcName, "Select_Records") == 0) {
		// Stored Procedure : 'Select_Records' (no param example)
		// Called By : CallSP.c
		//
		// Usage {CALL SelectRecords()}
		//
		// Has no parameters
		//
		// Creates Stored Procedure 'Select_Records'
		// Generates result set of entire table
		//
		strcpy (strCreateSP, "CREATE PROCEDURE Select_Records AS "
			"SELECT PersonID, FirstName, LastName, Address, City "
			"FROM TestTBL1");

	} else if (strcmp(ProcName, "Find_Record") == 0) {
		// Stored Procedure : 'Find_Record'
		// Called By : CallSPWithParam.c
		//
		// Usage {CALL Find_Record(?)}
		//
		// Has 1 Input Parameter - pPersonID (INPUT)
		//
		// Creates stored procedure 'Find_Record'
		// Generates result set of records matching pPersonID
		//
		strcpy (strCreateSP, "CREATE PROCEDURE Find_Record (@pPersonID INT) "
			"AS SELECT PersonID, FirstName, LastName, Address, City "
			"FROM TestTBL1 WHERE PersonID=@pPersonID");

	} else if (strcmp(ProcName, "Multi_Result_Sets") == 0) {
			// Stored Procedure : 'Multi_Result_Sets'
			// Called By : CallSPMultiResultSet.c
			//
			// Usage {CALL Multi_Result_Sets()}
			//
			// Has no parameters
			//
			// Creates stored procedure Multi_Result_Sets
			// Generates results set for all records in TestTBL1
			// Generates results set for all records in TestTBLCopy
			//
			strcpy (strCreateSP, "CREATE PROCEDURE Multi_Result_Sets AS "
				"SELECT PersonID, FirstName, LastName, Address, City "
				"FROM TestTBL1; "
				"SELECT PersonID, FirstName, LastName, Address, City "
				"FROM TestTBL1Copy");

	} else if (strcmp(ProcName, "InOutRet_Params") == 0) {
		// Stored Procedure : 'InOutRet_Params'
		// Called By : CallSPWithInOutParams.c
		//
		// Usage {? = CALL InOutRet_Params (?, ?)}
		//
		//  Has 3 Parameters - 1 - Proc return value (Proc RETURN value)
		//		       2 - table field PersonID (INPUT)
		//                     3 - matching record count (OUTPUT)
		//
		// Creates stored procedure 'InOutRet_Params'
		// Generates result set for records matching the input parameter
		// Returns number of records in result set
		// Function returns 100
		//
		strcpy(strCreateSP, "CREATE PROCEDURE InOutRet_Params ("
		    "@pPersonID int, @MatchingRecs int OUTPUT) AS "
			"SELECT PersonID, FirstName, LastName, Address, City "
			"FROM TestTBL1 WHERE PersonID=@pPersonID; "
		    "SELECT @MatchingRecs=count(*) FROM TestTBL1 WHERE "
					"PersonID=@pPersonID; "
		    "RETURN 100;");

	} else if (strcmp(ProcName, "Find_ByID") == 0) {
		// Stored Procedure : Find_ByID
		// Called By : CallSPFindID.c
		//
		// Usage {? = CALL Find_ByID (?, ?)}
		//
		// Uses 3 Parameters - 1 - return value
		//		       2 - pPersonIDs parameter array element (INPUT)
		//		       3 - pPersonIXs parameter array element (INPUT)
		//
		// Creates stored procedure Find_ByID
		// Generates a result set that matches the pPersonID parameter
		// Returns the pPersonIX. This can be used to tie the result set
		// to the pPersonID that generated it
		strcpy (strCreateSP, "CREATE PROCEDURE Find_ByID "
			"(@pPersonID int, @pPersonIX int) AS SELECT PersonID, FirstName, "
			"LastName, Address, City FROM TestTBL1 WHERE PersonID=@pPersonID; "
			"RETURN @pPersonIX");

	} else if (strcmp(ProcName, "Find_ByName") == 0) {
		// Stored Procedure : Find_ByName
		// Called By : CalLSPFindName.c
		//
		// Usage {? = CALL Find_ByName (?, ?)}
		//
		// Uses 3 Parameters - 1 - return value
		//		       2 - pFirstName parameter array element (INPUT)
		//		       3 - pPersonIXs parameter array element (INPUT)
		//
		// Creates stored procedure Find_ByName
		// Generates a result set that matches the pFirstName parameter
		// Returns the pPersonIX. This can be used to tie the result set
		// to the pFirstName that generated it
		strcpy (strCreateSP, "CREATE PROCEDURE Find_ByName ("
			"@pFirstName varchar(2), @pPersonIX int) "
			"AS SELECT PersonID, FirstName, LastName, Address, City "
			"FROM TestTBL1 WHERE FirstName LIKE @pFirstName+'%'; "
			"RETURN @pPersonIX");

	} else if (strcmp(ProcName, "Edit_Record") == 0) {
		// Stored Procedure : Edit_Record
		// Called By : CallSPEditRecord.c
		//
		// Usage {? = CALL Edit_Record (?, ?, ?, ?, ?, ?)}
		//
	        // Uses 7 Parameters - 1   - Proc return value   - RETURNED
	        //                     2   - Action - See below  - INPUT
	        //                     3   - RecordID            - INPUT
	        //                     4-7 - if action is SELECT
	        //                         - old column values   - OUTPUT
	        //                     4-7 - if action is UPDATE or INSERT
	        //                         - new column values   - INPUT
	        //		       4-7 - if action is DELETE
	        //			   - unused, delete is via RecordID
	        //
		// Action values are SELECT RECORD 0
		// 					 UPDATE 	   1
		//					 INSERT        2
		//					 DELETE        3
		//					 SELECT ALL    4
		//

		strcpy (strCreateSP, "CREATE PROCEDURE Edit_Record ("
			"@action int, @pPersonID int OUTPUT, "
			"@pFirstName nvarchar(256), @pLastName nvarchar(256), "
			"@pAddress nvarchar(256), @pCity nvarchar(256)) "
			"AS "
			"IF (@action=0) BEGIN "
					"SELECT PersonID, FirstName, LastName, Address, City "
					"FROM TestTBL1 WHERE PersonID=@pPersonID; "
				"END "
			"ELSE "
				"IF (@action=1) BEGIN "
					"UPDATE TestTBL1 "
					"SET FirstName=@pFirstName, LastName=@pLastName, "
					"Address=@pAddress, City=@pCity "
					"WHERE PersonID=@pPersonID; "
				"END "
			"ELSE "
				"IF (@action=2) BEGIN "
					"INSERT INTO TestTBL1 "
					"(FirstName, LastName, Address, City) "
					"VALUES (@pFirstName, @pLastName, @pAddress, @pCity);"
					"SET @pPersonID=@@IDENTITY; "
				"END "
			"ELSE "
				"IF (@action=3) BEGIN "
					"DELETE FROM TestTBL1 WHERE PersonID=@pPersonID; "
				"END "
			"ELSE "
				"IF (@action=4) BEGIN "
					"SELECT PersonID, FirstName, LastName, Address, City "
					"FROM TestTBL1 ORDER BY PersonID; "
				"END "
			"RETURN @action");
	}

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

    // Execute Create Procedure
    retcode = SQLExecDirect (hstmt, strCreateSP, SQL_NTS);
    CHECK_ERROR(retcode, "SQLExecDirect(CREATESP)",
                hstmt, SQL_HANDLE_STMT);

exit:

    // Free statement handle
    if (hstmt != SQL_NULL_HSTMT)
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);

    return retcode;
}

//
// Function to drop existing stored proc. Either Insert_Record or
// Select_Records, which are the two we are using in these examples.
//
SQLRETURN DropProcedure (SQLHDBC  hdbc, char *ProcName) {

    SQLHSTMT hstmt = SQL_NULL_HSTMT;  	// Statement handle
    SQLRETURN retcode = SQL_SUCCESS;	// Return status

    SQLCHAR   strDropSP[1024];

    sprintf (strDropSP, "IF EXISTS (SELECT * FROM sys.objects WHERE "
    		        "type='P' AND name='%s') DROP PROCEDURE %s", ProcName, ProcName);

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

    // Execute Drop Procedure
    retcode = SQLExecDirect (hstmt, strDropSP, SQL_NTS);
    CHECK_ERROR(retcode, "SQLExecDirect(SQL_HANDLE_STMT)", hstmt,
                SQL_HANDLE_STMT);

exit:

    // Free statement handle
    if (hstmt != SQL_NULL_HSTMT)
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);

    return retcode;
}

//
// Function to list the stored proc we are using in this example.
// Uses SQLProcedures () to find stored proc Insert_Record or Select_Records.
//
SQLRETURN ListProcedure (SQLHDBC hdbc, char *ProcName) {

    // Columns for binding to SQLProcedures() results set
    SQLCHAR strProcedureCat[BUFF_SIZE];
    SQLCHAR strProcedureSchema[BUFF_SIZE];
    SQLCHAR strProcedureName[BUFF_SIZE];
    SQLSMALLINT ProcedureType;

    SQLLEN  lenProcedureCat, lenProcedureSchema;
    SQLLEN  lenProcedureName, lenProcedureType;

    int header=0;
    SQLHSTMT hstmt = SQL_NULL_HSTMT;  	// Statement handle
    SQLRETURN retcode = SQL_SUCCESS;	// Return status

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

    retcode = SQLProcedures (hstmt,
                             NULL, 0,
                             NULL, 0,
                             ProcName, strlen(ProcName));

    if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
        // Bind columns in result set to buffers
        // column 1 is the catalogue
        SQLBindCol(hstmt, 1, SQL_C_CHAR, strProcedureCat,
                   sizeof(strProcedureCat), &lenProcedureCat);

        // column 2 is the schema
        SQLBindCol(hstmt, 2, SQL_C_CHAR, strProcedureSchema,
                   sizeof(strProcedureSchema), &lenProcedureSchema);

        // column 3 is the procedure name
        SQLBindCol(hstmt, 3, SQL_C_CHAR, strProcedureName,
                   sizeof(strProcedureName), &lenProcedureName);

        // columns 4 to 7 are skipped
        // column 8 is the procedure type
        SQLBindCol(hstmt, 8, SQL_C_SHORT,&ProcedureType,
                   sizeof(ProcedureType), &lenProcedureType);

        // fetch results (only expecting one row)
        while (SQL_SUCCESS == retcode) {
            retcode = SQLFetch(hstmt);

            if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {
                extract_error("SQLFetch Error : ", hstmt, SQL_HANDLE_STMT);
            }

            if (header++==0) {
                printf ("\nDSN : SQLSRV");
            }

            if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
                printf ("\nProcedure Cat    : %s\n", strProcedureCat);
                printf ("Procedure Schema : %s\n", strProcedureSchema);
                printf ("Procedure Name   : %s\n", strProcedureName);
                printf ("Procedure Type   : ");
                switch (ProcedureType) {
                    case SQL_PT_PROCEDURE:
                        printf ("%s\n", "SQL_PT_PROCEDURE");
                        break;
                    case SQL_PT_FUNCTION:
                        printf ("%s\n", "SQL_PT_FUNCTION");
                        break;
                    case SQL_PT_UNKNOWN:
                    default:
                        printf ("%s\n", "SQL_PT_UNKNOWN");
                        break;
                }
            }

            if (retcode==SQL_NO_DATA && header==1) {
                printf ("(NO DATA)\n");
            }
        }
    }

exit:

    // Free statement handle
    if (hstmt != SQL_NULL_HSTMT)
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);

    // if last status is no data it is ok
    if (retcode==SQL_NO_DATA) {
    	retcode=SQL_SUCCESS;
    }

    return retcode;
}


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