SQL

SQL is an acronym for Structured Query Language.

My only experience in programming is Omnis. I started with Omnis Classic using DML.

Herb Payerl, the person who introduced me to Omnis, advised me that SQL was the way to go, so when I bought Omnis Studio, I decided to switch to using SQL as well. I had ZERO experience in SQL. Learning Studio and SQL at the same time just about did me in. There were times I thought I was making 2 big mistakes - Studio and SQL. But everywhere I listened and read, SQL was the way to go, so I kept at it. Once I got the hang of using SQL I learned to love it. Now I'm a convert ... SQL is the way to go, and Studio works wonderfully with it!

NOTE: The advice I give is from a limited scope. At the time of writing this I have mainly used the Omnis data file with OmnisSQL. I have not used stored procedures. My SQL statements are very simple. I am sure more experienced SQL programmers would look at my SQL code and suggest other ways of writing it.

SQL vs DML

Opinions abound and I welcome anyone's input. The following are some reasons why I think you should drop DML and use SQL in Studio. My DML memories are fading, so forgive (and correct) me if I make any incorrect statements about DML.

1. DML is not conducive to object-oriented programming (OOP). With Object-Oriented Programming you don't want the data inside your objects to be affected by other the actions of objects and methods. The current record buffer (CRB) used by DML is global and can be a headache for Object-Oriented Programming.

2. With DML you always had to be conscious of the CRB and be sure you have all the connected records in the CRB when you do update (or you connections get messed up). In SQL there is no CRB, no prepare for update, no connections going on in the background.

3. With DML if you deleted a field from a file class, these #??? would show up all over the place. Quite a job to clean up. In DML if you want to added a new field while you are coding, you must first go to the file class, enter it, then continue with your code. In SQL you can write the code with the new fields, and then go and added them to the file.

4. DML is usually written 'modal'. You have an 'Enter Data' state and the user must Ok or Cancel. When I switched to Studio, going to 'modeless' data entry made it a lot easier to code.

5. DML only works with the Omnis data files. With SQL you can move your application to high powered SQL backends.

Books to read

See the "Books" section in StudioTips.

A great resource for learning SQL is Jim Pistrang's 'StudioDoesSQL.LBS' demo library available at Omnis-Dev. I sat through Jim's Studio does SQL session at Euromnis 2000 and highly recommend it.

Multi-threaded DAMs

The multi-threaded DAMs are important to the Omnis web clients. With the web client setup, a single instance of an Omnis application is running on a server waiting for requests. Multiple web clients can be sending requests over the internet. In a single threaded architecture if webclientA requests a report that takes a minutes to produce, the other clients would have to wait in the queue until the webclientA's report was completed. For the interent that is not acceptable. The multi-threading allows your Omnis application to start serving webclientA, jump to webclientB start serving them, jump to webclientC, back to webclientA, etc.

The multi-threaded DAMs also bring object-oriented style architecture to the DAMs. With the single-thread DAMs the "Set current session" is global, so if you were running multiple sessions or multiple applications at the same time you had to be very careful with your code to make sure the correct session was current. (Fortunately, table classes handle this for us automatically if you set the $sessionname property.)

Sessions and Cursors

Starting with zero SQL experience, the concept of "sessions" and "cursors" was very confusing to me.

Here's my current understanding of sessions and cursors.

SESSIONS

When you logon to a database you begin a "session" with the database. Think of the session like a telephone cable that connects your application to the database. Once the cable (session) is connected you can now have 2 way communication with the database.

You can start multiple sessions with the same database and/or multiple database. Each cable (session) is independent and can have different settings.

One session could be set to automatically commit every transaction, and another session could be set to manual commit, meaning the application could do a batch of updates, and then send a "commit" or "rollback" message to the database server to either commit all the changes or cancel them.

An example of this would be having an autocommit session for getting the next primary key from a table which stores the last used primary key for each table, and a manual commit session for the rest of the database communications.

CURSORS

I struggled for quite a while trying to understand cursors, only to find out that I really didn't need to worry about them. (At least for most applications.)

When you do a $select() to a SQL backend server, the server prepares a list of the records you've selected. Then with a $fetch() you can grab small chunks of the select list records from the server.

If you declare another cursor and do $select(), $fetch() on another table (or maybe even the same table), the server now has both your select lists available to you.

Single Thread vs. Multi-thread DAMs

SINGLE THREAD VS. MULTI-THREAD DAMs

Earlier versions of Omnis Studio used single threaded DAMs (Data Access Modules).

When Omnis Studio version 3 was released it included "multi-threaded DAMs" to support the Omnis web server. The multi-threaded DAMs were nicknamed "V3 DAMs" and the single-threaded DAMs were nicknamed "V2 DAMs".

Maintaining the V2 and V3 DAMs was an extra load on engineering so eventually Omnis announced that the single-threaded V2 DAMs would no longer be updated.

The single-threaded DAMs are being phased out, so you shouldn't even bother with them, unless there is a specific reason you must support them in your application.

The information presented in these sections of StudioTips assumes use of the multi-threaded DAMs.

Transactions & Concurrency

If you are new to SQL, probably one of the most confusing things when you are getting started with a real RDBMS is figuring out this business of transactions and concurrency. For many of us it would be nice if there was just one way to handle transactions and we would just follow that way. But it isn't that simple. There are many different choices, and you need to work through learning and understanding transactions and concurrency in order to figure out what will work best for your situation.

Many people learn in North America learn to drive in a car with an automatic transmission. Then one day they step into a car with a manual transmission. They have more control with the addition of a clutch and stick shift, but they need help figuring out how to use them. Much of the explanations that abound for transactions and concurrency assume we already know how to drive the SQL car. They explain how they work but few give simple examples, and suggest strategies, and recommendations for implementation. Hopefully this section helps you with learning to drive the SQL transactions and concurrency car. :-)

• Transactions are groups of SQL statements that are effectively executed as single units.
• Concurrency refers to mechanisms that the DBMS uses to keep operations on the same data by simultaneous users from interfering with one another. (Thinks of "concurrent users".)

You begin a transaction whenever you initiate a session with a SQL database. All the statements you enter will be part of the same transaction until you complete it by entering either a COMMIT or a ROLLBACK statement.

NOTE: OmnisSQL does not support COMMIT and ROLLBACK, so in OmnisSQL every SQL statement is automatically committed and there is no ROLLBACK option. You can set any RDBMS to autocommit transactions, but I have found there can be a big performance penalty for using autocommit with a real RDBMS.

With the multi-threaded DAMs you control the transaction setting with the $transactionmode property.

Do $sessions.SESSIONNAME.$transactionmode.$assign(kSessionTranAutomatic)

With databases such as FrontBase that support COMMIT and ROLLBACK you should make use of transactions and set the $transacation mode property to kSessionTranManual.

Do $sessions.SESSIONNAME.$transactionmode.$assign(kSessionTranManual)

That seems simple enough, but unfortunately it gets much more complicated than that. (Although the SQL gurus would replace "unfortunately" with "fortunately".)

Depending on your RDBMS there are numerous options to the manual transaction mode. (The information that follows is gleaned from a FrontBase list server posting by Geert Clemmensen of FrontBase.)

The SQL syntax for setting the transaction options is as follows:

SET TRANSACTION [ISOLATION LEVEL < level >] [,LOCKING < locking gt;] [,< access gt;]

Each set of < gt; angle brackets in the above SET TRANSACTION statement has the following options.

<level> can be VERSIONED or SERIALIZABLE or REPEATABLE READ or READ COMMITTED

< locking gt; can be OPTIMISTIC or PESSIMISTIC or DEFERRED

< access gt; can be READ ONLY or READ WRITE

That gives you a lot of combinations! (4 x 3 x 2 = 24 combinations) But life is simpler, experience tells us that only a few combinations really matter.

Note: VERSIONED is specific to FrontBase. It is a read only isolation level.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, LOCKING PESSIMISTIC

• Good for short transactions (read and/or write).
• Required for table definition changes. (eg. ALTER TABLE)
• Has the smallest overhead for write transactions (on lock per table involved).

SET TRANSACTION ISOLATION LEVEL VERSIONED

• Very good for read only transactions.
• Can't take advantage of table caches.
• No locking overhead.
• The database "snapshot" can be shared by as many clients as needed.
• The locking discipline and access mode doesn't matter.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, LOCKING OPTIMISTIC
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, LOCKING PESSIMISTIC

• Maximum concurrency will occur (OPTIMISTIC setting).
• Row-level locking (the most "expensive")
• Good for longer transactions (read and write)
• Transaction conflicts will occur!

SET TRANSACTION ISOLATION LEVEL READ COMMITTED, LOCKING OPTIMISTIC

• This is like REPEATABLE READ, LOCKING OPTIMISTIC.
• Read only transactions will never yield a transaction conflict.

Editor Note: This setting is close to VERSIONED.

Everyone has their own opinions on what is right and wrong for transaction settings, so feel free to make your own choices. The following is what I decided to go with.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED, LOCKING DEFERRRED, READ WRITE;

READ COMMITTED means that each time I select and fetch records, FrontBase automatically commits the transactions, so that the records are not blocked from other users who wish to read and write those records.

LOCKING DEFERRED means that if I read some records and then another user reads the same records, if I update my records, the other user is blocked from updating the same records. (Hope I got that right)

READ WRITE means that I can read and write records to the database, vs. READ ONLY.

For most of the work I do, I don't want to block users from reading the same records at the same time. Doing so, would cause unnecessary blocks and waits when two users want to look at the same record, or run reports on the same records.

