SQL Scripts

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. Server tables are the tables in the database. Table classes are a special class in you Omnis Studio library. I wish Omnis had picked a different name for table class... though I admit I haven't thought of a better name.

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

CREATE TABLE author (
author_pkey 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.

Calculate SQLText as "CREATE TABLE temp (temp_pkey INT NOT NULL, TempName VARCHAR(25) NOT NULL)"
Do StmntObj.$execdirect(SQLText) Returns FlagOK
If not(FlagOK)
   
   Do StmntObj.$nativeerrortext() Returns ErrorText
   OK message SQL Error (Icon) {$nativeerrortext = [StmntObj.$nativeerrortext]////when executing the SQL Script:////[SQLText]}
   
Else
   
   OK message (Icon) {The $execdirect ran successfully for the SQL script:////[SQLText]}
   
End If
Quit method FlagOK

Create index

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

CREATE [UNIQUE] INDEX IndexName ON tablename (ColName)

For a unique index, you can create a composite index by using a comma separated string of column names instead of a single column name.

The following are some examples of SQL text for creating indexes.

CREATE UNIQUE INDEX author_pkey ON author (author_pkey)
CREATE INDEX AuthorName ON Author (AuthorName)

Capitalization is not necessary.

Calculate SQLText as "CREATE UNIQUE INDEX temp_pkey ON temp (temp_pkey)"
Do StmntObj.$execdirect(SQLText) Returns FlagOK
If not(FlagOK)
   
   Do StmntObj.$nativeerrortext() Returns ErrorText
   OK message SQL Error (Icon) {$nativeerrortext = [StmntObj.$nativeerrortext]////when executing the SQL Script:////[SQLText]}
   
Else
   
   OK message (Icon) {The $execdirect ran successfully for the SQL script:////[SQLText]}
   
End If
Quit method FlagOK

If you are using the Omnis data file you should specify case-sensitive indexes. Case-insensitive indexes are useless with the Omnis data file because you end up scanning all the records. (Alas, Omnis SQL is not a full featured SQL backend, never was, never is, never meant to be.) To create case sensitive indexes for the Omnis data file use the syntax:


CREATE CASE SENSITIVE UNIQUE INDEX author_pkey ON Author (author_pkey)
CREATE CASE SENSITIVE INDEX AuthorName ON Author (AuthorName)


Do not use this syntax for real RDBMSs! Indexes in real RDBMSs are usually case insensitive by default. Some RDBMSs require that you specify a case-insensitive collation on the column in order for the index to be case-insensitive.

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 (BookType VARCHAR(10));

Capitalization is not necessary.

Calculate SQLText as "ALTER TABLE temp ADD (TempType VARCHAR(25))"
Do StmntObj.$execdirect(SQLText) Returns FlagOK
If not(FlagOK)
   
   Do StmntObj.$nativeerrortext() Returns ErrorText
   OK message SQL Error (Icon) {$nativeerrortext = [StmntObj.$nativeerrortext]////when executing the SQL Script:////[SQLText]}
   
Else
   
   OK message (Icon) {The $execdirect ran successfully for the SQL script:////[SQLText]}
   
End If
Quit method FlagOK

You can't add NOT NULL columns to a table after there is data in the table. You must first add the column, then update the NULL value columns to a non-null value, then alter the column to NOT NULL.

Drop table

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

DROP TABLE author;

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

Calculate SQLText as "DROP TABLE temp"
Do StmntObj.$execdirect(SQLText) Returns FlagOK
If not(FlagOK)
   
   Do StmntObj.$nativeerrortext() Returns ErrorText
   OK message SQL Error (Icon) {$nativeerrortext = [StmntObj.$nativeerrortext]////when executing the SQL Script:////[SQLText]}
   
Else
   
   OK message (Icon) {The $execdirect ran successfully for the SQL script:////[SQLText]}
   
End If
Quit method FlagOK

Drop index

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

DROP INDEX IndexName;

StudioTips Documentation - Copyright 2006 Vencor Software