/**********************************************************************
* FILENAME : CallSPInsertRecs.c
*
* DESCRIPTION :
* Illustrates using a stored procedure to performs several
* actions relating to inserting a record into a table. It
* shows how to set up input and output parameters and how to
* return a status value from the procedure.
*
* Calls a stored procedure as follows:
*
* {? = CALL Insert_Record (?, ?, ?, ?, ?, ?)}
*
* ODBC USAGE :
* Drops and recreates a procedure 'Insert_Record'
* Confirms the procedure exists via SQLProcedures ()
* Uses SQLBindParameter() to bind data to the 7
* parameters used in the stored procedure.
*
* 7 Parameters - 1 - Proc return value - RETURNED
* 2-5 - table fields - INPUT
* 6 - matching record count - OUTPUT
* 7 - total records - OUTPUT
* Loops asking for user inout for the 4 INPUT fields until
* blank FirstName (param 2) is entered:
* Uses SQLExecute () to execute stored proc which adds
* new person record into TestTBL1 using user input
* Returns all records in the table, ordered by ID
* Returns all records that match new record firstname
* Returns number of matching records
* Returns number of records in table
* Returns 100 as function return value
*
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include "util.c"
#include "CallSPUtil.c"
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
SQLCHAR * strCallSP = "{? = CALL Insert_Record (?, ?, ?, ?, ?, ?)}";
char * strProcName = "Insert_Record";
SQLSMALLINT columns; // Number of columns in result-set
int i, count;
// Stored proc paramaters, output variables and return variable.
SQLCHAR strFirstName[FIRSTNAME_LEN], strLastName[LASTNAME_LEN];
SQLCHAR strAddress[ADDRESS_LEN], strCity[CITY_LEN];
SWORD RetParam = 1, MatchingRecs = 1, TotalRecs = 1;
SQLLEN lFirstName, lLastName, lAddress, lCity;
SQLLEN lRetParam, lMatchingRecs, lTotalRecs;
// 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=DATASOURCE;", SQL_NTS, NULL,
0, NULL, SQL_DRIVER_COMPLETE);
CHECK_ERROR(retcode, "SQLDriverConnect(DATASOURCE)",
hdbc, SQL_HANDLE_DBC);
// Drop previous procedure called Insert_Record
retcode = DropProcedure (hdbc, strProcName);
// Create new procedure called Insert_Record
retcode = CreateProcedure (hdbc, strProcName);
// Use SQLPRocedures() to show Insert_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 parameteres. The order is the order in which they appear
// 1st is return parameter
retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_SSHORT,
SQL_INTEGER, 0, 0, &RetParam, 0, &lRetParam);
CHECK_ERROR(retcode, "SQLBindParameter(1)", hstmt, SQL_HANDLE_STMT);
// 2nd is FirstName passed as input
retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, FIRSTNAME_LEN, 0, strFirstName,
FIRSTNAME_LEN, &lFirstName);
CHECK_ERROR(retcode, "SQLBindParameter(2)", hstmt, SQL_HANDLE_STMT);
// 3rd is LastName passed as input
retcode = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, LASTNAME_LEN, 0, strLastName,
LASTNAME_LEN, &lLastName);
CHECK_ERROR(retcode, "SQLBindParameter(3)", hstmt, SQL_HANDLE_STMT);
// 4th is Address passed as input
retcode = SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, ADDRESS_LEN, 0, strAddress,
ADDRESS_LEN, &lAddress);
CHECK_ERROR(retcode, "SQLBindParameter(4)", hstmt, SQL_HANDLE_STMT);
// 5th is City passed as input
retcode = SQLBindParameter(hstmt, 5, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, CITY_LEN, 0, strCity,
CITY_LEN, &lCity);
CHECK_ERROR(retcode, "SQLBindParameter(5)", hstmt, SQL_HANDLE_STMT);
// 6th is returned as outout and is the number of records that match
// the FirstName of the record inserted. E.g. number of Johns, if
// John was just inserted.
// This is generated by the SELECT @MatchingRecs=count(*) FROM
// TestTBL1 WHERE FirstName=@pFirstName part of the stored proc.
retcode = SQLBindParameter(hstmt, 6, SQL_PARAM_OUTPUT, SQL_C_SSHORT,
SQL_INTEGER, 0, 0, &MatchingRecs, 0,
&lMatchingRecs);
CHECK_ERROR(retcode, "SQLBindParameter(6)", hstmt, SQL_HANDLE_STMT);
// 7th is used to return the total number of records in the table. This
// is generated by the SELECT @TotalRecs=count(*) FROM TestTBL1 part
// of the stored proc.
retcode = SQLBindParameter(hstmt, 7, SQL_PARAM_OUTPUT, SQL_C_SSHORT,
SQL_INTEGER, 0, 0, &TotalRecs, 0, &lTotalRecs);
CHECK_ERROR(retcode, "SQLBindParameter(7)", hstmt, SQL_HANDLE_STMT);
retcode = SQLPrepare (hstmt, strCallSP, SQL_NTS);
CHECK_ERROR(retcode, "SQLPrepare(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
SQLLEN indicator, RowCount;;
char *buf=malloc (255);
// Loop inserting and displaying records until blank firstname entered
while (1) {
// Initialise buffers
memset (strFirstName, ' ', FIRSTNAME_LEN);
memset (strLastName, ' ', LASTNAME_LEN);
memset (strAddress, ' ', ADDRESS_LEN);
memset (strCity, ' ', CITY_LEN);
// Get data to insert, quit loop on blank firstname
getStr ("\nFirst Name", strFirstName, FIRSTNAME_LEN, 'N');
if (strlen(strFirstName) == 0) break;
getStr ("Last Name", strLastName, LASTNAME_LEN, 'N');
getStr ("Address ", strAddress, ADDRESS_LEN, 'N');
getStr ("City ", strCity, CITY_LEN, 'N');
lFirstName=strlen(strFirstName);
lLastName=strlen(strLastName);
lAddress=strlen(strAddress);
lCity=strlen(strCity);
retcode = SQLExecute (hstmt);
CHECK_ERROR(retcode, "SQLExecute(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
// For a more generic example, use SQLDescribeCol here to
// find column names ...
printf ("\nPersonID Firstname Surname Address City");
printf ("\n-------- --------- ------- ------- ----");
// This loop will execute twice, once for each of the SELECT * FROM
// statements in the stored proc. The first gives all records in the
// table, the second gives all records that match the firstname of the
// record just added (so there should be at least one).
// Note, the while SQLFetch returns the records from a result set,
// SQLMoreResults indicates whether there are more results sets.
do {
// SQLNumResultCols() returns number of columns in result set.
// if non zero use SQLFetch until SQL_NO_DATA returned
retcode=SQLNumResultCols(hstmt, &columns);
CHECK_ERROR(retcode, "SQLNumResultCols()", hstmt, SQL_HANDLE_STMT);
printf ("\nColumns : %i", columns);
if (columns > 0) {
printf ("\nStart Fetch ...");
while (SQLFetch(hstmt) != SQL_NO_DATA) {
// Loop through the columns
memset (buf, ' ', 255);
printf ("\n");
for (i = 1; i <= columns; i++) {
// Retrieve column data for column i as a string
retcode = SQLGetData(hstmt, i, SQL_C_CHAR,
buf, 255, &indicator);
if (SQL_SUCCEEDED(retcode)) {
// Output NULL if indicator suggests so
if (indicator == SQL_NULL_DATA) {
strcpy (buf, "NULL");
}
buf=rtrim(buf, ' ');
if (i==1)
printf("%-8s ", buf);
else
printf("%-12s ", buf);
}
}
}
printf ("\nEnd Fetch ...\n");
} else {
// SQLRowCount returns number of rows affected by
// INSERT, UPDATE, DELETE or (if the driver provides) the
// number of rows returned by a SELECT
retcode=SQLRowCount(hstmt, &RowCount);
CHECK_ERROR(retcode, "SQLRowCount()", hstmt, SQL_HANDLE_STMT);
printf ("\nRow count is : %i\n", (int) RowCount);
}
} while (SQLMoreResults(hstmt) == SQL_SUCCESS);
// Output values for matching records and total records
printf("Matching Records : %d\n", MatchingRecs);
printf("Total Records : %d\n", TotalRecs);
// Output return parameter, set to 100 (SQL_NO_DATA) in the stored
// proc, but could be used for something more meaningful.
printf("Return Parameter : %d\n", RetParam);
}
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;
}
Further information
