/**********************************************************************
* FILENAME : Transactions.c
*
* DESCRIPTION :
* Example illustrates use of SQLSetConnectAttr with SQL_ATTR_AUTOCOMMIT
* and SQLEndTran to manually or automatically commit an update
* on a table with an identifier field
*
* ODBC USAGE :
* Prompts for Auto Commit or Manual Commit mode
* SQLSetConnectAttr with SQL_ATTR_AUTOCOMMIT to set commit mode
* SQLBindParameter to bind 4 parameters into
* SQLPrepare to get ready an INSERT
* SQLBindParameter to establish parameter bindings for the INSERT
* Prompts for data to be used in the insert
* SQLExecute to execute the statement
* If Manual commit specified, asks whether SQLEndTran called or not to
* either commit or rollback
* Reads table and displays records.
* NOTE: Behaviour of the identity column PersonID. Whether Commit or
* Rollback is sent, this is incremented for each record. i.e.
* a gap in the identity sequence will appear for records that where
* used Rollback.
*
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include "util.c"
#define LASTNAME_LEN 255
#define FIRSTNAME_LEN 255
#define ADDRESS_LEN 255
#define CITY_LEN 255
#define TRUE 1
#define FALSE 0
SQLLEN iPersonID;
SQLCHAR strFirstName[FIRSTNAME_LEN];
SQLCHAR strLastName[LASTNAME_LEN];
SQLCHAR strAddress[ADDRESS_LEN];
SQLCHAR strCity[CITY_LEN];
SQLLEN lenPersonID=0, lenFirstName=0, lenLastName=0, lenAddress=0, lenCity=0;
int main () {
SQLHENV henv = SQL_NULL_HENV; // Environment
SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
SQLRETURN retcode;
char inputBuff[5];
char commitMode='A'; // A - Auto, M - Manual
char completionType='C'; // C - COMMIT, R - ROLLBACK
char reply=' ';
// Get commit type - A Automatic, M Manual
reply=getStr ("Commit Type (A - Auto, M - Manual)",
inputBuff, sizeof(inputBuff), 'N');
commitMode=inputBuff[0];
commitMode=toupper(commitMode);
if (commitMode != 'A' && commitMode != 'M') commitMode='A';
// Allocate environment
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
// Set ODBC version
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER*)SQL_OV_ODBC3, 0);
CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)",
henv, SQL_HANDLE_ENV);
// Allocate connection
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
hdbc, SQL_HANDLE_DBC);
// Set Login Timeout and ask for manual/auto commit
retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)",
hdbc, SQL_HANDLE_DBC);
if (commitMode=='A') {
printf ("Setting AUTOCOMMIT AUTO\n");
retcode = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)TRUE, 0);
}
else {
printf ("Setting AUTOCOMMIT MANUAL\n");
retcode = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)FALSE, 0);
}
CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT)",
hdbc, SQL_HANDLE_DBC);
// Connect to DSN
retcode = SQLConnect(hdbc, (SQLCHAR*) "DATASOURCE", SQL_NTS,
(SQLCHAR*) NULL, 0, NULL, 0);
CHECK_ERROR(retcode, "SQLConnect(DATASOURCE)", hdbc, SQL_HANDLE_DBC);
// Allocate Statement Handle
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
// Bind Parameters to all fields
retcode = SQLBindParameter(hstmt, 1,
SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, FIRSTNAME_LEN, 0,
strFirstName, FIRSTNAME_LEN, &lenFirstName);
CHECK_ERROR(retcode, "SQLBindParameter(1)", hstmt, SQL_HANDLE_STMT);
retcode = SQLBindParameter(hstmt, 2,
SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, LASTNAME_LEN, 0,
strLastName, LASTNAME_LEN, &lenLastName);
CHECK_ERROR(retcode, "SQLBindParameter(2)", hstmt, SQL_HANDLE_STMT);
retcode = SQLBindParameter(hstmt, 3,
SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, ADDRESS_LEN, 0,
strAddress, ADDRESS_LEN, &lenAddress);
CHECK_ERROR(retcode, "SQLBindParameter(3)", hstmt, SQL_HANDLE_STMT);
retcode = SQLBindParameter(hstmt, 4,
SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, CITY_LEN, 0,
strCity, CITY_LEN, &lenCity);
CHECK_ERROR(retcode, "SQLBindParameter(4)", hstmt, SQL_HANDLE_STMT);
// Prepare INSERT
retcode = SQLPrepare(hstmt, (SQLCHAR*)
"INSERT INTO TestTBL1 (FirstName, LastName, Address, City)"
"VALUES (?, ?, ?, ?)", SQL_NTS);
CHECK_ERROR(retcode, "SQLPrepare(INSERT)", hstmt, SQL_HANDLE_STMT);
// Set up data and lengths
memset(strFirstName, ' ', FIRSTNAME_LEN);
memset(strLastName, ' ', LASTNAME_LEN);
memset(strAddress, ' ', ADDRESS_LEN);
memset(strCity, ' ', CITY_LEN);
reply=getStr ("First Name", strFirstName, sizeof (strFirstName), 'N');
reply=getStr ("Last Name", strLastName, sizeof (strLastName), 'N');
reply=getStr ("Address ", strAddress, sizeof (strAddress), 'N');
reply=getStr ("City ", strCity, sizeof (strCity), 'N');
// Remove string teminator
strFirstName[strlen(strFirstName)]=' ';
strLastName[strlen(strLastName)]=' ';
strAddress[strlen(strAddress)]=' ';
strCity[strlen(strCity)]=' ';
// Set lengths to field lengths
lenFirstName=sizeof(strFirstName);
lenLastName=sizeof(strLastName);
lenAddress=sizeof(strAddress);
lenCity=sizeof(strCity);
retcode = SQLExecute(hstmt);
CHECK_ERROR(retcode, "SQLExecute(INSERT)", hstmt, SQL_HANDLE_STMT);
if (commitMode=='M') {
reply=getStr ("Completion Type (C - Commit, R - Rollback)",
inputBuff, sizeof(inputBuff), 'N');
completionType=inputBuff[0];
completionType=toupper(completionType);
if (completionType != 'C' && completionType != 'R') commitMode='R';
if (completionType=='C') {
retcode = SQLEndTran (SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
CHECK_ERROR(retcode, "SQLEndTran(SQL_COMMIT)",
hdbc, SQL_HANDLE_DBC);
printf ("Transaction Committed (status %i)\n", retcode);
} else {
retcode = SQLEndTran (SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK);
CHECK_ERROR(retcode, "SQL_HANDLE_DBC(SQL_ROLLBACK)",
hdbc, SQL_HANDLE_DBC);
printf ("Transaction Rolled Back (status %i)\n", retcode);
}
} else {
printf ("Transaction Auto Committed\n");
}
// Read table back ...
retcode = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_ERROR(retcode, "SQLFreeStmt()", hstmt, SQL_HANDLE_STMT);
retcode = SQLExecDirect(hstmt, (SQLCHAR *)
"SELECT * FROM TestTBL1", SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect(SELECT)", hstmt, SQL_HANDLE_STMT);
for ( retcode = SQLFetch(hstmt) ;
retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO ;
retcode = SQLFetch(hstmt) ) {
memset(strFirstName, ' ', FIRSTNAME_LEN);
retcode = SQLGetData(hstmt, 1, SQL_C_USHORT,
(SQLPOINTER)&iPersonID, 0, NULL);
retcode = SQLGetData(hstmt, 2, SQL_C_CHAR,
strFirstName, FIRSTNAME_LEN, &lenFirstName);
retcode = SQLGetData(hstmt, 3, SQL_C_CHAR,
strLastName, LASTNAME_LEN, &lenLastName);
retcode = SQLGetData(hstmt, 4, SQL_C_CHAR,
strAddress, ADDRESS_LEN, &lenAddress);
retcode = SQLGetData(hstmt, 5, SQL_C_CHAR,
strCity, CITY_LEN, &lenCity);
printf ("\nPerson ID : %i", (int)iPersonID);
printf ("\nFirst Name : %.10s", strFirstName);
printf ("\nLast Name : %.10s", strLastName);
printf ("\nAddress : %.10s", strAddress);
printf ("\nCity : %.10s\n", strCity);
}
exit:
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
