Tutorials

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.

	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.

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

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

Introductory Tutorial

drivers\vb.nav\tutorials\Tutorial1.vb

This tutorial will take you through the basic use of the FairCom DB API .NET Visual Basic Framework.

As with all other examples in the c-tree tutorial series, this tutorial simplifies the creation and use of a database into four simple steps: Initialize(), Define(), Manage(), and You’re Done() !

Tutorial #1: Introductory - Simple Single Table

We wanted to keep this program as simple as possible. This program does the following:

• 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:

Imports System
Imports FairCom.CtreeDb
 
Module Tutorial1HL
   Dim MySession As CTSession
   Dim MyTable As CTTable
   Dim MyRecord As CTRecord
   '
   ' main()
   '
   ' The main() function implements the concept of "init, define, manage
   ' and you're done..."
   '
   Sub Main()
 
      Initialize()
      Define()
      Manage()
      Done()
 
      Console.WriteLine(vbCrLf + "Press <ENTER> key to exit . . .")
      Console.ReadLine()
 
   End Sub

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
   Sub Initialize()
 
      Console.WriteLine("INIT")
      Try
         ' allocate objects
         MySession = New CTSession(SESSION_TYPE.CTREE_SESSION)
         MyTable = New CTTable(MySession)
         MyRecord = New CTRecord(MyTable)
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
      Try
         ' connect to server
         Console.WriteLine(ControlChars.Tab + "Logon to server...")
         MySession.Logon("FAIRCOMS", "", "")
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub

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()
   '
   ' Open the table, if it exists. Otherwise create and open the table
   '
   Sub Define()
 
      Dim do_create As Boolean = False
 
      Console.WriteLine("DEFINE")
 
      Try
         Console.WriteLine(ControlChars.Tab + "Open table...")
         MyTable.Open("custmast", OPEN_MODE.NORMAL_OPEN)
      Catch
         ' table does not exist. Try to create it
         do_create = True
      End Try
 
      If (do_create) Then
         ' create the table
         Console.WriteLine(ControlChars.Tab + "Add fields...")
         Try
            MyTable.AddField("cm_custnumb", FIELD_TYPE.FSTRING, 4)
            MyTable.AddField("cm_custzipc", FIELD_TYPE.FSTRING, 9)
            MyTable.AddField("cm_custstat", FIELD_TYPE.FSTRING, 2)
            MyTable.AddField("cm_custrtng", FIELD_TYPE.FSTRING, 1)
            MyTable.AddField("cm_custname", FIELD_TYPE.VSTRING, 47)
            MyTable.AddField("cm_custaddr", FIELD_TYPE.VSTRING, 47)
            MyTable.AddField("cm_custcity", FIELD_TYPE.VSTRING, 47)
 
            Console.WriteLine(ControlChars.Tab + "Create table...")
            MyTable.Create("custmast", CREATE_MODE.NORMAL_CREATE)
 
            MyTable.Open("custmast", OPEN_MODE.NORMAL_OPEN)
         Catch E As CTException
            Handle_Exception(E)
         End Try
 
      Else
         Check_Table_Mode(MyTable)
      End If
 
   End Sub

 
 
   '
   ' Check_Table_Mode()
   '
   ' Check if existing table has transaction processing flag enabled.
   ' If a table is under transaction processing control, modify the
   ' table mode to disable transaction processing
   '
   Sub Check_Table_Mode(ByVal table As CTTable)
 
      Try
         ' get table create mode
         Dim mode As CREATE_MODE = table.GetCreateMode()
 
         ' check if table is under transaction processing control
         If ((mode And CREATE_MODE.TRNLOG_CREATE) <> 0) Then
            ' change file mode to disable transaction processing
            mode = mode Xor CREATE_MODE.TRNLOG_CREATE
            table.UpdateCreateMode(mode)
         End If
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub

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
   '
   Sub Manage()
 
      Console.WriteLine("MANAGE")
 
      ' delete any existing records
      Delete_Records()
 
      ' populate the table with data
      Add_Records()
 
      ' display contents of table
      Display_Records()
 
   End Sub

 
 
   '
   ' Delete_Records()
   '
   ' This function deletes all the records in the table
   '
   Sub Delete_Records()
 
      Dim found As Boolean
 
      Console.WriteLine(ControlChars.Tab + "Delete records...")
      Try
         ' read first record
         found = MyRecord.First()
 
         While (found)  ' while records are found
            ' delete record
            MyRecord.Delete()
            ' read next record
            found = MyRecord.Next()
         End While
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub
 
 
   '
   ' Add_Records()
   '
   ' This function adds records to a table in the database from an
   ' array of strings
   '
   Public Structure DATA_RECORD
      ' struct members
      Public number, zipcode, state, rating, name, address, city As String
      ' struct constructor
      Public Sub New(ByVal number As String, ByVal zipcode As String, ByVal state As String, ByVal rating As String, ByVal name As String, ByVal address As String, ByVal city As String)
         Me.number = number
         Me.zipcode = zipcode
         Me.state = state
         Me.rating = rating
         Me.name = name
         Me.address = address
         Me.city = city
      End Sub
   End Structure
 
   Sub Add_Records()
 
      Dim data(3) As DATA_RECORD
      data(0) = New DATA_RECORD("1000", "92867", "CA", "1", "Bryan Williams", "2999 Regency", "Orange")
      data(1) = New DATA_RECORD("1001", "61434", "CT", "1", "Michael Jordan", "13 Main", "Harford")
      data(2) = New DATA_RECORD("1002", "73677", "GA", "1", "Joshua Brown", "4356 Cambridge", "Atlanta")
      data(3) = New DATA_RECORD("1003", "10034", "MO", "1", "Keyon Dooling", "19771 Park Avenue", "Columbia")
      Dim nRecords As Integer = data.Length
 
      Console.WriteLine(ControlChars.Tab + "Add records...")
 
      Try
         Dim i As Integer
         For i = 0 To nRecords - 1
            MyRecord.Clear()
 
            ' populate record buffer with data
            MyRecord.SetFieldAsString(0, data(i).number)
            MyRecord.SetFieldAsString(1, data(i).zipcode)
            MyRecord.SetFieldAsString(2, data(i).state)
            MyRecord.SetFieldAsString(3, data(i).rating)
            MyRecord.SetFieldAsString(4, data(i).name)
            MyRecord.SetFieldAsString(5, data(i).address)
            MyRecord.SetFieldAsString(6, data(i).city)
 
            ' add record
            MyRecord.Write()
         Next
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub
 
 
   '
   ' Display_Records()
   '
   ' This function displays the contents of a table. First() and Next()
   ' fetch the record. Then each field is parsed and displayed
   '
   Sub Display_Records()
 
      Dim found As Boolean
      Dim custnumb As String
      Dim custname As String
 
      Console.Write(ControlChars.Tab + "Display records...")
 
      Try
         ' read first record
         found = MyRecord.First()
 
         While (found)
            custnumb = MyRecord.GetFieldAsString(0)
            custname = MyRecord.GetFieldAsString(4)
 
            Console.WriteLine(ControlChars.NewLine + ControlChars.Tab + ControlChars.Tab + "{0,-8}{1,-20}", custnumb, custname)
 
            ' read next record
            found = MyRecord.Next()
         End While
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub

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, freeing,
   ' disconnecting and logging out of the database
   '
   Sub Done()
 
      Console.WriteLine("DONE")
      Try
         ' close table
         Console.WriteLine(ControlChars.Tab + "Close table...")
         MyTable.Close()
 
         ' logout
         Console.WriteLine(ControlChars.Tab + "Logout...")
         MySession.Logout()
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub

Relationships

drivers\vb.nav\tutorials\Tutorial2.vb

Now we will build some table/file relationships using the FairCom DB API .NET Visual Basic Framework.

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

Like all other examples in the c-tree tutorial series, this tutorial simplifies the creation and use of a database into four simple steps: Initialize(), Define(), Manage(), and You’re Done() !

Tutorial #2: Relational Model and Indexing

Here we add a bit more complexity, introducing multiple tables, with related indices in order to form a simple "relational" database simulating an Order Entry system. Here is an overview of what will be created:

• Initialize() - Connects to the FairCom Database Engine.
• Define() - Defines and creates the "custmast", "custordr", "ordritem" and the "itemmast" tables/files with related indexes.
• Manage() - Adds some related rows/records to all tables/files. Then queries the database.
• Done() - Disconnects from FairCom Database Engine.

Note our simple Main() function:

