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.

StudioTips Documentation - Copyright 2006 Vencor Software