Site navigation
Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase, DB2, & more.
/**********************************************************************
* 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, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase, DB2, & more.