Imports System
Imports FairCom.CtreeDb
Module Tutorial2HL
 
   Dim MySession As CTSession
 
   Dim tableCustOrdr As CTTable
   Dim tableOrdrItem As CTTable
   Dim tableItemMast As CTTable
   Dim tableCustMast As CTTable
 
   Dim recordCustOrdr As CTRecord
   Dim recordOrdrItem As CTRecord
   Dim recordItemMast As CTRecord
   Dim recordCustMast As CTRecord
 
   '
   ' main()
   '
   ' The main() function implements the concept of "init, define, manage
   ' and you're done..."
   '
   Sub Main()
 
      Initialize()
      Define()
      Manage()
      Done()
 
      Console.WriteLine(ControlChars.NewLine + "Press <ENTER> key to exit . . .")
      Console.ReadLine()
 
   End Sub

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
   '
   Sub Initialize()
 
      Console.WriteLine("INIT")
      Try
         ' allocate the session object
         MySession = New CTSession(SESSION_TYPE.CTREE_SESSION)
 
         ' allocate the table objects
         tableCustOrdr = New CTTable(MySession)
         tableOrdrItem = New CTTable(MySession)
         tableItemMast = New CTTable(MySession)
         tableCustMast = New CTTable(MySession)
 
         ' allocate the record objects
         recordCustOrdr = New CTRecord(tableCustOrdr)
         recordOrdrItem = New CTRecord(tableOrdrItem)
         recordItemMast = New CTRecord(tableItemMast)
         recordCustMast = New CTRecord(tableCustMast)
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
      Try
         ' connect to server
         Console.WriteLine(ControlChars.Tab + "Logon to server...")
         MySession.Logon("FAIRCOMS", "", "")
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub

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()
   '
   ' Open the table, if it exists. Otherwise create and open the table
   '
   Sub Define()
 
      Console.WriteLine("DEFINE")
 
      Create_CustomerMaster_Table()
      Create_CustomerOrders_Table()
      Create_OrderItems_Table()
      Create_ItemMaster_Table()
 
   End Sub
 
 
   '
   ' Create_CustomerMaster_Table()
   '
   ' Open table CustomerMaster, if it exists. Otherwise create it
   ' along with its indexes and open it
   '
   Sub Create_CustomerMaster_Table()
 
      Dim do_create As Boolean = False
      Dim field1 As CTField
      Dim index1 As CTIndex
 
      ' define table CustomerMaster
      Console.WriteLine(ControlChars.Tab + "table CustomerMaster")
      Try
         tableCustMast.Open("custmast", OPEN_MODE.NORMAL_OPEN)
      Catch
         ' table does not exist
         do_create = True
      End Try
 
      If (do_create) Then
         ' define table fields
         Console.WriteLine(ControlChars.Tab + "Add fields...")
         Try
            field1 = tableCustMast.AddField("cm_custnumb", FIELD_TYPE.FSTRING, 4)
            tableCustMast.AddField("cm_custzipc", FIELD_TYPE.FSTRING, 9)
            tableCustMast.AddField("cm_custstat", FIELD_TYPE.FSTRING, 2)
            tableCustMast.AddField("cm_custratg", FIELD_TYPE.FSTRING, 1)
            tableCustMast.AddField("cm_custname", FIELD_TYPE.VSTRING, 47)
            tableCustMast.AddField("cm_custaddr", FIELD_TYPE.VSTRING, 47)
            tableCustMast.AddField("cm_custcity", FIELD_TYPE.VSTRING, 47)
 
            ' define index
            index1 = tableCustMast.AddIndex("cm_custnumb_idx", KEY_TYPE.FIXED_INDEX, False, False)
            index1.AddSegment(field1, SEG_MODE.SCHSEG_SEG)
 
            ' create table
            Console.WriteLine(ControlChars.Tab + "Create table...")
            tableCustMast.Create("custmast", CREATE_MODE.NORMAL_CREATE)
 
            ' open table
            Console.WriteLine(ControlChars.Tab + "Open table...")
            tableCustMast.Open("custmast", OPEN_MODE.NORMAL_OPEN)
         Catch E As CTException
            Handle_Exception(E)
         End Try
 
      Else
         Check_Table_Mode(tableCustMast)
 
         ' confirm the index exists, if not then add the index
         '
         ' Me scenario arises out of the fact that Me table was created in tutorial 1
         ' without indexes. The index is now created by the call to ctdbAlterTable
 
         do_create = False
         Try
            tableCustMast.GetIndex("cm_custnumb_idx")
         Catch
            do_create = True
         End Try
 
         If (do_create) Then
            Try
               field1 = tableCustMast.GetField("cm_custnumb")
               index1 = tableCustMast.AddIndex("cm_custnumb_idx", KEY_TYPE.FIXED_INDEX, False, False)
               index1.AddSegment(field1, SEG_MODE.SCHSEG_SEG)
               tableCustMast.Alter(ALTER_TABLE.NORMAL)
            Catch E As CTException
               Handle_Exception(E)
            End Try
         End If
      End If
 
   End Sub
 
 
   '
   ' Create_CustomerOrders_Table()
   '
   ' Open table CustomerOrders, if it exists. Otherwise create it
   ' along with its indexes and open it
   '
   Sub Create_CustomerOrders_Table()
 
      Dim do_create As Boolean = False
      Dim field1, field2 As CTField
      Dim index1, index2 As CTIndex
 
      ' define table CustomerOrders
      Console.WriteLine(ControlChars.Tab + "table CustomerOrders")
      Try
         tableCustOrdr.Open("custordr", OPEN_MODE.NORMAL_OPEN)
      Catch
         ' table does not exist
         do_create = True
      End Try
 
      If (do_create) Then
         Try
            ' define table fields
            tableCustOrdr.AddField("co_ordrdate", FIELD_TYPE.DATE, 4)
            tableCustOrdr.AddField("co_promdate", FIELD_TYPE.DATE, 4)
            field1 = tableCustOrdr.AddField("co_ordrnumb", FIELD_TYPE.FSTRING, 6)
            field2 = tableCustOrdr.AddField("co_custnumb", FIELD_TYPE.FSTRING, 4)
 
            ' define indexes
            index1 = tableCustOrdr.AddIndex("co_ordrnumb_idx", KEY_TYPE.LEADING_INDEX, False, False)
            index1.AddSegment(field1, SEG_MODE.SCHSEG_SEG)
            index2 = tableCustOrdr.AddIndex("co_custnumb_idx", KEY_TYPE.LEADING_INDEX, True, False)
            index2.AddSegment(field2, SEG_MODE.SCHSEG_SEG)
 
            ' create table
            Console.WriteLine(ControlChars.Tab + "Create table...")
            tableCustOrdr.Create("custordr", CREATE_MODE.NORMAL_CREATE)
 
            ' open table
            Console.WriteLine(ControlChars.Tab + "Open table...")
            tableCustOrdr.Open("custordr", OPEN_MODE.NORMAL_OPEN)
         Catch E As CTException
            Handle_Exception(E)
         End Try
      Else
         Check_Table_Mode(tableCustOrdr)
      End If
 
   End Sub
 
 
   '
   ' Create_OrderItems_Table()
   '
   ' Open table OrderItems, if it exists. Otherwise create it
   ' along with its indexes and open it
   '
   Sub Create_OrderItems_Table()
 
      Dim do_create As Boolean = False
      Dim field1, field2, field3 As CTField
      Dim index1, index2 As CTIndex
 
      ' define table OrderItems
      Console.WriteLine(ControlChars.Tab + "table OrderItems")
      Try
         tableOrdrItem.Open("ordritem", OPEN_MODE.NORMAL_OPEN)
      Catch
         ' table does not exist
         do_create = True
      End Try
 
      If (do_create) Then
         Try
            ' define table fields
            field1 = tableOrdrItem.AddField("oi_sequnumb", FIELD_TYPE.INT2, 2)
            tableOrdrItem.AddField("oi_quantity", FIELD_TYPE.INT2, 2)
            field2 = tableOrdrItem.AddField("oi_ordrnumb", FIELD_TYPE.FSTRING, 6)
            field3 = tableOrdrItem.AddField("oi_itemnumb", FIELD_TYPE.FSTRING, 5)
 
            ' define indexes
            index1 = tableOrdrItem.AddIndex("oi_ordrnumb_idx", KEY_TYPE.LEADING_INDEX, False, False)
            index1.AddSegment(field2, SEG_MODE.SCHSEG_SEG)
            index1.AddSegment(field1, SEG_MODE.SCHSEG_SEG)
            index2 = tableOrdrItem.AddIndex("oi_itemnumb_idx", KEY_TYPE.LEADING_INDEX, True, False)
            index2.AddSegment(field3, SEG_MODE.SCHSEG_SEG)
 
            ' create table
            Console.WriteLine(ControlChars.Tab + "Create table...")
            tableOrdrItem.Create("ordritem", CREATE_MODE.NORMAL_CREATE)
 
            ' open table
            Console.WriteLine(ControlChars.Tab + "Open table...")
            tableOrdrItem.Open("ordritem", OPEN_MODE.NORMAL_OPEN)
         Catch E As CTException
            Handle_Exception(E)
         End Try
      Else
         Check_Table_Mode(tableCustOrdr)
      End If
 
   End Sub
 
 
   '
   ' Create_ItemMaster_Table()
   '
   ' Open table ItemMaster, if it exists. Otherwise create it
   ' along with its indexes and open it
   '
   Sub Create_ItemMaster_Table()
 
      Dim do_create As Boolean = False
      Dim field1 As CTField
      Dim index1 As CTIndex
 
      ' define table ItemMaster
      Console.WriteLine(ControlChars.Tab + "table ItemMaster")
      Try
         tableItemMast.Open("itemmast", OPEN_MODE.NORMAL_OPEN)
      Catch
         ' table does not exist
         do_create = True
      End Try
 
      If (do_create) Then
         Try
            ' define table fields
            tableItemMast.AddField("im_itemwght", FIELD_TYPE.INT4, 4)
            tableItemMast.AddField("im_itempric", FIELD_TYPE.MONEY, 4)
            field1 = tableItemMast.AddField("im_itemnumb", FIELD_TYPE.FSTRING, 5)
            tableItemMast.AddField("im_itemdesc", FIELD_TYPE.VSTRING, 47)
 
            ' define indexes
            index1 = tableItemMast.AddIndex("im_itemnumb_idx", KEY_TYPE.LEADING_INDEX, False, False)
            index1.AddSegment(field1, SEG_MODE.SCHSEG_SEG)
 
            ' create table
            Console.WriteLine(ControlChars.Tab + "Create table...")
            tableItemMast.Create("itemmast", CREATE_MODE.NORMAL_CREATE)
 
            ' open table
            Console.WriteLine(ControlChars.Tab + "Open table...")
            tableItemMast.Open("itemmast", OPEN_MODE.NORMAL_OPEN)
         Catch E As CTException
            Handle_Exception(E)
         End Try
      Else
         Check_Table_Mode(tableCustOrdr)
      End If
 
   End Sub
 
 
   '
   ' Check_Table_Mode()
   '
   ' Check if existing table has transaction processing flag enabled.
   ' If a table is under transaction processing control, modify the
   ' table mode to disable transaction processing
   '
   Sub Check_Table_Mode(ByVal table As CTTable)
 
      Try
         ' get table create mode
         Dim mode As CREATE_MODE = table.GetCreateMode()
 
         ' check if table is under transaction processing control
         If ((mode And CREATE_MODE.TRNLOG_CREATE) <> 0) Then
            ' change file mode to disable transaction processing
            mode = mode Xor CREATE_MODE.TRNLOG_CREATE
            table.UpdateCreateMode(mode)
         End If
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub

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
   '
   Sub Manage()
 
      Dim quantity As Integer
      Dim price, total As Double
      Dim itemnumb, custnumb, ordrnumb, custname As String
      Dim isOrderFound, isItemFound As Boolean
 
      Console.WriteLine("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
 
      Console.WriteLine(ControlChars.NewLine + ControlChars.Tab + "Query Results")
 
      Try
         ' get the first order
         isOrderFound = recordCustOrdr.First()
 
         While (isOrderFound) ' for each order in the CustomerOrders table
            ' fetch order number
            ordrnumb = recordCustOrdr.GetFieldAsString(2)
            ' fetch customer number
            custnumb = recordCustOrdr.GetFieldAsString(3)
 
            ' fetch name from CustomerMaster table based on customer number
            recordCustMast.Clear()
            recordCustMast.SetFieldAsString(0, custnumb)
            If (recordCustMast.Find(FIND_MODE.EQ) <> True) Then
               Continue While
            End If
            custname = recordCustMast.GetFieldAsString(4)
 
            ' fetch item price from OrderItems table
            recordOrdrItem.Clear()
            recordOrdrItem.SetFieldAsString(2, ordrnumb)
            ' define a recordset to scan only items applicable to Me order
            recordOrdrItem.RecordSetOn(6)
            isItemFound = recordOrdrItem.First()
 
            total = 0
            While (isItemFound)   ' for each order item in OrderItems table
               ' fetch item quantity
               quantity = recordOrdrItem.GetFieldAsSigned(1)
               ' fetch item number
               itemnumb = recordOrdrItem.GetFieldAsString(3)
 
               ' fetch item price from ItemMaster table based on item number
               recordItemMast.Clear()
               recordItemMast.SetFieldAsString(2, itemnumb)
               recordItemMast.Find(FIND_MODE.EQ)
               price = recordItemMast.GetFieldAsFloat(1)
 
               ' calculate order total
               total += (price * quantity)
 
               isItemFound = recordOrdrItem.Next()
            End While
            recordOrdrItem.RecordSetOff()
 
            ' output data to stdout
            Console.WriteLine(ControlChars.Tab + ControlChars.Tab + "{0,-20}{1,-8}", custname, total)
 
            ' read next order
            If (recordCustOrdr.Next() <> True) Then
               isOrderFound = False
            End If
         End While
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub

 
 
   '
   ' Add_CustomerMaster_Records()
   '
   ' This function adds records to table CustomerMaster from an
   ' array of strings
   '
   Public Structure CUSTOMER_DATA
      ' struct members
      Public number, zipcode, state, rating, name, address, city As String
      ' struct constructor
      Public Sub New(ByVal number As String, ByVal zipcode As String, ByVal state As String, ByVal rating As String, ByVal name As String, ByVal address As String, ByVal city As String)
         Me.number = number
         Me.zipcode = zipcode
         Me.state = state
         Me.rating = rating
         Me.name = name
         Me.address = address
         Me.city = city
      End Sub
   End Structure
 
   Sub Add_CustomerMaster_Records()
      Dim data(3) As CUSTOMER_DATA
      data(0) = New CUSTOMER_DATA("1000", "92867", "CA", "1", "Bryan Williams", "2999 Regency", "Orange")
      data(1) = New CUSTOMER_DATA("1001", "61434", "CT", "1", "Michael Jordan", "13 Main", "Harford")
      data(2) = New CUSTOMER_DATA("1002", "73677", "GA", "1", "Joshua Brown", "4356 Cambridge", "Atlanta")
      data(3) = New CUSTOMER_DATA("1003", "10034", "MO", "1", "Keyon Dooling", "19771 Park Avenue", "Columbia")
      Dim nRecords As Integer = data.Length
 
      Delete_Records(recordCustMast)
 
      Console.WriteLine(ControlChars.Tab + "Add records in table CustomerMaster...")
 
      Try
         Dim i As Integer
         For i = 0 To nRecords - 1
            recordCustMast.Clear()
 
            ' populate record buffer with data
            recordCustMast.SetFieldAsString(0, data(i).number)
            recordCustMast.SetFieldAsString(1, data(i).zipcode)
            recordCustMast.SetFieldAsString(2, data(i).state)
            recordCustMast.SetFieldAsString(3, data(i).rating)
            recordCustMast.SetFieldAsString(4, data(i).name)
            recordCustMast.SetFieldAsString(5, data(i).address)
            recordCustMast.SetFieldAsString(6, data(i).city)
 
            ' add record
            recordCustMast.Write()
         Next
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub
 
 
   '
   ' Add_CustomerOrders_Records()
   '
   ' This function adds records to table CustomerOrders from an
   ' array of strings
   '
   Public Structure ORDER_DATA
      ' struct members
      Public orderdate, promisedate, ordernum, customernum As String
      ' struct constructor
      Public Sub New(ByVal orderdate As String, ByVal promisedate As String, ByVal ordernum As String, ByVal customernum As String)
         Me.orderdate = orderdate
         Me.promisedate = promisedate
         Me.ordernum = ordernum
         Me.customernum = customernum
      End Sub
   End Structure
 
   Sub Add_CustomerOrders_Records()
 
      Dim data(1) As ORDER_DATA
      data(0) = New ORDER_DATA("09/01/2002", "09/05/2002", "1", "1001")
      data(1) = New ORDER_DATA("09/02/2002", "09/06/2002", "2", "1002")
      Dim nRecords As Integer = data.Length
 
      Dim orderdate As CTDate = New CTDate()
      Dim promisedate As CTDate = New CTDate()
 
      Delete_Records(recordCustOrdr)
 
      Console.WriteLine(ControlChars.Tab + "Add records in table CustomerOrders...")
 
      Try
         Dim i As Integer
         For i = 0 To nRecords - 1
            recordCustOrdr.Clear()
 
            orderdate.StringToDate(data(i).orderdate, DATE_TYPE.MDCY_DATE)
            promisedate.StringToDate(data(i).promisedate, DATE_TYPE.MDCY_DATE)
            ' populate record buffer with data
            recordCustOrdr.SetFieldAsDate(0, orderdate)
            recordCustOrdr.SetFieldAsDate(1, promisedate)
            recordCustOrdr.SetFieldAsString(2, data(i).ordernum)
            recordCustOrdr.SetFieldAsString(3, data(i).customernum)
 
            ' add record
            recordCustOrdr.Write()
         Next
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub
 
 
   '
   ' Add_OrderItems_Records()
   '
   ' This function adds records to table OrderItems from an
   ' array of strings
   '
   Public Structure ORDERITEM_DATA
      ' struct members
      Public sequencenum, quantity As Integer
      Public ordernum, itemnum As String
      ' struct constructor
      Public Sub New(ByVal sequencenum As Integer, ByVal quantity As Integer, ByVal ordernum As String, ByVal itemnum As String)
         Me.sequencenum = sequencenum
         Me.quantity = quantity
         Me.ordernum = ordernum
         Me.itemnum = itemnum
      End Sub
   End Structure
 
   Sub Add_OrderItems_Records()
      Dim data(3) As ORDERITEM_DATA
      data(0) = New ORDERITEM_DATA(1, 2, "1", "1")
      data(1) = New ORDERITEM_DATA(2, 1, "1", "2")
      data(2) = New ORDERITEM_DATA(3, 1, "1", "3")
      data(3) = New ORDERITEM_DATA(1, 3, "2", "3")
      Dim nRecords As Integer = data.Length
 
      Delete_Records(recordOrdrItem)
 
      Console.WriteLine(ControlChars.Tab + "Add records in table OrderItems...")
 
      Try
         Dim i As Integer
         For i = 0 To nRecords - 1
            recordOrdrItem.Clear()
 
            ' populate record buffer with data
            recordOrdrItem.SetFieldAsSigned(0, data(i).sequencenum)
            recordOrdrItem.SetFieldAsSigned(1, data(i).quantity)
            recordOrdrItem.SetFieldAsString(2, data(i).ordernum)
            recordOrdrItem.SetFieldAsString(3, data(i).itemnum)
 
            ' add record
            recordOrdrItem.Write()
         Next
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub
 
 
   '
   ' Add_ItemMaster_Records()
   '
   ' This function adds records to table ItemMaster from an
   ' array of strings
   '
   Public Structure ITEM_DATA
      ' struct members
      Public weight As Integer
      Public price As CTMoney
      Public itemnum, description As String
      ' struct constructor
      Public Sub New(ByVal weight As Integer, ByVal price As CTMoney, ByVal itemnum As String, ByVal description As String)
         Me.weight = weight
         Me.price = price
         Me.itemnum = itemnum
         Me.description = description
      End Sub
   End Structure
 
   Sub Add_ItemMaster_Records()
      Dim data(3) As ITEM_DATA
      Data(0) = New ITEM_DATA(10, 1995, "1", "Hammer")
      Data(1) = New ITEM_DATA(3, 999, "2", "Wrench")
      Data(2) = New ITEM_DATA(4, 1659, "3", "Saw")
      Data(3) = New ITEM_DATA(1, 398, "4", "Pliers")
      Dim nRecords As Integer = Data.Length
 
      Delete_Records(recordItemMast)
 
      Console.WriteLine(ControlChars.Tab + "Add records in table ItemMaster...")
 
      Try
         Dim i As Integer
         For i = 0 To nRecords - 1
            recordItemMast.Clear()
 
            ' populate record buffer with data
            recordItemMast.SetFieldAsSigned(0, Data(i).weight)
            recordItemMast.SetFieldAsMoney(1, Data(i).price)
            recordItemMast.SetFieldAsString(2, Data(i).itemnum)
            recordItemMast.SetFieldAsString(3, Data(i).description)
 
            ' add record
            recordItemMast.Write()
         Next
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub
 
 
   '
   ' Delete_Records()
   '
   ' This function deletes all the records in the table
   '
   Sub Delete_Records(ByVal record As CTRecord)
 
      Dim found As Boolean
 
      Console.WriteLine(ControlChars.Tab + "Delete records...")
      Try
         ' read first record
         found = record.First()
 
         While (found)  ' while records are found
            ' delete record
            record.Delete()
            ' read next record
            found = record.Next()
         End While
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub

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, freeing,
   ' disconnecting and logging out of the database
   '
   Sub Done()
 
      Console.WriteLine("DONE")
      Try
         ' close table
         Console.WriteLine(ControlChars.Tab + "Close tables...")
         tableCustMast.Close()
         tableCustOrdr.Close()
         tableOrdrItem.Close()
         tableItemMast.Close()
 
         ' logout
         Console.WriteLine(ControlChars.Tab + "Logout...")
         MySession.Logout()
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub

Record/Row Locking

drivers\vb.nav\tutorials\Tutorial3.vb

Now we will explore row/record locks using the FairCom DB API .NET Visual Basic NAV Framework.

The functionality for this tutorial focuses on inserting/adding rows/records, then updating a single row/record in the customer master table under locking control. The application will pause after a LOCK is placed on a row/record. Another instance of this application should then be launched, which will block, waiting on the lock held by the first instance. Pressing the <Enter> key will enable the first instance to proceed. This will result in removing the lock thereby allowing the second instance to continue execution. Launching two processes provides a visual demonstration of the effects of locking and a basis for experimentation on your own.

As with all other examples in the c-tree tutorial series, this tutorial simplifies the creation and use of a database into four simple steps: Initialize(), Define(), Manage(), and you’re Done() !

Tutorial #3: Locking

Here we demonstrate the enforcement of data integrity by introducing record/row "locking".

• Initialize() - Connects to the FairCom Database Engine.
• Define() - Defines and creates a "customer master" (custmast) table/file.
• Manage() - Adds a few rows/records, manipulates them, and displays the results.
• Done() - Disconnects from FairCom Database Engine.

Note our simple Main() function:

Imports System
Imports FairCom.CtreeDb
 
Module Tutorial3HL
   Dim MySession As CTSession
   Dim MyTable As CTTable
   Dim MyRecord As CTRecord
 
   '
   ' main()
   '
   ' The main() function implements the concept of "init, define, manage
   ' and you're done..."
   '
   Sub Main()
 
      Initialize()
      Define()
      Manage()
      Done()
 
      Console.WriteLine(ControlChars.NewLine + "Press <ENTER> key to exit . . .")
      Console.ReadLine()
 
   End Sub

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
   '
   Sub Initialize()
 
      Console.WriteLine("INIT")
 
      Try
         ' allocate objects
         MySession = New CTSession(SESSION_TYPE.CTREE_SESSION)
         MyTable = New CTTable(MySession)
         MyRecord = New CTRecord(MyTable)
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
      Try
         ' connect to server
         Console.WriteLine(ControlChars.Tab + "Logon to server...")
         MySession.Logon("FAIRCOMS", "", "")
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub

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()
   '
   ' Open the table, if it exists. Otherwise create and open the table
   '
   Sub Define()
 
      Console.WriteLine("DEFINE")
 
      Create_CustomerMaster_Table()
 
   End Sub

 
 
   '
   ' Create_CustomerMaster_Table()
   '
   ' Open table CustomerMaster, if it exists. Otherwise create it
   ' along with its indexes and open it
   '
   Sub Create_CustomerMaster_Table()
 
      Dim do_create As Boolean = False
      Dim field1 As CTField
      Dim index1 As CTIndex
 
      ' define table CustomerMaster
      Console.WriteLine(ControlChars.Tab + "table CustomerMaster")
      Try
         MyTable.Open("custmast", OPEN_MODE.NORMAL_OPEN)
      Catch
         ' table does not exist
         do_create = True
      End Try
 
      If (do_create) Then
         ' define table fields
         Console.WriteLine(ControlChars.Tab + "Add fields...")
         Try
            field1 = MyTable.AddField("cm_custnumb", FIELD_TYPE.FSTRING, 4)
            MyTable.AddField("cm_custzipc", FIELD_TYPE.FSTRING, 9)
            MyTable.AddField("cm_custstat", FIELD_TYPE.FSTRING, 2)
            MyTable.AddField("cm_custratg", FIELD_TYPE.FSTRING, 1)
            MyTable.AddField("cm_custname", FIELD_TYPE.VSTRING, 47)
            MyTable.AddField("cm_custaddr", FIELD_TYPE.VSTRING, 47)
            MyTable.AddField("cm_custcity", FIELD_TYPE.VSTRING, 47)
 
            ' define index
            index1 = MyTable.AddIndex("cm_custnumb_idx", KEY_TYPE.FIXED_INDEX, False, False)
            index1.AddSegment(field1, SEG_MODE.SCHSEG_SEG)
 
            ' create table
            Console.WriteLine(ControlChars.Tab + "Create table...")
            MyTable.Create("custmast", CREATE_MODE.NORMAL_CREATE)
 
            ' open table
            Console.WriteLine(ControlChars.Tab + "Open table...")
            MyTable.Open("custmast", OPEN_MODE.NORMAL_OPEN)
         Catch E As CTException
            Handle_Exception(E)
         End Try
 
      Else
         Check_Table_Mode(MyTable)
 
         ' confirm the index exists, if not then add the index
         '
         ' Me scenario arises out of the fact that Me table was created in tutorial 1
         ' without indexes. The index is now created by the call to ctdbAlterTable
 
         do_create = False
         Try
            MyTable.GetIndex("cm_custnumb_idx")
         Catch
            do_create = True
         End Try
 
         If (do_create) Then
            Try
               field1 = MyTable.GetField("cm_custnumb")
               index1 = MyTable.AddIndex("cm_custnumb_idx", KEY_TYPE.FIXED_INDEX, False, False)
               index1.AddSegment(field1, SEG_MODE.SCHSEG_SEG)
               MyTable.Alter(ALTER_TABLE.NORMAL)
            Catch E As CTException
               Handle_Exception(E)
            End Try
         End If
      End If
 
   End Sub
 
 
   '
   ' Check_Table_Mode()
   '
   ' Check if existing table has transaction processing flag enabled.
   ' If a table is under transaction processing control, modify the
   ' table mode to disable transaction processing
   '
   Sub Check_Table_Mode(ByVal table As CTTable)
 
      Try
         ' get table create mode
         Dim mode As CREATE_MODE = table.GetCreateMode()
 
         ' check if table is under transaction processing control
         If ((mode And CREATE_MODE.TRNLOG_CREATE) <> 0) Then
            ' change file mode to disable transaction processing
            mode = mode Xor CREATE_MODE.TRNLOG_CREATE
            table.UpdateCreateMode(mode)
         End If
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub

Manage

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

Below is the code for Manage():

   '
   ' Manage()
   '
   ' This function performs record adds and updates using locking
   '
   Sub Manage()
 
      Console.WriteLine("MANAGE")
 
      ' 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()
 
   End Sub

 
 
   '
   ' Add_CustomerMaster_Records()
   '
   ' This function adds records to table CustomerMaster from an
   ' array of strings
   '
   Public Structure CUSTOMER_DATA
      ' struct members
      Public number, zipcode, state, rating, name, address, city As String
      ' struct constructor
      Public Sub New(ByVal number As String, ByVal zipcode As String, ByVal state As String, ByVal rating As String, ByVal name As String, ByVal address As String, ByVal city As String)
         Me.number = number
         Me.zipcode = zipcode
         Me.state = state
         Me.rating = rating
         Me.name = name
         Me.address = address
         Me.city = city
      End Sub
   End Structure
 
   Sub Add_CustomerMaster_Records()
      Dim data(3) As CUSTOMER_DATA
      data(0) = New CUSTOMER_DATA("1000", "92867", "CA", "1", "Bryan Williams", "2999 Regency", "Orange")
      data(1) = New CUSTOMER_DATA("1001", "61434", "CT", "1", "Michael Jordan", "13 Main", "Harford")
      data(2) = New CUSTOMER_DATA("1002", "73677", "GA", "1", "Joshua Brown", "4356 Cambridge", "Atlanta")
      data(3) = New CUSTOMER_DATA("1003", "10034", "MO", "1", "Keyon Dooling", "19771 Park Avenue", "Columbia")
      Dim nRecords As Integer = data.Length
 
      Delete_Records(MyRecord)
 
      Console.WriteLine(ControlChars.Tab + "Add records...")
 
      Try
         Dim i As Integer
         For i = 0 To nRecords - 1
            MyRecord.Clear()
 
            ' populate record buffer with data
            MyRecord.SetFieldAsString(0, data(i).number)
            MyRecord.SetFieldAsString(1, data(i).zipcode)
            MyRecord.SetFieldAsString(2, data(i).state)
            MyRecord.SetFieldAsString(3, data(i).rating)
            MyRecord.SetFieldAsString(4, data(i).name)
            MyRecord.SetFieldAsString(5, data(i).address)
            MyRecord.SetFieldAsString(6, data(i).city)
 
            ' add record
            MyRecord.Write()
         Next
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub
 
 
   '
   ' Delete_Records()
   '
   ' This function deletes all the records in the table
   '
   Sub Delete_Records(ByVal record As CTRecord)
 
      Dim found As Boolean
 
      Console.WriteLine(ControlChars.Tab + "Delete records...")
      Try
         ' enable session-wide lock flag
         MySession.Lock(LOCK_MODE.WRITE_BLOCK_LOCK)
 
         ' read first record
         found = record.First()
 
         While (found)  ' while records are found
            ' delete record
            record.Delete()
            ' read next record
            found = record.Next()
         End While
 
         ' reset session-wide locks
         MySession.Unlock()
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub
 
 
   '
   ' Display_Records()
   '
   ' This function displays the contents of a table. First() and Next()
   ' fetch the record. Then each field is parsed and displayed
   '
   Sub Display_Records()
 
      Dim found As Boolean
      Dim custnumb As String
      Dim custname As String
 
      Console.Write(ControlChars.Tab + "Display records...")
 
      Try
         ' read first record
         found = MyRecord.First()
 
         While (found)
            custnumb = MyRecord.GetFieldAsString(0)
            custname = MyRecord.GetFieldAsString(4)
 
            Console.WriteLine(ControlChars.NewLine + ControlChars.Tab + ControlChars.Tab + "{0,-8}{1,-20}", custnumb, custname)
 
            ' read next record
            found = MyRecord.Next()
         End While
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub
 
 
   '
   ' Update_CustomerMaster_Records()
   '
   ' Update one record under locking control to demonstrate the effects
   ' of locking
   '
   Sub Update_CustomerMaster_Record()
 
      Console.WriteLine(ControlChars.Tab + "Update Record...")
 
      Try
         ' enable session-wide lock flag
         MySession.Lock(LOCK_MODE.WRITE_BLOCK_LOCK)
 
         MyRecord.Clear()
         MyRecord.SetFieldAsString(0, "1003")
         ' find record by customer number
         If (MyRecord.Find(FIND_MODE.EQ)) Then
            MyRecord.SetFieldAsString(4, "KEYON DOOLING")
            ' rewrite record
            MyRecord.Write()
 
            Console.WriteLine(ControlChars.Tab + "Press <ENTER> key to unlock")
            Console.ReadLine()
         End If
 
         ' reset session-wide locks
         MySession.Unlock()
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub

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, freeing,
   ' disconnecting and logging out of the database
   '
   Sub Done()
 
      Console.WriteLine("DONE")
      Try
         ' close table
         Console.WriteLine(ControlChars.Tab + "Close table...")
         MyTable.Close()
 
         ' logout
         Console.WriteLine(ControlChars.Tab + "Logout...")
         MySession.Logout()
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub

Transaction Processing

drivers\vb.nav\tutorials\Tutorial4.vb

Now we will discuss transaction processing as it relates to the FairCom DB API .NET Visual Basic NAV Framework.

Transaction processing provides a safe method by which multiple database operations spread across separate tables/files are guaranteed to be atomic. By atomic, we mean 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.

Like all other examples in the c-tree tutorial series, this tutorial simplifies the creation and use of a database into four simple steps: Initialize(), Define(), Manage(), and You’re Done()!

Tutorial #4: Transaction Processing

Here we demonstrate transaction control.

• 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:

Imports System
Imports FairCom.CtreeDb
 
Module Tutorial4HL
 
   Dim MySession As CTSession
 
   Dim tableCustOrdr As CTTable
   Dim tableOrdrItem As CTTable
   Dim tableItemMast As CTTable
   Dim tableCustMast As CTTable
 
   Dim recordCustOrdr As CTRecord
   Dim recordOrdrItem As CTRecord
   Dim recordItemMast As CTRecord
   Dim recordCustMast As CTRecord
 
   '
   ' main()
   '
   ' The main() function implements the concept of "init, define, manage
   ' and you're done..."
   '
   Sub Main()
 
      Initialize()
      Define()
      Manage()
      Done()
 
      Console.WriteLine(ControlChars.NewLine + "Press <ENTER> key to exit . . .")
      Console.ReadLine()
 
   End Sub

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
   '
   Sub Initialize()
 
      Console.WriteLine("INIT")
 
      Try
         ' allocate the session object
         MySession = New CTSession(SESSION_TYPE.CTREE_SESSION)
 
         ' allocate the table objects
         tableCustOrdr = New CTTable(MySession)
         tableOrdrItem = New CTTable(MySession)
         tableItemMast = New CTTable(MySession)
         tableCustMast = New CTTable(MySession)
 
         ' allocate the record objects
         recordCustOrdr = New CTRecord(tableCustOrdr)
         recordOrdrItem = New CTRecord(tableOrdrItem)
         recordItemMast = New CTRecord(tableItemMast)
         recordCustMast = New CTRecord(tableCustMast)
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
      Try
         ' connect to server
         Console.WriteLine(ControlChars.Tab + "Logon to server...")
         MySession.Logon("FAIRCOMS", "", "")
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub

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()
   '
   ' Open the table, if it exists. Otherwise create and open the table
   '
   Sub Define()
 
      Console.WriteLine("DEFINE")
 
      Create_CustomerMaster_Table()
      Create_CustomerOrders_Table()
      Create_OrderItems_Table()
      Create_ItemMaster_Table()
 
   End Sub

 
 
   '
   ' Create_CustomerMaster_Table()
   '
   ' Open table CustomerMaster, if it exists. Otherwise create it
   ' along with its indexes and open it
   '
   Sub Create_CustomerMaster_Table()
 
      Dim do_create As Boolean = False
      Dim field1 As CTField
      Dim index1 As CTIndex
 
      ' define table CustomerMaster
      Console.WriteLine(ControlChars.Tab + "table CustomerMaster")
      Try
         tableCustMast.Open("custmast", OPEN_MODE.NORMAL_OPEN)
      Catch
         ' table does not exist
         do_create = True
      End Try
 
      If (do_create) Then
         ' define table fields
         Console.WriteLine(ControlChars.Tab + "Add fields...")
         Try
            field1 = tableCustMast.AddField("cm_custnumb", FIELD_TYPE.FSTRING, 4)
            tableCustMast.AddField("cm_custzipc", FIELD_TYPE.FSTRING, 9)
            tableCustMast.AddField("cm_custstat", FIELD_TYPE.FSTRING, 2)
            tableCustMast.AddField("cm_custratg", FIELD_TYPE.FSTRING, 1)
            tableCustMast.AddField("cm_custname", FIELD_TYPE.VSTRING, 47)
            tableCustMast.AddField("cm_custaddr", FIELD_TYPE.VSTRING, 47)
            tableCustMast.AddField("cm_custcity", FIELD_TYPE.VSTRING, 47)
 
            ' define index
            index1 = tableCustMast.AddIndex("cm_custnumb_idx", KEY_TYPE.FIXED_INDEX, False, False)
            index1.AddSegment(field1, SEG_MODE.SCHSEG_SEG)
 
            ' create table
            Console.WriteLine(ControlChars.Tab + "Create table...")
            tableCustMast.Create("custmast", CREATE_MODE.TRNLOG_CREATE)
 
            ' open table
            Console.WriteLine(ControlChars.Tab + "Open table...")
            tableCustMast.Open("custmast", OPEN_MODE.NORMAL_OPEN)
         Catch E As CTException
            Handle_Exception(E)
         End Try
 
      Else
         Check_Table_Mode(tableCustMast)
 
         ' confirm the index exists, if not then add the index
         '
         ' Me scenario arises out of the fact that Me table was created in tutorial 1
         ' without indexes. The index is now created by the call to ctdbAlterTable
 
         do_create = False
         Try
            tableCustMast.GetIndex("cm_custnumb_idx")
         Catch
            do_create = True
         End Try
 
         If (do_create) Then
            Try
               field1 = tableCustMast.GetField("cm_custnumb")
               index1 = tableCustMast.AddIndex("cm_custnumb_idx", KEY_TYPE.FIXED_INDEX, False, False)
               index1.AddSegment(field1, SEG_MODE.SCHSEG_SEG)
               tableCustMast.Alter(ALTER_TABLE.NORMAL)
            Catch E As CTException
               Handle_Exception(E)
            End Try
         End If
      End If
 
   End Sub
 
 
   '
   ' Create_CustomerOrders_Table()
   '
   ' Open table CustomerOrders, if it exists. Otherwise create it
   ' along with its indexes and open it
   '
   Sub Create_CustomerOrders_Table()
 
      Dim do_create As Boolean = False
      Dim field1, field2 As CTField
      Dim index1, index2 As CTIndex
 
      ' define table CustomerOrders
      Console.WriteLine(ControlChars.Tab + "table CustomerOrders")
      Try
         tableCustOrdr.Open("custordr", OPEN_MODE.NORMAL_OPEN)
      Catch
         ' table does not exist
         do_create = True
      End Try
 
      If (do_create) Then
         Try
            ' define table fields
            tableCustOrdr.AddField("co_ordrdate", FIELD_TYPE.DATE, 4)
            tableCustOrdr.AddField("co_promdate", FIELD_TYPE.DATE, 4)
            field1 = tableCustOrdr.AddField("co_ordrnumb", FIELD_TYPE.FSTRING, 6)
            field2 = tableCustOrdr.AddField("co_custnumb", FIELD_TYPE.FSTRING, 4)
 
            ' define indexes
            index1 = tableCustOrdr.AddIndex("co_ordrnumb_idx", KEY_TYPE.LEADING_INDEX, False, False)
            index1.AddSegment(field1, SEG_MODE.SCHSEG_SEG)
            index2 = tableCustOrdr.AddIndex("co_custnumb_idx", KEY_TYPE.LEADING_INDEX, True, False)
            index2.AddSegment(field2, SEG_MODE.SCHSEG_SEG)
 
            ' create table
            Console.WriteLine(ControlChars.Tab + "Create table...")
            tableCustOrdr.Create("custordr", CREATE_MODE.TRNLOG_CREATE)
 
            ' open table
            Console.WriteLine(ControlChars.Tab + "Open table...")
            tableCustOrdr.Open("custordr", OPEN_MODE.NORMAL_OPEN)
         Catch E As CTException
            Handle_Exception(E)
         End Try
      Else
         Check_Table_Mode(tableCustOrdr)
      End If
 
   End Sub
 
 
   '
   ' Create_OrderItems_Table()
   '
   ' Open table OrderItems, if it exists. Otherwise create it
   ' along with its indexes and open it
   '
   Sub Create_OrderItems_Table()
 
      Dim do_create As Boolean = False
      Dim field1, field2, field3 As CTField
      Dim index1, index2 As CTIndex
 
      ' define table OrderItems
      Console.WriteLine(ControlChars.Tab + "table OrderItems")
      Try
         tableOrdrItem.Open("ordritem", OPEN_MODE.NORMAL_OPEN)
      Catch
         ' table does not exist
         do_create = True
      End Try
 
      If (do_create) Then
         Try
            ' define table fields
            field1 = tableOrdrItem.AddField("oi_sequnumb", FIELD_TYPE.INT2, 2)
            tableOrdrItem.AddField("oi_quantity", FIELD_TYPE.INT2, 2)
            field2 = tableOrdrItem.AddField("oi_ordrnumb", FIELD_TYPE.FSTRING, 6)
            field3 = tableOrdrItem.AddField("oi_itemnumb", FIELD_TYPE.FSTRING, 5)
 
            ' define indexes
            index1 = tableOrdrItem.AddIndex("oi_ordrnumb_idx", KEY_TYPE.LEADING_INDEX, False, False)
            index1.AddSegment(field2, SEG_MODE.SCHSEG_SEG)
            index1.AddSegment(field1, SEG_MODE.SCHSEG_SEG)
            index2 = tableOrdrItem.AddIndex("oi_itemnumb_idx", KEY_TYPE.LEADING_INDEX, True, False)
            index2.AddSegment(field3, SEG_MODE.SCHSEG_SEG)
 
            ' create table
            Console.WriteLine(ControlChars.Tab + "Create table...")
            tableOrdrItem.Create("ordritem", CREATE_MODE.TRNLOG_CREATE)
 
            ' open table
            Console.WriteLine(ControlChars.Tab + "Open table...")
            tableOrdrItem.Open("ordritem", OPEN_MODE.NORMAL_OPEN)
         Catch E As CTException
            Handle_Exception(E)
         End Try
      Else
         Check_Table_Mode(tableCustOrdr)
      End If
 
   End Sub
 
 
   '
   ' Create_ItemMaster_Table()
   '
   ' Open table ItemMaster, if it exists. Otherwise create it
   ' along with its indexes and open it
   '
   Sub Create_ItemMaster_Table()
 
      Dim do_create As Boolean = False
      Dim field1 As CTField
      Dim index1 As CTIndex
 
      ' define table ItemMaster
      Console.WriteLine(ControlChars.Tab + "table ItemMaster")
      Try
         tableItemMast.Open("itemmast", OPEN_MODE.NORMAL_OPEN)
      Catch
         ' table does not exist
         do_create = True
      End Try
 
      If (do_create) Then
         Try
            ' define table fields
            tableItemMast.AddField("im_itemwght", FIELD_TYPE.INT4, 4)
            tableItemMast.AddField("im_itempric", FIELD_TYPE.MONEY, 4)
            field1 = tableItemMast.AddField("im_itemnumb", FIELD_TYPE.FSTRING, 5)
            tableItemMast.AddField("im_itemdesc", FIELD_TYPE.VSTRING, 47)
 
            ' define indexes
            index1 = tableItemMast.AddIndex("im_itemnumb_idx", KEY_TYPE.LEADING_INDEX, False, False)
            index1.AddSegment(field1, SEG_MODE.SCHSEG_SEG)
 
            ' create table
            Console.WriteLine(ControlChars.Tab + "Create table...")
            tableItemMast.Create("itemmast", CREATE_MODE.TRNLOG_CREATE)
 
            ' open table
            Console.WriteLine(ControlChars.Tab + "Open table...")
            tableItemMast.Open("itemmast", OPEN_MODE.NORMAL_OPEN)
         Catch E As CTException
            Handle_Exception(E)
         End Try
      Else
         Check_Table_Mode(tableCustOrdr)
      End If
 
   End Sub
 
 
   '
   ' Check_Table_Mode()
   '
   ' Check if existing table has transaction processing flag enabled.
   ' If a table is under transaction processing control, modify the
   ' table mode to disable transaction processing
   '
   Sub Check_Table_Mode(ByVal table As CTTable)
 
      Try
         ' get table create mode
         Dim mode As CREATE_MODE = table.GetCreateMode()
 
         ' check if table is not under transaction processing control
         If ((mode And CREATE_MODE.TRNLOG_CREATE) = 0) Then
            ' change file mode to enable transaction processing
            mode = mode Or CREATE_MODE.TRNLOG_CREATE
            table.UpdateCreateMode(mode)
         End If
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub

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
   '
   Sub Manage()
 
      Console.WriteLine("MANAGE")
 
      ' populate the tables with data
      Add_CustomerMaster_Records()
      Add_ItemMaster_Records()
 
      Add_Transactions()
 
      ' display the orders and their items
      Display_CustomerOrders()
      Display_OrderItems()
 
   End Sub

 
 
   '
   ' Add_CustomerMaster_Records()
   '
   ' This function adds records to table CustomerMaster from an
   ' array of strings
   '
   Public Structure CUSTOMER_DATA
      ' struct members
      Public number, zipcode, state, rating, name, address, city As String
      ' struct constructor
      Public Sub New(ByVal number As String, ByVal zipcode As String, ByVal state As String, ByVal rating As String, ByVal name As String, ByVal address As String, ByVal city As String)
         Me.number = number
         Me.zipcode = zipcode
         Me.state = state
         Me.rating = rating
         Me.name = name
         Me.address = address
         Me.city = city
      End Sub
   End Structure
 
   Sub Add_CustomerMaster_Records()
      Dim data(3) As CUSTOMER_DATA
      data(0) = New CUSTOMER_DATA("1000", "92867", "CA", "1", "Bryan Williams", "2999 Regency", "Orange")
      data(1) = New CUSTOMER_DATA("1001", "61434", "CT", "1", "Michael Jordan", "13 Main", "Harford")
      data(2) = New CUSTOMER_DATA("1002", "73677", "GA", "1", "Joshua Brown", "4356 Cambridge", "Atlanta")
      data(3) = New CUSTOMER_DATA("1003", "10034", "MO", "1", "Keyon Dooling", "19771 Park Avenue", "Columbia")
      Dim nRecords As Integer = data.Length
 
      Delete_Records(recordCustMast)
 
      Console.WriteLine(ControlChars.Tab + "Add records in table CustomerMaster...")
 
      Try
         ' start a transaction
         recordCustMast.Begin()
 
         Dim i As Integer
         For i = 0 To nRecords - 1
            recordCustMast.Clear()
 
            ' populate record buffer with data
            recordCustMast.SetFieldAsString(0, data(i).number)
            recordCustMast.SetFieldAsString(1, data(i).zipcode)
            recordCustMast.SetFieldAsString(2, data(i).state)
            recordCustMast.SetFieldAsString(3, data(i).rating)
            recordCustMast.SetFieldAsString(4, data(i).name)
            recordCustMast.SetFieldAsString(5, data(i).address)
            recordCustMast.SetFieldAsString(6, data(i).city)
 
            ' add record
            recordCustMast.Write()
         Next
 
         ' commit transaction
         recordCustMast.Commit()
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub
 
 
   '
   ' Add_ItemMaster_Records()
   '
   ' This function adds records to table ItemMaster from an
   ' array of strings
   '
   Public Structure ITEM_DATA
      ' struct members
      Public weight As Integer
      Public price As CTMoney
      Public itemnum, description As String
      ' struct constructor
      Public Sub New(ByVal weight As Integer, ByVal price As CTMoney, ByVal itemnum As String, ByVal description As String)
         Me.weight = weight
         Me.price = price
         Me.itemnum = itemnum
         Me.description = description
      End Sub
   End Structure
 
   Sub Add_ItemMaster_Records()
      Dim data(3) As ITEM_DATA
      Data(0) = New ITEM_DATA(10, 1995, "1", "Hammer")
      Data(1) = New ITEM_DATA(3, 999, "2", "Wrench")
      Data(2) = New ITEM_DATA(4, 1659, "3", "Saw")
      Data(3) = New ITEM_DATA(1, 398, "4", "Pliers")
      Dim nRecords As Integer = Data.Length
 
      Delete_Records(recordItemMast)
 
      Console.WriteLine(ControlChars.Tab + "Add records in table ItemMaster...")
 
      Try
         ' start a transaction
         recordItemMast.Begin()
 
         Dim i As Integer
         For i = 0 To nRecords - 1
            recordItemMast.Clear()
 
            ' populate record buffer with data
            recordItemMast.SetFieldAsSigned(0, Data(i).weight)
            recordItemMast.SetFieldAsMoney(1, Data(i).price)
            recordItemMast.SetFieldAsString(2, Data(i).itemnum)
            recordItemMast.SetFieldAsString(3, Data(i).description)
 
            ' add record
            recordItemMast.Write()
         Next
 
         ' commit transaction
         recordItemMast.Commit()
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub
 
 
   '
   ' Delete_Records()
   '
   ' This function deletes all the records in the table
   '
   Sub Delete_Records(ByVal record As CTRecord)
 
      Dim found As Boolean
 
      Console.WriteLine(ControlChars.Tab + "Delete records...")
      Try
         ' write lock required for transaction updates
         record.Lock(LOCK_MODE.WRITE_LOCK)
 
         ' start a transaction
         record.Begin()
 
         ' read first record
         found = record.First()
 
         While (found)  ' while records are found
            ' delete record
            record.Delete()
            ' read next record
            found = record.Next()
         End While
 
         ' commit transaction
         record.Commit()
 
         ' free locks
         record.Unlock()
     Catch E As CTException
         record.Abort()
         Handle_Exception(E)
      End Try
 
   End Sub
 
 
   '
   ' 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.  SavePoints are
   ' established as an order is processed, allowing a transaction to
   ' rollback to the previously verified item
   '
   Public Structure ORDER_DATA
      ' struct members
      Public orderdate, promdate, ordernum, custnum As String
      ' struct constructor
      Public Sub New(ByVal orderdate As String, ByVal promdate As String, ByVal ordernum As String, ByVal custnum As String)
         Me.orderdate = orderdate
         Me.promdate = promdate
         Me.ordernum = ordernum
         Me.custnum = custnum
      End Sub
   End Structure
 
   Public Structure ORDERITEM_DATA
      ' struct members
      Public ordernum As String
      Public seqnumber, quantity As Integer
      Public itemnum As String
      ' struct constructor
      Public Sub New(ByVal ordernum As String, ByVal seqnumber As Integer, ByVal quantity As Integer, ByVal itemnum As String)
         Me.ordernum = ordernum
         Me.seqnumber = seqnumber
         Me.quantity = quantity
         Me.itemnum = itemnum
      End Sub
   End Structure
 
   Sub Add_Transactions()
 
      Dim orders(2) As ORDER_DATA
      orders(0) = New ORDER_DATA("09/01/2002", "09/05/2002", "1", "1001")
      orders(1) = New ORDER_DATA("09/02/2002", "09/06/2002", "2", "9999") ' bad customer number
      orders(2) = New ORDER_DATA("09/22/2002", "09/26/2002", "3", "1003")
      Dim nOrders As Integer = orders.Length
 
      Dim items(5) As ORDERITEM_DATA
      items(0) = New ORDERITEM_DATA("1", 1, 2, "1")
      items(1) = New ORDERITEM_DATA("1", 2, 1, "2")
      items(2) = New ORDERITEM_DATA("2", 1, 1, "3")
      items(3) = New ORDERITEM_DATA("2", 2, 3, "4")
      items(4) = New ORDERITEM_DATA("3", 1, 2, "3")
      items(5) = New ORDERITEM_DATA("3", 2, 2, "99") ' bad item number
      Dim nItems As Integer = items.Length
 
      Dim orderdate As CTDate = New CTDate()
      Dim promdate As CTDate = New CTDate()
      Dim savepoint As Integer
      Dim j As Integer = 0
 
      Delete_Records(recordCustOrdr)
      Delete_Records(recordOrdrItem)
 
      Console.WriteLine(ControlChars.Tab + "Add Transaction Records...")
 
      ' process orders
      Dim i As Integer
      For i = 0 To nOrders - 1
         ' start a transaction
         MySession.Begin()
 
         Try
            recordCustOrdr.Clear()
 
            ' populate record buffer with order data
            orderdate.StringToDate(orders(i).orderdate, DATE_TYPE.MDCY_DATE)
            promdate.StringToDate(orders(i).promdate, DATE_TYPE.MDCY_DATE)
            recordCustOrdr.SetFieldAsDate(0, orderdate)
            recordCustOrdr.SetFieldAsDate(1, promdate)
            recordCustOrdr.SetFieldAsString(2, orders(i).ordernum)
            recordCustOrdr.SetFieldAsString(3, orders(i).custnum)
 
            ' add order record
            recordCustOrdr.Write()
         Catch E As CTException
            Handle_Exception(E)
         End Try
         ' set transaction savepoint
         savepoint = recordCustOrdr.SetSavePoint()
 
         ' process order items
         While (items(j).ordernum = orders(i).ordernum)
            Try
               recordOrdrItem.Clear()
 
               ' populate record buffer with order item data
               recordOrdrItem.SetFieldAsSigned(0, items(j).seqnumber)
               recordOrdrItem.SetFieldAsSigned(1, items(j).quantity)
               recordOrdrItem.SetFieldAsString(2, items(j).ordernum)
               recordOrdrItem.SetFieldAsString(3, items(j).itemnum)
 
               ' add order item record
               recordOrdrItem.Write()
 
               ' check that item exists in ItemMaster table
               recordItemMast.Clear()
               recordItemMast.SetFieldAsString(2, items(j).itemnum)
               If (recordItemMast.Find(FIND_MODE.EQ) <> True) Then
                  ' if not found, restore back to previous savepoint
                  recordItemMast.RestoreSavePoint(savepoint)
               Else
                  ' set transaction savepoint
                  savepoint = recordItemMast.SetSavePoint()
               End If
            Catch E As CTException
               Handle_Exception(E)
            End Try
 
            ' bump to next item
            j += 1
 
            ' exit the while loop on last item
            If (j >= nItems) Then
               Exit While
            End If
         End While
 
         ' check that customer exists in CustomerMaster table
         recordCustMast.Clear()
         recordCustMast.SetFieldAsString(0, orders(i).custnum)
 
         ' commit or abort the transaction
         If (recordCustMast.Find(FIND_MODE.EQ) <> True) Then
            MySession.Abort()
         Else
            MySession.Commit()
         End If
      Next
 
   End Sub
 
 
   '
   ' Display_CustomerOrders()
   '
   ' This function displays the contents of a table. ctdbFirstRecord() and
   ' ctdbNextRecord() fetch the record. Then each field is parsed and displayed
   '
   Sub Display_CustomerOrders()
 
      Dim custnumb As String
      Dim ordrnumb As String
 
      Console.WriteLine(ControlChars.Tab + "CustomerOrder table...")
 
      Try
         ' read first record
         If (recordCustOrdr.First()) Then
            Do
               ordrnumb = recordCustOrdr.GetFieldAsString(2)
               custnumb = recordCustOrdr.GetFieldAsString(3)
 
               ' display data
               Console.WriteLine(ControlChars.Tab + "   {0}   {1}", ordrnumb, custnumb)
 
            Loop While (recordCustOrdr.Next()) ' read next record until end of file
         End If
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub
 
 
   '
   ' Display_OrderItems()
   '
   ' This function displays the contents of a table. ctdbFirstRecord() and
   ' ctdbNextRecord() fetch the record. Then each field is parsed and displayed
   '
   Sub Display_OrderItems()
 
      Dim itemnumb As String
      Dim ordrnumb As String
 
      Console.WriteLine(ControlChars.NewLine + ControlChars.Tab + "OrderItems Table...")
 
      Try
         ' read first record
         If (recordOrdrItem.First()) Then
            Do
               ' get field data from record buffer
               ordrnumb = recordOrdrItem.GetFieldAsString(2)
               itemnumb = recordOrdrItem.GetFieldAsString(3)
 
               ' display data
               Console.WriteLine(ControlChars.Tab + "   {0}   {1}", ordrnumb, itemnumb)
 
            Loop While (recordOrdrItem.Next()) ' read next record until end of file
         End If
      Catch E As CTException
         Handle_Exception(E)
      End Try
 
   End Sub

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, freeing,
   ' disconnecting and logging out of the database
   '
   Sub Done()
 
      Console.WriteLine("DONE")
 
      Try
         ' close table
         Console.WriteLine(ControlChars.Tab + "Close tables...")
         tableCustMast.Close()
         tableCustOrdr.Close()
         tableOrdrItem.Close()
         tableItemMast.Close()
 
         ' logout
         Console.WriteLine(ControlChars.Tab + "Logout...")
         MySession.Logout()
      Catch E As CTException
         Handle_Exception(E)
      End Try
   End Sub

Additional Resources and Functionality

Be sure to see the developer's guide for .NET and Java stored procedures:

• FairCom DB SQL Stored Procedures and Triggers

Diving Deeper into the FairCom DB API "NAV" APIs

The introductory tutorials have offered a glimpse into the ease and flexibility of this interface. The FairCom Database Engine interfaces include a rich array of features for nearly any data management need. The following features can be found in the developers' guides:

• Logging in
• Databases
• Tables
• Fields
• Field Types
• Indexes
• Records
  • Inserting
  • Bulk Inserting
  • Updating
  • Deleting
• Navigating Records
  • Finding
  • Bookmarking
  • Filtering
    • By Query
    • By Partial Key
    • By Index Range
    • By Server-Side Batch Process
• Locking
• Transactions
• Metadata resources

To learn more about this API, see the developers' guides.

Additional APIs

The FairCom Database Engine provides a variety of APIs, such as Low-Level and the FairCom DB API API for C. In fact, the entire FairCom DB API API for C is available directly as function calls within the object-oriented FairCom DB API API for C#.

To learn more about FairCom APIs, see FairCom Database Engine Interfaces.