SQL — CREATE TABLE: Features and Usage

CREATE TABLE provides a mechanism for creating a new table in the database using SQL. In addition to standard syntax supported by SQL, BBj and PRO/5 support some additional options that are relevant to BBj and PRO/5 development. The syntax for CREATE TABLE in PRO/5 and BBj is as follows:

CREATE TABLE table_name ['filename'] (col_name1 type/size [constraint], ... , [constraints])

BBj-Specific Information

In addition to the syntax above, BBj supports the following syntax to specify the type of file to be created:

CREATE TABLE table_name ['filename'] (col_name1 type/size [constraint], ... , [constraints]) [filetype]

Valid file types are MKEYED, MKEYED_R (Mkeyed 64bit Highly Recoverable), XKEYED, XKEYED_R (Xkeyed Highly Recoverable), VKEYED, and ESQL.

BBj 18.0 and Higher

BBj also supports creating a table as an encrypted data file by supplying both the file type and a MODE string:

CREATE TABLE table_name ['filename'] (col_name1 type/size [constraint], ... , [constraints]) [filetype [, MODE = 'mode string']]

The MODE string should contain the same mode information that would normally be used to create or open an encrypted file, see the documentation for the MKEYED, XKEYED, or VKEYED verb. Creating an encrypted table will only work with those data file types.

Optionally Specify a Specific Physical File Name and Location

CREATE TABLE has the ability to specify a physical file name 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))

Data Type Mapping From SQL to PRO/5 and BBj Data Types

When performing a CREATE TABLE, the following SQL data types map to the corresponding PRO/5 and BBj string data types (Note: this table does NOT apply to ESQL tables since ESQL tables do not use the string template paradigm and data types for storage):

SQL Data Type	PRO/5 Data Type
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
DECIMAL (P,S)	N (P) Note: S (scale) is ignored except for BBj ESQL files.
NUMERIC (P,S)	N (P) Note: S (scale) is ignored except for BBj ESQL files.
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).
BINARY(BBj)	C It will also add a flag (0x08) to the data dictionary LOCAT.1 file in the FLAGS field to tell the SQL engine to treat the data as binary rather than character data.
VARBINARY (BBj)	O Type O is valid only in BBj.
LONGVARBINARY (BBj)	O Type O is valid only in BBj

 

CREATE TABLE Examples

Create a simple table using the default data file name and file type:

CREATE TABLE person

 (person_id CHAR(10) PRIMARY KEY, name VARCHAR(50))

Create a simple table using the default data file name but create a VKEYED file:

CREATE TABLE person

 (person_id CHAR(10) PRIMARY KEY, name VARCHAR(50)) VKEYED

Create a simple table using a specific data file name and make it an ESQL file that does not allow the NAME column to be NULL:

CREATE TABLE person '(DATA)person_table.dat'

 (person_id CHAR(10) PRIMARY KEY, name VARCHAR(50) NOT NULL ) ESQL