I prefer to use an "EditNum" integer column in each of my SQL server tables. The EditNum is incremented +1 each time the record is updated. When the update is requested, I check to make sure the record's EditNum value matches the OldRow.EditNum value. This is accomplished by appending "AND EditNum = OldRow.EditNum" to the SQL update statement.

This methodology is considered optimistic locking as opposed to pessimistic locking which won't allow other users to access records which have been selected by another user and not yet committed.

The transaction settings are session specific. You can change the transaction settings for a session at any time during the session. (Although not for records you've already selected.)

To set the transaction settings you issue the SET TRANSACTION statement after the session has been started.

GOTCHA: For FrontBase you must use $execdirect for SET TRANSACTION. If you use $prepare and $execute your SET TRANSACTION statement will fail. (SQL92 specific reasons for this. This may have been changed in later versions of the FrontBase DAM.)

LOCKING DEFERRED (Explained by Geert B. Clemmensen of FrontBase)

The DEFERRED locking discipline is almost like PESSIMISTIC with the main difference being that when a row/table is referenced, a READ lock is acquired. If the row/table is updated, the READ lock is automatically updated to a WRITE lock. The DEFERRED locking discipline is thus ideal when updates occur less frequently than reads.

THE TWO SESSIONS STRATEGY

Another strategy is to use two sessions.

1. SessionA is used for doing selects for read-only lists and running reports. This session would be VERSIONED or READ COMMITTED, OPTIMISTIC, READ ONLY
2. SessionB is used for modifications and would be SERIALIZEABLE, PESSIMISTIC.

The only way to make transactions 100% secure is to SELECT and UPDATE the data in the same transaction using a PESSIMISTIC locking discipline.

With the 2 session strategy you might fetch a batch of records into a list that is presented to the user. When the use signals a request to edit a record in the list you refetch the record with SessionB and then write and commit it when the user signals to save the changes. SessionB users could could continue to read the record being edited.

Remember: You begin a transaction whenever you initiate a session with a SQL database. All the statements you enter will be part of the same transaction until you complete it by entering either a COMMIT or a ROLLBACK statement.

Note: The caveat with VERSIONED is that table caches aren't used during a VERSIONED transaction. VERSIONED represents history, table caches represent history being written. VERSIONED is in particular ideal for long transactions that only read data, e.g. report writing. READ COMMITTED, OPTIMISTIC, READ ONLY is recommended for connections that only read data.

Session Objects

In order to begin a session with a database you need a "session object".

SQL session objects enable you to log on to an SQL database and then send SQL commands to the server using an SQL statement object. "SessObj" is an object variable that has a subtype of an External Session Object.

In the sample code below we have a local variable "SessionObject", object type. The subtype was set by:

1. Clicking the variable Subtype droplist.
2. Scrolling to "External Objects" at the bottom of the treelist.
3. Opening the "External Objects" node and selecting the "OMSQLSESS" object.

You can dynamically instantiate a session object the same way you dynamically bind an object type variable to an object class on the fly. For example:

Do $root.$extobjects.OMSQLDAM.$objects.OMSQLSESS.$new() Returns SessObj

This accomplishes the same thing as steps 1-3 above.

To find out what external objects are available to your copy of Omnis Studio:
1. F4 Notation Inspector > expand $extobjects node
2. Look for names that sound end with "DAM". (OMSQLDAM, FRONTBASEDAM, etc.)
3. Expand the "...DAM" node of your choice. (I wasn't swearing, honest!)
4. Expand the $objects node and notice the object name that ends with "SESS", this is the session object.

You add external session objects to the "xcomp" folder located in your Omnis Studio application folder.

After downloading and expanding the FrontBase DAM for Omnis Studio, I simply dragged file named "FrontBase DAM (Mac OS X)" into my "xcomp" folder, then quit and restarted Omnis Studio. Once that was done the FRONTBASEDAM was present in the $extobjects group of the F4 Notation Inspector.

$clear

$coltext

$commit, $rollback, $begin

Commit and rollback will only work if the RDBMS you are using supports transactions and you have the session object $transactionmode set to kSessionTranManual.

The session object property $allowtransactions tells you whether or not the RDBMS you are connected to supports transactions.

NOTE: The Omnis data file does not support commit and rollback.

$commit()

SessObj.$commit() commits the current transaction.
Should only be used if SessObj.$transactionmode=kSessionTranManual

$rollback()

SessObj.$commit() rolls back the current transaction.
Should only be used if SessObj.$transactionmode=kSessionTranManual

$begin()

SessObj.$begin() starts a new transaction. Should only be used if SessObj.$transactionmode=kSessionTranManual and SessObj.$autobegintran=kFalse

$createnames

$insertnames

SessObj.$insertnames(list or row variable) returns a text string which is a comma delimited list of all column names and values from the list or row variable columns that can be used in an INSERT statement.

$logoff

When you are finished with a session you will need to logoff. Logging off a database is real easy.

$logon

One of the first things you need to do is logon to the database. Once you are logged on you can communicate with the database.

$logon()
SessObj.$logon([cHostName][,cUserName][,cPassword][,cSessionName]) logs session object SessObj on to the database

If you successfully logon, your session will show up in the SQL Browser (Tools menu > select SQL Browser...). Your session will also show up in the F4 Notation Inspector > $sessions group.

$makeschema

$newstatement

SessObj.$newstatement(cStatementName) creates a new statement object with name cStatementName. The resulting statement object can be used to send commands to the database and process results.

NOTE: If you use table classes you don't need to worry about this method. The table class's built in methods automatically create new statement objects for you.

$nextnativeerror

$rpcdefine

SessObj.$rpcdefine(cProcedureName,list variable) defines a remote procedure for later use in a call to StatementObj.$rpc();

cProcedureName: is the case-sensitive name of the remote procedure in the database
list variable: defines the parameters and the return value of the remote procedure

$selectnames

SessObj.$selectnames(list or row variable[,cTableName]) returns a text string which is a comma delimited list of all column names from the list or row variable columns that can be used in a SELECT statement

If specified, cTableName is used to prefix all column names (i.e. cTableName.ColumnName).

$updatenames

SessObj.$updatenames(list or row variable) returns a text string which is a comma delimited list of all column names and new values from the list or row variable columns that can be used in an UPDATE statement.

$wherenames

SessObj.$wherenames(list or row variable[,cTableName] [,cComparison][,cOperator]) returns a text string which is a comma delimited list of all column names and current column values from the list or row variable columns, that can be used in a WHERE clause.

If specified, cTableName prefixes all column names (i.e. cTableName.ColumnName)

cComparison is the comparison operator used (default "=")

SQL Session Object Methods

If you right-click on a session object variable > select Interface Manager, you will see a list of the available session object methods. (In OO talk these are the "messages" which you "send" to the session object. You can drag and drop any of these methods into a "Do" statement in your code. Click "Run Demo" to see a session object's methods via the Interface Manager.

SQL Session Object Properties

The session object has both methods and properties. The Interface Manager has a "Properties" tab which lists all of the properties of the session object.

All properties will return the value of their current state. Some of the properties can be assigned. (e.g. $transactionmode could be changed from automatic to manual... provided the RDBMS supports it.)

The sample code below lists all the session object properites and descriptions.

Statement Objects

SQL statement objects enable you to send SQL commands to the server and process results.

StatementObj is an object variable that has a subtype initially empty but contains the value returned from executing the session object $newstatement method to generate a statement object called "MySql"

Do SessObj.$newstatement('MySql') Returns StatementObj

STATEMENT OBJECT METHODS

The various statement object methods are listed in this section.

$clear

$execdirect

StatementObj.$execdirect([cSQLText]) prepares and executes the SQL text cSQLText. If the cSQLText parameter is not specified then the SQL script of the current method stack is used (defined using the Begin statement, Sta:, and End statement commands.) The SQL statement is executed immediately.

$execute

$fetch

StatementObj.$fetch([row or list variable] [,iRowCount=1] [,bAppend=kFalse]) fetches one or more rows into the row or list variable.

iRowCount is the maximum number of rows to be fetched.

bAppend=kTrue indicates that the fetched rows are to be appended to the existing rows in the list; otherwise, $fetch() empties the list before fetching the rows.

@BUG:v3, Statement object $fetch spoils $cinst inside a table class

The $fetch doesn't hurt the list variable itself, but it spoils $cinst (which should work as a reference to the list) for the
remainder of the method which contains the $fetch.

@BUGWORKAROUND: What you can do to work around it is set a local var Item Ref (say lvMyRef) to $cinst.$ref before the $fetch. Then for the remainder of the table class method use lvMyRef. (e.g. lvMyRef.$line, lvMyRef.$sort)

This bug is supposed to be fixed in Studio v4.0.

$indexes

StatementObj.$indexes(cTableName[,Type-constant]) generates a result set describing the indexes of the specified table, containing columns for DatabaseOrCatalog, Owner, ColumnName, IndexName, Unique, ColumnPosition.

Parameter cTableName may take the form [Database.][Owner.]Name depending on whether database and owner qualifiers are supported.

$nextnativeerror

$prepare

$results

$rpc, $rpcprocedures, $rpcparameters

Many RDBM systems allow you to create stored procedures on the server. The $rpc methods allow you to call those stored procedures. There are 2 schools of thought when it comes to stored procedures.

1. Put all your business rules in stored procedures in the RDBMS so that no matter what software (Omnis, Java, etc.) is hitting the database the business rules will be enforced. Stored procedures are faster.

2. Don't use stored procedures because it makes your application dependent on the RDBMS, thereby making it impossible to switch to another RDBMS.

Whether or not you use stored procedures depends on your individual situation. As of the time of writing this I have not yet used remote (stored) procedures.

$rpc()

StatementObj.$rpc(cProcedureName,[Param1,]É[ParamN]) calls the remote procedure cProcedureName that must have previously been defined using SessObj.$rpcdefine() passing any parameters Param1É ParamN required.

$rpcprocedures()

StatementObj.$rpcprocedures([cOwner]) generates a result set describing remote procedures available on the server containing DatabaseOrCatalog,Owner,ProcedureName.

If cOwner is an empty string, omitted or not supported then this call returns all available procedures in all available databases.

Otherwise cOwner takes the form [Database.]Owner.

If only Owner is specified, and this form is supported by the object, returns all available procedures owned by Owner in all available databases.

If Database.Owner is specified, and this form is supported by the object, returns all available procedures owned by Owner in Database.

$rpcparameters()

$tables

StatementObj.$tables([Type-constant][,cOwner]) generates a result set containing a list of the tables or views on the server, containing columns for Owner, TableOrViewName, TableType, and Description.

The Type-constant indicates the type(s) of the objects to return:
kStatementServerAll: tables and views (default)
kStatementServerTable: tables only
kStatementServerView: views only

If specified, cOwner is used to restrict the returned tables to those owned by a particular user.

Session Pools

At the time of writing this tip, to the best of my knowledge, session pools are only included with the Omnis Web Development license. The pricing structure has been changing so session pools might become standard equipment for the developer version in future.

(The following information has been copied from the F1 Help.)

Pools of session instances can be created when Omnis starts up and made available to any methods running within Omnis. They are designed to be used by the multi-threaded server and allow client methods to quickly obtain SQL sessions without the burden of constructing their own instances.

There is a notation group $root.$sessionpools that contains the current session pools. Normally the session pools are created by the startup task and exist until Omnis is shut down.

When a session is required in order to perform a SQL query, it is obtained from the pool using an object variable and a statement object created in the normal way in order to execute the query and fetch any results. When the session object variable is destroyed the session instance is returned to the pool for later reuse.

SESSION POOL NOTATION

The group $sessionpools has the usual $findname, $first, $next and $makelist notation. The $remove() method is also implemented but not $add() since $makepool() is used to create a session pool.

A pool has the $name, $poolsize and $inuse properties. The $poolsize property is the number of instances stored in the pool and $inuse is the number of these which are currently assigned out. If you increase the value of $poolsize the new session instances are immediately constructed and if the hostname, username and password parameters were specified at $makepool, they are also logged on.

$makepool

Session pools are created using the $makepool() method. The call to $makepool() will only be successful if used with an external object that is a session object or an object class that has a session object as its superclass. The $makepool() method returns a reference to the pool if it is successfully created and the required number of session instances are constructed, otherwise it returns NULL. Once a session pool has been created there is no need to maintain it further during the time that the library is open, however it is possible to change the number of available instances using notation.

NOTE: Pooled sessions do not appear in the SQL Object Browser!

USING $makepool() WITH AN EXTERNAL OBJECT

Create a session pool using an external object with a method call of the form

Do $extobjects.DAMobject.$objects.SessObject.$makepool(pName, pSize,pHostName,pUserName,pPassword) Returns #F

DAMobject is the name of the external component. You can find out what DAM objects are available on your workstation by examining the $root.$extobjects branch of the notation tree using the Notation Inspector. SessObject is the name of the session object. You can find out what session object is available by expanding the $objects group for a particular DAM object using the Notation Inspector. The pName parameter is the name of the pool and must be unique amongst session pools and the pSize parameter is the number of object instances to be initially contained in the pool. The other three parameters are optional and if specified are passed to the $logon() method for the instance, if they are not specified the instance is constructed but not logged on.

USING $makepool() WITH AN OBJECT CLASS

Alternatively $makepool() may be used with an object class with a method call of the form

Do $objects.ObjectClass.$makepool(pName,pSize,pUserName,pPassword) Returns #F

Obtain Session Instance

The $new() method is used to assign a session instance from a pool. For example

Calculate SessObj as $sessionpools.poolone.$new()

If $new() is successful the session instance assigned from the pool belongs to SessObj and is normally returned to the pool when SessObj is destroyed (for example, if SessObj is a local variable the session is returned to the pool when the method containing SessObj terminates). Alternatively the session can be manually returned to the pool by assigning some other object or zero to SessObj. The $new method returns NULL if all instances contained in the pool have already been assigned out.

Remove Session Pool

SQL Scripts

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

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)
);

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.

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
);

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;

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;

