SQL for databases and ETL tools tutorials

Tutorial	Description
ODBC	The tutorials, in this section, are for C developers who want to use ODBC in their C application.
JDBC	The tutorials, in this section, are for running JDBC in a Java program.

ODBC tutorials

The tutorials, in this section, are for C developers who want to use ODBC in their C application.

Like all other tutorials in the c-tree series, each example simplifies the concepts of database programming into four simple steps:

Initialize()
Define()
Manage()
Done()

This is known as the Initialize(), Define(), Manage(), and You're Done()! approach.

No matter which FairCom interface language you are using, FairCom follows this same high-level flow in all tutorials. This makes it easy for developers to crossover from one language interface to another as these basic concepts apply to all.

`Initialize()`	Every language requires some form of initial `"logon"` or `"connection"` procedure to establish a session with the database. This is done in the `Initialize()` stage of the program.
`Define()`	Database definitions (DDL), table/file schema definitions, Index definitions, table/file creation, and table/file open operations are all addressed in the `Define()` stage of the program.
`Manage()`	This stage of the program is where the database is operated on, as in managing your data. Adding/reading/updating/deleting records/rows are handled in this stage of the program.
`Done()`	When the program ends, the database session should be closed. This stage handles the necessities to `"de-init"`, by closing tables/files and issuing any required `"logoff"` or `"disconnect"` type procedures.

This section provides tutorials that follow the Initialize(), Define(), Manage(), and You're Done()! approach.

Introduction

To compile and run this tutorial in C go to drivers\sql.odbc\tutorials\ODBCTutorial1.c.

This tutorial will take you through the basic use of the FairCom DB SQL ODBC interface.

The following sections provide the code for the four steps.

Simple single table

This program was designed to be as simple as possible.

Program step summary :

Initialize()
Connects to the FairCom Database Engine.
Define()
Defines and creates a customer master (custmast) table/file.
Manage()
Adds a few rows/records, reads the rows/records back from the database, displays the column/field content, and then deletes the rows/records.
Done()
Disconnects from FairCom DB engine

Example 1. Simple Main() function

It is suggested you open the source code with your own editor.

/*
 * 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);
}

Init

To open a connection to a database, provide the FairCom DB engine with:

a username
a password
the database name

Example 2. 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()");

   /* allocate statement handle */
   if ((rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt)) != SQL_SUCCESS)
      Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLAllocHandle(SQL_HANDLE_STMT)");
}

Define

The define step is where specific data definitions are established by your application and/or process. This involves defining columns/fields and creating the tables/files with optional indices.

Example 3. 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)");
}

Manage

The manage step provides data management functionality for your application and/or process.

Example 4. 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_Records();
   /* display contents of table */
   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)");
}
/*
 * Add_Records()
 *
 * This function adds records to a table from an array of strings
 */
void Add_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)");
   }
}
/*
 * 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);
}

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.

Example 5. 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)");
   /* 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)");
}

Relationships

To compile and run this tutorial in C go to drivers\sql.odbc\tutorials\ODBCTutorial2.c.

We will now build table/file relationships using the FairCom DB SQL ODBC interface.

This tutorial will advance the concepts introduced in the first tutorial by expanding the number of tables. This tutorial will define key columns/fields and create specific indexes for each table to form a relational model database.

Relational model and indexing

This example will add a bit more complexity, by introducing multiple tables with related indices in order to form a simple relational database simulating an order entry system. Figure 1, “Relational model tables” is an overview of what will be created

Figure 1. Relational model tables

Example 6. Simple Main() function

It is suggested to open the source code with your own editor.

/*
 * 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);
}

Init

To open a connection to a database, provide the FairCom DB engine with:

a username
a password
the database name

Example 7. 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()");
   /* allocate statement handle */
   if ((rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt)) != SQL_SUCCESS)
      Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLAllocHandle(SQL_HANDLE_STMT)");
}

Define

The define step is where specific data definitions are established by your application and/or process. This involves defining columns/fields and creating the tables/files with optional indices.

Example 8. Code for Define()

/*
 * Define()
 *
 * Create the tables
 */
void Define(void)
{
   printf("DEFINE\n");
   Create_CustomerMaster_Table();
   Create_CustomerOrders_Table();
   Create_OrderItems_Table();
   Create_ItemMaster_Table();
}
/*
 * Create_CustomerMaster_Table()
 *
 * Create the CustomerMaster
 */
void Create_CustomerMaster_Table(void)
{
   RETCODE  rc;
   /* define table CustomerMaster */
   printf("\ttable CustomerMaster\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)");
}
/*
 * Create_CustomerOrders_Table()
 *
 * Create the table CustomerOrders
 */
void Create_CustomerOrders_Table(void)
{
   RETCODE  rc;
   /* define table CustomerOrders */
   printf("\ttable CustomerOrders\n");
   if ((rc = SQLExecDirect(hStmt,
      "CREATE TABLE custordr ( \
         co_ordrdate DATE, \
         co_promdate DATE, \
         co_ordrnumb CHAR(6), \
         co_custnumb CHAR(4))",
      SQL_NTS)) != SQL_SUCCESS)
      Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE TABLE)");
   if ((rc = SQLExecDirect(hStmt,
      "CREATE UNIQUE INDEX co_ordrnumb_idx ON custordr (co_ordrnumb)",
      SQL_NTS)) != SQL_SUCCESS)
      Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE INDEX)");
   if ((rc = SQLExecDirect(hStmt,
      "CREATE INDEX co_custnumb_idx ON custordr (co_custnumb)",
      SQL_NTS)) != SQL_SUCCESS)
      Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE INDEX)");
}
/*
 * Create_OrderItems_Table()
 *
 * Create the table OrderItems
 */
void Create_OrderItems_Table(void)
{
   RETCODE  rc;
   /* define table OrderItems */
   printf("\ttable OrderItems\n");
   if ((rc = SQLExecDirect(hStmt,
      "CREATE TABLE ordritem ( \
         oi_sequnumb SMALLINT, \
         oi_quantity SMALLINT, \
         oi_ordrnumb CHAR(6), \
         oi_itemnumb CHAR(5))",
      SQL_NTS)) != SQL_SUCCESS)
      Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE TABLE)");
   if ((rc = SQLExecDirect(hStmt,
      "CREATE UNIQUE INDEX oi_ordrnumb_idx ON ordritem (oi_ordrnumb, oi_sequnumb)",
      SQL_NTS)) != SQL_SUCCESS)
      Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE INDEX)");
   if ((rc = SQLExecDirect(hStmt,
      "CREATE INDEX oi_itemnumb_idx ON ordritem (oi_itemnumb)",
      SQL_NTS)) != SQL_SUCCESS)
      Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE INDEX)");
}
/*
 * Create_ItemMaster_Table()
 *
 * Create the table ItemMaster
 */
void Create_ItemMaster_Table(void)
{
   RETCODE  rc;
   /* define table ItemMaster */
   printf("\ttable ItemMaster\n");
   if ((rc = SQLExecDirect(hStmt,
      "CREATE TABLE itemmast ( \
         im_itemwght INTEGER, \
         im_itempric MONEY, \
         im_itemnumb CHAR(5), \
         im_itemdesc VARCHAR(47))",
      SQL_NTS)) != SQL_SUCCESS)
      Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE TABLE)");
   if ((rc = SQLExecDirect(hStmt,
      "CREATE UNIQUE INDEX im_itemnumb_idx ON itemmast (im_itemnumb)",
      SQL_NTS)) != SQL_SUCCESS)
      Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE INDEX)");
}

