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:
Property | Data type | Description |
---|---|---|
Connection | Connection | Returns the parent Connection object of a table. |
Name | String | Returns the name of a DB table. |
PrimaryKey | String[ ] | 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. |
System | Boolean | Returns True if the table is a system table, i.e. a table used by the database server (internally). |
Type | String | Returns or sets the type of a DB table. This property is only used for MySQL DB tables. Types include MyISAM, InnoDB or BDB. |
Indexes | Table.Indexes | Returns 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. |
Fields | Table.Fields | Returns an overview (data type Table.Fields) of the fields of a DB table. |
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.
Method | Return type | Description |
---|---|---|
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 ) | Boolean | Delivers 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 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.
Method | Return type | Description |
---|---|---|
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 ) | Boolean | Returns 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 22.4.6.6.1 : Methods of the Table.Indexes class