ODBC Driver CREATE TABLE Features and UsageBeginning in Rev. 2.0 of the ODBC Driver, CREATE TABLE has the ability to specify a physical file name that is created and associated with the logical table name: create table foo 'c:/mydata/cust87.rgg' (f1 char(2), f2 char(2)) It is also possible to specify global values in the path: create table foo '(DATA)cust87.(COMPANY)' (f1 char(2), f2 char(2)) When performing a CREATE TABLE, the following SQL data types map to the corresponding PRO/5 data types:
If a table is created without any table constraints (i.e., UNIQUE, PRIMARY KEY, or FOREIGN KEY) clauses, the physical file that is generated will be a multi-keyed file. The primary key consists of the first 10 fields. TimestampThe TIMESTAMP data type records a unique instance in time. For additional information, see the Time and Date Functions. create table history (timeoftxn timestamp, bank_id integer, teller_id integer, account_id integer, amount real) The HISTORY.TIMEOFTXN field is a timestamp. A timestamp is stored internally as a 26-byte string. This string contains ASCII text and is formatted as follows: YYYY-MM-DD hh:mm:ss:ffffff Where Y=year, M=Month, D=Day, H=hour (in 24 hour format), m=minute, s=second, and f=fractional seconds. When a timestamp is specified in a CREATE TABLE, the BASIS ODBC Driver creates a BASIS Data Dictionary Typedef called TIMESTAMP. Any field in the BASIS Data Dictionary that is of type TIMESTAMP will be considered a timestamp by the BASIS ODBC Driver. Insert into history values (now(), 22, 89, 10098, 56.67) The NOW() function returns a unique string, ideal for a primary key. Time and Date ConstantsIt is possible to represent time and date values as constants. Date constants may be represented as strings in the YYYY-MM-DD format: Select DAYOFWEEK('1963-09-30') from customer Date constants can be represented as a standard ODBC date clause. For example: {d'1996-09-30'} Date constants must be complete and must contain valid year, month, and day information in the correct positions within the string. Select emp_name from timeclock where HOUR(checkin) = hour('04:11') ______________________________________________________________________________________ Copyright BASIS International Ltd. BBj®, Visual PRO/5®, PRO/5®, and BBx® are registered trademarks. |