Manage

The manage step provides data management functionality for your application and/or process.

Example 9. Code for Manage()

/*
 * Manage()
 *
 * Populates table and perform a simple query
 *
 */
void Manage(void)
{
   RETCODE  rc;
   SQLTCHAR custname[47+1];
   SQLREAL  total;
   SDWORD   cbData;
   printf("MANAGE\n");
   /* populate the tables with data */
   Add_CustomerMaster_Records();
   Add_CustomerOrders_Records();
   Add_OrderItems_Records();
   Add_ItemMaster_Records();
   /* perform a query:
      list customer name and total amount per order
      name               total
      @@@@@@@@@@@@@      $xx.xx
      for each order in the CustomerOrders table
         fetch order number
         fetch customer number
         fetch name from CustomerMaster table based on customer number
         for each order item in OrderItems table
            fetch item quantity
            fetch item number
            fetch item price from ItemMaster table based on item number
         next
      next
   */
   printf("\n\tQuery Results\n");
   if ((rc = SQLExecDirect(hStmt,
         "SELECT cm_custname, SUM(im_itempric * oi_quantity) "
         "FROM custmast, custordr, ordritem, itemmast "
         "WHERE co_custnumb = cm_custnumb AND co_ordrnumb = oi_ordrnumb AND oi_itemnumb = im_itemnumb "
         "GROUP BY cm_custnumb, cm_custname",
      SQL_NTS)) != SQL_SUCCESS)
      Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(SELECT)");
   /* for each order in the CustomerOrders table */
   while ((rc = SQLFetch(hStmt)) == SQL_SUCCESS)
   {
      if ((rc = SQLGetData(hStmt, 1, SQL_C_CHAR, custname, sizeof(custname), &cbData)) != SQL_SUCCESS)
         Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLGetData()");
      if ((rc = SQLGetData(hStmt, 2, SQL_C_FLOAT, &total, sizeof(total), &cbData)) != SQL_SUCCESS)
         Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLGetData()");
      /* output data to stdout */
      printf("\t\t%-20s %.2f\n", custname, total);
   }
   SQLFreeStmt(hStmt, SQL_CLOSE);
}
/*
 * Add_CustomerMaster_Records()
 *
 * This function adds records to table CustomerMaster 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]);
   Delete_Records("custmast");
   printf("\tAdd records in table CustomerMaster...\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)");
   }
}
/*
 * Add_CustomerOrders_Records()
 *
 * This function adds records to table CustomerOrders from an
 * array of strings
 */
void Add_CustomerOrders_Records(void)
{
   RETCODE     rc;
   SQLINTEGER  i;
   SQLTCHAR    sCommand[512];
   SQLTCHAR    *data[] = {
      "('09/01/2002','09/05/2002','1','1001')",
      "('09/02/2002','09/06/2002','2','1002')"
   };
   SQLINTEGER  nRecords = sizeof(data) / sizeof(data[0]);
   Delete_Records("custordr");
   printf("\tAdd records in table CustomerOrders...\n");
   /* add one record at time to table */
   for (i = 0; i < nRecords; i++)
   {
      strcpy (sCommand, "INSERT INTO custordr VALUES ");
      strcat (sCommand, data[i]);
      if ((rc = SQLExecDirect(hStmt, sCommand ,SQL_NTS)) != SQL_SUCCESS)
         Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");
   }
}
/*
 * Add_OrderItems_Records()
 *
 * This function adds records to table OrderItems from an
 * array of strings
 */
void Add_OrderItems_Records(void)
{
   RETCODE     rc;
   SQLINTEGER  i;
   SQLTCHAR    sCommand[512];
   SQLTCHAR    *data[] = {
      "(1,2,'1','1')",
      "(2,1,'1','2')",
      "(3,1,'1','3')",
      "(1,3,'2','3')"
   };
   SQLINTEGER  nRecords = sizeof(data) / sizeof(data[0]);
   Delete_Records("ordritem");
   printf("\tAdd records in table OrderItems...\n");
   /* add one record at time to table */
   for (i = 0; i < nRecords; i++)
   {
      strcpy (sCommand, "INSERT INTO ordritem VALUES ");
      strcat (sCommand, data[i]);
      if ((rc = SQLExecDirect(hStmt, sCommand ,SQL_NTS)) != SQL_SUCCESS)
         Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");
   }
}
/*
 * Add_ItemMaster_Records()
 *
 * This function adds records to table ItemMaster from an
 * array of strings
 */
void Add_ItemMaster_Records(void)
{
   RETCODE     rc;
   SQLINTEGER  i;
   SQLTCHAR    sCommand[512];
   SQLTCHAR    *data[] = {
      "(10,19.95,'1','Hammer')",
      "(3,  9.99,'2','Wrench')",
      "(4, 16.59,'3','Saw')",
      "(1,  3.98,'4','Pliers')"
   };
   SQLINTEGER  nRecords = sizeof(data) / sizeof(data[0]);
   Delete_Records("itemmast");
   printf("\tAdd records in table ItemMaster...\n");
   /* add one record at time to table */
   for (i = 0; i < nRecords; i++)
   {
      strcpy (sCommand, "INSERT INTO itemmast VALUES ");
      strcat (sCommand, data[i]);
      if ((rc = SQLExecDirect(hStmt, sCommand ,SQL_NTS)) != SQL_SUCCESS)
         Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");
   }
}
/*
 * Delete_Records()
 *
 * This function deletes all the records in a tables
 */
void Delete_Records(SQLTCHAR* table)
{
   RETCODE  rc;
   SQLTCHAR sCommand[512];
   printf("\tDelete records...\n");
   sprintf (sCommand, "DELETE FROM %s", table);
   if ((rc = SQLExecDirect(hStmt, sCommand, SQL_NTS)) != SQL_SUCCESS)
      Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(DELETE)");
}

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.

Example 10. 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)");
   /* 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)");
}

Record/row locking

To compile and run this tutorial in C go to drivers\sql.odbc\tutorials\ODBCTutorial3.c.

These tutorials will explore row/record locks using the FairCom DB SQL ODBC interface.

The functionality for this tutorial focuses on inserting or 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.

Locking

In this example, we demonstrate the enforcement of data integrity by introducing record/row locking.

Example 11. Simple Main() function

It is suggested to open the source code with your own editor.

/*
 * 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);
}

Init

To open a connection to a database, provide the FairCom DB engine with:

a username
a password
the database name

Example 12. Code for Init()

/*
 * 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

The define step is where specific data definitions are established by your application and/or process. This involves defining columns/fields and creating the tables/files with optional indices.

Example 13. 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

The manage step provides data management functionality for your application and/or process.

Example 14. 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.

Example 15. 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)");
}

Transaction processing

To compile and run this tutorial in C go to drivers\sql.odbc\tutorials\ODBCTutorial4.c.

This tutorial discusses transaction processing as it relates to the FairCom DB SQL ODBC interface.

Transaction processing provides a safe method by which multiple database operations spread across separate tables/files are guaranteed to be atomic. Meaning that, within a transaction, either all of the operations succeed or none of the operations succeed. This "either all or none" atomicity ensures that the integrity of the data in related tables/files is secure.

Transaction processing

This example demonstrates transaction control.

Example 16. Simple Main() function

It is suggested to open the source code with your own editor.

/*
 * 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);
}

Init

To open a connection to a database, provide the FairCom DB engine with:

a username
a password
the database name

Example 17. 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

The define step is where specific data definitions are established by your application and/or process. This involves defining columns/fields and creating the tables/files with optional indices.

Example 18. Code for Define()

/*
 * Define()
 *
 * Create the tables
 */
