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.

StudioTips Documentation - Copyright 2006 Vencor Software