<faircom>\drivers\sql.odbc\tutorials\ODBCTutorial3.c
Now we will explore row/record locks using the FairCom DB SQL ODBC Interface.
The functionality for this tutorial focuses on inserting/adding rows/records, then updating a single row/record in the customer master table under locking control. The application will pause after a LOCK is placed on a row/record. Another instance of this application should then be launched, which will block, waiting on the lock held by the first instance. Pressing the <Enter> key will enable the first instance to proceed. This will result in removing the lock thereby allowing the second instance to continue execution. Launching two processes provides a visual demonstration of the effects of locking and a basis for experimentation on your own.
As with all other examples in the c-tree tutorial series, this tutorial simplifies the creation and use of a database into four simple steps: Initialize(), Define(), Manage(), and you’re Done() !
Tutorial #3: Locking
Here we demonstrate the enforcement of data integrity by introducing record/row "locking".
- Initialize() - Connects to the FairCom Database Engine.
- Define() - Defines and creates a "customer master" (custmast) table/file.
- Manage() - Adds a few rows/records, manipulates them, and displays the results.
- Done() - Disconnects from FairCom Database Engine.
Note our simple Main() function:
/*
* main()
*
* The main() function implements the concept of "init, define, manage
* and you're done..."
*/
int main(int argc, char* argv[])
{
Initialize();
Define();
Manage();
Done();
printf("\nPress <ENTER> key to exit . . .\n");
getchar();
return(0);
}
We suggest opening the source code with your own editor.
Continue now to review these four steps.
Init
First we need to open a connection to a database by providing the FairCom Database Engine with a user name, password and the database name.
Below is the code for Initialize():
/*
* Initialize()
*
* Perform the minimum requirement of logging onto the c-tree Server
*/
void Initialize(void)
{
RETCODE rc;
printf("INIT\n");
/* allocate environment handle */
if ((rc = SQLAllocEnv(&hEnv)) != SQL_SUCCESS)
Handle_Error(0, NULL, "SQLAllocEnv()");
/* allocate connection handle */
if ((rc = SQLAllocConnect(hEnv, &hDbc)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_ENV, hEnv, "SQLAllocConnect()");
/* connect to server */
printf("\tLogon to server...\n");
if ((rc = SQLConnect(hDbc, MY_DSN, SQL_NTS, "admin", SQL_NTS, "ADMIN", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLConnect()");
/* disable commit after each single SQL statement */
if ((rc = SQLSetConnectAttr(hDbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLSetConnectAttr(SQL_AUTOCOMMIT_OFF)");
/* allocate statement handle */
if ((rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLAllocHandle(SQL_HANDLE_STMT)");
}
Define
Define() establishes specific data definitions. This involves defining columns/fields and creating the tables/files with optional indexes.
Below is the code for Define():
/*
* Define()
*
* Create the table for containing a list of existing customers
*/
void Define(void)
{
RETCODE rc;
printf("DEFINE\n");
/* create table */
printf("\tCreate table...\n");
if ((rc = SQLExecDirect(hStmt,
"CREATE TABLE custmast ( \
cm_custnumb CHAR(4), \
cm_custzipc CHAR(9), \
cm_custstat CHAR(2), \
cm_custrtng CHAR(1), \
cm_custname VARCHAR(47), \
cm_custaddr VARCHAR(47), \
cm_custcity VARCHAR(47))",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE TABLE)");
if ((rc = SQLExecDirect(hStmt,
"CREATE UNIQUE INDEX cm_custnumb_idx ON custmast (cm_custnumb)",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE INDEX)");
if ((rc = SQLExecDirect(hStmt, "COMMIT WORK", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(COMMIT WORK)");
}
Manage
Manage() provides data management functionality for your application and/or process.
Below is the code for Manage():
/*
* Manage()
*
* This function performs simple record functions of add, delete and gets
*/
void Manage(void)
{
printf("MANAGE\n");
/* delete any existing records */
Delete_Records();
/* populate the table with data */
Add_CustomerMaster_Records();
/* display contents of table */
Display_Records();
/* update a record under locking control */
Update_CustomerMaster_Record();
/* display again after update and effects of lock */
Display_Records();
}
/*
* Delete_Records()
*
* This function deletes all the records in the table
*/
void Delete_Records(void)
{
RETCODE rc;
printf("\tDelete records...\n");
if ((rc = SQLExecDirect(hStmt, "DELETE FROM custmast", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(DELETE)");
if ((rc = SQLExecDirect(hStmt, "COMMIT WORK", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(COMMIT WORK)");
}
/*
* Add_CustomerMaster_Records()
*
* This function adds records to a table from an array of strings
*/
void Add_CustomerMaster_Records(void)
{
RETCODE rc;
SQLINTEGER i;
SQLTCHAR sCommand[512];
SQLTCHAR *data[] = {
"('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')",
"('1001','61434','CT','1','Michael Jordan','13 Main','Harford')",
"('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')",
"('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')"
};
SQLINTEGER nRecords = sizeof(data) / sizeof(data[0]);
printf("\tAdd records...\n");
/* add one record at time to table */
for (i = 0; i < nRecords; i++)
{
strcpy (sCommand, "INSERT INTO custmast VALUES ");
strcat (sCommand, data[i]);
if ((rc = SQLExecDirect(hStmt, sCommand ,SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");
}
if ((rc = SQLExecDirect(hStmt, "COMMIT WORK", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(COMMIT WORK)");
}
/*
* Display_Records()
*
* This function displays the contents of a table.
*/
void Display_Records(void)
{
RETCODE rc;
SQLTCHAR custnumb[4+1];
SQLTCHAR custname[47+1];
SDWORD cbData;
printf("\tDisplay records...");
rc = SQLExecDirect(hStmt, "SELECT * FROM custmast" ,SQL_NTS);
if (rc != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(SELECT)");
/* fetch and display each individual record */
while ((rc = SQLFetch(hStmt)) == SQL_SUCCESS)
{
SQLGetData(hStmt, 1, SQL_C_CHAR, custnumb, sizeof(custnumb), &cbData);
SQLGetData(hStmt, 5, SQL_C_CHAR, custname, sizeof(custname), &cbData);
printf("\n\t\t%-8s%10s\n", custnumb, custname);
}
SQLFreeStmt(hStmt,SQL_CLOSE);
}
/*
* Update_CustomerMaster_Records()
*
* Update one record under locking control to demonstrate the effects
* of locking
*/
void Update_CustomerMaster_Record(void)
{
RETCODE rc;
printf("\tUpdate record...\n");
rc = SQLExecDirect(hStmt, "UPDATE custmast SET cm_custname = 'KEYON DOOLING' WHERE cm_custnumb = '1003'", SQL_NTS);
if (rc != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(UPDATE)");
printf("\tPress <ENTER> key to unlock\n");
getchar();
if ((rc = SQLExecDirect(hStmt, "COMMIT WORK", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(COMMIT WORK)");
SQLFreeStmt(hStmt,SQL_CLOSE);
}
Done
When an application and/or process has completed operations with the database, it must release resources by closing the open files and disconnecting from the database engine.
Below is the code for Done():
/*
* Done()
*
* This function handles the housekeeping of closing connection and
* freeing of associated memory
*/
void Done(void)
{
RETCODE rc;
printf("DONE\n");
/* free statement handle */
if ((rc = SQLFreeHandle(SQL_HANDLE_STMT, hStmt)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLFreeHandle(SQL_HANDLE_STMT)");
/* re-enable autocommit */
if ((rc = SQLSetConnectAttr(hDbc, SQL_ATTR_AUTOCOMMIT, (void *)SQL_AUTOCOMMIT_ON, 0)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLSetConnectAttr(SQL_AUTOCOMMIT_ON)");
/* disconnect from server */
printf("\tLogout...\n");
if ((rc = SQLDisconnect(hDbc)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLDisconnect()");
/* free connection handle */
if ((rc = SQLFreeHandle(SQL_HANDLE_DBC, hDbc)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_ENV, hEnv, "SQLFreeHandle(SQL_HANDLE_DBC)");
/* free environment handle */
if ((rc = SQLFreeHandle(SQL_HANDLE_ENV, hEnv)) != SQL_SUCCESS)
Handle_Error(0, NULL, "SQLFreeHandle(SQL_HANDLE_ENV)");
}
Additional Resources
We encourage you to explore the additional resources listed here:
- Complete source code for this tutorial can be found in ODBCTutorial3.c in your installation directory, within the <faircom>\drivers\sql.odbc\tutorials directory for your platform.
- Additional documentation for other APIs and administering the FairCom DB Database Engine may be found in the Documentation section of the FairCom website.