StudioTips - To Do - SQL Scripts

SQL Scripts

This section covers some basic SQL Scripts to help you get started with creating and modifying server tables in your database.

NOTE: The SQL Assist Utility included with the subscriber version of StudioTips can help to automatically generate CREATE TABLE, ALTER TABLE, DROP TABLE, and the add index SQL Scripts for you based on existing schema classes.

Alter table

Be sure to read CREATE TABLE before you read this tip.

If you need to add column(s) to an existing table, you will need to use the ALTER TABLE statement. The following is a simple example.

ALTER TABLE book ADD (
Description VARCHAR (500),
Type VARCHAR (10)
);

Capitalization is not necessary. You can't add NOT NULL columns to a table after there is data in the table.

Create index

To create an index in OmnisSQL you need to send a CREATE INDEX SQL statement.

The following are some examples:

CREATE CASE SENSITIVE UNIQUE INDEX author_Key ON author (author_Key)
CREATE CASE SENSITIVE INDEX AuthorName ON author (AuthorName)

Capitalization is not necessary.

If you are using the Omnis data file, always create CASE SENSITIVE indexes.

The NON case sensitive indexes are useless because you end up scanning all the records anyway with OmnisSQL. (Alas, Omnis SQL is not a full featured SQL backend, never was, never is, never meant to be. It works fine for testing and for small databases. At the time of writing this I am into my second datafile using about 400 MB with 10 users and quite content with performance.)

Create table

Once you've logged onto the database and started a session, you will need to create table(s). Be careful not to confuse server tables with table classes. I wish Omnis had picked a different name for table class. Though I admit I haven't thought of a better name yet.

In DML we talk about files and field names. In SQL we call them tables and columns.

To create a table in and SQL database you need to send a CREATE TABLE statement. Here is a simple example:

CREATE TABLE author (
author_Key VARCHAR (50) NOT NULL,
EditNum INTEGER,
ModDateTime TIMESTAMP NOT NULL,
ModBy VARCHAR (6) NOT NULL,
AuthorName VARCHAR (50) NOT NULL
);

Capitalization is not necessary. You can only create NOT NULL columns on empty tables. You can't add NOT NULL columns to a table after there is data in the table.

Drop index

To drop an index in SQL you need to send a DROP INDEX SQL statement. The following is an example:

DROP INDEX AuthorName;

Easy eh!?

Drop table

To drop a table in SQL you need to send a DROP TABLE SQL statement. The following is an example:

DROP TABLE author;

Easy eh!? But be careful, there's no reversing this one.