Method
Database_Class.FieldSchema
Warning
This item was deprecated in version 2019r2. Please use Database.TableColumns as a replacement.
Description
Returns a RowSet with information about all the columns (fields) in the specified TableName. These are the typical columns returned in the RecordSet, although they may be different depending on the database being used: ColumnName As String, FieldType As Integer, IsPrimary As Boolean, NotNull As Boolean and Length As Integer.
Notes
Recordset
FieldSchema typically 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).
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. |
Integer |
3 |
A numeric data type with no fractional part. |
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. |
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. |
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. |
TimeStamp |
10 |
Stores both date and time information in the format YYYY-MM-DD HH:MM:SS. |
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. |
Binary |
14 |
Stores code, images, and hexadecimal data. |
Long Text (Blob) |
15 |
Stores a text object. |
Long VarBinary |
Blob) |
16 |
MacPICT |
17 |
Stores a Macintosh PICT image. |
String |
18 |
Text up to about 2 billion bytes. The same as VarChar. |
Int64 |
19 |
Stores a 64-bit integer. |
Unknown |
255 |
Unrecognized data type. |
Sample code
The following code creates a table and then gets the FieldSchema:
Dim db As SQLiteDatabase
Dim dbFile As FolderItem
Dim result As Boolean
dbFile = New FolderItem("mydb.sqlite")
db = New SQLiteDatabase
db.DatabaseFile = dbFile
result = db.CreateDatabaseFile
If db.Connect Then
db.SQLExecute("CREATE TABLE invoices(id INTEGER, CustID INTEGER, Amount Double, Note TEXT)")
db.Commit
Dim rs As RecordSet
rs = db.FieldSchema("invoices")
Dim colName As String
colName = rs.IdxField(1).StringValue
MsgBox("Column: " + colName)
Else
MsgBox("Database error: " + db.ErrorMessage)
End If
Compatibility
All project types on all supported operating systems.