User Tools

Site Tools




22.4.6 Classes Table, Table.Fields and Table.Indexes (gb.db)

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. Table properties

The Table class has these properties:

PropertyData typeDescription
ConnectionConnectionReturns the parent Connection object of a table.
NameStringReturns the name of a DB table.
PrimaryKeyString[ ]Returns the primary key of a DB table as a string array or specifies the primary key. This primary key is a string array with the names of the individual primary key fields.
SystemBooleanReturns True if the table is a system table, i.e. a table used by the database server (internally).
TypeStringReturns or sets the type of a DB table. This property is only used for MySQL DB tables. Types include MyISAM, InnoDB or BDB.
IndexesTable.IndexesReturns an overview (data type .Table.Indexes) of the indexes of a DB table. The primary key is by far the most important index in a table.
FieldsTable.FieldsReturns an overview (data type Table.Fields) of the fields of a DB table.

Table : Properties of the Table class

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.


  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
  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. Method Table

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
   '-- Define DBIndex » Indexname: `index_lastname_members`, Fieldname: `lastname`
       If Not hDBTable.Indexes.Exist("index_lastname_members") Then 
          hDBTable.Indexes.Add("index_lastname_members", ["lastname"])
   '-- The existing DB table `members` becomes the current DB table
       hDBTable = DBCS.DBConnection.Tables["members"]
  Endif Table.Fields properties

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. Methods Table.Fields

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.

MethodReturn typeDescription
Table.Fields.Add ( Name As String, Type As Integer [ , Length As Integer, Default As Variant ] )-Inserts a new field into a DB table. `Name` is the name of the field. `Type` is its data type. The possible field data types are: db.Blob (-2), db.Boolean (1), db.Date (8), db.Float (7), db.Integer (4), db.Long (5), db.Serial (-1) and db.String (9). Note on `db.Serial`: This constant stands for a serial field data type. A serial field is a field whose value is unique and is automatically incremented when each new data record is created (auto-increment). `Length` is the maximum length for text fields. `Default` is another optional parameter that corresponds to the default value of the underlying field type. If it is not specified, the default value is 0.
Table.Fields.Exist ( Key As String )BooleanDelivers True if the specified field exists in the DB table.
Table.Fields.Refresh ( )-Refreshes the field overview (data type Table.Fields) of a DB table by deleting the internal cache.

Table : Methods of the Table.Fields class Table.Indexes properties

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

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.

MethodReturn typeDescription
Sub Table.Indexes.Add ( Name As String, Fields As String[] [ , Unique As Boolean ] )-Inserts a new index into an existing table. `Name` is the name of the index. `Fields` is a string array. The optional parameter `Unique` indicates whether this index must be unique or not. By default, the index is not unique.
Table.Indexes.Exist ( Key As String )BooleanReturns True if the specified index exists in the DB table, otherwise False.
Table.Indexes.Refresh ( )-Refreshes the index overview (data type Table.Indexes) of a DB table by deleting the internal cache.
Table.Indexes.Remove ( Name As String )-Deletes the specified index in a DB table.

Table : Methods of the Table.Indexes class

The website uses a temporary session cookie. This technically necessary cookie is deleted when the browser is closed. You can find information on cookies in our privacy policy.
k22/k22.4/k22.4.6/start.txt · Last modified: 27.04.2024 by emma

Page Tools