SQL Classes

File classes

The "File Class" is only used if you are working with the Omnis data file... and even then use of file classes is optional. The advantage of using file classes if you are using the Omnis data file is that you can check and reorganize the data file if you have file classes which are CORRECTLY mapped to your data file slots.

USING FILE CLASSES FOR STORING SQL TABLE INFORMATION

I read a posting by Reg Paling where he mentioned he was storing his SQL table information in the file classes. After testing the idea I found it worked very well.

If you are using the Omnis data file, there are added advantages in that you can change a field name or field name definition and then reorganize the Omnis data file, something you can't do with using OmnisSQL.

Using notation you can generate the CREATE TABLE and CREATE INDEX statements from the file classes. Using notation you can also get the tables and indexes from a SQL server and create the File Classes.

A WORD OF CAUTION: Omnis maps the file classes to the Omnis data file by row number in the file classes NOT field name. NEVER try to move the position of a field in the File Class if you are using the Omnis Data file. Your data will not be mapped correctly.

Do NOT leave empty rows in your file classes, they can give you grief later on. For those of us who like our field names list in a logically grouped and sorted order, forget it when it comes to SQL server tables. Just keep adding new fields to the end of the list. Do NOT reorder the fields in the File Classes.

The schema classes ARE mapped by name, so if you want to change the order of the column names in the schema classes that is no problem. Just drag the columns to whatever order you like. The F9 Catalog window will reflect the order in the schema class. But remember, the next time you 'Create a Schema from the Server' your nicely reordered list will be history.

Query classes

Query classes are used to join tables/table columns together. See Joining Tables. You can also use them for subsets of schema class columns, where the query class has less columns than the schema class.

I tend to use query classes for reports and for importing data from other data files.

The Omnis data file doesn't support "SQL Views", so query classes help to fill the void. Even if you are using a real RDBMS the query classes can be quite handy.

For example if I wanted to run a report for all the books in the books database and include the author name in the rerport I would need to join the "book" table and the "author" table. To do this with a query class I would create a query class named "qBooksAuthors" and drag and drop the columns from from the "s_book" schema class and from the "s_author" schema class in the F9 Catalog to the "qBooksAuthors".

Each query class has a multi-line entry field for text to be appended to queries. I would enter the following extra query text:

WHERE author.author_Key = book.author_Key

When we bind a list variable to the query class and issue a $select, Omnis Studio will automatically include the extra query text in the select statement.

Do List.$definefromsqlclass('qBooksAuthors')
Do List.$select()
Do List.$fetch(kFetchAll)

The select statement issued by Omnis Studio will look something like:

SELECT (query class column names) FROM author,book WHERE author.author_Key = book.author_Key

Schema classes

For every table in the database (server table), you must have a matching schema class. The schema class maps your Omnis Studio application to the server table.

You can manually type up your schema classes (the hard way). Using the SQL Browser you can drag and drop a server table onto your library (the easy way). You can using notation to create your schema classes (see $makeschema).

IMPORTANT: Make sure you have the primary key column in your schema class is set to kTrue. When I was learning Studio/SQL I couldn't figure out why I was able to $insert records, but not $update them. Eventually I discovered it was because I had not set primarykey=kTrue in my schema class. Studio needs to know the primary key column in order for the built in $update method to work.

FOREIGN KEYS

Unfortunately the schema classes are not set up to store information about foreign keys. If you wish to generate 'CREATE TABLE' statements from your Studio application you need to store this information somewhere. A work around which I use is to add @TAGs to the schema column descriptions.

For example, the "book" server table has a column "author_Key" which is the foreign key to the "author" server table's primary key column "author_Key". In my application I would have a schema class called "s_book" which has its $servertablname property set to "book". "s_book" would have a column "author_Key" and in the description column you would find the tag "@FK:author.author_Key,R". The tag reads: I am a foreign key column, pointing to the "author_Key" column of the "author" server table and the relationship is "restricted" meaning you can't delete my parent if I exist. The opposite to "restricted" is "cascading". With a "cascading" relationship child records would automatically be deleted with the parent record. The Omnis data file does not support restricted and cascading, you must enforce this with your own table class code.

See SQL Assist for more information on @TAGs and generating CREATE/ALTER TABLE statements from schema classes.

INDEXES

If you wish to generate 'CREATE TABLE' statements from your Studio application you need to store information about the indexs somewhere in your application. A work around which I use is to add @TAGs to the schema column descriptions.

@IX means this is an indexed column.
@UIX means this is a unique index column.

Table Classes

If you are using SQL, then you will have a schema class for each table on your SQL server.

Omnis's schema classes have some built in methods such as: $select, $fetch, $insert, $update, $dowork. You can then use $definefromsqlclass to bind a list or row variable to the schema class and use those built in schema class methods. (From what I understand in the background Omnis binds its own default table class to your row or list variable.)