void Define(void)
{
   RETCODE  rc;
   printf("DEFINE\n");
   /* delete tables... */
   Delete_Tables();

   /* ...and re-create them with constraints */
   Create_CustomerMaster_Table();
   Create_ItemMaster_Table();
   Create_CustomerOrders_Table();
   Create_OrderItems_Table();

   if ((rc = SQLExecDirect(hStmt, "COMMIT WORK", SQL_NTS)) != SQL_SUCCESS)
      Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(COMMIT WORK)");
}
/*
 * Create_CustomerMaster_Table()
 *
 * Create the table CustomerMaster
 */
void Create_CustomerMaster_Table(void)
{
   RETCODE  rc;

   /* define table CustomerMaster */
   printf("\ttable CustomerMaster\n");

   if ((rc = SQLExecDirect(hStmt,
      "CREATE TABLE custmast ( \
         cm_custnumb CHAR(4) PRIMARY KEY, \
         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)");
}

/*
 * Create_CustomerOrders_Table()
 *
 * Create the table CustomerOrders
 */
void Create_CustomerOrders_Table(void)
{
   RETCODE  rc;

   /* define table CustomerOrders */
   printf("\ttable CustomerOrders\n");

   if ((rc = SQLExecDirect(hStmt,
      "CREATE TABLE custordr ( \
         co_ordrdate DATE, \
         co_promdate DATE, \
         co_ordrnumb CHAR(6) PRIMARY KEY, \
         co_custnumb CHAR(4), \
         FOREIGN KEY (co_custnumb) REFERENCES custmast)",
      SQL_NTS)) != SQL_SUCCESS)
      Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE TABLE)");
}

/*
 * Create_OrderItems_Table()
 *
 * Create the table OrderItems
 */
void Create_OrderItems_Table(void)
{
   RETCODE  rc;

   /* define table OrderItems */
   printf("\ttable OrderItems\n");

   if ((rc = SQLExecDirect(hStmt,
      "CREATE TABLE ordritem ( \
         oi_sequnumb SMALLINT, \
         oi_quantity SMALLINT, \
         oi_ordrnumb CHAR(6), \
         oi_itemnumb CHAR(5), \
         FOREIGN KEY (oi_itemnumb) REFERENCES itemmast, \
         FOREIGN KEY (oi_ordrnumb) REFERENCES custordr)",
      SQL_NTS)) != SQL_SUCCESS)
      Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE TABLE)");
}

/*
 * Create_ItemMaster_Table()
 *
 * Create the table ItemMaster
 */
void Create_ItemMaster_Table(void)
{

   RETCODE  rc;

   /* define table ItemMaster */
   printf("\ttable ItemMaster\n");

   if ((rc = SQLExecDirect(hStmt,
      "CREATE TABLE itemmast ( \
         im_itemwght INTEGER, \
         im_itempric MONEY, \
         im_itemnumb CHAR(5) PRIMARY KEY, \
         im_itemdesc VARCHAR(47))",
      SQL_NTS)) != SQL_SUCCESS)
      Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE TABLE)");
}

Manage

The manage step provides data management functionality for your application and/or process.

Example 19. Code for Manage()

/*
 * Manage()
 *
 * Populates table and performs a simple query
 *
 */
void Manage(void)
{
   printf("MANAGE\n");
  
 /* populate the tables with data */
   Add_CustomerMaster_Records();
   Add_ItemMaster_Records();
   Add_Transactions();

   /* display the orders and their items */
   Display_CustomerOrders();
   Display_OrderItems();

}
/*
 * Delete_Tables()
 *
 * This function removes all existing tables
 */
void Delete_Tables(void)
{
   RETCODE  rc;

   if ((rc = SQLExecDirect(hStmt, "DROP TABLE ordritem", SQL_NTS)) != SQL_SUCCESS)
      Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(DROP TABLE)");

   if ((rc = SQLExecDirect(hStmt, "DROP TABLE custordr", SQL_NTS)) != SQL_SUCCESS)
      Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(DROP TABLE)");

   if ((rc = SQLExecDirect(hStmt, "DROP TABLE custmast", SQL_NTS)) != SQL_SUCCESS)
      Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(DROP TABLE)");

   if ((rc = SQLExecDirect(hStmt, "DROP TABLE itemmast", SQL_NTS)) != SQL_SUCCESS)
      Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(DROP TABLE)");
}

/*
 * Add_Transactions()
 *
 * Add an Order and associated Items "as a transaction" to their
 * respective tables.  A transaction is committed or aborted if the
 * customer number on the order is confirmed valid.  Likewise each
 * item in the order is verified to be a valid item.
 */
typedef struct {
   SQLTCHAR       *ordrdate, *promdate, *ordrnumb, *custnumb;
} ORDER_DATA;
typedef struct {
   SQLTCHAR       *ordrnumb;
   SQLUSMALLINT   sequnumb;
   SQLUSMALLINT   quantity;
   SQLTCHAR       *itemnumb;
} ORDERITEM_DATA;
ORDER_DATA orders[] = {
   {"09/01/2002", "09/05/2002", "1", "1001"},
   {"09/02/2002", "09/06/2002", "2", "9999"},   /* bad customer number */
   {"09/22/2002", "09/26/2002", "3", "1003"}
};
ORDERITEM_DATA items[] = {
   {"1", 1, 2, "1"},
   {"1", 2, 1, "2"},
   {"2", 1, 1, "3"},
   {"2", 2, 3, "4"},
   {"3", 1, 2, "3"},
   {"3", 2, 2, "99"} /* bad item number */
};
void Add_Transactions(void)
{
   RETCODE     rc;
   SQLINTEGER  i, j = 0;
   SQLINTEGER  nOrders = sizeof(orders) / sizeof(ORDER_DATA);
   SQLINTEGER  nItems = sizeof(items) / sizeof(ORDERITEM_DATA);
   SQLTCHAR    sCommand[512];

   printf("\tAdd transaction records... \n");
   for (i = 0; i < nOrders; i++)

   {
      /* add order record */
      sprintf(sCommand, "INSERT INTO custordr VALUES ('%s', '%s', '%s', '%s')",
         orders[i].ordrdate,
         orders[i].promdate,
         orders[i].ordrnumb,
         orders[i].custnumb);

      if ((rc = SQLExecDirect(hStmt, sCommand, SQL_NTS)) != SQL_SUCCESS)
         Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");

      /* process order items */
      while (!(strcmp(items[j].ordrnumb, orders[i].ordrnumb)))
      {
         /* add item record */
         sprintf(sCommand, "INSERT INTO ordritem VALUES (%d, %d, '%s', '%s')",
            items[j].sequnumb,
            items[j].quantity,
            items[j].ordrnumb,
            items[j].itemnumb);

         if ((rc = SQLExecDirect(hStmt, sCommand , SQL_NTS)) != SQL_SUCCESS)
            Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");
         /* bump to next item */
         j++;
         /* exit the while loop on last item */

         if (j >= nItems)
            break;
      }

      /* commit the transaction */

      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 table CustomerMaster 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 in table CustomerMaster...\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)");
}
/*
 * Add_ItemMaster_Records()
 *
 * This function adds records to table ItemMaster from an
 * array of strings
 */
