Database.TableColumns

From Xojo Documentation

You are currently browsing the old Xojo documentation site. Please visit the new Xojo documentation site!
Method

Database.TableColumns(TableName as String) As RowSet

New in 2019r2

Supported for all project types and targets.

Returns a RowSet with information about all the columns (fields) in the specified TableName.

Notes

A DatabaseException will be raised if the an error occurs.

RowSet

TableColumns returns a RowSet with these columns:

  • ColumnName: A string that is the name of the column.
  • FieldType: An integer that describes the type of the column (refer to the table below).
  • IsPrimary: A boolean that indicates if the column is part of the primary key.
  • NotNull: A boolean that indicates if the column can be set to NULL.
  • Length: An integer that describes the length of the field (for some text fields), the precision of numeric data (for some numeric fields) or the default value for a field (for SQLite columns).
  • cid: (SQLite Only) The ID of the column.

ODBC Databases may return different columns depending upon the database to which you are connecting.

Data Types

Use the table below to identify the Column Type based on the FieldType Integer (Not all databases use all these types).

FieldType Value Description
Null 0 Denotes the absence of any value, i.e., a missing value.
Byte 1 Stores the byte representation of a character string.
SmallInt 2 A numeric data type with no fractional part.

The maximum number of digits is implementation-specific, but is usually less than or equal to INTEGER. SQLite supports 4-byte smallints. If you are using another data source, check the documentation of your data source.

Integer 3 A numeric data type with no fractional part.

The maximum number of digits is implementation-specific. SQLite supports 8-byte integer columns and the FieldType evaluates to 19 (64-bit integer).

Char 4 Stores alphabetic data, in which you specify the maximum number of characters for the field, i.e., CHAR (20) for a 20 character field.

If a record contains fewer than the maximum number of characters for the field, the remaining characters will be padded with blanks.

Text or VarChar 5 Stores alphabetic data, in which the number of characters vary from record to record, but you don't want to pad the unused characters with blanks.

For example, "VARCHAR (20)" specifies a VARCHAR field with a maximum length of 20 characters.

Float 6 Stores floating-point numeric values with a precision that you specify, i.e., FLOAT (5).
Double 7 Stores double-precision floating-point numbers.
Date 8 Stores year, month, and day values of a date in the format YYYY-MM-DD. The year value is four digits; the month and day values are two digits.
Time 9 Stores hour, minute, and second values of a time in the format HH:MM:SS.

The hours and minutes are two digits. The seconds values is also two digits, may include a optional fractional part, e.g., 09:55:25.248. The default length of the fractional part is zero.

TimeStamp 10 Stores both date and time information in the format YYYY-MM-DD HH:MM:SS.

The lengths of the components of a TimeStamp are the same as for Time and Date, except that the default length of the fractional part of the time component is six digits rather than zero. If a TimeStamp values has no fractional component, then its length is 19 digits If it has a fractional component, its length is 20 digits, plus the length of the fractional component.

Currency 11 This is a 64-bit fixed-point number format that holds 15 digits to the left of the decimal point and 4 digits to the right.
Boolean 12 Stores the values of TRUE or FALSE.
Decimal 13 Stores a numeric value that can have both an integral and fractional part.

You specify the total number of digits and the number of digits to the right of the decimal place, i.e., DECIMAL (5.2) specifies a decimal field that can contain values up to 999.99. DECIMAL (5) specifies a field that can contain values up to 99,999.

Binary 14 Stores code, images, and hexadecimal data.

Consult the documentation of your data source for information on the maximum size of a Binary field.

Long Text (Blob) 15 Stores a text object.

Consult the documentation of your data source for information on the maximum size of a Blob.

Long VarBinary

(Blob)

16 Stores a binary object.

SQLite supports blobs of up to any size. Furthermore, a blob can be stored in a column of any declared data affinity. If you are using another data source, check the documentation of your data source.

MacPICT 17 Stores a Macintosh PICT image.

SQLite does not support this data type. Use a Blob to store images.

String 18 Text up to about 2 billion bytes. The same as VarChar.
Int64 19 Stores a 64-bit integer.

Integer fields in SQLite are 64 bits and FieldType returns 19.

Unknown 255 Unrecognized data type.

Sample Code

The following code creates a table and then display each column name one at a time:

Var db As SQLiteDatabase
Var dbFile As FolderItem

dbFile = New FolderItem("mydb.sqlite")
If dbFile.Exists Then
dbFile.Remove
End If
db = New SQLiteDatabase
db.DatabaseFile = dbFile

Try
db.CreateDatabase
db.ExecuteSQL("CREATE TABLE Invoices (ID INTEGER, CustID INTEGER, Amount Double, Note TEXT)")

Var columns As RowSet = db.TableColumns("Invoices")

For Each c As DatabaseRow In columns
MessageBox("Column: " + c.Column("ColumnName").StringValue)
Next
Catch error As IOException
MessageBox("The database could not be created: " + error.Message)
Catch error As DatabaseException
MessageBox("Database error: " + error.Message)
End Try

See Also

Database.Tables