Statements that define the schema (DDL)

xfODBC supports the following SQL statements that define the schema:

CREATE INDEX

The CREATE INDEX command creates an index for a specified table. 

CREATE [UNIQUE] INDEX index_name ON table_name
    (column_name [ASC|DESC][, column_name [ASC|DESC]]...)

where

index_name is the name of the index that will be created. UNIQUE specifies that no two rows of the index can have the same value.

table_name is the name of the table that the index will be created for.

column_name is the name of the column to create the index on. ASC | DESC specifies the sort direction for the column (ascending or descending).

For example:

CREATE INDEX my_key1 ON public.orders (or_vendor DESC)

CREATE INDEX my_key1 ON public.orders (or_vendor, or_item)

Note the following:

CREATE SYNONYM

The CREATE SYNONYM command creates a synonym, which is an alternate name for a table or view. 

CREATE SYNONYM [owner_name.]synonym_name FOR 
    [owner_name.]object_name

where

owner_name is the name of the schema that will contain the synonym. If you don’t specify owner_name, the synonym is created in your default schema.

synonym_name is the name of the synonym you are creating.

object_name is the name of the object (table or view) that the synonym will be created for.

Note the following:

SELECT * FROM orders
CREATE SYNONYM newname FOR public.orders

this statement will result in an error:

SELECT * FROM orders

CREATE TABLE

The CREATE TABLE command creates a table and its columns. It creates ISAM files for the table and adds table information to the system catalog. 

CREATE TABLE [owner.]table_name 
    (column_definition [, column_definition]...)

where table_name is the name of the table to be created, and column_definition is the following:

column_name data_type [NOT NULL]

Data_type must be one of the following. Note that these SQL data types are not related to Synergy DBL types; instead they are the SQL ODBC data types we support within ODBC only. (The only data types that can be mapped directly in a Synergy application are char/varchar type to alpha, smallint to i2, and int to i4. Other than these, none of the data types can be directly used in non-SQL Connection Synergy applications.)

CREATE TABLE Data Types

Data_type

Size,

Described as…

char[ (n) ]

n (default is 1, maximum is 4000)

SQL_VARCHAR

date

10 (YYYY-MM-DD)

SQL_TYPE_TIMESTAMP

datetime

19 (YYYY-MM-DD HH:MI:SS)

SQL_TYPE_TIMESTAMP

decimal[ (p [,s] ) ]

p is precision (default is 10, maximum is 28)

s is scale (default is 0, maximum is 28)

SQL_DECIMAL

double

16 (equivalent to decimal(16,6))

SQL_FLOAT

integer

10

SQL_INTEGER

number[ (p [,s] ) ]

p is precision (default is 10, maximum is 28)

s is scale (default is 0, maximum is 28)

SQL_DECIMAL

numeric[ (p [,s] ) ]

p is precision (default is 10, maximum is 28)

s is scale (default is 0, maximum is 28)

SQL_DECIMAL

real

8 (equivalent to decimal(8,6))

SQL_DECIMAL

smallint

5

SQL_SMALLINT

time

8 (HH:MI:SS)

SQL_TYPE_TIMESTAMP

timestamp

19 (YYYY-MM-DD HH:MI:SS)

SQL_TYPE_TIMESTAMP

varchar[ (n) ]

n (default is 1, maximum is 4000)

SQL_VARCHAR

NOT NULL prevents a column from being updated with null values and values that xfODBC considers null. See Preventing null updates and interpreting spaces, zeros, and null values.

For example:

CREATE TABLE mytable (col_1 integer NOT NULL, col_2 char(10), 
    col_3 decimal(4), col_4 decimal(5,2))

Note the following:

CREATE VIEW

The CREATE VIEW command creates a logical view of one or more tables or one or more views. 

CREATE VIEW [user_name.]view_name (view_col [, ...]) 
     AS sel_stmnt

where

user_name is the table owner name.

view_name is the name of the resulting view.

view_col is the name of the column in the view. Column names are generally optional, but they are required if more than one column in the resulting view has the same name (usually because of a join) or if a column is derived from an arithmetic expression, function, or constant value. Column names may also be assigned in the SELECT statement by assigning correlation names to the columns. Note that if you do name columns, you must name them all, and they must all have different names. We recommend using all uppercase characters for view_col names because some products, such as Microsoft Office, remove quotation marks needed to preserve case.

sel_stmnt is a SELECT statement.

Views contain data from tables, have columns, and otherwise appear as tables, but they’re not the actual database tables. You can use views to present table information in different ways and to enable users to view data without having access to the actual database tables. You can use joins to include multiple views or tables.

For example:

CREATE VIEW contacts (Company, Contact, Phone)
    AS SELECT cust_name, cust_contact, cust_phone
    FROM customers

You can then use the view_col names in queries—for example:

SELECT phone FROM contacts
    WHERE company='Victorian Gardens'

The following CREATE VIEW example uses a join:

CREATE VIEW cust_orders AS
    SELECT orders.or_item, orders.or_number, customers.cust_name
    FROM {OJ public.orders LEFT OUTER JOIN public.customers
    ON orders.or_customer = customers.cust_key}

You cannot use a view created for multiple tables to update, insert, or delete rows.

Do not use column aliases in a CREATE VIEW statement. To specify column names for a view, include them in the list of view columns, which is (view_col [, ...]) in the following:

CREATE VIEW [user_name.]view_name (view_col [, ...]) AS sel_stmnt

DROP SYNONYM

The DROP SYNONYM command deletes a synonym. 

DROP SYNONYM [owner_name.]synonym_name

where owner_name is the name of the schema that contains the synonym, and synonym_name is the name of the synonym you want to delete.

DROP TABLE

The DROP TABLE command removes a table. 

DROP TABLE [owner_name.]table_name

where owner_name is the name of the schema that contains the table, and table_name is the name of the table or view you want to delete.

DROP VIEW

The DROP VIEW command deletes a view. 

DROP VIEW [owner_name.]view_name

where owner_name is the name of the schema that contains the view, and view_name is the name of the view you want to delete.

Note the DROP TABLE command can also drop views.