Statements that define the schema (DDL)

xfODBC supports the following SQL statements that define the schema:

For links to more information on xfODBC's support for SQL, see Appendix B: SQL Support.

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. Views represent data from tables, have columns, and otherwise appear as tables, but they are not the actual database tables, and they do not have their own data. You can use views to present table information in different ways and to enable users to view data without having access to the base tables (the actual database tables). You can use joins to include multiple views or tables, and in some cases views can be updated — i.e., they can be used to modify underlying data (see Inserting, updating, and deleting values in views below).

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 (see The column list below).

sel_stmnt is a SELECT statement.

A view is an object that is treated as a table, but whose definition contains a query (a valid SELECT statement). Because the query may access more than one table, a view can combine data from several tables.

Note the following:

Views can be based on tables or other views, which can in turn be based on other views or tables, as long as these relationships are not circular. Tables or views directly referenced in the query for a view are the simply underlying tables. These tables, and any tables or views they reference (all the way down to the base tables), are the generally underlying tables. And the base tables (which do not reference any other tables, but actually contain the data) are the leaf underlying tables. No view can be one of its own generally underlying tables.

Once you've created a view, you can use it by referencing it in an SQL statement just as you would an actual database table (e.g., SELECT * FROM my_view). When you use a view in a statement, the output of the query is drawn from the data that the view represents. In cases where views can be updated, the changes are made to the underlying data in the base tables (see Inserting, updating, and deleting values in views below).

The column list

Column names are generally optional in a CREATE VIEW statement, 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, they must all have different names, and the number of column names must match the number of columns returned by the SELECT statement for the view. We recommend using all uppercase characters for view_col names because some products, such as Microsoft Office, remove quotation marks needed to preserve case.

Inserting, updating, and deleting values in views

When you insert, update, or delete a value in a view, the change is made to the underlying data if the following are true:

For a view to be updatable,

Additionally, if the SELECT statement has a subquery, the view will be updatable only if the subquery does not reference any of the generally underlying tables that the view is based on.

Examples

The following example creates a view named contacts with three columns (Company, Contact, and Phone) created from three columns in the customers table.

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

This example shows how the view created in the previous example can be used in queries:

SELECT phone FROM contacts
    WHERE company='Victorian Gardens'

This CREATE VIEW statement 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}

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.