ODBC and JDBC Guide

Chapter 7 | Supported standards 43
In this case, the salary is changed to the average salary in the company for the employee having
employee ID E10001.
Note In container fields, you can UPDATE with text only, unless you prepare a parameterized
statement and stream the data from your application. To use binary data, you must specify the
type in a PutAs() function: PutAs(col,
'type'), where the type value is a type as described
in
“Retrieving the contents of a container field: CAST() function and GetAs() function” on page 39.
CREATE TABLE statement
Use the CREATE TABLE statement to create a table in a database file. The format of the CREATE
TABLE statement is:
CREATE TABLE table_name ( table_element_list [, table_element_list...] )
Within the statement, you specify the name and data type of each column.
1 table_name is the name of the table. table_name has a 100 character limit. A table with the
same name must not already be defined.
1 The format for table_element_list is:
field_name field_type [DEFAULT expr][UNIQUE][NOT NULL] [EXTERNAL
relative_path_string [SECURE | OPEN calc_path_string]]
1 field_name is the name of the field. No field in the same table may have the same name.
You specify a field repetition by using a number in square brackets. For example:
lastDates[4].
1 field_type may be any of the following: NUMERIC, DECIMAL, INT, DATE, TIME,
TIMESTAMP, VARCHAR, CHARACTER VARYING, BLOB, VARBINARY, LONGVARBINARY, or
BINARY VARYING. For NUMERIC and DECIMAL, you can specify the precision and scale.
For example: DECIMAL(10,0). For TIME and TIMESTAMP, you can specify the precision.
For example: TIMESTAMP(6). For VARCHAR and CHARACTER VARYING, you can specify
the length of the string. For example: VARCHAR(255).
1 The DEFAULT keyword allows you to set a default value for a column. For expr, you may
use a constant value or expression. Allowable expressions are USER, USERNAME,
CURRENT_USER, CURRENT_DATE, CURDATE, CURRENT_TIME, CURTIME,
CURRENT_TIMESTAMP, and CURTIMESTAMP.
1 Defining a column to be UNIQUE automatically selects the Unique Validation Option for the
corresponding field in the FileMaker database file.
1 Defining a column to be NOT NULL automatically selects the Not Empty Validation Option for
the corresponding field in the FileMaker database file. The field is flagged as a Required
Value in the Fields tab of the Manage Database dialog box in FileMaker
Pro.
1 To define a column as a container field, use BLOB, VARBINARY, or BINARY VARYING for
the field_type.
1 To define a column as a container field that stores data externally, use the EXTERNAL
keyword. The relative_path_string defines the folder where the data is stored
externally, relative to the location of the FileMaker database. This path must be specified as
the base directory in the FileMaker
Pro Manage Containers dialog box. You must specify
either SECURE for secure storage or OPEN for open storage. If you are using open storage,
the calc_path_string is the folder inside the relative_path_string folder where
container objects are to be stored. calc_path_string can be a FileMaker calculation.