SQL

SQL is an acronym for Structured Query Language.

My only experience in programming is Omnis. I started with Omnis Classic using Omnis DML, that is using the Omnis date file with file formats... no SQL)

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 two big mistakes; Omnis 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 Omnis Studio works wonderfully with it!

SQL vs DML

Opinions abound on this topic and everyone is entitled to their opinion. I used the Omnis DML (Omnis data file with File Classes) for many years and then reluctantly switched to using SQL to access the Omnis data file. Once I got through the SQL learning curve The following are some reasons why I believe SQL is the way to go.

1. Omnis 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 the actions of other objects and methods. The current record buffer (CRB) used by DML is global which can be a headache for object-oriented programming.
2. With Omnis 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 updates (or your connections get messed up). In SQL there is no CRB, no prepare for update, and no connections going on in the background.
3. With Omnis 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 add them to the file.
4. Omnis DML is usually written modal. You have an Enter Data state and the user must OK or Cancel. When I switched to Omnis Studio, going to modeless data entry made it a lot easier to code.
5. Omnis DML only works with the Omnis data files. With SQL you can move your application to high powered SQL databases.
6. Omnis DML does not support commit and rollback transactions, which is very important for maintaining data integrity in mission critical applications.
7. Omnis DML does not support or enforce foreign key relationships. You are responsible for the integrity of foreign keys (making sure the parent record exists) and enforcing any constraints (prevent deletion of a parent record if their are child records linked to it)
   
   
8. Omnis DML does not support multiple joins to the same table.

Omnis Data File vs. RDBMS

For the first few years of my journey into the land of SQL I used the OmnisSQL DAM with the Omnis data file. The OmnisSQL/Omnis data file was free. All the other RDBMS (Relational Database Management Systems) that had Omnis Studio DAMs had to be purchased. That is no longer the case. Omnis Studio developed a DAM for MySQL which is a free RDBMS, and FrontBase changed their pricing structure so that their RDBMS is now free. If you want FrontBase tech support you need to purchase that separately.

Now that there are free RDBMS solutions available, why would you bother using the Omnis data file?

There are a couple of reasons for using the Omnis data file:

1. The base developer version of Omnis Studio only allows connection to the Omnis data file. You have to purchase the Enterprise edition to connect to other RDBMSs.
2. For demos which you ship to potential customers the Omnis data file does not require the installation of any additional software.

One of the problems I ran into with using the Omnis data file for demos is if you have any complex table relationships. Selecting and fetching records can be brutally slow with the Omnis data file for complex table relationships. I had one demo which was embarassingly slow with the Omnis data file. I spent hours trying to tweak the select and fetch to speed it up and had very limited success. I moved the data over to FrontBase and the demo changed from being embarrassingly slow to impressively fast!

Another problem with using the Omnis data file for demos is that you can't use views. Views are extremely handy for doing multiple joins to the same table. For example, if you want to join a correspondence record to the contacts table several times. (From, To, Cc) you can't do this with the Omnis data file. The only way to to write a work around which pulls records and joins them manually in Omnis Studio code... a lot of extra work for the programmer, and much slower performance.

Learning SQL

SQL was something I had to learn when moving from Omnis Classic to Omnis Studio. After reading a number of books on SQL I purchased Mastering SQL by Martin Gruber. This is the reference book which I turn to when I need to figure out a SQL script. Mastering SQL has depth, but doesn't assume too much knowledge on the part of the reader. A nice balance for programmers who are new to SQL.

Bind Variables

Bind variables are very important to use in your SQL script related Omnis Studio code.

Bind variables allow you to put variables in the SQL script that gets passed through to the database. There are several advantages to using bind variables:

1. Avoid mismatched data type SQL errors.
2. Solves problems with single and double quotes in the comparison string.
3. Ensures that date, time, and timestamp values are in the right format for the RDBMS.

To use bind variables, you simply enclose the variable you want to used in the SQL script with @[].

The following example uses bind variables for the date range.

