GAMBAS-BOOK 3.19.5

The Table class represents, among other things, the definition of a database table. You can use the methods of the Table.Fields class to define fields in a DB table and the methods of the Table.Indexes class to add an index to a DB table.

The Table class has these properties:

Table 22.4.6.1.1 : Properties of the Table class

Note:
Due to the uniqueness of the primary key, its value must never be undefined (even NULL is not permitted) and its value may only occur once. If a primary key is transferred to another table as an additional attribute, it is called a foreign key. It can be used once per table and may be defined for one or more columns. The primary key is also the most important index in a table.

Example

  Dim iCount As Integer, vElement As Variant, hDBTable As Table
 
  hDBTable = DBCS.DBConnection.Tables["contacts"]
 
  Print "DBTableName: "; hDBTable.Name
  Print "DBName: "; hDBTable.Connection.Name
  Print "DBPrimaryKey: "; IIf(hDBTable.PrimaryKey.Count = 0, "No primary key defined.", "There is a primary key.")
  For Each vElement In hDBTable.PrimaryKey
      Print "Primary key field "; iCount; ": "; vElement
      Inc iCount
  Next
  Print "DBSystem: "; IIf(hDBTable.System, "Table is a DBSystem table", "Table is not a DBSystem table.")
  Print "DBTabellentyp: "; IIf(hDBTable.Type, hDBTable.Type, "Table is not a MySQL table - no type defined.")

This is the output in the IDE console:

DBTableName: contacts
DBName: contacts.sqlite
DBPrimaryKey:There is a primary key.
Primary key field 1: id
DBSystem: Table is not a DBSystem table.
DBTabellentyp: Table is not a MySQL table - no type defined.

The Table class only has one method: Sub Update( ). It creates a DB table in the current database whose fields, primary key and table type - only for MySQL - have been defined beforehand. A table index on one or more fields is created separately.

    Dim hDBTable As Table
    Dim hIndex As Index
    Dim sTableIndexName As String = "index_lastname_members"
    Dim iCount As Integer = 1
 
 
    If Not DBCS.DBConnection.Tables.Exist("members") Then 
 
   '-- Create DB table object - Table name: `members`
       hDBTable = DBCS.DBConnection.Tables.Add("members")
 
   '-- Define DB fields
       With hDBTable.Fields
         .Add("m_id", db.Serial) ' gb.Serial => INTEGER PRIMARY KEY AUTOINCREMENT
         .Add("lastname", db.String)
         .Add("date", db.Date)
         .Add("description", db.String)
       End With
 
   '-- Define primary key » Field name: `m_id`
       hDBTable.PrimaryKey = ["m_id"]
 
   '-- Create DB table `members` in the database `contacts.sqlite
       hDBTable.Update()
 
   '-- Define DBIndex » Indexname: `index_lastname_members`, Fieldname: `lastname`
       If Not hDBTable.Indexes.Exist("index_lastname_members") Then 
          hDBTable.Indexes.Add("index_lastname_members", ["lastname"])
       Endif
 
    Else    
   '-- The existing DB table `members` becomes the current DB table
       hDBTable = DBCS.DBConnection.Tables["members"]
 
  Endif

The class only has one (readable) property: Table.Fields.Count of data type Integer. It returns the number of fields in an (existing) DB table.

You can use the methods of the Table.Fields class to define new fields in a DB table. This class behaves like a read-only array and can be enumerated with the keyword FOR EACH.

Table 22.4.6.4.1 : Methods of the Table.Fields class

The class only has one property: Property Read Table.Indexes.Count As Integer. It returns the number of indexed fields in a DB table.

You can add an index to the fields of a DB table using the methods of the Table.Indexes class. The Table.Indexes class has four methods.

Table 22.4.6.6.1 : Methods of the Table.Indexes class