void Add_ItemMaster_Records(void)
{

   RETCODE     rc;

   SQLINTEGER  i;
   SQLTCHAR    sCommand[512];
   SQLTCHAR    *data[] = {
      "(10,19.95,'1','Hammer')",
      "(3,  9.99,'2','Wrench')",
      "(4, 16.59,'3','Saw')",
      "(1,  3.98,'4','Pliers')"
   };
   SQLINTEGER  nRecords = sizeof(data) / sizeof(data[0]);

   printf("\tAdd records in table ItemMaster...\n");

   /* add one record at time to table */
   for (i = 0; i < nRecords; i++)
   {
      strcpy (sCommand, "INSERT INTO itemmast 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_CustomerOrders()
 *
 * This function displays the contents of CustomerOrders table
 */
void Display_CustomerOrders(void)
{
   RETCODE  rc;
   SQLTCHAR ordrnumb[6+1], custnumb[4+1];
   SDWORD   cbData;

   printf("\n\tCustomerOrders Table...\n");
   rc = SQLExecDirect(hStmt, "SELECT * FROM custordr" ,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, 3, SQL_C_CHAR, ordrnumb, sizeof(ordrnumb), &cbData);
      SQLGetData(hStmt, 4, SQL_C_CHAR, custnumb, sizeof(custnumb), &cbData);
      printf("\t   %s   %s\n", ordrnumb, custnumb);
   }
   SQLFreeStmt(hStmt,SQL_CLOSE);
}
/*
 * Display_OrderItems()
 *
 * This function displays the contents of OrderItems table
 */
void Display_OrderItems(void)
{
   RETCODE  rc;
   SQLTCHAR ordrnumb[6+1], itemnumb[5+1];
   SDWORD   cbData;

   printf("\n\tOrderItems Table...\n");
   rc = SQLExecDirect(hStmt, "SELECT * FROM ordritem" ,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, 3, SQL_C_CHAR, ordrnumb, sizeof(ordrnumb), &cbData);
      SQLGetData(hStmt, 4, SQL_C_CHAR, itemnumb, sizeof(itemnumb), &cbData);
      printf("\t   %s   %s\n", ordrnumb, itemnumb);
   }
   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.

Example 20. 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");

   /* 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)");

   Delete_Tables();

   /* free statement handles */
   if ((rc = SQLFreeHandle(SQL_HANDLE_STMT, hStmt)) != SQL_SUCCESS)
      Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLFreeHandle(SQL_HANDLE_STMT)");

   /* 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 & functionality

For .NET and Java stored procedures. see the developers guide FairCom Java and .NET stored procedures, triggers, and user-defined functions.
The FairCom DB engine provides a variety of APIs — for example, low-level and the c-treeDB API for C. The entire c-treeDB API for C is available directly as function calls within the object-oriented c-treeDB API for C#. To learn more about FairCom APIs, see FairCom DB interfaces.

JDBC tutorials

The tutorials, in this section, are for running JDBC in a Java program.

Like all other tutorials in the c-tree series, each example simplifies the concepts of database programming into four simple steps:

Initialize()
Define()
Manage()
Done()

This is known as the Initialize(), Define(), Manage(), and You're Done()! approach.

`Initialize()`	Every language requires some form of initial `"logon"` or `"connection"` procedure to establish a session with the database. This is done in the `Initialize()` stage of the program.
`Define()`	Database definitions (DDL), table/file schema definitions, Index definitions, table/file creation, and table/file open operations are all addressed in the `Define()` stage of the program.
`Manage()`	This stage of the program is where the database is operated on, as in managing your data. Adding/reading/updating/deleting records/rows are handled in this stage of the program.
`Done()`	When the program ends, the database session should be closed. This stage handles the necessities to `"de-init"`, by closing tables/files and issuing any required `"logoff"` or `"disconnect"` type procedures.

This section provides tutorials that follow the Initialize(), Define(), Manage(), and You're Done()! approach.

Introduction

To compile and run this tutorial in Java go to drivers\sql.jdbc\tutorials\JDBC_Tutorial1.java.

This tutorial will detail the basic use of the FairCom DB JDBC Interface.

The following sections provide the code for the four steps.

Simple single table

This program was designed to be as simple as possible.

Program step summary :

Initialize()
Connects to the FairCom Database Engine.
Define()
Defines and creates a customer master (custmast) table/file.
Manage()
Adds a few rows/records, reads the rows/records back from the database, displays the column/field content, and then deletes the rows/records.
Done()
Disconnects from FairCom DB engine

Example 21. Simple Main() function

It is suggested you open the source code with your own editor.

import java.sql.*;
import java.io.*;
public class JDBC_Tutorial1 {
   static Connection conn;
   static Statement stmt;
   //
   // main()
   //
   // The main() function implements the concept of "init, define, manage
   // and you're done..."
   //
   public static void main (String[] args)
      throws java.io.IOException
   {
      Initialize();
      Define();
      Manage();
      Done();
      System.out.print("\nPress <ENTER> key to exit . . .");
      System.in.read();
      System.exit(0);
   }

Init

To open a connection to a database, provide the FairCom DB engine with:

a username
a password
the database name

Connection string

Beginning with FairCom DB V11.2 and FairCom RTG V2, the connection string is in the following format:
```
jdbc:ctree://<host>[:portnumber]/<dbname>[?param=value[&param=value]...]
```
The valid params are:
- characterEncoding
  Replace encoding with a valid Java encoding name — for example, US‑ASCII, ISO‑8859-1, UTF‑8, and so forth.
- password
- User
- ssl
  Values of basic (no peer certificate authentication) or peerAuthentication (server certificate authentication).
  When peerAuthentication is requested, the client’s trust store must contain the server’s certificate as shown in the example.
The tutorials use a connection string that is set for the default configuration.
```
"jdbc:ctree://localhost:6597/ctreeSQL", "ADMIN", "ADMIN"
```

Example 22. TLS/SSL

This example enables basic SSL encryption, but does not provide any authentication.

Connection c = getConnection("jdbc:ctree://localhost:6597/ctreeSQL?ssl=basic");

System.setProperty("javax.net.ssl.trustStore","TrustStore.key");
System.setProperty("javax.net.ssl.trustStorePassword","mypassword");
Connection c = getConnection("jdbc:ctree://localhost:6597/ctreeSQL?ssl=peerAuthentication");

Note

For backward compatibility, the older format ("jdbc:ctree:6597@localhost:ctreeSQL", "ADMIN", "ADMIN") is still supported but should be considered deprecated.

Example 23. Code for Initialize()

   //
   // Initialize()
   //
   // Perform the minimum requirement of logging onto the c-tree Server
   //
   private static void Initialize ()
   {
      System.out.println("INIT");
      try
      {
         // load the driver
         Class.forName ("ctree.jdbc.ctreeDriver");
         // connect to server
         System.out.println("\tLogon to server...");
         conn = DriverManager.getConnection ("jdbc:ctree://localhost:6597/ctreeSQL", "ADMIN", "ADMIN");
         // create a statement handle
         stmt = conn.createStatement();
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
      catch (Exception e)
      {
         Handle_Exception(e);
      }
   }

Define

The define step is where specific data definitions are established by your application and/or process. This involves defining columns/fields and creating the tables/files with optional indices.

Example 24. Code for Define()

   //
   // Define()
   //
   // Create the table for containing a list of existing customers
   //
   private static void Define ()
   {
      System.out.println("DEFINE");
      try
      {
         stmt.executeUpdate("DROP TABLE ordritem");
      }
      catch (SQLException e)
      {
      }
      try
      {
         stmt.executeUpdate("DROP TABLE custordr");
      }
      catch (SQLException e)
      {
      }
      try
      {
         stmt.executeUpdate("DROP TABLE itemmast");
      }
      catch (SQLException e)
      {
      }
      try
      {
         stmt.executeUpdate("DROP TABLE custmast");
      }
      catch (SQLException e)
      {
      }
      try
      {
         // create table
         System.out.println("\tCreate table...");
         stmt.executeUpdate("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))"
         );
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }

Manage

The manage step provides data management functionality for your application and/or process.

Example 25. Code for Manage()

   //
   // Manage()
   //
   // This function performs simple record functions of add, delete and gets
   //
   private static void Manage ()
   {
      System.out.println("MANAGE");
      // delete any existing records
      Delete_Records();
      // populate the table with data
      Add_Records();
      // display contents of table
      Display_Records();
   }
   //
   // Delete_Records()
   //
   // This function deletes all the records in the table
   //
   private static void Delete_Records ()
   {
      System.out.println("\tDelete records...");
      try
      {
         stmt.executeUpdate("DELETE FROM custmast");
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }
   //
   // Add_Records()
   //
   // This function adds records to a table in the database from an
   // array of strings
   //
   private static void Add_Records ()
   {
      System.out.println("\tAdd records...");
      String 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')"
      };
      try
      {
         // add one record at time to table
         for (int i = 0; i < data.length; i++) {
            stmt.executeUpdate("INSERT INTO custmast VALUES " + data[i]);
         }
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }
   //
   // Display_Records()
   //
   // This function displays the contents of a table.
   //
   private static void Display_Records ()
   {
      System.out.print("\tDisplay records...");
      try
      {
         // execute a query statement
         ResultSet rs = stmt.executeQuery ("SELECT * FROM custmast");
         // fetch and display each individual record
         while (rs.next()) {
            System.out.println("\n\t\t" + rs.getString(1) + "   " + rs.getString(5));
         }
         rs.close();
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }

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.

Example 26. Code for Done()

   //
   // Done()
   //
   // This function handles the housekeeping of closing, freeing,
   // disconnecting and logging out of the database
   //
   private static void Done ()
   {
      System.out.println("DONE");
      try
      {
         stmt.close();
         // logout
         System.out.println("\tLogout...");
         conn.close();
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }

Relationships

To compile and run this tutorial in Java go to sql.jdbc\tutorials\JDBC_Tutorial2.java.

We will now build table/file relationships using the FairCom DB JDBC interface.

Relational model and indexing

Figure 2. Relational model tables

Example 27. Simple Main() function

It is suggested to open the source code with your own editor.

import java.sql.*;
import java.io.*;
public class JDBC_Tutorial2 {
   static Connection conn;
   static Statement stmt;
   //
   // main()
   //
   // The main() function implements the concept of "init, define, manage
   // and you're done..."
   //
   public static void main (String[] args)
      throws java.io.IOException
   {
      Initialize();
      Define();
      Manage();
      Done();
      System.out.print("\nPress <ENTER> key to exit . . .");
      System.in.read();
      System.exit(0);
   }

Init

To open a connection to a database, provide the FairCom DB engine with:

a username
a password
the database name

Example 28. Code for Initialize()

   //
   // Initialize()
   //
   // Perform the minimum requirement of logging onto the c-tree Server
   //
   private static void Initialize ()
   {
      System.out.println("INIT");
      try
      {
         // load the driver
         Class.forName ("ctree.jdbc.ctreeDriver");
         // connect to server
         System.out.println("\tLogon to server...");
         conn = DriverManager.getConnection ("jdbc:ctree://localhost:6597/ctreeSQL", "ADMIN", "ADMIN");
         // create statement handles
         stmt = conn.createStatement();
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
      catch (Exception e)
      {
         Handle_Exception(e);
      }
   }

Define

The define step is where specific data definitions are established by your application and/or process. This involves defining columns/fields and creating the tables/files with optional indices.

Example 29. Code for Define()

   //
   // Define()
   //
   // Create the tables
   //
   private static void Define ()
   {
      System.out.println("DEFINE");
      try
      {
         stmt.executeUpdate("DROP TABLE ordritem");
      }
      catch (SQLException e)
      {
      }
      try
      {
         stmt.executeUpdate("DROP TABLE custordr");
      }
      catch (SQLException e)
      {
      }
      try
      {
         stmt.executeUpdate("DROP TABLE itemmast");
      }
      catch (SQLException e)
      {
      }
      try
      {
         stmt.executeUpdate("DROP TABLE custmast");
      }
      catch (SQLException e)
      {
      }
      Create_CustomerMaster_Table();
      Create_CustomerOrders_Table();
      Create_OrderItems_Table();
      Create_ItemMaster_Table();
   }
   //
   // Create_CustomerMaster_Table()
   //
   // Create the table CustomerMaster
   //
   private static void Create_CustomerMaster_Table ()
   {
      // define table CustomerMaster
      System.out.println("\ttable CustomerMaster");
      try
      {
         stmt.executeUpdate("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))"
         );
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
      try
      {
         stmt.executeUpdate("CREATE UNIQUE INDEX cm_custnumb_idx ON custmast (cm_custnumb)");
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }
   //
   // Create_CustomerOrders_Table()
   //
   // Create the table CustomerOrders
   //
   private static void Create_CustomerOrders_Table ()
   {
      // define table CustomerOrders
      System.out.println("\ttable CustomerOrders");
      try
      {
         stmt.executeUpdate("CREATE TABLE custordr (" +
            "co_ordrdate DATE, " +
            "co_promdate DATE, " +
            "co_ordrnumb CHAR(6), " +
            "co_custnumb CHAR(4))"
         );
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
      try
      {
         stmt.executeUpdate("CREATE UNIQUE INDEX co_ordrnumb_idx ON custordr (co_ordrnumb)");
         stmt.executeUpdate("CREATE INDEX co_custnumb_idx ON custordr (co_custnumb)");
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }
   //
   // Create_OrderItems_Table()
   //
   // Create the table OrderItems
   //
   private static void Create_OrderItems_Table ()
   {
      // define table OrderItems
      System.out.println("\ttable OrderItems");
      try
      {
         stmt.executeUpdate("CREATE TABLE ordritem (" +
            "oi_sequnumb SMALLINT, " +
            "oi_quantity SMALLINT, " +
            "oi_ordrnumb CHAR(6), " +
            "oi_itemnumb CHAR(5))"
         );
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
      try
      {
         stmt.executeUpdate("CREATE UNIQUE INDEX oi_ordrnumb_idx ON ordritem (oi_ordrnumb, oi_sequnumb)");
         stmt.executeUpdate("CREATE INDEX oi_itemnumb_idx ON ordritem (oi_itemnumb)");
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }
   //
   // Create_ItemMaster_Table()
   //
   // Create the table ItemMaster
   //
   private static void Create_ItemMaster_Table ()
   {
      // define table ItemMaster
      System.out.println("\ttable ItemMaster");
      try
      {
         stmt.executeUpdate("CREATE TABLE itemmast (" +
            "im_itemwght INTEGER, " +
            "im_itempric MONEY, " +
            "im_itemnumb CHAR(5), " +
            "im_itemdesc VARCHAR(47))"
         );
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
      try
      {
         stmt.executeUpdate("CREATE UNIQUE INDEX im_itemnumb_idx ON itemmast (im_itemnumb)");
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }

Manage

The manage step provides data management functionality for your application and/or process.

Example 30. Code for Manage()

   //
   // Manage()
   //
   // Populates table and perform a simple query
   //
   private static void Manage ()
   {
      System.out.println("MANAGE");
      // populate the tables with data
      Add_CustomerMaster_Records();
      Add_CustomerOrders_Records();
      Add_OrderItems_Records();
      Add_ItemMaster_Records();
      // perform a query:
      // list customer name and total amount per order
      // name               total
      // @@@@@@@@@@@@@      $xx.xx
      // for each order in the CustomerOrders table
      //    fetch order number
      //    fetch customer number
      //    fetch name from CustomerMaster table based on customer number
      //    for each order item in OrderItems table
      //       fetch item quantity
      //       fetch item number
      //       fetch item price from ItemMaster table based on item number
      //    next
      // next
      System.out.println("\n\tQuery Results");
      try
      {
         ResultSet rs = stmt.executeQuery (
            "SELECT cm_custname, SUM(im_itempric * oi_quantity) " +
            "FROM custmast, custordr, ordritem, itemmast " +
            "WHERE co_custnumb = cm_custnumb AND co_ordrnumb = oi_ordrnumb AND oi_itemnumb = im_itemnumb " +
            "GROUP BY co_ordrnumb, cm_custname");
         // read resultset
         while (rs.next())
         {
            // fetch customer name
            String custname = rs.getString(1);
            // fetch item price
            float total = rs.getFloat(2);
            System.out.println("\t\t" + custname + "\t" + total);
         }
         rs.close();
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }
   //
   // Add_CustomerMaster_Records()
   //
   // This function adds records to table CustomerMaster from an
   // array of strings
   //
   private static void Add_CustomerMaster_Records ()
   {
      System.out.println("\tAdd records in table CustomerMaster...");
      String 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')"
      };
      Delete_Records("custmast");
      try
      {
         // add one record at time to table
         for (int i = 0; i < data.length; i++) {
            stmt.executeUpdate("INSERT INTO custmast VALUES " + data[i]);
         }
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }
   //
   // Add_CustomerOrders_Records()
   //
   // This function adds records to table CustomerOrders from an
   // array of strings
   //
   private static void Add_CustomerOrders_Records ()
   {
      System.out.println("\tAdd records in table CustomerOrders...");
      String data[] = {
         "('09/01/2002','09/05/2002','1','1001')",
         "('09/02/2002','09/06/2002','2','1002')"
      };
      Delete_Records("custordr");
      try
      {
         // add one record at time to table
         for (int i = 0; i < data.length; i++) {
            stmt.executeUpdate("INSERT INTO custordr VALUES " + data[i]);
         }
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }
   //
   // Add_OrderItems_Records()
   //
   // This function adds records to table OrderItems from an
   // array of strings
   //
   private static void Add_OrderItems_Records ()
   {
      System.out.println("\tAdd records in table OrderItems...");
      String data[] = {
         "(1,2,'1','1')",
         "(2,1,'1','2')",
         "(3,1,'1','3')",
         "(1,3,'2','3')"
      };
      Delete_Records("ordritem");
      try
      {
         // add one record at time to table
         for (int i = 0; i < data.length; i++) {
            stmt.executeUpdate("INSERT INTO ordritem VALUES " + data[i]);
         }
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }
   //
   // Add_ItemMaster_Records()
   //
   // This function adds records to table ItemMaster from an
   // array of strings
   //
   private static void Add_ItemMaster_Records ()
   {
      System.out.println("\tAdd records in table ItemMaster...");
      String data[] = {
         "(10,19.95,'1','Hammer')",
         "(3,  9.99,'2','Wrench')",
         "(4, 16.59,'3','Saw')",
         "(1,  3.98,'4','Pliers')"
      };
      Delete_Records("itemmast");
      try
      {
         // add one record at time to table
         for (int i = 0; i < data.length; i++) {
            stmt.executeUpdate("INSERT INTO itemmast VALUES " + data[i]);
         }
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }
   //
   // Delete_Records()
   //
   // This function deletes all the records in a tables
   //
   private static void Delete_Records (String table)
   {
      System.out.println("\tDelete records...");
      try
      {
         stmt.executeUpdate("DELETE FROM " + table);
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }

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.

Example 31. Code for Done()

   //
   // Done()
   //
   // This function handles the housekeeping of closing, freeing,
   // disconnecting and logging out of the database
   //
   private static void Done ()
   {
      System.out.println("DONE");
      try
      {
         stmt.close();
         // logout
         System.out.println("\tLogout...");
         conn.close();
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }

Record/row locking

To compile and run this tutorial in Java go to sql.jdbc\tutorials\JDBC_Tutorial3.java.

These tutorials will explore row/record locks using the FairCom DB SQL ODBC interface.

Locking

In this example, we demonstrate the enforcement of data integrity by introducing record/row locking.

Example 32. Simple Main() function

It is suggested to open the source code with your own editor.

import java.sql.*;
import java.io.*;
public class JDBC_Tutorial3 {
   static Connection conn;
   static Statement stmt;
   //
   // main()
   //
   // The main() function implements the concept of "init, define, manage
   // and you're done..."
   //
   public static void main (String[] args)
      throws java.io.IOException
   {
      Initialize();
      Define();
      Manage();
      Done();
      System.out.print("\nPress <ENTER> key to exit . . .");
      System.in.read();
      System.exit(0);
   }

Init

To open a connection to a database, provide the FairCom DB engine with:

a username
a password
the database name

Example 33. Code for Init()

   //
   // Initialize()
   //
   // Perform the minimum requirement of logging onto the c-tree Server
   //
   private static void Initialize ()
   {
      System.out.println("INIT");
      try
      {
         // load the driver
         Class.forName ("ctree.jdbc.ctreeDriver");
         // connect to server
         System.out.println("\tLogon to server...");
         conn = DriverManager.getConnection ("jdbc:ctree://localhost:6597/ctreeSQL", "ADMIN", "ADMIN");
         // disable commit after each single SQL statement
         conn.setAutoCommit(false);
         // create a statement handle
         stmt = conn.createStatement();
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
      catch (Exception e)
      {
          Handle_Exception(e);
      }
   }

Define

The define step is where specific data definitions are established by your application and/or process. This involves defining columns/fields and creating the tables/files with optional indices.

Example 34. Code for Define()

   //
   // Define()
   //
   // Create the table for containing a list of existing customers
   //
   private static void Define ()
   {
      System.out.println("DEFINE");
      try
      {
         stmt.executeUpdate("DROP TABLE ordritem");
      }
      catch (SQLException e)
      {
      }
      try
      {
         stmt.executeUpdate("DROP TABLE custordr");
      }
      catch (SQLException e)
      {
      }
      try
      {
         stmt.executeUpdate("DROP TABLE itemmast");
      }
      catch (SQLException e)
      {
      }
      try
      {
         stmt.executeUpdate("DROP TABLE custmast");
      }
      catch (SQLException e)
      {
      }
      try
      {
         // create table
         System.out.println("\tCreate table...");
         stmt.executeUpdate("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))"
         );
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
      try
      {
         stmt.executeUpdate("CREATE UNIQUE INDEX cm_custnumb_idx ON custmast (cm_custnumb)");
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
      try
      {
         conn.commit();
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }

Manage

The manage step provides data management functionality for your application and/or process.

Example 35. Code for Manage()

   //
   // Manage()
   //
   // This function performs record adds and updates using locking
   //
   private static void Manage ()
   {
      System.out.println("MANAGE");
      // 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
   //
   private static void Delete_Records ()
   {
      System.out.println("\tDelete records...");
      try
      {
         stmt.executeUpdate("DELETE FROM custmast");
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
      try
      {
         conn.commit();
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }
   //
   // Add_CustomerMaster_Records()
   //
   // This function adds records to table CustomerMaster from an
   // array of strings
   //
   private static void Add_CustomerMaster_Records ()
   {
      System.out.println("\tAdd records...");
      String 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')"
      };
      try
      {
         // add one record at time to table
         for (int i = 0; i < data.length; i++) {
            stmt.executeUpdate("INSERT INTO custmast VALUES " + data[i]);
         }
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
      try
      {
         conn.commit();
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }
   //
   // Display_Records()
   //
   // This function displays the contents of a table.
   //
   private static void Display_Records ()
   {
      System.out.print("\tDisplay records...");
      try
      {
         // execute a query statement
         ResultSet rs = stmt.executeQuery ("SELECT * FROM custmast");
         // fetch and display each individual record
         while (rs.next()) {
            System.out.println("\n\t\t" + rs.getString(1) + "   " + rs.getString(5));
         }
         rs.close();
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }
   //
   // Update_CustomerMaster_Records()
   //
   // Update one record under locking control to demonstrate the effects
   // of locking
   //
   private static void Update_CustomerMaster_Record()
   {
      System.out.println("\tUpdate record...");
      try
      {
         stmt.executeUpdate("UPDATE custmast SET cm_custname = 'KEYON DOOLING' where cm_custnumb = '1003'");
         System.out.println("\tPress <ENTER> key to unlock");
         System.in.read(new byte[256]);
         conn.commit();
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
      catch (Exception e)
      {
         Handle_Exception(e);
      }
   }

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.

Example 36. Code for Done()

   //
   // Done()
   //
   // This function handles the housekeeping of closing, freeing,
   // disconnecting and logging out of the database
   //
   private static void Done ()
   {
      System.out.println("DONE");
      try
      {
         stmt.close();
         // logout
         System.out.println("\tLogout...");
         conn.close();
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }

Transaction processing

To compile and run this tutorial in Java go to sql.jdbc\tutorials\JDBC_Tutorial4.java.

This tutorial discusses transaction processing as it relates to the FairCom DB JDBC interface.

Transaction processing

This example demonstrates transaction control.

Example 37. Simple Main() function

It is suggested to open the source code with your own editor.

import java.sql.*;
import java.io.*;
public class JDBC_Tutorial4 {
   static Connection conn;
   static Statement stmt;
   //
   // main()
   //
   // The main() function implements the concept of "init, define, manage
   // and you're done..."
   //
   public static void main (String[] args)
      throws java.io.IOException
   {
      Initialize();
      Define();
      Manage();
      Done();
      System.out.print("\nPress <ENTER> key to exit . . .");
      System.in.read();
      System.exit(0);
   }

Init

To open a connection to a database, provide the FairCom DB engine with:

a username
a password
the database name

Example 38. Code for Initialize()

   //
   // Initialize()
   //
   // Perform the minimum requirement of logging onto the c-tree Server
   //
   private static void Initialize ()
   {
      System.out.println("INIT");
      try
      {
         // load the driver
         Class.forName ("ctree.jdbc.ctreeDriver");
         // connect to server
         System.out.println("\tLogon to server...");
         conn = DriverManager.getConnection ("jdbc:ctree://localhost:6597/ctreeSQL", "ADMIN", "ADMIN");
         // disable commit after each single SQL statement
         conn.setAutoCommit(false);
         // create statement handles
         stmt = conn.createStatement();
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
      catch (Exception e)
      {
         Handle_Exception(e);
      }
   }

Define

The define step is where specific data definitions are established by your application and/or process. This involves defining columns/fields and creating the tables/files with optional indices.

Example 39. Code for Define()

   //
   // Define()
   //
   // Create the tables
   //
   private static void Define ()
   {
      System.out.println("DEFINE");
      // delete tables ...
      Delete_Tables();
      // ...and re-create them with constraints
      Create_CustomerMaster_Table();
      Create_ItemMaster_Table();
      Create_CustomerOrders_Table();
      Create_OrderItems_Table();
      try
      {
         conn.commit();
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }
   //
   // Delete_Tables()
   //
   // This function removes all existing tables
   //
   private static void Delete_Tables ()
   {
      try
      {
         stmt.executeUpdate("DROP TABLE ordritem");
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
      try
      {
         stmt.executeUpdate("DROP TABLE custordr");
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
      try
      {
         stmt.executeUpdate("DROP TABLE custmast");
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
      try
      {
         stmt.executeUpdate("DROP TABLE itemmast");
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }
   //
   // Create_CustomerMaster_Table()
   //
   // Create the table CustomerMaster
   //
   private static void Create_CustomerMaster_Table ()
   {
      // define table CustomerMaster
      System.out.println("\ttable CustomerMaster");
      try
      {
         stmt.executeUpdate("CREATE TABLE custmast (" +
            "cm_custnumb CHAR(4) PRIMARY KEY, " +
            "cm_custzipc CHAR(9), " +
            "cm_custstat CHAR(2), " +
            "cm_custrtng CHAR(1), " +
            "cm_custname VARCHAR(47), " +
            "cm_custaddr VARCHAR(47), " +
            "cm_custcity VARCHAR(47))"
         );
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }
   //
   // Create_CustomerOrders_Table()
   //
   // Create the table CustomerOrders
   //
   private static void Create_CustomerOrders_Table ()
   {
      // define table CustomerOrders
      System.out.println("\ttable CustomerOrders");
      try
      {
         stmt.executeUpdate("CREATE TABLE custordr (" +
            "co_ordrdate DATE, " +
            "co_promdate DATE, " +
            "co_ordrnumb CHAR(6) PRIMARY KEY, " +
            "co_custnumb CHAR(4), " +
            "FOREIGN KEY (co_custnumb) REFERENCES custmast)"
         );
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }
   //
   // Create_OrderItems_Table()
   //
   // Create the table OrderItems
   //
   private static void Create_OrderItems_Table ()
   {
      // define table OrderItems
      System.out.println("\ttable OrderItems");
      try
      {
         stmt.executeUpdate("CREATE TABLE ordritem (" +
            "oi_sequnumb SMALLINT, " +
            "oi_quantity SMALLINT, " +
            "oi_ordrnumb CHAR(6), " +
            "oi_itemnumb CHAR(5), " +
            "FOREIGN KEY (oi_itemnumb) REFERENCES itemmast, " +
            "FOREIGN KEY (oi_ordrnumb) REFERENCES custordr)"
         );
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }
   //
   // Create_ItemMaster_Table()
   //
   // Create the table ItemMaster
   //
   private static void Create_ItemMaster_Table ()
   {
      // define table ItemMaster
      System.out.println("\ttable ItemMaster");
      try
      {
         stmt.executeUpdate("CREATE TABLE itemmast (" +
            "im_itemwght INTEGER, " +
            "im_itempric MONEY, " +
            "im_itemnumb CHAR(5) PRIMARY KEY, " +
            "im_itemdesc VARCHAR(47))"
         );
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }

Manage

The manage step provides data management functionality for your application and/or process.

Example 40. Code for Manage()

   //
   // Manage()
   //
   // Populates table and perform a simple query
   //
   private static void Manage ()
   {
      System.out.println("MANAGE");
      // populate the tables with data
      Add_CustomerMaster_Records();
      Add_ItemMaster_Records();
      Add_Transactions();
      // display the orders and their items
      Display_CustomerOrders();
      Display_OrderItems();
   }
   //
   // Add_CustomerMaster_Records()
   //
   // This function adds records to table CustomerMaster from an
   // array of strings
   //
   private static void Add_CustomerMaster_Records ()
   {
      String 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')"
      };
      System.out.println("\tAdd records in table CustomerMaster...");
      try
      {
         // add one record at time to table
         for (int i = 0; i < data.length; i++) {
            stmt.executeUpdate("INSERT INTO custmast VALUES " + data[i]);
         }
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
      try
      {
         conn.commit();
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }
   //
   // Add_ItemMaster_Records()
   //
   // This function adds records to table ItemMaster from an
   // array of strings
   //
   private static void Add_ItemMaster_Records ()
   {
      String data[] = {
         "(10,19.95,'1','Hammer')",
         "(3,  9.99,'2','Wrench')",
         "(4, 16.59,'3','Saw')",
         "(1,  3.98,'4','Pliers')"
      };
      System.out.println("\tAdd records in table ItemMaster...");
      try
      {
         // add one record at time to table
         for (int i = 0; i < data.length; i++) {
            stmt.executeUpdate("INSERT INTO itemmast VALUES " + data[i]);
         }
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
      try
      {
         conn.commit();
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }
   //
   // Add_Transactions()
   //
   // Add an Order and associated Items "as a transaction" to their
   // respective tables.  A transaction is committed or aborted if the
   // customer number on the order is confirmed valid.  Likewise each
   // item in the order is verified to be a valid item.
   //
   private static void Add_Transactions()
   {
      int   i, j = 0;
      String[][] orders = {
         { "09/01/2002", "09/05/2002", "1", "1001" },
         { "09/02/2002", "09/06/2002", "2", "9999" },  // bad customer number
         { "09/22/2002", "09/26/2002", "3", "1003" }
      };
      String[][] items = {
         { "1", "1", "2", "1" },
         { "1", "2", "1", "2" },
         { "2", "1", "1", "3" },
         { "2", "2", "3", "4" },
         { "3", "1", "2", "3" },
         { "3", "2", "2", "99"}  // bad item number
      };
      System.out.println("\tAdd transaction records...");
      for (i = 0; i < orders.length; i++)
      {
         try
         {
            // add order record
            stmt.executeUpdate("INSERT INTO custordr VALUES (" +
               "'" + orders[i][0] + "'," +
               "'" + orders[i][1] + "'," +
               "'" + orders[i][2] + "'," +
               "'" + orders[i][3] + "')");
         }
         catch (SQLException e)
         {
            Handle_Exception(e);
         }
         // process order items
         while (items[j][0].compareTo(orders[i][2]) == 0)
         {
            try
            {
               // add item record
               stmt.executeUpdate("INSERT INTO ordritem VALUES (" +
                  items[j][1] + "," +
                  items[j][2] + "," +
                  "'" + items[j][0] + "'," +
                  "'" + items[j][3] + "')");
            }
            catch (SQLException e)
            {
               Handle_Exception(e);
            }
            // bump to next item
            j++;
            // exit the while loop on last item
            if (j >= items.length)
               break;
         }
         
         try
         {
            conn.commit();
         }
         catch (SQLException e)
         {
            Handle_Exception(e);
         }
      }
   }
   //
   // Display_CustomerOrders()
   //
   // This function displays the contents of CustomerOrders table.
   //
   private static void Display_CustomerOrders ()
   {
      System.out.println("\n\tCustomerOrders Table...");
      try
      {
         // execute a query statement
         ResultSet rs = stmt.executeQuery ("SELECT * FROM custordr");
         // fetch and display each individual record
         while (rs.next()) {
            System.out.println("\t   " + rs.getString(3) + "   " + rs.getString(4));
         }
         rs.close();
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }
   //
   // Display_OrderItems()
   //
   // This function displays the contents of OrderItems table.
   //
   private static void Display_OrderItems ()
   {
      System.out.println("\n\tOrderItems Table...");
      try
      {
         // execute a query statement
         ResultSet rs = stmt.executeQuery ("SELECT * FROM ordritem");
         // fetch and display each individual record
         while (rs.next()) {
            System.out.println("\t   " + rs.getString(3) + "   " + rs.getString(4));
         }
         rs.close();
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }

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.

Example 41. Code for Done()

   //
   // Done()
   //
   // This function handles the housekeeping of closing, freeing,
   // disconnecting and logging out of the database
   //
   private static void Done ()
   {
      System.out.println("DONE");
      Delete_Tables();
      try
      {
         conn.commit();
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
      try
      {
         stmt.close();
         // logout
         System.out.println("\tLogout...");
         conn.close();
      }
      catch (SQLException e)
      {
         Handle_Exception(e);
      }
   }

Additional resources and functionality

For .NET and Java stored procedures. see the developers guide FairCom Java and .NET stored procedures, triggers, and user-defined functions.
The FairCom DB engine provides a variety of APIs — for example, low-level and the c-treeDB API for C. The entire c-treeDB API for C is available directly as function calls within the object-oriented c-treeDB API for C#. To learn more about FairCom APIs, see FairCom DB interfaces.