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