You can also create your own table class linked to a schema class (or query class) and then $definefromsqlclass to bind a list or row variable to your table class (rather than directly to the schema class). When learning Studio, I thought why bother? That seems like exta work. Then at the Omnis conference in Denver my eyes were opened to the power of creating custom table class methods. WOW! Table classes are powerful!

I recommend you create a matching table class for EVERY schema class. Do ALL your data fetching, checking, inserting, updating, etc. through your table classes. This is the best point of data control. It will take a lot of the 'load' (and code) off your windows and objects.

TABLE CLASSES - BRIEF EXPLANATION

When you $definefromsqlclass a list or row with a table class, Omnis "binds" your table class to the list or row variable.

Do LISTNAME.$definefromsqlclass("[LibName.] TableClassName")

I normally prefix my schema class with "s_" (s_ServerTableName)
and my table classes with 't_' (t_ServerTableName)
Note: ServerTableName is the table name in the database, not to be confused with table class name.

After you bind your table class to the list or row variable using $definefromsqlclass all the public methods (methods starting with $) in the table class are now directly available to your list or row variable.

Do List|Row".$TableMethodName (parameters) Return FlagOK

Think of the table class as an object class specially designed for working between your list or row variable and the server table in the database. If the table class was an object class the above Do statement would have read:

Do oObjectClass.$ObjectMethodName (parameters) Return FlagOK

Plus you would have to send the list or row variable along as a Field Reference parameter. Not so with a list or row bound to a table class.

Another thing I like about the table class is that calculations inside the table class methods do not have the list or row prefix. Outside the table class methods you might do something like:

Calculate $cinst.iList.ColumnName as 10

Inside the table class methods you would simply do:

Calculate $cinst.ColumnName as 10

That's because your list or row and the table class are 'one and the same', they've been "bound" together.

The other great thing about table classes is you can intercept the $update, $insert, $delete, $dowork, and other Omnis hidden table class methods. You do this by simply adding a method by the same name.

In my $insert and $update I first call a table method which I name $checkData. For each table it makes sure the data is acceptable before it can be saved to the database. If $checkData doesn't pass on any column it gives an OK message stating the problem, and Quit method kFalse. Otherwise it returns kTrue and $update then does 'Do default'.

TABLE CLASSES - INHERITANCE USING BASE SUPERCLASSES

You should create a base superclass table class, make it a supercomponent, and then whenever you create a new SQL table/schema, drag the base superclass table class out of the Component Store and then name and point it to your new schema class.

In the base table superclass create your own generic methods.

I have included in StudioTips my base superclass table class for your review. The methods are also listed under their own tab strip heading "Table Classes."

When it comes to SQL there are so many different ways to do things, everyone has their own style and issues that they have to address, so just look at my table class methods for ideas. I do not intend to state that the methods I use are the best way to do things. At the time of writing this my experience and scope is mainly with OmnisSQL.

If you have any table class methods or ideas that you find work very well or could improve on ones that I've listed, I'd love to hear from you. table classes are powerful! They are a very compelling reason for using SQL rather than DML. Use table classes to your fullest advantage.

TABLE CLASSES - $sessionobject

$sessionobject is a table class runtime property. Runtime properties can only be set using $assign on a instance of the table class. The Studio manual defines $sessionname as follows:

A row or list variable defined from a SQL class has the $sessionobject property which is the session object that is used by the table. For a new table instance $sessionobject is initially empty. The $sessionobject may be assigned in the table class $construct method or elsewhere.

So, if you assign the $sessionobject in the $construct of your table class, you never have to worry about using the correct session (database) before reading or writing to the data file. Every time you use the list or row variable bound to that table class instance, Studio will automatically use the $sessionobject for that table class.

SQL Misc

Begin SQL Text

; @MOD:1 Modified method to reopen multiple main window instances if they exist. ;; 2005-09-22 Doug K per Andy HOmnis Studio provide a handy set of Omnis commands for preparing lengthly SQL scripts. The following sample code shows how you can use the SQL script set of Omnis commands.

Begin critical block
Begin SQL script
SQL: SELECT SUM(tsheet_Hours)
SQL: FROM tsheet
SQL: WHERE tsheet_Date >= @[DateFrom]
SQL: AND tsheet_Date <= @[DateTo]
SQL: AND tsheet_emp_Serial = IN ([InClause])
End SQL script
Get SQL script {SQLText}
End critical block

Reading the SQL text code in the above format is much easier than a lengthy con() function and it saves you from having to type all of those double quotes and commas around the characters and variables in the con() function.

Connecting Records

NULLS

There are some people that like NULLS, others dislike them.

NULLS are important for statistics. A NULL value won't affect the average, whereas a ZERO value does affect the average.

Let's say you are doing a survey of households, and a person leaves the question asking for their annual income blank. In the database NULL is better than ZERO because you can calculate a valid average household income on all the records, since the NULLS won't be included in the average. (Zero are included.)
However if you calcluate a A as B*C, and B or C is NULL your result 'A' will be NULL. That can be a real problem when calculating invoices!

So you need to be very careful with NULLS.

Generally I don't want NULL values in my records so in my base superclass table class I have a $setNullsToBlank method which loops through the boolean, number, and integer columns in the current line and sets any NULL values to zero.

SQL Assist

SQL Assist is a utility, currently available with the subscriber version of StudioTips. SQL Assist was written to make it easier for developers (like me) who aren't familiar with SQL syntax. SQL Assist generates SQL scripts for creating and modifying SQL server tables from your schema classes.

There are various features in SQL Assist to help you move from OmnisDML to OmnisSQL.

SQL Browser

I found the SQL Browser to be marginally helpful when I was learning SQL. At times it was counter productive to the learning process. I found the interface is a bit clunky and buggy. (that was v1.2).

I still use the SQL Browser from time to time to look at the server tables.

When I was learning SQL, I used the SQL browser to create my first few SQL tables. But I knew that if I wanted to recreate my tables in another database I would be better off storing my SQL server table columns and indexes information somewhere and write code to prepare the CREATE TABLE statements from that stored information.

I created a parent table called 'sqltable' and a child table called 'sqlcols' and entered by SQL table and related columns in those tables. This worked fine, but there was a catch. The SQL table/column data had to be stored in a separate data file, because if I wanted to start a new data file, where would the SQL table data be? Heaven forbid if I lost the data file with my SQL table definitions.

2 years later I read an Omnis list server posting by Reg Paling where he mentioned he was storing his SQL table information in the file classes. I tested the idea and found that to be a much better way. See the topic 'File Classes' for more information.

SQL Problem Solving

If you are new to SQL (like me) it's easy to hit a problem that baffles you for hours. This section covers a couple of head scratchers that have had me or other stumped. If you have any to add to this list, be sure to email them to me.

SQL ERROR HANDLER

It's a good idea to add a $sqlerror handler method in your base superclass table class.

If you put a "Calculate Row as $cinst" and "Get SQL script {#S1})" followed by a permanent 'Breakpoint' in the $sqlerror handler method you will be able to catch and analyze any SQL errors. See the t_Base table class in StudioTips for the $sqlerror handler method I use. It's not fancy but it helps me out when I hit SQL errors. Feel free to copy and modify it.

YOU CAN INSERT AND DELETE RECORDS BUT CAN'T UPDATE OLD RECORDS

1. Check the Schema class. You MUST have a primary key (Unique field field). And it must be set it to kTrue.
2. Makes sure you are sending the OldRow as a parameter. Row.$update(OldRow)

YOU CAN INSERT ONE RECORD, BUT CAN'T INSERT A SECOND RECORD

1. The second record doesn't have a unique value for field(s) in that table which require unique values.

YOU CAN'T INSESRT ANY RECORDS

1. Are you logged onto the data file?
2. Do you have a session and cursor? And is it the right one?
3. Is one of the columns in the record a NULL value for a column set to No Nulls?

FETCH ONLY BRINGS BACK ONE RECORD TO THE LIST

1. You must specify the number or records to $fetch. Use $fetch(kFetchAll). $fetch() only gets 1 record.
2. $fetch(kFetchAll) only brings back 1 record. If you did a $fetch() before, the list.$linemax will be 1.

FIELD REFERENCE PARAMETERS IN TABLE CLASS METHODS

Passing 'Field Reference' parameters to a table class method can give you problems.

Floris Buyse ran into quite a 'hair puller' when 'Do default' refused to work in the table class method. After looking through the code, I noticed the call to the table class method was:

Do pRow.$update(ivRowOLD) ;; pRow is a field reference parameter
Changing pRow from a field reference parameter to a row parameter solved the problem.

I caught the problem because I too have had odd things happen when I passed field reference type parameters to table class methods.

SELECT FOR UPDATE USING ONE CURSOR

OmnisSQL has no transaction management, ie rollback capability, and I'm using optimistic record locking, only locking records at the last moment to do the update. It made sense to me to lock the parent record at the start with a Select ... For Update, then process all the child records, and finally update the parent record at the end, unlocking with a "Where Current Of.." update statement. For some reason Studio started crashing on both Mac and Windows platforms. I finally realized it was because I was attempting to do this all in one cursor and YOU CAN'T DO THAT. Mac's have a Type 1 error, Windows NT popped up Dr. Watson.

So the fix was to Select ... For Update, change to a new cursor, update the child records, change back to the original cursor and do the Update ... Where Current Of.

Thanks to Michael Houlberg for this tip.

USING SQL RESERVED WORDS FOR COLUMN NAMES

Using SQL Reserved Words for column names will give you troubles. While I was creating an email module I used the words "To" and "From" as column names. The CREATE TABLE statement would not execute. Looking at the back of the Omnis Programming Manual (blue on) I soon found the problem. "To" and "From" are SQL reserved words.