Calculate DateFrom as fday(kMonth,#D)
Calculate DateTo as lday(kMonth,#D)

Calculate SQLText as "SELECT * FROM Salesorder"
Calculate SQLText as con(SQLText," WHERE OrderDate >= @[DateFrom] AND OrderDate <= @[DateTo]")

Do StmntObj.$execdirect(SQLText) Returns FlagOK

One thing you have to be careful with bind variables and table classes is that the bind variable must be visible to the table class instance. That means inside the table class you must use instance variables for any bind variables. If you pass in comparison variables as parameters, you must copy them to instance variable before using them as bind variables.

Use the columns of a row variable for your bind variable values and pass the row in as a parameter to your table class method. The table class method can then copy the parameter to an instance row variable. The following sample code demonstrates the technique which we use in StudioWorks.

; Some method which gets a batch of order by date range.

Do List.$definefromsqlclass('tSalesorder')
Do List.$sessionobject.$assign(sessobj)

Calculate DateFrom as fday(kMonth,#D)
Calculate DateTo as lday(kMonth,#D)

; The SQL text specifies column numbers for the bind variables.
Calculate SQLText as "WHERE OrderDate >= @[2] AND OrderDate <= @[3]"

; Use columns of a row variable for the variables.
Calculate WhereRow as row(SQLText,DateFrom,DateTo)

; Call a custom $getWhere table class method.
Do List.$getWhere(WhereRow)

; $getWhere (table class method)

; Copy the where row to an ivar row.
Calculate iBindRow as pWhereRow

; Get the SQL text from column 1.
Calculate SQLText as pWhereRow.C1

; Loop through columns 2 to the end.
For ColNum from 2 to pWhereRow.$colcount step 1
   
   ; Replace the column number @[#] with @[iBindRow.C#]
   Calculate Find as con('@[',ColNum,']')
   Calculate Replace as con('@[iBindRow.C',ColNum,']')
   Calculate SQLText as replaceall(SQLText,Find,Replace)
   
End For

; Execute the select with the SQL text that now points to the bind variable.
Do $cinst.$select(SQLText) Returns FlagOK
If FlagOK
   
   Do $cinst.$fetch(kFetchAll) Returns FetchStatus
   Calculate FlagOK as FetchStatus<>kFetchOk
   
End If
Quit method FlagOK

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 have been phased out, so you shouldn't even bother with them, unless there is a specific reason you must support them in your application.

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 Omnis command 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.

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 two way communication with the database.
  
  You can start multiple sessions with the same database and/or multiple databases. 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 and $fetch on another table (or maybe even the same table), the server now has both your select lists available to you.
  
  You can now jump back and forth and $fetch records from either select list using the cursors. If you do another select with the same cursor, the old select list is replaced with the new one.

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. (Think 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.

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.

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

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

With databases that support COMMIT and ROLLBACK you should make use of transactions and set the $transacation mode property to kSessionTranManual when you are doing a batch or series of database updates.

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 >] [,< access >]

Each set of < > 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 > can be OPTIMISTIC or PESSIMISTIC or DEFERRED

< access > 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.

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.

SQL Scripts

Create table

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

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

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

Capitalization is not necessary.

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

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

Create index

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

CREATE [UNIQUE] INDEX IndexName ON TableName (ColName)

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

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

CREATE UNIQUE INDEX Author_pkey ON Author (Author_pkey)
CREATE INDEX AuthorName ON Author (AuthorName)

Capitalization is not necessary.

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

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


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


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

Alter table

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

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

ALTER TABLE Book ADD (
BookType VARCHAR(10)
);

Capitalization is not necessary.

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

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

Drop table

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

DROP TABLE Author;

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

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

Drop index

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

DROP INDEX IndexName;

SQL Misc

Begin SQL Text

Omnis 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.

The SQL script Omnis commands are global so if you want to be extra careful you should enclose each SQL script group in a Begin/End critical block to safeguard against multi-threading issues.

There is 32,000 character limit on the Begin/End SQL script Omnis commands.

LIKE Operator

The LIKE operator lets you search for begins with, contains, or ends with.

If you want to find all the author names which begin with the word Scott the SQL text would look like this:

SELECT * FROM Author WHERE AuthorName LIKE 'Scott%'

The % character is a multi-character wildcard in SQL. The % character can be replaced by any character or no character.

The _ character is a single character wildcard in SQL. The _ character can, and must, be replaced by any single character.

If you want to find all the author names which contain the word Scott the SQL text would look like this:

SELECT * FROM Author WHERE AuthorName LIKE '%Scott%'

If you want to use bind variables with the LIKE operator the SQL text would look like this:

Calculate Value as 'Scott%'
Calculate SQLText as "SELECT * FROM Author WHERE AuthorName LIKE @[Value]"

There are a couple of gotchas to watch for when combining bind variables with the LIKE operator.

1. OmnisSQL does not allow the use bind variables with the LIKE operator. Be sure to replace any single quotes with the _ underscore character in your comparison value.
   
   

   Calculate Value as "%O'Brian%"
   Calculate Value as replaceall(Value,"'","_")
   Calculate SQLText as con("SELECT * FROM Author WHERE AuthorName LIKE '",Value,"'")

2. FrontBase will give you an error if you try to use LIKE on a non-character data type column. The work around is to use CAST() on the column name.
   
   

   Calculate PKeyValue as "105%"
   Calculate SQLText as "SELECT * FROM Author WHERE CAST(Author_pkey AS CHAR(255)) LIKE @[PKeyValue]"

There may be other databases which have problems with using the LIKE operator with bind variables or against non-character data type columns. I tested it with MySQL and there were no problems. I've been told that it works with Oracle as well.

NULL Values

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 (0) 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 (0) because you can calculate a valid average household income on all the records, since the NULL values won't be included in the average. (Zero values are included.)
However if you calcluate a A as B*C, and B or C is NULL your result will be NULL. That can be a real problem when calculating the payroll!

So you need to be very careful with NULL values.

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

SQL Browser

I found the SQL Browser to be marginally helpful when I was learning SQL. I still use the SQL Browser from time to time to test logging onto a database and to look at the servertables.

SQL Problem Solving

If you are new to SQLit's easy to hit a problem that baffles you for hours. This section covers a couple of head scratchers that have had me or others 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

If you are trying to select LastName = O'Sullivan, the single quote character might can you grief. Use of bind variables will eliminate the problem.

Alternate solutions are:

1. Replace the single quotes with two single quotes so that LastName = O''Sullivan. This trick works for OmnisSQL, MySQL, FrontBase, DB2, and ....?
2. Replace all the single and double quote characters with underscore characters so that LastName = O_Sullivan. In SQL the underscore character is a single character wildcard.

SQL Problem Solving

SQL Error Handler

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

The built-in table class method call the $sqlerror method if it hits an error.

The following is the code from my base table superclass $sqlerror method.

Switch pkErrorType
   Case kTableSelectError
      Calculate ErrType as 'Select Error'
      Calculate ErrCode as $cinst.$statementobject.$errorcode
      If ErrCode=0
         ; This just means that the select came up with no records. Not an error.
         Quit method kTrue
      End If
   Case kTableFetchError
      Calculate ErrType as 'Fetch Error'
   Case kTableUpdateError
      Calculate ErrType as 'Update Error'
   Case kTableDeleteError
      Calculate ErrType as 'Delete Error'
   Case kTableInsertError
      Calculate ErrType as 'Insert Error'
   Case kTableGeneralError
      Calculate ErrType as 'General Error'
   Default
      Calculate ErrType as 'Unknown Error'
End Switch

; Copy the row to a local row variable.
Calculate Row as $cinst

Calculate SQLClassName as $cinst.$sqlclassname
Calculate Mssg as con("SQL Error: ",ErrType,", SQL Class Name: ",SQLClassName)
Calculate Dtls as con("SQL Error: ",$cinst.$statementobject.$nativeerrorcode," - ",$cinst.$statementobject.$nativeerrortext,", SQL Text: ",$cinst.$statementobject.$sqltext)

Breakpoint

; Log the error.
Do errhndlr.$logSQLError($cmethod,Mssg,Dtls)

Quit method kFalse

Fetch Only Gets One Record

Fetch only brings back one record into your list variable.

1. You must specify the number or records to $fetch. $fetch() only gets 1 record. Use $fetch(kFetchAll).
2. Check to make sure your list variable isn't actually a row variable.
3. $fetch(kFetchAll) only brings back 1 record. Check the $linemax value of the list you are using.
   
   Calculate #1 as List.$linemax
   
   If $linemax is only 1, set it to kFetchAll
   
   Calculate List.$linemax as kFetchAll

Quotes in SQL Text

If you are trying to select LastName = O'Sullivan, the single quote character can give you grief. Use of bind variables will eliminate the problem.

Unable to Insert a Second Record

You can insert the first record in a table, but you can't insert a second record.

Unable to Insert any Records

You are unable to insert any records into a table.

1. Do you have a session open with the database? And is it the right one?
2. Are you logged onto the database?
3. Did you set the $sessionobject property of the list or row that is bound to the table class?
4. Is one of the columns in the record a NULL value for a column set to No Nulls?

Unable to Update Records

If you find you can insert records but can't update old records:

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

Using SQL Reserved Words

StudioTips Documentation - Copyright 2006 Vencor Software