FairCom — Direct SQL for C and C++ Tutorials

These tutorials introduce you to the most basic Direct SQL API functions, to create and manipulate a table through the ctreeServer.

As with all other tutorials in the c-tree series, each of these database programming tutorials is implemented with four simple code procedures: Initialize(), Define(), Manage(), and Done().

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

Presented here are tutorials that follow the "Initialize(), Define(), Manage(), and Done()" approach.

You can also view similar tutorials for all supported languages online.

Tutorial 1: Initialize a single table

<faircomInstallFolder>\drivers\c.sql.direct\tutorials\DSQL_tutorial1.c

As with all other examples in the c-tree tutorial series, this tutorial uses four standard procedures: Initialize(), Define(), Manage(), and Done().

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 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)
{
   CTSQLRET rc;

   printf("INIT\n");

   /* allocate connection handle */
   if ((hConn = ctsqlNewConnection()) == NULL)
      Handle_Error("ctsqlNewConnection()");

   /* allocate command handle */
   if ((hCmd = ctsqlNewCommand(hConn)) == NULL)
      Handle_Error("ctsqlNewCommand()");

   /* connect to server */
   printf("\tLogon to server...\n");
   if ((rc = ctsqlConnect(hConn, CT_STRING_LITERAL("6597@localhost:ctreeSQL"), CT_STRING_LITERAL("admin"), CT_STRING_LITERAL("ADMIN"))) != CTSQLRET_OK)
      Handle_Error( "ctsqlConnect()");

   /* enable autocommit */
   if ((rc = ctsqlSetAutoCommit(hConn, CTSQL_TRUE)) != CTSQLRET_OK)
      Handle_Error( "ctsqlSetAutoCommit()");
}

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)
{
   CTSQLRET rc;

   printf("DEFINE\n");

   ctsqlExecuteDirect(hCmd, CT_STRING_LITERAL("DROP TABLE ordritem"));
   ctsqlExecuteDirect(hCmd, CT_STRING_LITERAL("DROP TABLE custordr"));
   ctsqlExecuteDirect(hCmd, CT_STRING_LITERAL("DROP TABLE custmast"));
   ctsqlExecuteDirect(hCmd, CT_STRING_LITERAL("DROP TABLE itemmast"));

   /* create table */
   printf("\tCreate table...\n");
   if ((rc = ctsqlExecuteDirect(hCmd,
      CT_STRING_LITERAL("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))")
      )) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(CREATE TABLE)");
}

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

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)
{
   CTSQLRET rc;

   printf("\tDelete records...\n");

   if ((rc = ctsqlExecuteDirect(hCmd, CT_STRING_LITERAL("DELETE FROM custmast"))) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(DELETE)");
}


/*
 * Add_Records()
 *
 * This function adds records to a table from an array of strings
 */

void Add_Records(void)
{
   CTSQLRET    rc;
   INTEGER     i;
   CTSQLCHAR   sCommand[512];
   CTSQLCHAR   *data[] = {
      CT_STRING_LITERAL("('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')"),
      CT_STRING_LITERAL("('1001','61434','CT','1','Michael Jordan','13 Main','Harford')"),
      CT_STRING_LITERAL("('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')"),
      CT_STRING_LITERAL("('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')")
   };
   INTEGER     nRecords = sizeof(data) / sizeof(data[0]);

   printf("\tAdd records...\n");

   /* add one record at time to table */
   for (i = 0; i < nRecords; i++)
   {
      ctsql_snprintf(sCommand, sizeof(sCommand) / sizeof(*sCommand), CT_STRING_LITERAL("INSERT INTO custmast VALUES %") CT_PRINTF_STR, data[i]);
      if ((rc = ctsqlExecuteDirect(hCmd, sCommand)) != CTSQLRET_OK)
         Handle_Error("ctsqlExecuteDirect(INSERT)");
   }
}


/*
 * Display_Records()
 *
 * This function displays the contents of a table.
 */

void Display_Records(void)
{
   CTSQLRET       rc;
   char           custnumb[4+1];
   char           custname[47+1];
   pCTSQLCURSOR   hCursor = NULL;

   printf("\tDisplay records...");

   rc = ctsqlPrepare(hCmd, CT_STRING_LITERAL("SELECT * FROM custmast"));
   if (rc != CTSQLRET_OK)
      Handle_Error("ctsqlPrepare(SELECT)");

   rc = ctsqlExecute(hCmd, &hCursor);
   if (rc != CTSQLRET_OK)
      Handle_Error("ctsqlExecute(SELECT)");

   /* fetch and display each individual record */
   while ((rc = ctsqlNext(hCursor)) == CTSQLRET_OK)
   {
      ctsqlGetChar(hCursor, 0, custnumb);
      ctsqlGetChar(hCursor, 4, custname);

      printf("\n\t\t%-8s%10s\n", custnumb, custname);
   }

   ctsqlFreeCursor(hCursor);
}


/*
 * Handle_Error()
 *
 * General error routine that retrieves and displays specific SQL Error
 * before exiting the tutorial.  If the error returned indicates an object
 * already exists, execution is returned to the calling function.
 */

void Handle_Error(char *sErrMsg)
{
   CTSQLRET rc;

   const INTEGER  TABLE_ALREADY_EXISTS = -20041;
   const INTEGER  INDEX_ALREADY_EXISTS = -20028;

   if (!hConn) {
      printf("%s - SQL ERROR: %s\n", sErrMsg, "Out of memory");
   } else {
      rc = ctsqlGetError (hConn);
      if (rc == TABLE_ALREADY_EXISTS || rc == INDEX_ALREADY_EXISTS)
         return;
   
      printf("%s - SQL ERROR: [%d] - %" CT_PRINTF_STR "\n", sErrMsg, rc, ctsqlGetErrorMessage(hConn));

      if (rc == -20212)
         printf("Perhaps your c-tree server is not running?\n");
   }
   printf("*** Execution aborted *** \nPress <ENTER> key to exit...");
   getchar();

   exit(1);
}

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)
{
   CTSQLRET rc;

   printf("DONE\n");

   /* disconnect from server */
   printf("\tLogout...\n");
   if ((rc = ctsqlDisconnect(hConn)) != CTSQLRET_OK)
      Handle_Error("ctsqlDisconnect()");

   /* free command handle */
   if (hCmd)
      ctsqlFreeCommand(hCmd);

   /* free connection handle */
   ctsqlFreeConnection(hConn);
}

