ODBC Driver CREATE TABLE Features and Usage

Beginning 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:

SQL Data Types	PRO/5 Data Types
CHARACTER	C
CHAR	C
CHARACTER VARYING	C** (Variable length character field padded with null characters).
CHAR VARYING	C** (Variable length character field padded with null characters).
VARCHAR	C** (Variable length character field padded with null characters).
LONG VARCHAR	C** (Variable length character field padded with null characters).
FLOAT	F
DOUBLE	Y
REAL	B
BYTE	I(1)
SMALLINT	I(2)
INTEGER	I(4)
UNSIGNED BYTE	U(1)
UNSIGNED SMALLINT	U(2)
UNSIGNED INTEGER	U(4)
DATE	If Date Type set in data source setup, then C(OEM date size) else I(4) for Julian. OEM Dates that cannot be recognized will be treated as NULLs.
TIMESTAMP	C(26) equals a TIMESTAMP Typedef. Data is stored in ASCII format, YYYY-MM-DD hh:mm:ss:ffffff format (the Y=Year, M=Month, D=Day, h=hour, m=minute, s=second, and "f"=fraction of a second).

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.

CREATE INDEX will attempt to add an index to an existing multi-keyed file. The contents of the file will be preserved. If the UNIQUE qualifier is used, all other indexes will be dropped prior to adding the new unique index.

Timestamp

The TIMESTAMP data type records a unique instance in time. For additional information, see the Time and Date Functions.

Suppose the transaction events were to be recorded in a history file. The history file would look like this:

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.

The new NOW() function can be used to write data to the HISTORY file. The NOW() function returns a timestamp that represents the current time.

Insert into history values (now(), 22, 89, 10098, 56.67)

The NOW() function returns a unique string, ideal for a primary key.

There are many functions that act on timestamp values, including: YEAR(), MONTH(), DAYOFWEEK(), QUARTER(), DAYNAME(), DAYOFMONTH(), DAYOFYEAR(), MONTHNAME(), HOUR(), MINUTE(), and SECOND(). All of these functions but HOUR(), MINUTE(), and SECOND() may also be applied to OEM or Julian dates. The power that is available from these functions can be easily seen when used in conjunction with the timestamp data type.

In addition to the NOW() function, the BASIS ODBC Driver supports the CURDATE() function and the CURTIME() function. CURDATE() returns a Julian number that represents the current date. CURTIME() returns the current time formatted in the same way as the time portion of a timestamp string (hh:mm:ss:ffffff). The scalar functions HOUR(), MINUTE(), and SECOND() may take CURTIME() as an argument.

Time and Date Constants

It 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.

Time constants follow the hh:mm:ss:ffffff format and are more relaxed than date constants. For example, a complete date constant does not need to be supplied for the time functions to understand it:

Select emp_name from timeclock where HOUR(checkin) = hour('04:11')

______________________________________________________________________________________