SINGLE OR DOUBLE QUOTE CHARACTER (' OR ") IN SELECT COMPARISON VALUE

Omnis SQL

Using OmnisSQL you can use SQL with the Omnis Data file. Omnis is really storing the data in OmnisDML format. OmnisSQL is simply a SQL interface to the Omnis data file.

Omnis is not in the business of building SQL backends. They are not trying to compete with Oracle DB2, Frontbase and other commercial SQL backends. If you have many records and need lots of speed then you should be looking at 3rd party backends.

At the time of writing these I'd been using OmnisSQL for 2 years with about 10 users accessing the data file. Running Accounting, Payroll, Personnel, Purchasing, Inventory, etc. for a 200 staff operation. To date the speed has been quite acceptable and the data file rock solid reliable.

OMNIS SQL LIMITATIONS

Since OmnisSQL is not a full blown SQL server back end, it has limitations. Listed below are the limitations that others have told me about. I'm not an SQL expert, so some of this information might not be explained 100% correct.

TRANSACTION MANAGEMENT
OmnisSQL does not have transaction management. There is no ROLLBACK or COMMITT.

PRIMARY KEYS
You must generate your own primary keys.

FORIEGN KEYS
OmnisSQL does not support foreign key relationships. OmnisSQL can not enforce restricted or cascading relationships. You must write the code to enforce foreign key relationships.

SUBQUERIES
OmnisSQL does not support subqueries.

SELF JOINS
You can not join a table more than once to another table. To work around this you need to make the first join, then fetch the second parent record and copy its values to the correct columns in the initial join row.

Notation Methods

$definefromsqlclass

Do List|Row.$definefromsqlclass("[LibName.] TableClassName')
Do List|Row.$definefromsqlclass("[LibName.] SchemaClassName')
Do List|Row.$definefromsqlclass("[LibName.] QueryClassName')

The $definefromsqlclass defines your list or row to match the columns in the SQL class.

When you define from a schema class, Omnis binds its default table class to the schema class. The invisible default table class, has the Omnis $select, $fetch, $dowork, etc. methods.

When you define from a table class, you can add your own custom table class methods, and you can intercept the Omnis default table class methods ($select, $fetch, etc.).

You don't have to use table classes, but I strongly recommend you, create a table class for EVERY Schema class in your application. You can also create a table class for a query class.

Table classes are powerful, don't ignore them. Table classes are a very compelling reason for moving from DML to SQL.
You can read up more about table classes under that topic.

$delete

Do Row.$delete()

$delete deletes the row in the datafile.

$delete only works for Row variables.

The row must have been $definefromsqlclass or copied from a list or row which was.

TIP: If you set up a table class method called $delete, you can prevent deletion of critical records (Payroll records, AP Cheque records, etc.). For those tables I have a $delete method in the table class with the following code:

$dowork

Do List.$dowork()

The list must be a smartlist. Smartlists are a terrific feature of Studio. There is a separate topic in StudioTips for smartlists.

Smartlists keep track of list rows that are changed, rows that are added, and rows that are deleted. When you "Do List.$dowork()" Studio rips through the smartlist and carries out the $updates, $inserts, and $deletes for you.

NOTE: You can not make a Row variable into a smartlist.

$fetch

Do Row.$fetch()
Do List.$fetch([NumberOfRecords])

$fetch simply follows $select. $select prepares the list of records matching the select statement. $fetch then grabs the records. You don't have to take all the records at once. You can do a $fetch(10), then another, then another. So far I've always been fetching all the records. I use kFetchAll.

Do List.$fetch(kFetchAll)
... or
Do List.$fetch($ref.$linemax)

If you don't include a parameter or are using a Row variable fetch will only return 1 record at a time. If I recall correctly, in the case of the list, whatever value you $fetch sets the list.$linemax.

kFetchAll = 1,000,000,000 (One billion) $linemax = 1,000,000,000 for a new list.

$insert

Do Row.$insert()

$insert simply inserts the current row data into the data file as a new record. $insert only works with a row, not a list.

$select

Do List|Row.$select('where ColName = Value)

I'm no expert with SQL selects, so if you can get better advice, use it (and e-mail it to me).

I got confused by the syntax of the stuff that goes between the brackets in the $select().

What worked best for me was to calculate my select criteria to a local variable called 'Script', then put that in the brackets. This also give me the chance to look at my select string as I'm stepping through the code to figure why it doesn't work. So the $selects in my code tend to look like this:

Calculate Script as con("where ColName ='",Value,"'")
Do List|Row.$select(Script)
Do List|Row.$fetch(kFetchAll)

Note the single quote ' after the = sign, and between the double quotes after the Value. For SQL you must enclose character and date values in quotes.

% is a wild card, so where ColName ='Kuy%', will select all the names starting with Kuy.

Read up in the manual on the various operators you can use > = < %

$update

Do Row.$update(OldRow)

$update updates the row in the datafile with the new row.

$update only works for row variables.

The row must have been $definefromsqlclass or copied from a list or row which was.

Note: Make CERTAIN the schema class has a primarykey column set to kTrue.

Smartlists

Smartlists are a terrific feature of Studio. You can turn any list into a smartlist by issuing:

Do List.$smartlist.$assign(kTrue)

The first time you do this to a list, Omnis creates a matching History list. The history list tracks all the changes you make to the normal list.

If the smartlist was $definefromsqlclass() you can issue a $dowork() and Studio automatically inserts the new records, updates the changed records, and deletes the deleted records. Smartlists combined with $dowork() can save you a TON of work if you are using SQL... another reason for using SQL instead of DML.

You can also filter smartlists to hide and then show different sets of records.

The documentation on smartlists is pretty good in Studio, so there's no need for me to repeat what has already been said.

Some things that weren't clear to me in the documentation:
1. You can't view the $history list (bummer), you can only imagine and interrogate it. However with StudioTips you can 'view' the history list ... try the History List demo.

2. A row can't be a smartlist. (You can't add or delete rows from a row).

Note: You can use a single row smartlist instead of a row variable (Craig Lewis and others use this trick to make a "smart" row). The thing to watch out for is that when you create the single row list the current line is zero, you must remember to "Do List.$line.$assign(1)" so that your single row will be the current row.

3. If you copy a List which is a smartlist to List2, List2 is also a smartlist with the exact same history list.

4. $dowork calls $dodeletes, then $doupdates, and finally $doinserts. Each plural named method then calls the similarly named but singular method for the approriate rows. (i.e ) $doupdates calls $doupdate for each row.

$dowork SQL Demo

When you send a $dowork message to a smartlist what other $do... methods are invoked, and in what order?

$dowork processes each line of the smartlist which has a status other than kRowUnchanged. Processing is done in the following order: deletes, updates, inserts.

It makes sense to do the deletes first. If there was a unique index constraint conflict between an inserted record and a deleted record, you would want to make sure the kRowDeleted record was removed from the database before doing the insert.

For each kRowDeleted $dowork issues a $dodelete
For each kRowUpdated $dowork issues a $doupdate
For each kRowInserted $dowork issues a $doinsert

$dodelete does NOT issue a $delete
$doupdate does NOT issue a $update
$doinsert does NOT issue a $insert

$dowork does NOT issue $doinserts, $doupdates, or $dodeletes. These plural forms of the $do... methods are only invoked if you call them yourself. They in turn call their respective singular form for each insert, or update, or delete.

If you add a $doupdate method to your table class, be sure to add the parameters:
->pfRow: Field reference variable ->pRowOLD: Row variable.

If you add a $doinsert or $dodelete method to your table class, be sure to add the parameter:
->pfRow: Field reference variable

The "pfRow" field reference variables give you access to the row. If you change any column values in your custom $do... method, the changes will then be reflected in the normal list.

Trying to redirect $dodelete to $delete using pfRow does not work. You can only accomplish this using a "clean $definefromsqlclass" row variable, assigning the column values to it, and then sending the $delete message to the "clean" row variable. This gets a little tricky because you have to properly handle SQL errors and make sure the smartlist history status is properly set. (Use $savelistdeletes or $undolistdeletes)

$dowork with Query Classes

What happens if you send a $dowork message to a smartlist that is based on a query class?

If the query class only includes a single schema class $dowork behaves exactly as it would for a schema class based list or row variable.

If the query class includes more than one schema class, $dowork receives the first message but then Studio doesn't attempt to process the $dowork any further. The SQL Standard, for very logical reasons, doesn't allow you to execute insert, delete, or update on views which include more than one server table. $dowork is following the SQL standard.

It is possible to have a own multi-servertable query class "smartlist" by creating a table class which has its $sqlclassname property pointing to a multi-servertable query class and then adding your own $dowork method. The custom $dowork method would need to manually copy the column values for the individual schema classes and then insert, update, or delete
the rows based on the smartlist row status and whether it was processing a foreign table parent record or a child record.

$filter - using a variable for the criteria

If you want to use a variable for your $filter criteria, do the following:

Calculate SearchString as '$ref.ColumnName>Value'
Do List.$filter(eval(SearchString))

$includelines

Do List.$includelines(kRowInserted+kRowUnchanged+kRowInserted)

Filters

Do List.$filter($ref.ColName=Value)
Do List.$unfilter(1) ;; removes all filters back to the first filter applied
Do List.$unfilter(0) ;; removes all filters, showing all prefiltering lines
Do List.$refilter() ;; Is supposed to reapply all filters. I haven't gotten this one to work yet. (See Demo)

Filters only work on smartlists Filter removes the line(s) from the normal list which don't match the filter criterial, but doesn't change the line status in the history list. You can stack up to 15 filters and recall any of them. Anytime you use $unfilter(0), all the lines will be shown again. I either don't understand $refilter() or it doesn't work. The documentation says it is supposed to reapply all the filters?

WARNING: $search is MUCH faster than $filter. $filter can be SLOW on large lists (1000 lines)

SUGGESTION: ONLY used $filter if you are going to follow with $dowork()or you need the ability to $unfilter(0)

There is a LOT to learn about $smartlist and $filter, read the documentation

I used $smartlists for all my connected child records (ie. Invoice items, PO Items) that way I don't have to keep track of whether a child item was added, deleted, or updated the $smartlist, $history list, and $dowork keep track of this and handle it without my intervention.

WARNING about History List the Line# of a record in the SMARTLIST is NOT NECESSARILY the same Line# in the HISTORYLIST!!!

History List

The moment you "Do List.$smartlist.$assign(kTrue), a history list is created which tracks each line in the normal list. The history list contains all the lines in the normal (visible) list, plus any filtered or deleted lines. The history list keeps track of the status for each line. You can access the information in the history list using $history.LineNumber to reference the specified line.

CAUTION: The line number in the history list does NOT necessarily match the line number in the normal list. If any lines were deleted from the normal list, they are still in the history list. To find the status of a line in the smartlist, you must $search for the same line in the $history list. The following is an example how find the matching line in the history list.

-----
Do List.$history.$makelist($ref.$rownumber) Returns HistoryList
Do HistoryList.$search($ref.C1=List.$line,kTrue,kFalse,kFalse,kFalse) Returns %L \
Calculate #S1 as HistoryList.$status ;; tells you the status of the current line in the list
$status can be kRowUnchanged, kRowDeleted, kRowUpdated, or kRowInserted

HISTORY LIST ATTRIBUTES

$currentcontents - current value of the row from the normal list,
if the status is not kRowDeleted, and $rowpresent is kTrue

$errorcode - error code associated with a row (initially zero)

$linecount - number of rows in the history list

$oldcontents - the old contents of a row

$rowpresent - removes or adds the row to the normal list

Overriding smartlist methods

The smartlist is a powerful feature of Studio. However, if you want to add special calculations, check the data, etc. in your table class before allowing records to be saved you need to intercept these smartlist methods.

When $dowork executes, Studio calls $dodeletes, then $doupdates, and finally $doinserts. Each of these methods process the rows in the smartlist which match their status type. The respective $dodelete,$doupdate,$doinsert method (singular) is called. The row variable is passed to it.

So, if you want to make a calculation or redirect any of the smartlist methods, you simply insert the appropriate $do... method in your table class and add your own code.

I like to intercept the singular $do methods ($dodelete,$doinsert,$doupdate). For each of these single row methods you need you need to add a "pfRow" field reference parameter. You must use a field reference parameter in order to make your calculations 'stick'.

EXAMPLE:
In my application, I wanted to redirect the smartlist $doinsert to use my base table superclass $insert, the $doupdate to use my $update, the $dodelete to use the $delete. Each of these methods is automatically called by $dowork > $doinserts,... (plural) if you call them.

The code I use for accomplishing this is as follows:

$doupdate (pfRow,pfRowOLD) ;; field reference parameters
Calculate Row as $cinst ;; bind Row to the current table class Do Row.$assignrow(pfRow) ;; copy the values to lv Row Do Row.$update(pfRowOLD) ;; call $update method for this table class

$doinsert (pfRow) ;; field reference parameter
Calculate Row as $cinst ;; bind Row to the current table class Do Row.$assignrow(pfRow) ;; copy the values to lv Row Do Row.$insert ;; call $insert method for this table class

$dodelete (pfRow) ;; field reference parameter
Calculate Row as $cinst ;; bind Row to the current table class Do Row.$assignrow(pfRow) ;; copy the values to lv Row Do Row.$delete ;; call $delete method for this table class

I tested $dowork twice in a row to make sure $dowork would not try to $insert the records twice. Oops it did. I had to modify $dowork as follows to prevent this from happening. If you were not redirecting $doinsert to $insert you would not need to add the following:

$dowork
Do default Do $cinst.$savelistwork() ;; match the history list to current list

SmartList Notation

Listed below are the available smartlist notation methods.

$savelistdeletes()
removes all kRowDeleted rows from the history list, and also from the normal list if $rowpresent is kTrue

$savelistinserts()
changes all kRowInserted rows to kRowUnchanged, and sets the old contents of those rows to the current contents. It does not change $rowpresent

$savelistupdates()
changes all kRowUpdated rows to kRowUnchanged and, for all rows, sets the old contents to the current contents; this does not change $rowpresent

$savelistwork() quick and easy way to execute the $savelist... methods

$revertlistdeletes()
changes all kRowDeleted rows to kRowUnchanged or kRowUpdated (depending on whether the contents have been changed); for these rows $rowpresent is set to kTrue

$revertlistinserts()
removes any inserted rows from both the normal and history list

$revertlistupdates()
changes all kRowUpdated rows to kRowUnchanged and, for all rows, the current contents are set to the old contents; this does not change $rowpresent

$revertlistwork() quick and easy way to execute the $revertlist... methods

$includelines()
$includelines(status) includes rows of a given status, represented by the sum of the status values of the rows to be included. Thus 0 means no rows, kRowUnchanged + kRowDeleted means unchanged and deleted rows, and kRowAll means all rows, irrespective of

$filter()
$filter(search-calculation) applies a filter to a smart list; this method restricts the list to only those rows which match the search calculation; for example, Do LIST.$filter(COL1 = Ô10Õ) will only display lines where COL1 is 10

$unfilter()
$unfilter(level) removes a filter or filters from a smart list

FrontBase

FrontBase is an SQL backend which can run on MacOSX, Linux, Unix, and Windows NT.

FrontBase appealed to me because it runs on most server platforms, supports Mac and Wintel clients, and has DAMs built for Omnis Studio, and is priced affordably with unlimited runtime licensing. (At least at the time of writing this)

FrontBase is very stringent in following the SQL92 standard. Some developers (& competitors) criticize FrontBase for being so stringent. Personally, this trait appeals to me. If I'm going to learn SQL92 I'd like to learn and follow the real rules so that my application can be backend vendor independent. If I start using non-SQL92 tricks that are specific to one vendor my code is now married to that backend vendor.

If you are a SQL guru, you might laugh at some of the problems I ran into or the things I didn't understand. At the time of writing this, I am a still an SQL newbie, coming from OmnisDML and OmnisSQL.

A lot of the jargon in SQL just plain baffled me. Here's just a few terms that you need to deal with when trying to understand SQL record locking: Transactions, Concurrency, Isolation Levels, Read Uncommitted, Read Committed. Repeatable Read, Serializable, Pessimistic Locking, Optimistic Locking, Share and Exclusive Locks, Commit, Rollback, Discrete Commit, Granularity of Locks.

Yikes! For those of us new to SQL it's enough to make you run back to OmnisDML!

In this section of StudioTips, I will take you through my adventures in installing and working with FrontBase.

Add Constraint - Unique Index

In OmnisSQL we would use "CREATE UNIQUE INDEX" to enforce unique values on a column.

OmnisSQL uses "CREATE UNIQUE INDEX". SQL92 uses "ADD CONSTRAINT".

SQL92 uses "ADD CONSTRAINT" to set a unique index constraint for a specific column.

An example script for adding a unique constraint in FrontBase/SQL92 is as follows:

Add Foreign Key

OmnisSQL does not support foreign keys. With OmnisSQL you have to write your own table class methods/code to handle foreign key constraints.

An example script for adding a foreign key in FrontBase/SQL92 is as follows:

ADD FOREIGN KEY (To_Person_Key) REFERENCES person (Person_Key) ON UPDATE NO ACTION ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;

"NO ACTION" can be replaced with "CASCADE". For "ON DELETE" cascade would cause all the child records to automatically be deleted. In some cases that would be handy. In many cases that would be lethal.

Alter Table - Add Column

Adding a column is quite easy. An example script follows:

Create Table - Primary Key

When you create a table in SQL92 you can (should) specify the primary key.

CREATE TABLE "person" (
person_key INT NOT NULL PRIMARY KEY,
person_name VARCHAR (30) NOT NULL,
);

GOTCHA: Be careful with carriage returns!
I caused grief for myself more than once by inadvertently including a carriage return in the table name of the CREATE table statement. FrontBase is very particular about following the SQL92 standard, so if you follow their examples they alway enclose table and column names in quotes. Here's an example of how I accidentally added a carriage return to the table name.

CREATE TABLE "person
"(
"person_key" INT NOT NULL PRIMARY KEY,
"person_name" VARCHAR (30) NOT NULL,
);

The carriage return is locate between "n" of "person" and it's close double quote character.
Arrgh this little mistake can have you scratching your head for a while!

Create Table - Relate to Schema

Each table is related to a schema. (NOTHING to do with "Schema Classes"! See Users & Schemas) Each schema is owned by a user.

To create a table related to a schema you must logon as the user which owns the schema.

If you logon as a different user and try to create a table:

1. If the user owns a schema, the table will be related to that schema.

2. If the user doesn't own a schema, an error message will be generated stating that the user is not the owner of the current schema.

CREATE TABLE person (...

... will create the table "person" and relate it to the schema object owned by the current user.

NOTE: Only the owner of a table can DROP a table.

At this point in the SQL92 learning curve, I'm thinking to keep things simple, only use one schema, and relate all tables in the database to the one schema.

FrontBase Gotchas

Here's some new things I had to learn when starting to work with FrontBase. Be sure to test these gotchas, some of them may be my misunderstanding or may have been fixed/changed in newer version of FrontBase.

Omnis character fields - Length of 10 million.

FrontBase will hiccup and your SQL won't work. Don't go over 1 million characters long in your schema classes on character fields. If you use SQL ASSIST to create your schema classes and CREATE/ALTER SQL statements, SQL ASSIST will automatically limit them to 1 million characters.

DROP TABLE - requires the addition of "CASCADE".

If you use SQL ASSIST, it handles this for you.

SQL statements must be suffixed with ";" colon.

FrontBase requires all SQL statement to end with a semi-colon ";". If you are submitting your SQL statements throught the "FRONTBASESESS" V3DAM, the DAM adds the ";" for you. (unless you issue a "Do StatementObj.$sqlbrowser.$assign(kFalse)")

ALTER TABLE SYNTAX

I had been using the syntax "ALTER TABLE table ADD (ColName DataType, ColName DataType)" ;

Frontbase won't allow you to put multiple columns inside brackets like OmnisSQL.

The syntax for adding a column is as follows:

ALTER TABLE tablename ADD COLUMN colname VARCHAR (50) ;

This means you have to issue ADD COLUMN statements one at a time for each column. Once I got the ALTER TABLE syntax right, I ran into another problem. FrontBase reported:

Exception 384: Transaction MUST be SERIALIZABLE, PESSIMISTIC

To get around this, I had to issue the following "SET" SQL statement before the first time you issue an ADD COLUMN statement. Once you've issued it once, you don't need to reissue it for the rest of the session.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;

PICTURE, LIST, ROW datatypes

You can not save a "blank" (empty) values to BLOB data types. You either save a value or a NULL.

DATE datatypes

If you attempt to save a "blank" date FrontBase converts it to the default first date (Jan 1, 19...). To save an empty date it must be NULL.

CASE SENSITIVE INDEXES

With OmnisSQL you have to CREATE CASE SENSITIVE indexes if you want decent performance. FrontBase does not need (nor allow you to create) case sensitive indexes. SQL Assist handles this automatically for you.

SET TRANSACTION

Installing FrontBase

This section covers the adventures of installing FrontBase on Mac OS X. Installation was easy!

Learning FrontBase

The first time I tackled FrontBase I got all bogged down in V3DAMs and the Omnis Studio manuals.

It was frustrating trying to learn and understand the V3DAMs, session objects and all that new stuff, not knowing whether the problem was with my code, with FrontBase, or the V3 DAMs. Things did not go very smoothly.

AN OMNIS STUDIO APPLICATION - FRONTBASE TUTORIAL

The 2nd time around, I downloaded the following PDF from the Documenation section of the FrontBase web site: "An Omnis Studio Application - Describes how to use Omnis Studio as a front end to a FrontBase database."

Following this tutorial (and unfortunately not so clear screen shots) gave me much better confidence that FrontBase and Omnis Studio were compatable cousins. Everything worked as it should. Within 30 minutes you are reading and writing records to FrontBase and even doing a query class. I can't say I agree with the naming conventions they use or the SQL Wizard's window interface, but heh, they get you up and running!

After you've built the application, go look at the $construct code in the window classes, and study the code behind the Insert, Update, and Delete pushbuttons. It is surprisingly simple.

You wouldn't want to build an application with all that duplicate code, but it gives you a starting point that is simple to follow.

FRONTBASE TEST LIBRARY

The FrontBase DAM download also comes with a Test Library folder which contains a FrontBase Test.lbs library. Open the library and tinker around with the it.

1. Create a new database using FrontBase Manager and get it running.

2. Open the "Connect" window in the FrontBase Test library.

3. Enter the logon information. In my case:
->Server Name: localhost
->Database Name: TestDB
->Database Password:
->User Name: _System
->User Password:

4. Click the Connect pushbutton. All going well you will connect. One time I had problems with 'localhost' not be recognized. I replaced 'localhost' with the actual IP address of my computer, and then it would work.

TIP: When you are testing connections to databases it is handy to have the Tools menu > SQL Browser window open. If the logon code works you will see your session show up in the SQL Object Browser window. If the logon fails, you can test your settings by creating a Session Template in the SQL Object Browser. (Sessions menu > Modify Session Template). Then test it using Sessions > Open >.

5. The Connect window will display a "Drivers & Lists" tab pane. Click the Create Drivers pushbutton. This creates the DRIVERS table in the Frontbase database.

6. Insert a few drivers. Then click Select All to list the drivers.

7. Go to the Lists tab and repeat the same thing. (Create List, Insert List, Select All)

8. Open the Tools menu > SQL Brower window and double-click on the session object in the window. Double-click the Tables icon. Double-click the DRIVERS icon. Here you will see the column names.

Take a look at the code behind the window's buttons. I found it interesting to see that this library doesn't use schema classes or table classes. The Create List button demonstrates a list/BLOB type column. The "Connect" pushbutton shows you the code for logging on to FrontBase.

SUMMARY

It helps to build your confidence by seeing Omnis Studio working with FrontBase before you venture into building your own logon method.

The code, naming conventions, window designs, etc. in these tutorials is not great, but that isn't the objective of the FrontBase tutorial and the FrontBase test libraries.

Multi-threaded DAMs

Transactions and Concurrency

In the SQL world we are faced with a number of options dealing with transactions and concurrency.

Transactions are groups of SQL statements that are effectively executed as single units.

Concurrency rerfers to mechanisms that the DBMS uses to keep operations on the same data by simultaneous users from interfering with one another.

You begin a transaction whenever you initiate a session with SQL. All the statements you enter will be part of the same transaction until you complete it by entering either a COMMIT WORK or a ROLLBACK WORK statement. OmnisSQL does not support COMMIT and ROLLBACK, so in OmnisSQL every SQL statement is automatically committed and there is no ROLLBACK option. You can set any DBMS to autocommit transactions. With the V3 DAMs you control this with the $transactionmode property.

Do $sessions.SESSIONNAME.$transactionmode.$assign(kSessionTranAutomatic)

However, with databases such as FrontBase that support COMMIT and ROLLBACK you should make use of transactions and set the $transacation mode property to kSessionTranManual.

The FrontBase Manager manual > Appendix 1 - Transaction Settings, gives a pretty user friendly explanation about how Isolation Levels and Locking Disciplines for FrontBase. But to the newbie, like myself, even after reading it carefully and referring to other SQL manuals I still wasn't 100% sure what isolation level and locking discipline settings I should use. A telephone call to Joe Maus, helped to fill in the empty blanks.

Everyone has their own opinions on what is right and wrong for these settings, so feel free to make your own choices. The following is what I decided to go with.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED, LOCKING DEFERRRED, READ WRITE;

READ COMMITTED means that each time I select and fetch records, FrontBase automatically commits the transactions, so that the records are not blocked from other users who wish to read and write those records.

LOCKING DEFERRED means that if I read some records and then another user reads the same records, if I update my records, the other user is blocked from updating the same records. (Hope I got that right)

READ WRITE means that I can read and write records to the database, vs. READ ONLY.

For most of the work I do, I don't want to block users from reading the same records at the same time. Doing so, would cause unnecessary blocks and waits when two users want to look at the same record, or run reports on the same records.

I prefer to use an EditNum (or TS) integer column in each of my SQL tables. The EditNum is incremented +1 each time the record is updated. When the update is done, I check to make sure the record's EditNum value matches the OldRow.EditNum value. This can be done by accomplished by appending "AND EditNum = OldRow.EditNum" to the SQL update statement. This methodology is considered optimistic locking as opposed to pessimistic locking which won't allow other users to access records selected by another user and not yet committed.

The transaction settings are session specific. You can change the transaction settings for a session at any time during the session. (Although not for records you've already selected)

To set the transaction setting you issue the SET TRANSACTION statement after the session has been started.

GOTCHA: For FrontBase you must use $execdirect for SET TRANSACTION. If you use $prepare and $execute your SET TRANSACTION statement will fail. (SQL92 specific reasons for this.)

LOCKING DEFERRED - EXPLAINED BY FRONTBASE

The DEFERRED locking discipline is almost like PESSIMISTIC with the main difference being that when a row/table is referenced, a READ lock is acquired. If the row/table is updated, the READ lock is automatically updated to a WRITE lock. The DEFERRED locking discipline is thus ideal when updates occur less frequently than reads.

Geert B. Clemmensen FrontBase, Inc.

SELECT BY ONE USER CAUSES OTHER USER SELECTS TO HANG
A SELECT needs a transaction so if there is no active transaction, one will get created. No matter
the actual transaction settings, you will eventually (and the sooner the better) have to either ROLLBACK or COMMIT a transaction.

Transcations

If you ar new to SQL, probably one of the most confusing things it figuring out this business of transactions. For many of us it would be nice if there was just one way to handle transactions and we would just follow that way. But it isn't that simple. There are many different choices and you need to work through things and figure out what will work best for your situation. In fact, you can have different users accessing your database with different transaction settings that suit the particular data they ware working with.

The following is an explanation about transactions by Geert Clemmensen of FrontBase.

The most important bottom line there is in regards to transaction is: A transaction is the only way, no matter what, to guarantee anything about the integrity of your most valuable IT asset: your data. The sooner you learn and understand transactions and what they can do for you, the sooner will you be able to master them and thus provide the solutions your customers ask for.

I have included a brief PDF document about transactions, which actually stems from a Keynote (Apple's presentation software) presentation I gave at AmerOmnis in March. Maybe this will help shed some light on the "preferred" transaction settings.

FrontBase implements the transaction model defined in the SQL 92 standard, a model that has been accepted by most modern database layers, incl. JDBC.

We have augmented the transaction model of SQL 92 in two ways:

1) The VERSIONED isolation level. A VERSIONED transactions provides a snapshot of the latest COMMITted version
of the database, i.e. the snap-shot represents history and the transaction is thus read-only and there is no locking
whatsoever involved.

2) Locking discipline. FrontBase offers OPTIMISTIC, PESSIMISTIC and DEFERRED as locking disciplines.
OPTIMISTIC means that you will always get access to your data, but if you modify them, a subsequent COMMIT
may fail because somebody modified the same data and executed a COMMIT before you.
PESSIMISTIC means that you will acquire a lock and that this lock isn't released until ROLLBACK or COMMIT time.
This also means that PESSIMISTIC is a so-called blocking locking discipline.
DEFERRED is a variant of PESSIMISTIC in which you will acquire a READ lock, if you modify the data, the lock
will automatically be upgraded to a WRITE lock. This means that it is possible to have concurrent PESSIMISTIC
readers.
The locking discipline has no meaning whatsoever if the isolation level is VERSIONED.

The SELECT ... FOR UPDATE syntax that lately has been referenced on fb-dev is a legacy or old-fashioned syntax that doesn't make sense in SQL 92, each SELECT in SQL 92 is automatically a FOR UPDATE select. Each table and row that a SELECT "touches", is automatically locked according to the isolation level and locking discipline. In many "older" database servers, a SELECT without the FOR UPDATE clause means that the server is using a one-shot transaction (a kind of auto commit), which can mean that two subsequent SELECTs executed seemingly immediately after each other can return different data. Please note that a SELECT ... FOR UPDATE is a blocking statement.

When you do a backup in FrontBase (WRITE BACKUP ...;) it will implicitly use the VERSIONED locking discipline, i.e. you can continue to modify the database while the database is running.

Let me try and address some specific issues/questions that have been posted to fb-dev.

In step 1 any data can be selected and shown, regardless if the same data is
used by another session-process.
The fullest meaning of this implies what is called dirty-reads which is not supported by FrontBase and which doesn't really make any sense from the users perspective. The choices are in reality:

1) OPTIMISTIC, i.e. multiple users can see the same COMMITted data. COMMITting an OPTIMISTIC transaction
is much more likely to cause a transaction conflict than a PESSIMISTIC transaction, but the upside is a much
higher degree of concurrency.

2) PESSIMISTIC, i.e. only one user at a time can access and modify a given table or row.

The strategy of having two connections, i.e. one for doing SELECTs and one for doing modifications has been adopted by quite a few developers. The downside is that rows you have selected between the time of SELECT and UPDATE, may have been changed or even deleted by somebody else (the whole table may have been DROPped). The upside is a high degree of concurrency.

The TEST AND SET LOCKING ON < table > (< row index >) statement is a twist to the PESSIMISTIC locking discipline that can tell you if a given row is already locked by somebody else (or let you acquire the lock).

The ONLY way to make the strategy 100% watertight is to SELECT and UPDATE the data in the same transaction using a PESSIMISTIC locking discipline. This applies not just to FrontBase, but to all database servers that are serious about data integrity.

If your app can deal gracefully with transaction conflicts, something that is less than 100% watertight can be made to work quite appealing, while still preserving the data integrity.

1. User can selected any data available (for that screen)
2. User can signal a wish to alter the data shown (on the screen)
3. User can input the alterations.
4. User must signal whether the alterations must be stored or not.
C. At the same time any process must be able to read the same data without
hindrance.
D. Waiting by users for release of a lock by another user is not
allowed-appreciated. So in case this could happen a test must be available
to avoid it.
The only way to achieve the "conflicting" requirements put forward above, is to SELECT the data (and COMMIT the transaction right away or use a separate OPTIMISTIC or VERSIONED connection), lock the appropriate rows when editing starts (using a SELECT or TEST AND SET LOCKING). NO MATTER WHAT CHOICE YOU MAKE, YOUR APP MUST BE ABLE TO DEAL WITH TRANSACTION CONFLICTS (somebody may have changed the data between the SELECT and UPDATE).

An analogy describing the OPTIMISTIC situation is ordering food in a cafeteria: You make your choice of meal (SELECT) and are told to go and pick it up after it has been paid for (COMMIT), you go to the cashier and want to pay (UPDATE and COMMIT), only to find out that somebody else has just paid for the last portion of that meal. This means that you will have to go back and make a new choice (ROLLBACK), go to the cashier, ...

The PESSIMISTIC version of the cafeteria analogy is: you make your choice (SELECT, nobody else can now get it), get your meal (UPDATE), go to the cashier and pay for it (COMMIT).

Connection 1 (C-RW) has the following settings:
set transaction isolation level repeatable read, locking optimistic, read write;
SET TRANSACTION ISOLATION LEVEL VERSIONED; is enough, as it implies a read only transaction that only can read historic data (the latest COMMITted version of the database).

Here I also have to note that this can be tricky in Frontbase, and connections sometimes "hang" without any obvious reason.
I am willing to be a six-pack of your choice that there isn't a bug in FrontBase in this area :o) If you can forward us a reproducible "hang" situation, my bet is that we can explain the problem to be on the client side.

Hope the above helps.

Users and Schemas

If you come into FrontBase from OmnisSQL the whole issue of "Users" and "Schemas" in SQL92/FrontBase is an extra layer that gets in between you and the server tables. Learning to work with this extra layer can be frustrating when you are first moving to FrontBase/SQL92.

The concept of "Schemas" in SQL92 is not to be confused with "Schema Classes" in Studio. Studio's choice of the term "Schema Class" got in the way of my understanding SQL92 Schemas. When we talk about "Schemas" in FrontBase/SQL92, don't even think about Schema Classes in Studio!

According to Mastering SQL by Martin Gruber; A group of interrelated tables is called a schema. A database can contain any number of schemas. So, a SQL92 "schema" is a collection of server tables. (plus views and few other things)

The concept of "Users" is explained by Martin Gruber as follows:

* A DBMS (Database management system) must be capable of recognizing individual "users". Users are identified with a logon procedure (username/password).

* Once they logon they initiate a "session" (also called a "connection") with the database. Within a session a sequence of "statements" is issued. Statements from simultaneous sessions of the same or different users for independent sequences.

* Users have a set of "privileges" that inidcate what they may and may not do.

* A database user typically has ownership of one "schema" and may have access to others as well. (Remember a schema is a collection of tables)

* A user can own one or more schemas.

To the best of my knowledge OmnisSQL does not support SQL92 "schemas" or "users". So when you move from OmnisSQL this is NEW territory!

USERS & SCHEMAS - FRONTBASE MANAGER

Assuming you are using the FrontBase Manager, if you create a new database and then connect and logon as _SYSTEM and then click "Users" you will find that the user _SYSTEM has a "Default Schema" of "None". You can edit the user and change the Default Schema to "_SYSTEM". (Go ahead try it.)

If you click on"Schema" you will see a schema by the name of "_SYSTEM" that is owned by the user "_SYSTEM". (The names are the same, but they don't have to be.)

If you click on "Schema Objects" you will see a list of the "Schemas". If you click on the "_SYSTEM" schema, you will see that a schema has; Tables, Views, Procedures, Functions, and Collations.

If you click on "Tables" you will see the tables related to the schema. If this is a brand new database then there won't be any tables listed yet.

SUMMARY:

There is a "user" called "_SYSTEM".
There is a "schema" called "_SYSTEM" which is "owned" by the user "_SYSTEM".
The "schema" called "_SYSTEM" can have a group of interrelated tables.

USERS & SCHEMA STRUCTURE

Being very new to SQL92 I was (am) unsure what the best structure is for Users & Schema. I was told to think of the _SYSTEM user as the super user, and that it would probably be best not to related tables directly to _SYSTEM.

In my own application, I did the following from the FrontBase Manager.

1. Connected as the user, "_SYSTEM".
2. Added a user called "SYSADMIN" without creating a default schema for it.
3. Added a user called "STAFF" without creating a default schema for it.
4. Closed and reconnected as "SYSADMIN"
5. Created a Schema called "DATABASE". (The owner is automatically the current user, "SYSADMIN".
6. Closed and reconnected as "_SYSTEM".
7. Edited the user, SYSADMIN and set its default schema to DATABASE.
8. Edited the user, STAFF and set its default schema to DATABASE.

We now have a schema called DATABASE which is owned by SYSADMIN. We would normally connect to the database as SYSADMIN. Any tables we create when connected as SYSADMIN will automatically be related to the DATABASE schema which is owned by SYSADMIN.

SYSADMIN would GRANT privileges to STAFF for the many of the tables & view which it creates.

Other users would connect as the user STAFF (think of STAFF as privileges for a group). Additional types of users could be added if we wanted to further fine tune privileges. (eg. ACCOUNTING)

Using FrontBase

So now you've seen that FrontBase works and the Omnis Studio can talk with it. What next?

1. You need to get an understanding of how the multi-thread DAMs work in Omnis Studio.

2. You will need to likely need to build a logon window and logon session object class for your application.

3. You need to get an understanding of transactions and concurrency. (Commit, Rollback, etc.)

StudioTips Documentation - Copyright 2006 Vencor Software