Tutorial 2: Relational Model and Indexing

<faircomInstallFolder>\drivers\c.sql.direct\tutorials\DSQL_tutorial2.c

Here we will will perform the following:

Create a database
Create 4 tables each with an index
Populate each table with a few records
Build a query utilizing the advantage of indexes
Output the results of the query

To keep this program as simple as possible, this program uses four standard procedures:

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 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)
{
   CTSQLRET rc;

   printf("INIT\n");

   /* allocate connection handle */
   if ((hConn = ctsqlNewConnection()) == NULL)
      Handle_Error("ctsqlNewConnection()");

   /* allocate command handle */
   if ((hCmd = ctsqlNewCommand(hConn)) == NULL)
      Handle_Error("ctsqlNewCommand()");

   /* connect to server */
   printf("\tLogon to server...\n");
   if ((rc = ctsqlConnect(hConn, CT_STRING_LITERAL("6597@localhost:ctreeSQL"), CT_STRING_LITERAL("admin"), CT_STRING_LITERAL("ADMIN"))) != CTSQLRET_OK)
      Handle_Error( "ctsqlConnect()");

   /* enable autocommit */
   if ((rc = ctsqlSetAutoCommit(hConn, CTSQL_TRUE)) != CTSQLRET_OK)
      Handle_Error( "ctsqlSetAutoCommit()");
}

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

void Define(void)
{
   printf("DEFINE\n");

   ctsqlExecuteDirect(hCmd, CT_STRING_LITERAL("DROP TABLE ordritem"));
   ctsqlExecuteDirect(hCmd, CT_STRING_LITERAL("DROP TABLE custordr"));
   ctsqlExecuteDirect(hCmd, CT_STRING_LITERAL("DROP TABLE custmast"));
   ctsqlExecuteDirect(hCmd, CT_STRING_LITERAL("DROP TABLE itemmast"));

   Create_CustomerMaster_Table();
   Create_CustomerOrders_Table();
   Create_OrderItems_Table();
   Create_ItemMaster_Table();
}

/*
 * Create_CustomerMaster_Table()
 *
 * Create the CustomerMaster
 */

void Create_CustomerMaster_Table(void)
{
   CTSQLRET rc;

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

   if ((rc = ctsqlExecuteDirect(hCmd,
      CT_STRING_LITERAL("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))")
      )) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(CREATE TABLE)");

   if ((rc = ctsqlExecuteDirect(hCmd,
      CT_STRING_LITERAL("CREATE UNIQUE INDEX cm_custnumb_idx ON custmast (cm_custnumb)")
      )) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(CREATE INDEX)");
}


/*
 * Create_CustomerOrders_Table()
 *
 * Create the table CustomerOrders
 */

void Create_CustomerOrders_Table(void)
{
   CTSQLRET rc;

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

   if ((rc = ctsqlExecuteDirect(hCmd,
      CT_STRING_LITERAL("CREATE TABLE custordr ( \
         co_ordrdate DATE, \
         co_promdate DATE, \
         co_ordrnumb CHAR(6), \
         co_custnumb CHAR(4))")
      )) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(CREATE TABLE)");

   if ((rc = ctsqlExecuteDirect(hCmd,
      CT_STRING_LITERAL("CREATE UNIQUE INDEX co_ordrnumb_idx ON custordr (co_ordrnumb)")
      )) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(CREATE INDEX)");

   if ((rc = ctsqlExecuteDirect(hCmd,
      CT_STRING_LITERAL("CREATE INDEX co_custnumb_idx ON custordr (co_custnumb)")
      )) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(CREATE INDEX)");
}


/*
 * Create_OrderItems_Table()
 *
 * Create the table OrderItems
 */

void Create_OrderItems_Table(void)
{
   CTSQLRET rc;

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

   if ((rc = ctsqlExecuteDirect(hCmd,
      CT_STRING_LITERAL("CREATE TABLE ordritem ( \
         oi_sequnumb SMALLINT, \
         oi_quantity SMALLINT, \
         oi_ordrnumb CHAR(6), \
         oi_itemnumb CHAR(5))")
      )) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(CREATE TABLE)");

   if ((rc = ctsqlExecuteDirect(hCmd,
      CT_STRING_LITERAL("CREATE UNIQUE INDEX oi_ordrnumb_idx ON ordritem (oi_ordrnumb, oi_sequnumb)")
      )) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(CREATE INDEX)");

   if ((rc = ctsqlExecuteDirect(hCmd,
      CT_STRING_LITERAL("CREATE INDEX oi_itemnumb_idx ON ordritem (oi_itemnumb)")
      )) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(CREATE INDEX)");
}


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

void Create_ItemMaster_Table(void)
{
   CTSQLRET rc;

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

   if ((rc = ctsqlExecuteDirect(hCmd,
      CT_STRING_LITERAL("CREATE TABLE itemmast ( \
         im_itemwght INTEGER, \
         im_itempric MONEY, \
         im_itemnumb CHAR(5), \
         im_itemdesc VARCHAR(47))")
      )) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(CREATE TABLE)");

   if ((rc = ctsqlExecuteDirect(hCmd,
      CT_STRING_LITERAL("CREATE UNIQUE INDEX im_itemnumb_idx ON itemmast (im_itemnumb)")
      )) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(CREATE INDEX)");
}

Manage

Manage() provides data management functionality for your application and/or process.

Below is the code for Manage():

/*
 * Manage()
 *
 * Populates table and perform a simple query
 *
 */

void Manage(void)
{
   CTSQLRET       rc;
   char           custname[47+1];
   NUMERIC        ntotal;
   SMALLFLOAT     ftotal;
   pCTSQLCURSOR   hCursor = NULL;

   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 = ctsqlPrepare(hCmd,
         CT_STRING_LITERAL("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")
      )) != CTSQLRET_OK)
      Handle_Error("ctsqlPrepare(SELECT)");

   if ((rc = ctsqlExecute(hCmd, &hCursor)) != CTSQLRET_OK)
      Handle_Error("ctsqlExecute(SELECT)");

   /* for each order in the CustomerOrders table */
   while ((rc = ctsqlNext(hCursor)) == CTSQLRET_OK)
   {
      if ((rc = ctsqlGetChar(hCursor, 0, custname)) != CTSQLRET_OK)
         Handle_Error("ctsqlGetChar()");
      if ((rc = ctsqlGetMoney(hCursor, 1, &ntotal)) != CTSQLRET_OK)
         Handle_Error("ctsqlGetReal()");
      if ((rc = ctsqlConvert(CTSQL_MONEY, &ntotal, sizeof(ntotal),
         NULL, CTSQL_REAL, &ftotal, sizeof(ftotal))) != CTSQLRET_OK)
         Handle_Error("ctsqlConvert()");

      /* output data to stdout */
      printf("\t\t%-20s %.2f\n", custname, ftotal);
   }

   ctsqlFreeCursor(hCursor);
}

/*
 * Add_CustomerMaster_Records()
 *
 * This function adds records to table CustomerMaster from an
 * array of strings
 */

void Add_CustomerMaster_Records(void)
{
   CTSQLRET    rc;
   INTEGER     i;
   CTSQLCHAR   sCommand[512];
   CTSQLCHAR   *data[] = {
      CT_STRING_LITERAL("('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')"),
      CT_STRING_LITERAL("('1001','61434','CT','1','Michael Jordan','13 Main','Harford')"),
      CT_STRING_LITERAL("('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')"),
      CT_STRING_LITERAL("('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')")
   };
   INTEGER     nRecords = sizeof(data) / sizeof(data[0]);

   Delete_Records(CT_STRING_LITERAL("custmast"));

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

   /* add one record at time to table */
   for (i = 0; i < nRecords; i++)
   {
      ctsql_snprintf (sCommand, sizeof(sCommand)/sizeof(*sCommand), CT_STRING_LITERAL("INSERT INTO custmast VALUES %") CT_PRINTF_STR, data[i]);
      if ((rc = ctsqlExecuteDirect(hCmd, sCommand)) != CTSQLRET_OK)
         Handle_Error("ctsqlExecuteDirect(INSERT)");
   }
}


/*
 * Add_CustomerOrders_Records()
 *
 * This function adds records to table CustomerOrders from an
 * array of strings
 */

void Add_CustomerOrders_Records(void)
{
   CTSQLRET    rc;
   INTEGER     i;
   CTSQLCHAR   sCommand[512];
   CTSQLCHAR   *data[] = {
      CT_STRING_LITERAL("('09/01/2002','09/05/2002','1','1001')"),
      CT_STRING_LITERAL("('09/02/2002','09/06/2002','2','1002')")
   };
   INTEGER     nRecords = sizeof(data) / sizeof(data[0]);

   Delete_Records(CT_STRING_LITERAL("custordr"));

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

   /* add one record at time to table */
   for (i = 0; i < nRecords; i++)
   {
      ctsql_snprintf (sCommand, sizeof(sCommand)/sizeof(*sCommand), CT_STRING_LITERAL("INSERT INTO custordr VALUES %") CT_PRINTF_STR, data[i]);
      if ((rc = ctsqlExecuteDirect(hCmd, sCommand)) != CTSQLRET_OK)
         Handle_Error("ctsqlExecuteDirect(INSERT)");
   }
}


/*
 * Add_OrderItems_Records()
 *
 * This function adds records to table OrderItems from an
 * array of strings
 */

void Add_OrderItems_Records(void)
{
   CTSQLRET    rc;
   INTEGER     i;
   CTSQLCHAR   sCommand[512];
   CTSQLCHAR   *data[] = {
      CT_STRING_LITERAL("(1,2,'1','1')"),
      CT_STRING_LITERAL("(2,1,'1','2')"),
      CT_STRING_LITERAL("(3,1,'1','3')"),
      CT_STRING_LITERAL("(1,3,'2','3')")
   };
   INTEGER     nRecords = sizeof(data) / sizeof(data[0]);

   Delete_Records(CT_STRING_LITERAL("ordritem"));

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

   /* add one record at time to table */
   for (i = 0; i < nRecords; i++)
   {
      ctsql_snprintf (sCommand, sizeof(sCommand)/sizeof(*sCommand), CT_STRING_LITERAL("INSERT INTO ordritem VALUES %") CT_PRINTF_STR, data[i]);
      if ((rc = ctsqlExecuteDirect(hCmd, sCommand)) != CTSQLRET_OK)
         Handle_Error("ctsqlExecuteDirect(INSERT)");
   }
}


/*
 * Add_ItemMaster_Records()
 *
 * This function adds records to table ItemMaster from an
 * array of strings
 */

void Add_ItemMaster_Records(void)
{
   CTSQLRET    rc;
   INTEGER     i;
   CTSQLCHAR   sCommand[512];
   CTSQLCHAR   *data[] = {
      CT_STRING_LITERAL("(10,19.95,'1','Hammer')"),
      CT_STRING_LITERAL("(3,  9.99,'2','Wrench')"),
      CT_STRING_LITERAL("(4, 16.59,'3','Saw')"),
      CT_STRING_LITERAL("(1,  3.98,'4','Pliers')")
   };
   INTEGER     nRecords = sizeof(data) / sizeof(data[0]);

   Delete_Records(CT_STRING_LITERAL("itemmast"));

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

   /* add one record at time to table */
   for (i = 0; i < nRecords; i++)
   {
      ctsql_snprintf (sCommand, sizeof(sCommand)/sizeof(*sCommand), CT_STRING_LITERAL("INSERT INTO itemmast VALUES %") CT_PRINTF_STR, data[i]);
      if ((rc = ctsqlExecuteDirect(hCmd, sCommand)) != CTSQLRET_OK)
         Handle_Error("ctsqlExecuteDirect(INSERT)");
   }
}

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)
{
   CTSQLRET rc;

   printf("DONE\n");

   /* disconnect from server */
   printf("\tLogout...\n");
   if ((rc = ctsqlDisconnect(hConn)) != CTSQLRET_OK)
      Handle_Error("ctsqlDisconnect()");

   /* free command handle */
   if (hCmd)
      ctsqlFreeCommand(hCmd);

   /* free connection handle */
   ctsqlFreeConnection(hConn);
}

Tutorial 3: Locking

<faircomInstallFolder>\drivers\c.sql.direct\tutorials\DSQL_tutorial3.c

This tutorial performs the following:

Logon onto a session
Add 1 table with some fields
Populate the table with a few records
Display the contents of the table
Update one record under locking control

To keep this program as simple as possible, we use four standard procedures:

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 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)
{
   CTSQLRET rc;

   printf("INIT\n");

   /* allocate connection handle */
   if ((hConn = ctsqlNewConnection()) == NULL)
      Handle_Error("ctsqlNewConnection()");

   /* allocate command handle */
   if ((hCmd = ctsqlNewCommand(hConn)) == NULL)
      Handle_Error("ctsqlNewCommand()");

   /* connect to server */
   printf("\tLogon to server...\n");
   if ((rc = ctsqlConnect(hConn, CT_STRING_LITERAL("6597@localhost:ctreeSQL"), CT_STRING_LITERAL("admin"), CT_STRING_LITERAL("ADMIN"))) != CTSQLRET_OK)
      Handle_Error( "ctsqlConnect()");

   /* disable autocommit */
   if ((rc = ctsqlSetAutoCommit(hConn, CTSQL_FALSE)) != CTSQLRET_OK)
      Handle_Error( "ctsqlSetAutoCommit()");
}

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)
{
   CTSQLRET rc;

   printf("DEFINE\n");

   ctsqlExecuteDirect(hCmd, CT_STRING_LITERAL("DROP TABLE ordritem"));
   ctsqlExecuteDirect(hCmd, CT_STRING_LITERAL("DROP TABLE custordr"));
   ctsqlExecuteDirect(hCmd, CT_STRING_LITERAL("DROP TABLE custmast"));
   ctsqlExecuteDirect(hCmd, CT_STRING_LITERAL("DROP TABLE itemmast"));

   /* create table */
   printf("\tCreate table...\n");
   if ((rc = ctsqlExecuteDirect(hCmd,
      CT_STRING_LITERAL("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))")
      )) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(CREATE TABLE)");

   if ((rc = ctsqlExecuteDirect(hCmd,
      CT_STRING_LITERAL("CREATE UNIQUE INDEX cm_custnumb_idx ON custmast (cm_custnumb)")
      )) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(CREATE INDEX)");

   if ((rc = ctsqlCommit(hConn)) != CTSQLRET_OK)
      Handle_Error("ctsqlCommit()");
}

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)
{
   CTSQLRET rc;

   printf("\tDelete records...\n");

   if ((rc = ctsqlExecuteDirect(hCmd, CT_STRING_LITERAL("DELETE FROM custmast"))) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(DELETE)");

   if ((rc = ctsqlCommit(hConn)) != CTSQLRET_OK)
      Handle_Error("ctsqlCommit()");
}


/*
 * Add_CustomerMaster_Records()
 *
 * This function adds records to a table from an array of strings
 */

void Add_CustomerMaster_Records(void)
{
   CTSQLRET    rc;
   INTEGER     i;
   CTSQLCHAR   sCommand[512];
   CTSQLCHAR   *data[] = {
      CT_STRING_LITERAL("('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')"),
      CT_STRING_LITERAL("('1001','61434','CT','1','Michael Jordan','13 Main','Harford')"),
      CT_STRING_LITERAL("('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')"),
      CT_STRING_LITERAL("('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')")
   };
   INTEGER     nRecords = sizeof(data) / sizeof(data[0]);

   printf("\tAdd records...\n");

   /* add one record at time to table */
   for (i = 0; i < nRecords; i++)
   {
      ctsql_snprintf (sCommand, sizeof(sCommand)/sizeof(*sCommand), CT_STRING_LITERAL("INSERT INTO custmast VALUES %") CT_PRINTF_STR, data[i]);
      if ((rc = ctsqlExecuteDirect(hCmd, sCommand)) != CTSQLRET_OK)
         Handle_Error("ctsqlExecuteDirect(INSERT)");
   }

   if ((rc = ctsqlCommit(hConn)) != CTSQLRET_OK)
      Handle_Error("ctsqlCommit()");
}


/*
 * Display_Records()
 *
 * This function displays the contents of a table.
 */

void Display_Records(void)
{
   CTSQLRET       rc;
   char           custnumb[4+1];
   char           custname[47+1];
   pCTSQLCURSOR   hCursor = NULL;

   printf("\tDisplay records...");

   rc = ctsqlPrepare(hCmd, CT_STRING_LITERAL("SELECT * FROM custmast"));
   if (rc != CTSQLRET_OK)
      Handle_Error("ctsqlPrepare(SELECT)");

   rc = ctsqlExecute(hCmd, &hCursor);
   if (rc != CTSQLRET_OK)
      Handle_Error("ctsqlExecute(SELECT)");

   /* fetch and display each individual record */
   while ((rc = ctsqlNext(hCursor)) == CTSQLRET_OK)
   {
      ctsqlGetChar(hCursor, 0, custnumb);
      ctsqlGetChar(hCursor, 4, custname);

      printf("\n\t\t%-8s%10s\n", custnumb, custname);
   }

   ctsqlFreeCursor(hCursor);
}


/*
 * Update_CustomerMaster_Records()
 *
 * Update one record under locking control to demonstrate the effects
 * of locking
 */

void Update_CustomerMaster_Record(void)
{
   CTSQLRET rc;

   printf("\tUpdate record...\n");

   rc = ctsqlExecuteDirect(hCmd, CT_STRING_LITERAL("UPDATE custmast SET cm_custname = 'KEYON DOOLING' WHERE cm_custnumb = '1003'"));
   if (rc != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(UPDATE)");

   printf("\tPress <ENTER> key to unlock\n");
   getchar();

   if ((rc = ctsqlCommit(hConn)) != CTSQLRET_OK)
      Handle_Error("ctsqlCommit()");
}

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)
{
   CTSQLRET rc;

   printf("DONE\n");

   /* disconnect from server */
   printf("\tLogout...\n");
   if ((rc = ctsqlDisconnect(hConn)) != CTSQLRET_OK)
      Handle_Error("ctsqlDisconnect()");

   /* free command handle */
   if (hCmd)
      ctsqlFreeCommand(hCmd);

   /* free connection handle */
   ctsqlFreeConnection(hConn);
}

Tutorial 4: Transaction Processing

<faircomInstallFolder>\drivers\c.sql.direct\tutorials\DSQL_tutorial4.c

This tutorial code performs the following:

Logon onto a session
Create 4 tables each with an index
Add records in multiple tables under transaction processing control
Display the contents of the tables

Transaction processing provides a method by which multiple related database operations that are spread across separate tables/files are guaranteed to succeed or none of the operations succeed. This ensures the integrity of the data in related tables/files.

To keep this tutorial simple, we use four procedures: Initialize(), Define(), Manage(), and You’re Done()!

Initialize() - Connects to the FairCom Database Engine.
Define() - Defines and creates our four tables/files.
Manage() - Adds rows/records to multiple tables/files under transaction control.
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)
{
   CTSQLRET rc;

   printf("INIT\n");

   /* allocate connection handle */
   if ((hConn = ctsqlNewConnection()) == NULL)
      Handle_Error("ctsqlNewConnection()");

   /* allocate command handle */
   if ((hCmd = ctsqlNewCommand(hConn)) == NULL)
      Handle_Error("ctsqlNewCommand()");

   /* connect to server */
   printf("\tLogon to server...\n");
   if ((rc = ctsqlConnect(hConn, CT_STRING_LITERAL("6597@localhost:ctreeSQL"), CT_STRING_LITERAL("admin"), CT_STRING_LITERAL("ADMIN"))) != CTSQLRET_OK)
      Handle_Error( "ctsqlConnect()");

   /* disable autocommit */
   if ((rc = ctsqlSetAutoCommit(hConn, CTSQL_FALSE)) != CTSQLRET_OK)
      Handle_Error( "ctsqlSetAutoCommit()");
}

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

void Define(void)
{
   CTSQLRET rc;

   printf("DEFINE\n");

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

   if ((rc = ctsqlCommit(hConn)) != CTSQLRET_OK)
      Handle_Error("ctsqlCommit()");
}

/*
 * Create_CustomerMaster_Table()
 *
 * Create the table CustomerMaster
 */

void Create_CustomerMaster_Table(void)
{
   CTSQLRET rc;

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

   if ((rc = ctsqlExecuteDirect(hCmd,
      CT_STRING_LITERAL("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))")
      )) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(CREATE TABLE)");
}


/*
 * Create_CustomerOrders_Table()
 *
 * Create the table CustomerOrders
 */

void Create_CustomerOrders_Table(void)
{
   CTSQLRET rc;

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

   if ((rc = ctsqlExecuteDirect(hCmd,
      CT_STRING_LITERAL("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)")
      )) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(CREATE TABLE)");
}


/*
 * Create_OrderItems_Table()
 *
 * Create the table OrderItems
 */

void Create_OrderItems_Table(void)
{
   CTSQLRET rc;

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

   if ((rc = ctsqlExecuteDirect(hCmd,
      CT_STRING_LITERAL("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)")
      )) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(CREATE TABLE)");
}


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

void Create_ItemMaster_Table(void)
{
   CTSQLRET rc;

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

   if ((rc = ctsqlExecuteDirect(hCmd,
      CT_STRING_LITERAL("CREATE TABLE itemmast ( \
         im_itemwght INTEGER, \
         im_itempric MONEY, \
         im_itemnumb CHAR(5) PRIMARY KEY, \
         im_itemdesc VARCHAR(47))")
      )) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(CREATE TABLE)");
}

Manage

Manage() provides data management functionality for your application and/or process.

Below is the code for Manage():

/*
 * Manage()
 *
 * Populates table and perform 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();
}

/*
 * Add_CustomerMaster_Records()
 *
 * This function adds records to table CustomerMaster from an
 * array of strings
 */

void Add_CustomerMaster_Records(void)
{
   CTSQLRET    rc;
   INTEGER     i;
   CTSQLCHAR   sCommand[512];
   CTSQLCHAR   *data[] = {
      CT_STRING_LITERAL("('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')"),
      CT_STRING_LITERAL("('1001','61434','CT','1','Michael Jordan','13 Main','Harford')"),
      CT_STRING_LITERAL("('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')"),
      CT_STRING_LITERAL("('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')")
   };
   INTEGER     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++)
   {
      ctsql_snprintf (sCommand, sizeof(sCommand)/sizeof(*sCommand), CT_STRING_LITERAL("INSERT INTO custmast VALUES %") CT_PRINTF_STR, data[i]);
      if ((rc = ctsqlExecuteDirect(hCmd, sCommand)) != CTSQLRET_OK)
         Handle_Error("ctsqlExecuteDirect(INSERT)");
   }

   if ((rc = ctsqlCommit(hConn)) != CTSQLRET_OK)
      Handle_Error("ctsqlCommit()");
}


/*
 * Add_ItemMaster_Records()
 *
 * This function adds records to table ItemMaster from an
 * array of strings
 */

void Add_ItemMaster_Records(void)
{
   CTSQLRET    rc;
   INTEGER     i;
   CTSQLCHAR   sCommand[512];
   CTSQLCHAR   *data[] = {
      CT_STRING_LITERAL("(10,19.95,'1','Hammer')"),
      CT_STRING_LITERAL("(3,  9.99,'2','Wrench')"),
      CT_STRING_LITERAL("(4, 16.59,'3','Saw')"),
      CT_STRING_LITERAL("(1,  3.98,'4','Pliers')")
   };
   INTEGER     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++)
   {
      ctsql_snprintf (sCommand, sizeof(sCommand)/sizeof(*sCommand), CT_STRING_LITERAL("INSERT INTO itemmast VALUES %") CT_PRINTF_STR, data[i]);
      if ((rc = ctsqlExecuteDirect(hCmd, sCommand)) != CTSQLRET_OK)
         Handle_Error("ctsqlExecuteDirect(INSERT)");
   }

   if ((rc = ctsqlCommit(hConn)) != CTSQLRET_OK)
      Handle_Error("ctsqlCommit()");
}


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

void Add_Transactions(void)
{
   typedef struct {
      CTSQLCHAR       *ordrdate, *promdate, *ordrnumb, *custnumb;
   } ORDER_DATA;

   typedef struct {
      CTSQLCHAR      *ordrnumb;
      USMALLINT      sequnumb;
      USMALLINT      quantity;
      CTSQLCHAR      *itemnumb;
   } ORDERITEM_DATA;

   ORDER_DATA orders[] = {
      {CT_STRING_LITERAL("09/01/2002"), CT_STRING_LITERAL("09/05/2002"), CT_STRING_LITERAL("1"), CT_STRING_LITERAL("1001")},
      {CT_STRING_LITERAL("09/02/2002"), CT_STRING_LITERAL("09/06/2002"), CT_STRING_LITERAL("2"), CT_STRING_LITERAL("9999")},   /* bad customer number */
      {CT_STRING_LITERAL("09/22/2002"), CT_STRING_LITERAL("09/26/2002"), CT_STRING_LITERAL("3"), CT_STRING_LITERAL("1003")}
   };
   INTEGER  nOrders = sizeof(orders) / sizeof(ORDER_DATA);

   ORDERITEM_DATA items[] = {
      {CT_STRING_LITERAL("1"), 1, 2, CT_STRING_LITERAL("1")},
      {CT_STRING_LITERAL("1"), 2, 1, CT_STRING_LITERAL("2")},
      {CT_STRING_LITERAL("2"), 1, 1, CT_STRING_LITERAL("3")},
      {CT_STRING_LITERAL("2"), 2, 3, CT_STRING_LITERAL("4")},
      {CT_STRING_LITERAL("3"), 1, 2, CT_STRING_LITERAL("3")},
      {CT_STRING_LITERAL("3"), 2, 2, CT_STRING_LITERAL("99")} /* bad item number */
   };
   INTEGER  nItems = sizeof(items) / sizeof(ORDERITEM_DATA);

   CTSQLRET    rc;
   INTEGER     i, j = 0;
   CTSQLCHAR   sCommand[512];

   printf("\tAdd transaction records... \n");

   for (i = 0; i < nOrders; i++)
   {
      /* add order record */
      ctsql_snprintf(sCommand, sizeof(sCommand)/sizeof(*sCommand), CT_STRING_LITERAL("INSERT INTO custordr VALUES ('%" CT_PRINTF_STR "', '%" CT_PRINTF_STR "', '%" CT_PRINTF_STR "', '%" CT_PRINTF_STR "')"),
         orders[i].ordrdate,
         orders[i].promdate,
         orders[i].ordrnumb,
         orders[i].custnumb);
      if ((rc = ctsqlExecuteDirect(hCmd, sCommand)) != CTSQLRET_OK)
         Handle_Error("ctsqlExecuteDirect(INSERT)");

      /* process order items */
      while (!(ctsql_strcmp(items[j].ordrnumb, orders[i].ordrnumb)))
      {
         /* add item record */
         ctsql_snprintf(sCommand, sizeof(sCommand)/sizeof(*sCommand), CT_STRING_LITERAL("INSERT INTO ordritem VALUES (%d, %d, '%" CT_PRINTF_STR "', '%" CT_PRINTF_STR "')"),
            items[j].sequnumb,
            items[j].quantity,
            items[j].ordrnumb,
            items[j].itemnumb);
         if ((rc = ctsqlExecuteDirect(hCmd, sCommand)) != CTSQLRET_OK)
            Handle_Error("ctsqlExecuteDirect(INSERT)");

         /* bump to next item */
         j++;

         /* exit the while loop on last item */
         if (j >= nItems)
            break;
      }

      /* commit the transaction */
      if ((rc = ctsqlCommit(hConn)) != CTSQLRET_OK)
         Handle_Error("ctsqlCommit()");
   }
}


/*
 * Display_CustomerOrders()
 *
 * This function displays the contents of CustomerOrders table
 */

void Display_CustomerOrders(void)
{
   CTSQLRET       rc;
   char           ordrnumb[6+1], custnumb[4+1];
   pCTSQLCURSOR   hCursor = NULL;

   printf("\n\tCustomerOrders Table...\n");

   rc = ctsqlPrepare(hCmd, CT_STRING_LITERAL("SELECT * FROM custordr"));
   if (rc != CTSQLRET_OK)
      Handle_Error("ctsqlPrepare(SELECT)");

   rc = ctsqlExecute(hCmd, &hCursor);
   if (rc != CTSQLRET_OK)
      Handle_Error("ctsqlExecute(SELECT)");

   /* fetch and display each individual record */
   while ((rc = ctsqlNext(hCursor)) == CTSQLRET_OK)
   {
      ctsqlGetChar(hCursor, 2, ordrnumb);
      ctsqlGetChar(hCursor, 3, custnumb);

      printf("\t   %s   %s\n", ordrnumb, custnumb);
   }

   ctsqlFreeCursor(hCursor);
}


/*
 * Display_OrderItems()
 *
 * This function displays the contents of OrderItems table
 */

void Display_OrderItems(void)
{
   CTSQLRET       rc;
   char           ordrnumb[6+1], itemnumb[5+1];
   pCTSQLCURSOR   hCursor = NULL;

   printf("\n\tOrderItems Table...\n");

   rc = ctsqlPrepare(hCmd, CT_STRING_LITERAL("SELECT * FROM ordritem"));
   if (rc != CTSQLRET_OK)
      Handle_Error("ctsqlPrepare(SELECT)");

   rc = ctsqlExecute(hCmd, &hCursor);
   if (rc != CTSQLRET_OK)
      Handle_Error("ctsqlExecute(SELECT)");

   /* fetch and display each individual record */
   while ((rc = ctsqlNext(hCursor)) == CTSQLRET_OK)
   {
      ctsqlGetChar(hCursor, 2, ordrnumb);
      ctsqlGetChar(hCursor, 3, itemnumb);

      printf("\t   %s   %s\n", ordrnumb, itemnumb);
   }

   ctsqlFreeCursor(hCursor);
}

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)
{
   CTSQLRET rc;

   printf("DONE\n");

   /* re-enable autocommit */
   if ((rc = ctsqlSetAutoCommit(hConn, CTSQL_TRUE)) != CTSQLRET_OK)
      Handle_Error( "ctsqlSetAutoCommit()");

   Delete_Tables();

   /* disconnect from server */
   printf("\tLogout...\n");
   if ((rc = ctsqlDisconnect(hConn)) != CTSQLRET_OK)
      Handle_Error("ctsqlDisconnect()");

   /* free command handle */
   if (hCmd)
      ctsqlFreeCommand(hCmd);

   /* free connection handle */
   ctsqlFreeConnection(hConn);
}


/*
 * Delete_Tables()
 *
 * This function removes all existing tables
 */

void Delete_Tables(void)
{
   CTSQLRET rc;

   if ((rc = ctsqlExecuteDirect(hCmd, CT_STRING_LITERAL("DROP TABLE ordritem"))) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(DROP TABLE)");
   if ((rc = ctsqlExecuteDirect(hCmd, CT_STRING_LITERAL("DROP TABLE custordr"))) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(DROP TABLE)");
   if ((rc = ctsqlExecuteDirect(hCmd, CT_STRING_LITERAL("DROP TABLE custmast"))) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(DROP TABLE)");
   if ((rc = ctsqlExecuteDirect(hCmd, CT_STRING_LITERAL("DROP TABLE itemmast"))) != CTSQLRET_OK)
      Handle_Error("ctsqlExecuteDirect(DROP TABLE)");
}

	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.

Direct SQL for C and C++ Tutorials

Tutorial 1: Initialize a single table

Init

Define

Manage

Done

Tutorial 2: Relational Model and Indexing

Init

Define

Manage

Done

Tutorial 3: Locking

Init

Define

Manage

Done

Tutorial 4: Transaction Processing

Init

Define

Manage

Done

Was this article helpful?

Related Articles

FairCom DB JSON APIs

FairCom DB API for C Overview

SQL APIs