Part 5: Adding multi lingual support

This is a repost of my old tutorial I wrote in late 2007. I'm presenting them as they where.

Now that our application is still small its easy to add multi language support into it. I always suggest doing this when you start building your application, not later down the line.
For multi lingual support Omnis internally uses string tables. String table translate a label to the current language in the background so you can build your application using the labels. As the string table is stored separately from your library you can have someone translate the labels while you continue working on your application.
Well be using Omnis string table editor to create our string table. This is a fine tool to use when youre a single developer but when youre working in a team you may want to take a different approach. Ive build my own version of the string table editor that uses a database backend so a team of developers can maintain a single collection of translations and build string tables from there. However I would probably not go down that route today but instead let each developer maintain their own IDs and merge string tables together.
For our tutorial well use a single string table file, that is enough to show you how it works. If you have a big application, or think your application may become big in the future, I do advise you to create multiple string table files. What I do and what works fine for me is that I place all the string tables I need in a subfolder and when my library loads, I load all string tables within that subfolder into memory using the string table file name as a guide.
The reason I suggest using multiple string tables is two fold:
1) very very big string tables are hard to maintain and can have a performance penalty
2) you can add string tables with translations that only apply for a certain customer or module
You can also choose not to use string table files but store the translations separately. For instance the string table module contains functions from loading translations from a list that you can build from a database. But Ill leave that up to your own imagination.
You can find the string table editor by going to Tools->Add Ons->String Table Editor. Each string table has an "ID" column and one or more language columns. You can open my example string table and take a look at its contents. There are translations in there for 3 languages at the moment.
The ID column is whats important to our application. If you use a single string table you can use this ID field directly but if you have multiple string tables loaded, its smart to prefix the ID with the string table name when using it in your application. So a ID called "Username" you would use as "demolib.Username" in the application. I always prefix it because I may be using a single string table today, but maybe tomorrow I will add a second or third.
Now that we have created our string table we need to load it into memory to be able to use it. Add the following code to your startup_task:

Startup_Task.$reloadStringTable()
----
;  Get the location of our library, our string table should be in the same directory
Split path name (sys(10),tmpDrive,tmpDir) 

;  Get the current selected language (if we are reloading)
Calculate tmpColumn as StringTable.$getcolumnnumber(demolib)
If tmpColumn=-22     ;; No columns set?
Calculate tmpColumn as 2     ;; 2 is the first language!
End If

;  Unload string table just in case
Do StringTable.$unloadstringtable(demolib)

;  Now load our string table
Do StringTable.$loadstringtable(demolib,con(tmpDrive,tmpDir,demolib.stb))

;  Make the language current, note that if you have multiple string tables you need to do this for each one!
Do StringTable.$setcolumn(con(demolib.,tmpColumn))

We need to call this method from our startup tasks $construct. Note that its called $reloadStringTable, not $loadStringTable and that the code allows you to call it at a later time again. As youre developing youll be altering the string table and you will want to load the changes into memory without restarting your whole application. Adding a menu item or toolbar button that is only available during development and calls $ctask.$reloadStringTable will give you the ability to quickly load the updated version of the string table.
Most of the time you will be using the string table to translate labels on your window. For this Raining Data has created a nifty little background object called a string label field. Its in the last pane of your component store. Well start by making our logon window multi lingual and replace all our label fields with string label fields.
Remove the username label from the logon window and drag a string label field onto the logon window and place it where our username used to be. Then rightclick on the string label and select properties, the property manager should popup.
In the property manager there is a new tab called "Custom". In this tab there is a property called rowid. In this you should put the ID of the string table, type in "demolib.Username".
Note that the text isnt translated until runtime.
Also assign CtrlLabel to its $fieldstyle (note, in early versions of the string label $fieldstyle didnt work and you had to set all the properties manually!).
Now do the same for the password and hostname fields.
We also need to do the logon button. Now there is no string table version of the pushbutton, instead well need to handle the translation ourselves. This is easy. Select the $text property of the logon pushbutton and clear it, now press F9.
Select the StringTable pane in the catalog and doubleclick the logon label. Omnis will insert the correct text, all we need to do is place this text between brackets so $text becomes: [StringTable.$gettext("demolib.Logon")]
We will do the same with the window title. I have a standard that I always name my label the same as my window, so wLogon.$title becomes: [StringTable.$gettext("demolib.wLogon")].
Now when you open the logon window it should show all the labels in Dutch. Since Dutch is my first column in my StringTable, it has become my default.
However we want the user to select a different language. You might want to make this part of your applications configuration or maybe store it along with the user details and not set it until after the user logs on. For our tutorial well make the language selectable by the user on the logon window as that is the first window the user sees. Well add a dropdown list field to our logon window and add the following two methods to it:

wLogon.iLanguageList kList

wLogon.iLanguageList.$construct
----
;  the stringtable framework should always be installed so we can use that..
Calculate tmpCurrentColumn as StringTable.$getcolumnnumber(demolib)

Do iLanguageList.$define()
Do iLanguageList.$cols.$add("Description",kCharacter,0,250)

For tmpColNumber from 2 to StringTable.$colcnt(demolib) step 1
  ;  Make the column current so we can get the column name!
  Do StringTable.$setcolumn(con(demolib.,tmpColNumber))
  Calculate tmpColName as StringTable.$getcolumnname(demolib)
  Do iLanguageList.$add(tmpColName)
End For

;  Change it back to what it was
Do StringTable.$setcolumn(con(demolib.,tmpCurrentColumn))
Calculate iLanguageList.$line as tmpCurrentColumn-1

wLogon.iLanguageList.$event
-----
On evClick     ;; Event Parameters - pRow ( Itemreference )
  Do StringTable.$setcolumn(con(demolib.,iLanguageList.$line+1))
  Do StringTable.$redraw($cwind.$hwnd)
  Do $cinst.$redraw(kTrue,kTrue)
  ;  Title is not reset automatically....
  Calculate $cinst.$title as StringTable.$gettext(demolib.wLogon)

Now when you open the logon window you should be able to select from the 3 languages and the interface should change itself.
So resume:

• Use string labels for your labels and they will automatically translate themselves
• Use the method stringtable.$gettext(stringtable.label), between [ and ] where needed, to translate on the fly
• Always prefix your labels with the name of your stringtable.

Two last things that remain to be said:

• The standard $gettext is fun, but I always implement a Startup_task.$gettext(pLabel, p1, p2, p3, ... p9) method that translates the label pLabel and then does a replaceall function for each specified parameter. This is great for translating error messages. My error label "mandatoryError" translates to "%1 is a mandatory field". Not I can simply do a $ctask.$gettext("mandatoryError", $ctask.$gettext("Username")) and it will come back with a nicely translated "Username is a mandatory field". Ill leave it up to you to make a nice one for yourself.
• I dont particularly like the placement of some of my routines. Proper OO programming, if there is such a thing, would dictate that I would subclass the StringTable object and implement a $reloadStringTable, $buildLangList, $setLanguage, $getLanguage and $gettext (overridden) method in there with my logic. But I can not subclass from the StringTable object in the way that I want and so I generally dont bother because its only the $gettext that I frequently use. Still it may be nicer to create a single object that encapsulates this logic so its in one place and then call the logic from the places its needed.

Thanks to Rainer R. Greim for the German translations.
----
Disclaimer: Im sure some of the listers will recognize their ideas in this tutorial in one form or another. Some ideas are truly mine, some are inspired by what Ive learned in over 10 years of conferencing and reading the list. It would be an impossible task for me to list the many many people who I owe thanks to. Im sure you know who you are my friends. I take only credit for taking the time to put this series together. Use the code you find in this tutorials and the library/libraries attached freely and as you see fit. I take no responsibility and will not be liable for any damages resulting directly or indirectly from using the information I present here.

Part 4: Adding a broker

This is a repost of my old tutorial I wrote in late 2007. I'm presenting them as they where.

Simply looking at our current application we already see a problem prop up. I try to enter a new contact and I find out there is no contact type that suits my needs. I open up the window where I can add a contact type and I do this but when I return to my contact window, my new contact type is nowhere to be found.
A broker is one way to solve this. A broker is a mechanism where objects can subscribe to messages (events) that they would like to know about and to which they can send messages when a certain action has been performed.
Our contact maintenance window can tell the broker system it wants to be informed whenever the content of the contact type table changes, and our contact type maintenance window can tell the broker system when its changed it.
The contact maintenance window and the contact type maintenance window do not need to know anything about each other nor about their individual needs. The broker system needs to know nothing about how contact types are created or what the contact maintenance window requires to do when a contact type changes. All it needs to do is figure out who needs to be told about what.
Before we implement our broker system however, we're first going to readdress and old issue :) we're going to auto number the contact number. So how to implement this? Well very simple, we'll handle it at the source.
Our tContact table class handles the insert logic for our contact records so every time we need to insert a new record, we call its $insert, so every time we need a new contact number, we call our $insert, so why not put the logic to determine a new contact number in our $insert of our tContact table class.
First we change our $validateRecord and remove the check that validates the number, the user won't be filling in the contact number. Then we change our $insert

tContact.$insert
----
local variable tmpNewID kCharacter(25)
local variable tmpResult kBoolean

;  Get a counter value for our contact number

Calculate tmpNewID as $cinst.$getNextValue('ContactNumber')
If tmpNewID<0
  Quit method kFalse
End If

Calculate $cinst.Number as jst(tmpNewID,'-8P0')

Do inherited Returns tmpResult
If not(tmpResult)
  Calculate $cinst.Number as ''
End If

Quit method tmpResult

We also change the contact maintenance window so the contact number field is always disabled and we change the record browser base window to redraw on a successful save.
Now it's time to build our broker. Now this broker is going to be really simple, it's only going to support windows and it's only going to support messages to inform of record updates. You can build a more complex broker if you want.
I've created an object base class with some basic error handling that we can extend later on. I won't detail it here right now as we're not really using it for the broker.
We start with the constructor for the broker.

class variable cSubscribeList kList
oBroker.$construct
----
Do inherited 

If cSubscribeList.$cols.$count=0     ;; only init our subscription list once
  Do cSubscribeList.$cols.$add("instanceName",kCharacter,0,250)
  Do cSubscribeList.$cols.$add("tableName",kCharacter,0,250)
End If

Our broker class contains a class variable in which we keep the information about the windows that have subscribed to the broker. This is a class list since we will have an instance of our broker within our window that is subscribing and an instance of our broker in the window that will be sending the messages. You can think of the broker object as a doorway to our subscription list that we can construct and throw away as we please.

oBroker.$subscribe(pInstanceName, pTableName)
----
If cSubscribeList.$search($ref.instanceName=pInstanceName&$ref.tableName=pTableName,kTrue,kFalse,kFalse,kFalse)=0
  Do cSubscribeList.$add(pInstanceName,pTableName)
End If

Quit method kTrue

This method allows us to subscribe to our broker. It tells the broker we want to receive information about changes to a specific table.

oBroker.$unsubscribe(pInstanceName[, pTableName])
----
If cSubscribeList.$search($ref.instanceName=pInstanceName&($ref.tableName=pTableName|pTableName=''),kTrue,kFalse,kTrue,kTrue)>0
  Do cSubscribeList.$remove(kListDeleteSelected)
End If

Quit method kTrue

Our unsubscribe method unsubscribes our window. We can unsubscribe all our subscriptions at once by only specifying the instance name. Note that in the window base class $destruct method I've put the following code:

wBase.$destruct()
----
local variable tmpBrokerObj kObject(oBroker)
Do tmpBrokerObj.$unsubscribe($cinst().$name)

This will ensure any subscriptions still active for our window will be unsubscribed when a window closes, I never have to worry about this anymore.
Now for the code that will actually send the messages around:

oBroker.$sendmsg(pSenderName, pTablename, pDataList)
----
;  pDataList could be a list of primary keys that have been effected
For cSubscribeList.$line from 1 to cSubscribeList.$linecount step 1
  If cSubscribeList.instanceName<>pSenderName&cSubscribeList.TableName=pTableName
    ;  inform each of the subscribed windows
    If $iwindows.[cSubscribeList.Instancename].$methods.$findname('$receiveBrokerMsg')
      Do $iwindows.[cSubscribeList.instanceName].$receiveBrokerMsg(pSenderName,pTableName,pDataList)
    End If
  End If
End For

Our send logic simply loops through the subscription list and sends the message to any instance that has subscribed to the table that has been changed unless it's the window that send the message.
pDataList is a list variable that should contain at least the primary key column of each record that was changed in the table.
For our example however we'll make it easy for ourselves. We'll change our wBaseRecordBrowser.$evSave method to automatically call the $sendmsg method of the broker when we save a record and just send the row being changed in its completeness:

wBaseRecordBrowser.$evSave
----
local variable tmpBrokerObj kObject(oBroker)
[...]
If tmpSuccess
  Do gDatabase.$commit()
  ;  Tell our broker, we updated something...
  Do tmpBrokerObj.$sendmsg($cinst().$name,iRecordRow.$servertablenames,iRecordRow)
[...]

Now all we need to do is use our message. When a user changes an exist or adds a new contact type, we want our contact window to reflect this. So we handle the message on our contact window. Two changes are needed:

local variable tmpBrokerObj kObject(oBroker)
wContact.$construct
----
;  First define our record row for the correct type!
Do iRecordRow.$definefromsqlclass('tContact')

;  Tell the broker we want to know about ContactType changes
Do tmpBrokerObj.$subscribe($cinst().$name,'ContactType')

;  Then do the default construct!
Do inherited

wContact.$receiveBrokerMsg(pSenderName, pTableName)
----
Switch pTableName
  Case 'ContactType'
    ;  Rebuild our contact type list!
    Do $cinst.$objs.iContactTypeList.$buildList()
  Default
End Switch

Note that we've also changed the $construct code and added a $buildList method to the iContactTypeList field on this window. The $buildList will rebuild the contents of the contact type list and make sure the correct line is reselected. In this case its easier to just retrieve the updated data from the database but we could also have looked up the data within the contact list using the pDataList parameter.
Now open both windows, look up a contact record and change the description of the contact type for that contact and you'll see it gets updated.

Part 3: Transactions

This is a repost of my old tutorial I wrote in late 2007. I'm presenting them as they where.

Transactions can be a very complex subject and we'll only touch it a little deeper here then we've done so far. We'll look at why transactions are needed and how Omnis implements transactions cross platform. We'll then look into the short comings of what we've done with transactions so far and see how we can improve on them.
Now keep in mind, not all databases that we can use with Omnis support transactions. Our DAM object has a property called $allowtransactions that will tell us if the DAM thinks if transaction support is available. I generally make it a rule not to build applications based on databases that don't support transactions. It simply gives me to many headaches :D But you can't always avoid this.
So what is a transaction? Well a transaction is a mechanism that ensures that any action that happens with in that transaction is seen as a single unit of work. That means that until you finish (commit) the transaction all actions that have happened within that transaction can be undone if something goes wrong. The transaction always either succeeds or fails in its entirety.
An example, let's say that you've created a window where you can enter orders into your system. Your order consists of an order header record that contains an order number, the link to the customer and the total amount for that order, and you have a number of order detail records that specify the individual items that make up this order and their price.
When you save this order you want to save the order in its completeness. If you safe the header but there is a problem saving the details, the data in your database suddenly is inconsistent. You have an order header stating you sold $100 worth of goods, but if only half of the details are saved, the details only add up to $50.
A transaction lets you protect this. If you start a transaction before you save any changes to the database, then insert/update your order header, then insert/update/delete your order details and finally commit the transaction you will be certain the database will not make those changes permanent until they have all happened. If anything happens in between, you can simply do a rollback and all changes since the start of the transaction are undone.
A transaction is also rolled back if something unexpected happens such as when the database connection is lost, or your application crashes or your customer trips over his/her power cables.
You can be sure you're changes are not applied until you execute your commit and that certainly safes a few nightmares and helps keep your database consistent.
Note that some database platforms require you to always start and commit a transaction to execute a query. Oracle I believe automatically starts a new transaction when you commit the last transaction. If you forget to execute a commit your transaction stays open and when you later close your application, all your changes will be lost (hurrah). Omnis luckily helps you there (autocommit).
Sybase ASE, which I have the most experience with mostly does not require a transaction to run queries, any query run when no transaction is started is automatically committed.
To support transactions Omnis has a number of different transaction modes that are important to look at first. You can change/check the transaction mode through the $transactionmode property of the DAM object. It has the following values:

• kSessionTranAutomatic, this leaves it up to Omnis, Omnis will automatically start a transaction for each query executed if the platform requires this, and commit the transaction afterwards (similar to the old "autocommit on" in Classic). This means you do not need to, and I believe can not, start your own transactions.
• kSessionTranManual, Omnis now lets you do your own thing and Omnis will not automatically start or commit a transaction. You now need to call $begin, $commit and $rollback to do session control (this is similar to the old "autocommit off" in Classic). So now you have to do your own transaction control.
• kSessionTranServer, basically means hands off to Omnis. This means that you take over from Omnis and do everything yourself by executing the right SQL statements on the database. There is a big difference between kSessionTranManual and Oracle is a good example here. Note that I just mentioned that Oracle automatically starts a new transaction when you do a commit? That means that if you execute a "begin transaction" SQL command you actually get a nested transaction. That in turn means that you need to execute two commit statements to commit your transaction, one for you own transaction, and one for the Oracle one! So you really need to know the ins and outs of your platform to properly handle this mode, but it does give you the most flexibility

Transactions have another benefit to them. To be able to undo your changes in case of a rollback your database will lock any record to which you've made changes. This means no other user can touch those records until you are done with it. Look at our order example again, no user can see or touch (the changes to) our order until we've fully created/updated it, so there is no possibility that another user is running an order rapport at the same time and suddenly find the total order amount to include our new order amount, but is missing the detail information.
There is an important difference between platforms to know about though. Again looking at the difference between Oracle and Sybase they handle locking differently when another user tries to select the data. If we look at the default behaviour of these platforms we'll see that with Oracle, when a user is running an order rapport, and another user is changing an order at that same time, the rapport will be run as if no changes have happened. Oracle will see that the other user has locked the record(s) and simply retrieve the data as it was before the user started modifying the record(s).
Sybase on the other hand will wait until the user modifying the record is finished and either commits or rolls back his/her transaction. This means that the user running the order rapport will have to wait a little longer but after waiting he/she will get the rapport including the change made by the other user (personally I like Oracle's approach better).
You can do, what is often referred to as, a dirty read if you really want to. Again the different platforms will have different ways of implementing this and different rules apply (which is why I generally keep my hands off of it). A dirty read lets you select locked data in the database. That means that I can run my rapport and I actually get my results including the changes the other user has made even if a possibility exists that that user may rollback his/her transaction. On Sybase this is the only way to get my rapport without waiting for the other user to finish, but if this is your reason, I suggest you go back to the drawing board, you're doing something wrong already. I must add to this that there are some viable situations where you may want to do this. For instance in a search window you really don't care that you might get a result once in a while that may change again before you use it. But our order rapport example is one where I would never use dirty reads simply because it could make my order rapport incorrect if I read only part of the changes a user is making while my rapport is running (my order header amounts may not add up to my order detail amounts!).
Note: there are some databases out there that either allow a default to be set to, or always perform dirty reads.
When two users try to update the same record the first user will lock that record and the second user will always have to wait until the first user has finished. As you may remember we used this nice little fact to protect our auto numbering approach in tBaseCounted.$getNextValue().
Finally, I can lock records on selects but this is usually not done by default, again different databases implement this with slightly different SQL syntax. Any select on a table will result in the record being retrieved being locked. I generally don't use it.
So what about nested transactions? What are they and why do I need them? Well lets start by saying that the way Omnis implements transactions they are not supported by default, you will have to create support for them. Look at our tBaseCounted.$getNextValue() method. It starts a transaction, updates our counter table, gets the result and commits the transaction. Since there is only one update we're not really using our transaction to safeguard multiple changes here, but we're using the locking side effect from transactions to safeguard giving out unique values.
However, now I want to implement my order saving logic. My order saving logic looks like this:
1) get the next value for the order header
2) insert the order header
3) get the next value for the order detail
4) save the order detail
5) repeat 3 and 4 for each order detail
So what if I start a transaction before I execute these commands? Well we then go to step one, and in step one, uhm, I get a begin transaction? But I already have one! Hmmm.. Well we don't support nested transactions so it gets ignored, then it gets my next value, and it commits.. Oh oh.... That's the end of our transaction, right then and there.
The rest of our save thus happens outside of my transaction, if somewhere down the line a problem happens, well bad luck, there is no more transaction and its not going to rollback the way I expect it to.
Nested transactions allow me to do this, whenever a begin transaction is encountered I nest the transaction. This means that when a commit is encountered, it doesn't actually commit anything, it just goes back one transaction, until finally there are an equal amount of commits as there where begins and then my unit of work actually gets committed. If anywhere between the first begin and where I am right now there is a rollback, all changes are undone regardless of any previous commits.
Okay, now there is a catch here. Again different platforms implement nesting differently, some don't support it. Most will rollback all nested transactions on a rollback so you have to be careful that after a rollback, you know that you are no longer in a transaction. You don't need to do an equal number of rollbacks to begins.
We do not want to assume anything about the way that our platform would handle this, so we'll have to handle this within Omnis. How can we implement this?

class variable oDatabase.cNestCountList (kList)
instance variable oDatabase.iNestIdx (kLongInt)

oDatabase.$construct
----
;  Start in automatic mode (should be the default)
Calculate $cinst.$transactionmode as kSessionTranAutomatic

;  When we copy our database connection object around we share the same database connection
;  But we do not share the same instance variables as a copy is a new instance
;  So if we did our nesting count with an instance variable we're in trouble.
;  Class variables are shared amongst instances so we need to use those but we do
;  Want a counter per database connection. This is the way we work around that :)
If cNestCountList.$cols.$count=0
  Do cNestCountList.$cols.$add("NestCount",kInteger,kLongint)
End If
Do cNestCountList.$add(0)
Calculate iNestIdx as cNestCountList.$linecount
;  iNestIdx is copied to each copy of our database connection and thus they all point to
;  the same entry in our counting list!

Two thing happen in our new $construct method for our database. First we set the transaction mode to automatic so we don't have to worry about it when we're not using transactions yet. Second we initialise a class variable list to keep nested levels. Remember that in our table class we assign our database object instance to $sessionobject of our tableclass? This actually creates a copy of our object (a second instance) that shares the same database connection but otherwise has a copy of the instance variables our original object instance has. Once we start altering those instance variables their value becomes different from the original. This is not wanted behaviour!
Class variables are shared amongst all instances of our object however simply using a nested counter directly doesn't work either. If we can assume we only have one database connection or we never start transactions in parallel on different database connections I guess we could but I find that assumption very dangerous. That's why I keep a list. Every time I instantiate a connection object it adds a line to this list and the instance variable iNestIdx points to this line in the list. Every time the connection instance gets copied it copies that same iNestIdx value and thus points to the same line in my class list. Each line in my class list thus uniquely represents a database connection.
(note: with object references we should be able to fix this as well and just use instance variables, which is much nicer, but I am not sure if object references work with $sessionobject and haven't experimented enough with it to answer that)

oDatabase.$begin()
----
If cNestCountList.[iNestIdx].NestCount=0
  ;  Set manual transaction mode (autocommit off).
  Calculate $cinst.$transactionmode as kSessionTranManual

  ;  No transaction started, so we start one!
  Do $inherited.$begin()

  Send to trace log (Diagnostic message) {oDatabase.$begin(): Transaction started}
End If

Calculate cNestCountList.[iNestIdx].NestCount as cNestCountList.[iNestIdx].NestCount+1

Send to trace log (Diagnostic message) {oDatabase.$begin(): Transaction nest level is now [cNestCountList.[iNestIdx].NestCount]}

Quit method kTrue

We've overridden our $begin method and changed it so we only start a transaction when no transaction is started. Before we start the transaction we switch the transaction mode to manual.
Notice the use of trace log with diagnostic message turned on. This means these message do not get logged to the trace log until you rightclick in the trace log and select "Log diagnostic messages". The trace log is a great tool for checking if your code runs correctly. In this case it is always important to check if your save logic correctly handles nested transactions.
Also notice the way the classlist is used for keeping the nested transaction count.

oDatabase.$commit()
----
If cNestCountList.[iNestIdx].NestCount>0     ;; Still transactions open?
  Calculate cNestCountList.[iNestIdx].NestCount as cNestCountList.[iNestIdx].NestCount-1

  Send to trace log (Diagnostic message) {oDatabase.$commit(): Transaction nest level is now [cNestCountList.[iNestIdx].NestCount]}

  If cNestCountList.[iNestIdx].NestCount=0
    Do $inherited.$commit()

    ;  Return to automatic mode...
    Calculate $cinst.$transactionmode as kSessionTranAutomatic

    Send to trace log (Diagnostic message) {oDatabase.$commit(): Successful commit}
  End If

  Quit method kTrue
Else
  Send to trace log (Diagnostic message) {oDatabase.$commit(): Warning: Commit without a transaction!}

  Calculate iLastError as "Warning: Commit without a transaction!"

  Quit method kFalse     ;; Very likely we've already had an auto commit but we may still want to inform the user!
End If

The commit logic will decrease the nested transaction counter and only when 0 is reached actually commit the transaction. After commit we bring the transaction mode back to automatic.
If we get a commit when there is no transaction open, we'll log a warning.

oDatabase.$rollback()
----
;  $rollback() Rollback session transaction

If cNestCountList.[iNestIdx].NestCount>0     ;; Is there a transaction open?
  Calculate cNestCountList.[iNestIdx].NestCount as 0
  Send to trace log (Diagnostic message) {oDatabase.$rollback(): Transaction nest level is now [cNestCountList.[iNestIdx].NestCount]}

  Do $inherited.$rollback()

  Send to trace log (Diagnostic message) {oDatabase.$rollback(): Successful rollback}

  ;  Go back to automatic mode!
  Calculate $cinst.$transactionmode as kSessionTranAutomatic

  Quit method kTrue
Else
  ; Could be that we already rolled back the transaction.
  Send to trace log (Diagnostic message) {oDatabase.$rollback(): Warning: Rollback without a transaction!}

  Quit method kTrue     ;; We do return true because most likely we just encounter a rollback from a lower nest level.
End If

The rollback is always immediately performed and brings the nested count back to 0. If the rollback is performed when no transaction is run we log this but do not see this as an error.
This last bit is very important because it allows us to do some defensive programming. When I use transactions within an object I can not assume that we're going to use this transaction within another transaction or not. I will always end any begin with a commit or rollback. This means that if I nest transactions I will have an equal number of commits+rollbacks (a combination is possible) even if I do not need all the rollbacks. But it makes my code safe, I don't need to make any assumptions about the code calling my method or about any method I call from my method that also use transactions.

;  Always structure transaction handling like this:
Do $cinst.$sessionobject.$begin()  ;; using $cinst.$sessionobject as an example but could also be any other connection instance.

;  Perform any changes, calls whatever but do not escape from the code!

;  We should always reach this point, every $begin has its $commit or $rollback!
if tmpSuccess ;; on success we commit
  Do $cinst.$sessionobject.$commit()
  Quit method kTrue
else ;; on failure we rollback
  Do $cinst.$sessionobject.$rollback()
  Quit method kFalse
end if 

So lets put this into practise. We revisit our wBaseRecordBrowser. First I've made a few changes to this window that I will only mention. $updateDetail has been renamed to $$updateDetail to emphasise this is an internal method that can be overridden. Again note that this is not enforced by Omnis but my own naming convention for protected methods.
I've also added a $$saveDetail method that we don't really need right now but I've introduced them for later. This handles saving of any detail records so if we ever use our wBaseRecordBrowser for an order header/order detail window we can handle the saving of order details in this.
Finally I've added a $$detailInTransaction method that specifies whether saving the details should be part of the main transaction or not. With our order header/detail example this should be answered with a "true" but for saving customers with for instance address details we may return a false allowing the customer to be created but the address information to fail.
Some of the other methods have been adjusted to take these changes into account. Also note I did a small fix in tBaseCounted.$loadPrev() and in tBase.$newRecord().
Our new transaction handling we've placed here:

wBaseRecordBrowser.$evSave()
----
If iRecordRow.$validateRecord()
  Calculate tmpSuccess as kTrue     ;; Assume we'll be successful
  Calculate tmpNewRecord as kFalse     ;; Assume this is an existing record

  ;  Begin a transaction on our global database connection, this one is used by our record row aswell!
  Do gDatabase.$begin()

  If iRecordRow.C1>0
    If not(iRecordRow.$update(iRecordRow))
      Calculate tmpErrorMsg as iRecordRow.$GetLastError()
      Calculate tmpSuccess as kFalse
    End If
  Else
    If not(iRecordRow.$insert())
      Calculate tmpErrorMsg as iRecordRow.$GetLastError()
      Calculate tmpSuccess as kFalse
    Else
      Calculate tmpNewRecord as kTrue
    End If
  End If

  If tmpSuccess     ;; Only save details if our main record was saved
    If $cinst.$$detailInTransaction()
      If not($cinst.$$saveDetail(tmpErrorMsg))
        If tmpNewRecord
          Calculate iRecordRow.C1 as 0     ;; Reset the ID, with the rollback it will be undone...
        End If

        Calculate tmpSuccess as kFalse
      End If
    End If
  End If

  If tmpSuccess
    Do gDatabase.$commit()

    ;  Save of details should be outside of our transaction?
    If not($cinst.$$detailInTransaction())
      If not($cinst.$$saveDetail(tmpErrorMsg))
        OK message Error {[tmpErrorMsg]}
        Quit method kFalse
      End If
    End If

    Do $cinst.$setState(1)
    Quit method kTrue
  Else
    ;  Always rollback before informing the user or the records will be locked until 
    ;  the user returns from his/her coffee break!
    ;  Note, its possible we've already rolled back, actually we should assume this, 
    ; but better safe then sorry..
    Do gDatabase.$rollback()
    OK message Error {[tmpErrorMsg]}
    Quit method kFalse
  End If
Else
  OK message Error {[iRecordRow.$getLastError()]}
  Quit method kFalse
End If

Our save has chanced a bit, first we now start a transaction before we do any changes, then we do the changes, and when we're done, we do either a commit or a rollback depending on success or failure. Now on failure it is possible that we've already done a rollback, but we're not sure, so we always do the rollback. In our case, we will have gotten a rollback if our changes failed on getting a new ID value, but won't if the failure happened insert data into our database.
Note that we do not give any feedback to the user until we've done our commit or our rollback.
Open up the trace log (Tools->Trace log) and enabled logging of diagnostic messages.
Now try changing or creating some records with one of our two windows. We should see in our trace log that the transaction is started, nested and eventually committed.
Lets have some fun with this. Edit wContact and override its $$saveDetail. Change its code to:

wContact.$$saveDetail(pErrorText)
----
Calculate pErrorText as "We're just testing.."

Quit method kFalse

This will ensure any save on our contact details will fail :D Note that $$detailInTransaction is set to kTrue by default.
Before you begin, do a select * from Counter using an SQL client tool for your database to see the current contents of the Counter table.
Now try creating a new contact record. First we should see in our trace log that it will start a new transaction, it will nest a transaction (for out $getNextValue) it will commit that transaction, now it creates the contact record but we don't see that, finally it rollback the transaction due to our simulated error.
If we execute our selection in our SQL client we should see that nothing in this table has changed. If we cancel our editing and we browse through the records in our table, we should not be able to find our contact record. It is as if we've never tried to save our contact.
Now lets have some more fun, lets do something we should never ever do!

wContact.$$saveDetail(pErrorText)
----
OK message Keeping our transaction open {Go drink some coffee and make your collegues very upset....}

Quit method kTrue

Try and create a new contact again but leave the OK message on the screen!
Our transaction is still open, our Counter table record should now be locked, our newly inserted record is also still locked (though nobody will notice that most likely).
Now try and do your select in your SQL client again. Three possibilities here:
1) you get results but you should notice that your counter has not increased (Oracle, etc.)
2) you're now waiting.. and waiting... and waiting.. (Sybase ASE, MSSQL, etc.)
3) your database defaults to dirty reads and you can see the change after all (Sybase Anywhere as it turns out)
Press OK in your OK message.
Only after you press OK and the changes are committed then either your other connection should be unblocked and you should see the end results, or you can rerun the query and now see the table having been updated.
If you start the application twice (start Studio twice) and logon to the same database, and you insert a contact with one of the applications but do not press the OK button, and you then try to insert a contact with the other. Or if you try and edit the same record, you should also notice that one application waits for the other.
This is also why you should never put an OK message within a transaction :)
Finally, deadlocks, the achillis heel of transactions. Take the above example where one user is waiting for the other user to finish his or her changes. What if the other user is also waiting for the first? Then they are waiting for each other and they can wait till the end of time. This is called a deadlock.
Now in our little application so far deadlocks are not very likely, our transactions are far to simple. But take our order header/detail example and we have introduced a risk, well at least with Sybase ASE in page locking mode :D Here it is possible that a page on the counter table is locked that contains both the counter for the header and detail table. If user one updates an order header, and user two insert a new order header (and thus updates the counter) then it is possible that the insert from user #2 is locked by the update of user #1 (page locking, the insert page may be the update page!) and user #1 gets locked by user #2 as he/she tries to add a detail to the order and thus needs access to the already locked page of the counter.
Luckily Sybase these days supports row locking as do most major database vendors and this would work fine. But as transactions become more complex and effect more records, the risk of deadlocking increases.
Two important rules to follow:

• Always insert/update your tables in the same order. Don't create your header and then your details, but in your update first update your details and then your header.
• Try and keep transactions small, don't overdo it. For instance, if you make a quick entry window where a new customer can be entered together with the first order he/she places it makes little sense to put that entire action in a transaction. When the customer is created, commit that, if the order creation fails, only rollback the order creation, then treat the customer as a returning customer from that point onwards.

The attached library is again an Omnis Studio 4.2 Unicode library, the script to create the required database is attached to tutorial part 2.
----
Disclaimer: I'm sure some of the listers will recognise their ideas in this tutorial in one form or another. Some ideas are truly mine, some are inspired by what I've learned in over 10 years of conferencing and reading the list. It would be an impossible task for me to list the many many people who I owe thanks to. I'm sure you know who you are my friends. I take only credit for taking the time to put this series together. Use the code you find in this tutorials and the library/libraries attached freely and as you see fit. I take no responsibility and will not be liable for any damages resulting directly or indirectly from using the information I present here.

Part 2: Creating base classes to speed up development of your application

This is a repost of my old tutorial I wrote in late 2007. I'm presenting them as they where.

Okay, it's time for part two:)
We'll continue where we left off with our library, I've attached the end result to this blog entry again. If you open it you will see I've made a few changes to part one that go outside of our tutorial.
I've put some of my classes into folders. Folders are a great way to organise your library. Before you know it your library will consist of dozens of classes and you'll find yourself getting lost in them.
I've also removed our test window and test schema and table and replaced them with some schema and table classes we'll be using.
Finally I've altered my table base class slightly. The $sqlerror has been changed to retrieve all errors and we have a method called $newRecord. On hindsight I named $newRecord a little bit wrong because I originally implemented it for row variables but it works just the same for list values. It clears the values of the current row but its meant to be a little more intelligent. Right now it simply sets the value based on the column type, but in most of my applications I enhance this by conventions such as setting a column called "createdBy" to the user name or "createdOn" to the current date. It saves me the trouble of writing that logic time and time again.
Note: Please note the $$ prefix I use in the $$setFieldState method. This is a personal convention I use meaning this is a protected method. A protected method can be called from any subclass of this class but it is not allowed to call the method from outside. Omnis does not enforce this, from Omnis' perspective, this is a public method.
Attached to this blog is a small SQL file with some SQL code that will create the needed tables on the database. I like working with scripts like these to install databases because they allow me much greater control in creating constraints and indexs but they do tent to be platform dependent. You can off course also drag the schema classes in the library to the SQL browser (the reverse of what we did in the first section) and Omnis will create the tables for you. I'll show you some time later how you can write some code that will create all the tables in an empty database using the schema classes in the library.
A little information about this script. It creates 3 tables, Counter, ContactType and Contact. Counter is a special table as it allows us to keep a number of counters for auto numbers. Many SQL databases offer great native support for this, Sybase and Microsoft have identity fields, MySQL has the autonumber default property, Oracle has sequence objects, etc. If you commit to a certain platform I suggest you use this functionality. But since I can not assume anything about your platform choice, I'll show a solution that is cross platform that uses a counter table.
The other two tables, ContactType and Contact are my two data tables. All my data tables have something in common. They all start with an ID field. This is a field I generally do not show to the end user but that for my application is vital. Its a unique number that identifies my record and that I use as the primary key. This would be the field I would apply the auto numbering to. In our example I've used an integer field, which is fine for a small demonstration app like this, but for larger apps I'd suggest using a larger field type such as a numeric(15) field (maximum for a number 0dp field, at least when using Sybase).
My ContactType table and Contact table are related to each other through the field FkTypeID in the Contact table. In my script I also create a constraint that informs my database about this relation. This serves a number of functions:

• It makes sure that my database gives an error if I violate this relationship
• it documents this relation meaning someone looking at my database that is not formiliar with it, immediately knows this column points to the ContactType table
• I can query this information and build logic on it enabling me to automate things, though this is harder to do cross platform

Note: This time I'm not going to put every line of code in this blog entry, that would be crazy, I'll just be putting the important bits in.
I started by creating a new table base class called tBaseCounted. We set the $superclass of tBaseCounter to tBase. There are several methods in this class but I want to hilite a few of them:

tBaseCounted.$getNextValue(pCounterName)
----
Do $cinst.$sessionobject.$begin()     ;; Begin a transaction, very important!
Begin statement (Carriage return,Linefeed)
Sta: if not exists (select 1 from Counter where Name = '[pCounterName]') begin
Sta:   insert into Counter (Name, LastID) values ('[pCounterName]', 1)
Sta: end else begin
Sta:   update Counter set LastID = LastID + 1 where Name = '[pCounterName]'
Sta: end
Sta: select LastID from Counter where Name = '[pCounterName]'
End statement
If not($cinst.$statementobject.$execdirect())
    Do $cinst.$sqlerror('$getNextValue',$cinst.$statementobject.$nativeerrorcode,$cinst.$statementobject.$nativeerrortext)
    Do $cinst.$sessionobject.$rollback()
    Quit method -1     ;; Something went wrong!
End If
Do $cinst.$statementobject.$fetch(tmpResult,kFetchAll)
If tmpResult.$linecount=0
    Do $cinst.$sessionobject.$rollback()
    Calculate iLastError as "Couldn't retrieve counter value for an unknown reason!"
    Quit method -2     ;; No value?
Else
    Do $cinst.$sessionobject.$commit()     ;; We're good
    Quit method tmpResult.1.LastID     ;; We should have only 1 result!
End If

This does a couple of things. First it starts a transaction, now not all platforms will support this, and there is an issue with nesting that we'll re-address much much later, but its very important we do this if we're building a multiuser application because of the second thing we do.
We execute a query batch, through means of a statement block, that in one go either inserts a new counter or updates the existing counter and then returns the new value. Because we are doing this in a transaction the insert or update action will lock the record we're after. That means that by the time we select our value it is impossible for another user to do the same, they will be locked by us until we're done selecting our value and committing our transaction. This ensures we do not give out the same number twice.
There is a tiny tiny tiny theoretical risk that the first time a counter is used two users try to create the same counter but this will lead into one user encountering a primary key constraint error. In real life however you'll probably end up creating all counters from the get go and users will only be updating counters.
One more important thing I want to note. I'm using $cinst.$sessionobject and $cinst.$statementobject. During the $construct of my table class my global gDatabase was assigned to the $sessionobject property of my list and at that moment a statementobject was created. I am using these here however by accessing them this way I am making my code safe in case I later decide to use this object using a different database connection. My global database connection has become my default, but not my only means of using my code!

tBaseCounted.$insert()
----
Calculate tmpNewID as $cinst.$getNextValue($cinst.$servertablenames)     ;; Get a counter value based on our server table name
If tmpNewID<0
    Quit method kFalse
End If
Calculate $cinst.C1 as tmpNewID     ;; Assume the first column in our table (and thus in our list) is the primary key!
;  Do the original insert!
Do default Returns tmpResult
If not(tmpResult)
  Calculate $cinst.C1 as 0
End If

Quit method tmpResult

Here we've overridden the standard Omnis $insert method. Whenever we insert a record we now first get a new value from our counter table and assign it to the primary key. Since I've made a rule for myself that the first column of my table is always my auto numbering primary key I can make life easy and select the first column for this (you can use C1, C2, C3, etc to access columns regardless of their name).

tBaseCounted.$validateRecord
----
;  Override this method to do any validation on the data of the current record
Quit method kTrue

Ok, this is important, this method does absolutely nothing. Its there because I want to make sure my developers are aware that this method needs to exist in subclasses of this base class. As validation rules can be very different per table, I need to override this logic per subclass. Also by implementing this method in my base class I can build logic that assumes this method exists even though when I build the logic, I know nothing about how it will work. Also if after months I decide I do implement some generic validation logic, I have already created a place to put it.
There are also a number of other methods added to tBaseCounted that work together and that are specifically meant to be used for row variables:

• $loadRecord, loads a record with a certain ID
• $loadFirst, loads the first record in our database
• $loadLast, loads the last record in our database
• $loadPrev, loads the record previous to the current record
• $loadNext, loads the record next to the current record

Take a look at how these methods work, the code pretty much explains itself.
Now we create our schema and table classes for our ContactType and Contact tables by dragging them from our SQL browser into our library. We don't need a schema and table class for our Counter table as we won't access that directly.
Tip: Click on SQL Browser, select Options, go to the drag and drop tab pane and select the "create table and schema class" option to create both schema and table classes when you drag a table into your library. Note that Omnis creates the table classes with a T_ prefix, I tent to use t as a prefix, pick a standard you like but I do advise you to use a naming convention.
We assign tBaseCounted to the $superclass property of our two new table classes. In our two new table classes we only override the $validateRecord method, you can check the code but for now, it's really simple.
Now its time for some windows. We're going to make a couple of really simple maintenance windows for our two tables. Really straight forward. Our windows will be standard insert/edit/delete windows with a save/undo button and some browse buttons to allow us to browse through the records. The idea will be to place as much logic as we can in a base class and keep our final windows as clean as possible. Once we're finished creating another maintenance window for a new table in our database will be a matter of minutes. This is our payback on our investment.
I started with creating a window class called wBase. This will be a window class that will form the basis of all windows I'll be making. I've implemented only some basic logic within this class but it is very important logic. I've implemented what we call a state engine. This current state engine currently recognises 5 states:

• View state, no record selected
• View state, record selected
• Edit state, new record
• Edit state, existing record
• Direct editing

The way I've implemented this is that you can call the $setState method of the base class to change the state, this method then registers the current state for future reference and checks the objects on the window to enable/disable them according to the state. It also calls the toolbars and any subwindows and informs them about the state change.
The state engine will call the $getEnabled() method of an object if it exists and expects that method to return if that field should be enabled in the current state.
I base my visual settings fully on #STYLES. Go to the system classes folder and double click on #STYLES to look at them. Omnis creates most of the default styles, you will see that I have created copies of some of the styles suffixed with "Disabled". I toggle between the style and its disabled version to visually inform the user that a field is disabled.
(RD if you're reading this, GREAT ENHANCEMENT IDEA, a single style that lets me configure how a field should look when its enabled, and what it should look like when its disabled!).
More about styles some other day, needless to say: Use them, they are great!
I also have a toolbar base class called tbBase, again, this purely implements the state engine. Using this base class I've created two toolbar classes, tbBrowse and tbInsertEditDelete.
tbBrowse is a toolbar that will allow the user to browse through the records in a table:
When we look at the first button on this toolbar we see the following methods:

tbBrowse.FirstBtn.$event()
----
On evClick
    Do $cwind.$evFirst()

tbBrowse.FirstBtn.$getEnabled()
----
Quit method pick(iState,kTrue,kTrue,kFalse,kFalse,kTrue)

Our $event method simply handles a click event that sends a message to the window this toolbar is on. The actual logic thus is present in the window. This way this toolbar can be used for any sort of window that has any sort of browse logic.
Our $getEnabled() method is used by the state engine and we can see how this button reacts to our 5 states. The button is enabled in the two view states and in the direct edit state, but disabled in the two edit states.
The tbInsertEditDelete toolbar is a toolbar that gives us a nice insert, edit and delete button with a save and undo button to confirm our change. Sorry I couldn't find a nice delete icon and didn't feel artistic enough to make one :D

Now that we have our toolbars we can create the window where it all starts to come together. wBaseRecordBrowser is a base class that puts all the pieces of the puzzle together and its become really simple. I've added our two toolbars to this window but there are no fields on this window. There is a new instance variable called iRecordRow but we do not initialise it in our base class yet.
When we look at the code we see that except for one additional method, this class only implements the logic for the calls that the toolbars make to this window. Owh we also override our $construct but all we do here is call our $evFirst method, the same method the first button on our browse toolbar calls. This will ensure that when a subclass of this window opens, the first record of the table is shown.
The additional method is called $updateDetail and there is no logic in there. This method gets called after a new record is loaded into the screen and allows any subclass of our window to load additional details into the window or perform additional logic that is required. We may or may not need it.
When we look at the methods of our first toolbar, our browse toolbar we see that the logic follows a simple pattern:

• call the appropriate method of our row variable to get the first/last/previous/next record
• if successful call $updateDetail, set the correct state depending on whether a record was found and redraw the window
• if we fail, give an error

When we look at the methods of our second toolbar, our insert/edit/delete toolbar, the logic also remains pretty simple.

• For the insert button we first clear the record and then change the state.
• For the edit button we simply change the state (note that the edit button is only enabled in a state where a record is available that we can edit!)
• For the delete button we ask the user if he/she is sure, and the call the $delete method of our row variable and act according to the result
• For the save we either call the $insert or $update method of our row variable and act according to the result, finally we change the state back to view.
• Last but not least, for the undo we simply reload the current record and change the state, off course after asking the user if he/she is sure

Okay, at this point we've done all the hard stuff. Our base classes are finished, we've spend alot of work planning our logic and building a structure that will allow us to produce. So lets do that.
Now remember we've already dragged our tables from the SQL browser into our library, in doing so created the schema class and the table classes, we've subclassed our new table classes from tBaseCounted and last but not least, we've implemented the $validateRecord method for our new table classes. These are roughly the steps you would have to repeat for each new table that you want to introduce to your application. Piece of cake right?
Creating our first window is just as easy. Create a subclass of wBaseRecordBrowser and call it wContactType.
Now override the $construct and change the code to this:

wContactType.$construct
----
;  First define our record row for the correct type!
Do iRecordRow.$definefromsqlclass('tContactType')
;  Then do the default construct!
Do inherited

All we do here is define our iRecordRow (remember, it was created in our base class) so it uses our table class (which was subclassed from tBaseCounted and thus contains all that cool logic we implemented).
Now we need to create fields on our window for our table so our window looks like this:
We have 3 fields on this. A background field which is just a label. An entry field where we set the $dataname property to iRecordRow.Description and a checkbox field where we set the $dataname property to iRecordRow.IsPerson and we change the $text property.
Visually we don't need to do anything else.
We do need to implement a $getEnabled() method for the entry field and the checkbox. Both contain a single line of code:

Quit method pick(iState,kFalse,kFalse,kTrue,kTrue,kTrue)

And thats it.
Really.
Thats all. Press CTRL-T and you'll see...
Lets do another one.
Create a subclass of wBaseRecordBrowser and call it wContact. Change the construct so that iRecordRow uses tContact. Add fields to this window so you can edit the record, add a $getEnabled() to each of these fields, and it should work.

Now if you look at the version I made, you can see I made a dropdown list for the column FkTypeID since this is a foreign key to our contact type table. I have to add a little more code for that:

wContact.$updateDetail
----
Calculate tmpFKID as iRecordRow.FkTypeID
Do iContactTypeList.$search($ref.ID=tmpFKID,kTrue,kFalse,kFalse,kFalse)

$UpdateDetail simply sets our dropdown list to the correct line for the current record. If the line can't be found the list will have $line = 0 which is fine by me.

wContact.iContactTypeList.$construct
----
;  Init our contact list
Do iContactTypeList.$definefromsqlclass('tContactType')
If iContactTypeList.$select('order by upper(Description)')
    Do iContactTypeList.$fetch(kFetchAll)
Else
    OK message Error {[iContactTypeList.$getLastError()]}
End If

This is a $construct on our dropdown list. $construct methods on fields get automatically called when a window is opened and are run before the $construct of the window runs. Here we do nothing more then load the values from our contact type table into our list so that our dropdown list is populated.

wContact.iContactTypeList.$event
----
On evClick     ;; Event Parameters - pRow ( Itemreference )
    Calculate iRecordRow.FkTypeID as iContactTypeList.ID
    Do $cinst.$$setFieldState($cinst.$objs.CustomerName)     ;; Changing this dropdownlist could influence the state of this field!
    Do $cinst.$redraw(kTrue,kTrue)

Finally we have to react when the user selects a line from our dropdown list. We update our current record with this information but we also reapply the field state for our customer name field. This is because this field is not only dependent on the state, but also on the value of ContactType.IsPerson.
All that's left now is opening our windows from a menu. We can test our windows by pressing CTRL-T because we are still using a single library and a single task but that's not very use full for a user.
If you look at mMain you will see I'm now opening these windows.
Ok, there are two things missing in this tutorial that I've left out a little bit on purpose. There is enough in this tutorial for you to add this logic yourself, at least for the most part.
First, the user is now still forced to enter a customer number manually. It would be nicer if the system generates a new number for him/her. We can simply disable the customer number field by removing the $getEnabled method and disabling the field. But what to do about auto numbering this field? Hmmm.. where would be the best place? In the window? No that's not it... In the $validateRecord? No that's not it either.....
Second, the user can't search for a record. Our database is still small so there is no problem here, but wouldn't it be nice if the user could search? In one of my next tutorials we'll be building a separate search window but there are other options. Here is a suggestion, add a search button to the browse toolbar and introduce a new state (the search state). When the user presses the search button the window should go into a search state meaning that he/she can no longer browse, he/she can no longer insert/edit/delete/save/undo, he/she can fill in all the information on the screen and finally he/she can press the search button again (or another button) that will cause the system to look for a record in the table that matches what the user has entered.
Let me know where you would put what logic, how you would do this, surprise me :)
I will add a solution myself in the near future but since this is not my preferred way of designing windows I won't max it out.
I hope you enjoyed this part of the tutorial, took me a lot more time then I expected.
----
Disclaimer: I'm sure some of the listers will recognise their ideas in this tutorial in one form or another. Some ideas are truly mine, some are inspired by what I've learned in over 10 years of conferencing and reading the list. It would be an impossible task for me to list the many many people who I owe thanks to. I'm sure you know who you are my friends. I take only credit for taking the time to put this series together. Use the code you find in this tutorials and the library/libraries attached freely and as you see fit. I take no responsibility and will not be liable for any damages resulting directly or indirectly from using the information I present here.

Part 1 : Connecting to a database

This is a repost of my old tutorial I wrote in late 2007. I'm presenting them as they where.

All right, time for my first productive blog entry so we'll be doing a little more then ranting this time I hope. I'm going to try and make this into a little series in which I'll discuss a number of steps in building a small application. In these steps I'm going to try to keep to the basics and not dive into endless amounts of detail, my goal is to write some tutorial style articles that will help someone on their way in the wonderful world of Studio.

I'm going to assume you've started Omnis Studio a few times already and know your way around, I won't be telling you which buttons to push or tell you what a 'calculate as' 4GL command does. I'm also going to try and stick to the information you need to obtain the goal of working with Studio and not stray into to much theory or detail where this is not needed. In that way this will be a hands on explanation. I might go into more detail or complexity later on in the series when I revisit a topic. For instance I may handle database connections now, but an advanced topic like transactions later.
Today we'll handle the basics of setting up an application and connecting to a database. We are after all mostly busy with building client-server software, that's what Omnis is for.
The first thing we need to do is create a new library, we'll call it demolib.lbs. Omnis automatically creates something called a startup_task. This is a task class that is automatically instantiated when the library is opened and is automatically destroyed when the library is closed. Any window, menu, report or other entity that is instantiated within the context of a task lives within the scope of that task but knows nothing about anything that exists outside of that task. Tasks can be very powerful tools when used properly but also very confusing especially to a new developer. Our life for the time being is made much more simple if for now we'll just assume that our startup_task is the only task that we have, that everything exists within its context and we thus do not have to worry about what it is exactly and how it works. To be honest, for most of you out there, you'll probably never have to deal with any situation different then that unless you start working with remote tasks or with applications consisting of multiple libraries, though in this last case you can still work with a single startup task. So for now I'll make some bold statements that apply only when your startup task is your only task, just to keep things simple.
So what do we need to know right now about the startup task?

• just like with every other class, when the startup task gets constructed its $construct method is called. This simply means that when your application is loaded, and the startup task gets instantiated, its $construct gets called. You can thus use its $construct to do any initialisation of your application.
• when your application closes, the startup task is automatically destructed and thus its $destruct method is automatically called. You can do any cleanup of your application in this method.
• Any public method you add to your startup task can be access from any place within your application by simply calling $ctask.$methodName
• Any task variable you create is part of the startup task and can be accessed from any place within your application. Task variables are basically global.
• you can destruct and instantiate your startup task by right clicking on it and select Close Task and then Open task. When you manually destruct your task EVERYTHING within its scope gets destructed, any window you have opened, any menu you have installed, so it is completely equal to closing your library and reopening it. So its an easy way to restart your application.

Now that we have our library we need to create a few things before we can do something. We are going to setup a connection to our database so that's where we'll start. With Omnis Studio 3 a new set of DAM (Data Access Modules) were released that are affectionally known as v3 DAMs and we'll solely be looking at them.
Now we can use the v3 DAMS directly but I don't like to do that. I recommend creating an object class and assigning the DAM you want to use as its superclass. So we create an object class called oDatabase and assign its $superclass property to the DAM we'll be using.
This does two things for us:

• it makes the interface much more visible
• it makes it possible to enhance the default

I'll leave it to you to choose a DAM that fits your needs to subclass your database object from. The nice thing is that Omnis has made their DAMs in such a way that they all work the same and you are only limited by the capabilities of your database server software. Also for many tasks you will not need to know anything about your database server, Omnis already takes care of this.
For now I'll assume you will only be connecting to a database of a single vendor. If you want to create an application that connects to a range of different backends you can do two things:
1) change the $superclass of the object to use the correct DAM before instantiating the connection object. This will do in many cases
2) create a subclass object for each DAM for each backend you plan to use. This gives you the ability to put any code that handles differences between the platform within these subclasses.
But both we'll not go into right now.
I'm also going to assume you already have tables and such setup in your database.
For now we're going to add these methods to our oDatabase object:

instance variables:
oDatabase.iDefaultStatement Object ;; Our default statement object
oDatabase.iLastError character(1000) ;; the last error that we registered

oDatabase.$getLastError()
----
Quit method iLastError ;; Its always a good idea to create access methods like this

oDatabase.$logon(cHostname,cUsername,cPassword[,cSessionName])
----
;; cHostname character(1000) = name of the host/DSN/datafile
;; cUsername character(50) = name of the user
;; cPassword character(50) = password for the user
;; cSessionName character(50) = optional session name for pre V3 DAM compatibility

If $inherited.$logon(cHostName,cUserName,cPassWord,cSessionName)
  Calculate iDefaultStatement as $cinst.$newstatement('default') ;; Create a default statement object we can use to do run of the mill stuff
  Quit method kTrue
Else
  Quit method kFalse
End If

oDatabase.$fetchSQL(pQuery, pResultList [,pMaxResults])
----
If iDefaultStatement.$exec_sql(pQuery)
  Calculate pResultList as iDefaultStatement.$fetch(pMaxResults)     ;; Get the results
  Quit method kTrue
Else
  ;  Just some simple error handling, it will do for now...
  If iDefaultStatement.$nativeerrorcode=0
    Calculate iLastError as con(iDefaultStatement.$errorcode," - ",iDefaultStatement.$errortext)
  Else
    Calculate iLastError as con(iDefaultStatement.$nativeerrorcode," - ",iDefaultStatement.$nativeerrortext)
  End If
  Quit method kFalse
End If

What we've done here is change the $logon method so that after the user connects to the database a statement object automatically gets created. The DAM object that we've used to create our database object only handles the database connection, we need a statement object to actually perform a query on the database and to return results. As we'll find out later Omnis often creates and uses statement objects in the background where it needs to communicate with the database but there are situations where you quickly need to perform a query and get some results. Having a convenient method such as the $fetchSQL method I've implemented here can be very useful, but you might find you'll never use it. I just wanted to show an example of adding logic to our standard DAM and overriding its default behaviour. We actually use this in practise when connecting to a Sybase database through the Sybase DAM where the first action after connecting to the database server is choosing the database itself by performing a "use database" command so we've implemented that within the $logon of the Sybase database object.
I've also added some very very basic error handling, it's enough for now but we'll need something much nicer later on.
I never output errors directly to the screen within logic like this because I do not know what bit of my application is using this object. It could be that a scheduled process is calling this method and if there is an error and this error results in a nice error screen or OK message on the screen that halts my process, that doesn't work. I leave it up to the logic that calls this method to act upon the error and present it to the user. A scheduled process might do this by logging the error into an error log and emailing this to the user, while a window may still simply pop up the error message.
Now we need to use our object to actually connect to our database. Now I know I'm going to have some people complain about how bad the next thing I'll do is. But the fact is, 99% of the applications out there connect to a single backend and the few that connect to multiple database still always have one of those databases as their primary server. So to me it makes sense to instantiate your primary database connection as a task variable. Now this doesn't mean that you should write all your code in such a way that it can only work with this global connection, but you can make your life much easier by defaulting to this connection and only switching to an alternative connection when you really need to.
So the next thing we'll do is create a task variable in our startup_task, we'll call it gDatabase, make it of type object and subtype oDatabase. This will ensure it gets instantiated as oDatabase when we start using it.
We also need to create a logon window for it. We'll create a logon window that works only for our task variable gDatabase and is designed to be used when our application starts so it doubles as a welcome screen for our user. We could make a logon screen that takes a database connection object and a callback as parameters to its
$construct so it can be used in other situations as well, but for now that is overkill.
Create a new window class called wLogon and add 3 instance variables to it: iHostName, iUsername and iPassword. The type of fields these should be depends a little on the DAM you are using but they will be character fields of a usable size. Next create 3 entry fields on the window bound to these variables so the user can enter their values. Finally create a pushbutton on the window that the user should press to logon (make it a kBMok button).

wLogon.LogonBtn.$event()
----
On evClick     ;; Event Parameters - pRow( Itemreference )
  If gDatabase.$logon(iHostname,iUsername,iPassword)=kTrue
    Do $ctask.$afterLogon()     ;; Call our $afterLogon method of the task to finish the logon
      Queue close [$cinst().$name]     ;; Close this window
    Else
      OK message Error {[gDatabase.$getLastError()]}
    End If

Startup_Task.$construct()
----
Do $clib.$windows.wLogon.$openonce('*',kWindowCenter)     ;; Open our logon window

Startup_Task.$afterLogon()
----
Install menu mMain

When the user presses the logon button the system will try to logon using the credentials specified by the user. If this fails we'll present an error message to the user, for now simply the message returned by the DAM.
If the logon succeeds we call back to our task by calling the method $afterLogon and then we close the logon window.
The $afterLogon method for now simply installs the menu mMain. We haven't created it yet so its time to create it. Simply create a new menu class and call it mMain. Set its title to Main and add a single line to it and call that test for now. Don't add any code to it yet.
At this point we're ready to test the fruit of our labour. Right click on the startup task, chose Close task, right click on it again, and chose Open task. This should reset your application and if we've done everything correctly, it should now display our logon window. Try to logon to your database by filling in your credentials and pressing the logon button. If all goes well we should connect to our database and our menu should be installed.
If something doesn't work you'll need to place a breakpoint in either the $construct of the startup task or the $event of the button and step through the code. Or just take a look at the sample library I've attached.
Now its time to make our first little window that actually shows some data from our database. First things first. If you've paid attention you should have noticed an optional parameter called cSessionName with the $logon method of our DAM. If we specify a session name our v3 DAM starts acting like a pre v3 DAM. Now this is something you should not use in your application because session names are purely global and need to be unique and you'll run into trouble sooner or later. However there are some bits of the development environment that make life very easy after you specify a session name.
Lets change our code a little and try this out, change the appropriate line of code in wLogon.LogonBtn.$event to:

  If gDatabase.$logon(iHostname,iUsername,iPassword,'MYDATABASE')=kTrue

Now close and open the startup task again and logon to your database again.
Now in the studio browser open the SQL browser, you should now see your MYDATABASE database connection there and you should now be able to navigate to your databases tables and views.
Pick a table and drag it to your library. If you now look at your library it should have created a schema class for this table. Depending on your Studio settings it may also have created a table class for you, if it hasn't manually create a table class and set its $sqlclassname to point to your new schema class. Give the table class the same name as your schemaclass but prefix it with a t (at least that's the standard I suggest using).
Note: if you do not have a table in your database, manually create a schema class in our library and drag it to your database, Omnis will create the table in your database for you!
Now we'll create another table class but this one we won't connect to a schema class. We'll call this table class "tBase". Next we'll set the $superclass property of our table's table class to tBase.
Now add the following code to tBase:

tBase.$construct()
----
Calculate $cinst.$useprimarykeys as kTrue     ;; Please use our primary key field!
Calculate $cinst.$sessionobject as gDatabase     ;; Default to our global database connection

The first line tells the table class to use primary keys, I'm assuming you have primary keys in your tables and that you've identified them in your schema class. If you haven't go fire your DBA.
The second line assigns our database connection to our table class by default. This way we won't ever have to worry about that.
Because the table class we've created for our table is now subclassed from tBase it will automatically perform this logic when we use it.
We'll add another two methods:

Instance variable:
tBase.iLastError character(1000)

tBase.$getLastError()
----
Quit method iLastError

tBase.$sqlError(pErrortype,pErrorcode,pErrortext)
----
Calculate iLastError as con(pErrortype,": ",pErrorcode," - ",pErrortext)

This is just some very very simple error handling same as we've done for the DAM object.
Time to create a window that uses this. Create a window called wTest, add an instance variable to it called iTableList of type list and subtype it with your table class.
Change the mMain menu that you've already created and add the following line of code to the menu line you added to it:

mMain.Test.$event
----
Do $clib.$windows.wTest.$open('*',kWindowCenter)

We should be able to open our new window through our already installed menu as often as we want. But it wont do much yet. Note that you can always test a window by pressing CTRL-T but I personally like opening windows from menus to ensure it opens within the right context. This is however not an issue until you start writing applications with multiple libraries and/or multiple task classes.
Create a headed list box on your window and set its $dataname to iTableList. This binds the contents of the table list variable to the headed list box. I find it good practise to also set the name of a field to the same value as the variable its displaying if possible. It makes it much easier to recognise fields.
Now its time to add some code, hope you have some data in your table (if not, add one or two rows to it using a maintenance tool for now).

wTest.iTableList.$construct
----
;  This method is called when the field on the window is constructed, it is called before the $construct of the window itself
If iTableList.$select('order by description')
  Do iTableList.$fetch(kFetchAll)     ;; Fetch the data
Else
  OK message Error {[iTableList.$getLastError()]}     ;; Display to the user what went wrong
End If

This bit of code will fill the list with the data contained within the table when the window opens.
That's as far as I want to go today. Next time we'll continue with table classes and we'll create a little window that we can use to maintain some records with.
Please note that the attached library is a Studio 4.2 Unicode library!
----
Disclaimer: I'm sure some of the listers will recognise their ideas in this tutorial in one form or another. Some ideas are truly mine, some are inspired by what I've learned in over 10 years of conferencing and reading the list. It would be an impossible task for me to list the many many people who I owe thanks to. I'm sure you know who you are my friends. I take only credit for taking the time to put this series together. Use the code you find in this tutorials and the library/libraries attached freely and as you see fit. I take no responsibility and will not be liable for any damages resulting directly or indirectly from using the information I present here.

Omnis tutorial demo files

Just to get things started, here is a link to the original tutorial files of my "getting started with Omnis Studio" tutorial. I'll post the original tutorial posts here soon as well: Omnis demo libraries.zip

Checking out blogger....

As the software I was using on my personal website has become defunct and I can't seem to find the time to set up new software I figured it is time to give Blogger a try...

For my personal life I'm finding sharing stuff with family through G+ and Facebook suffices and I hardly post anything anymore anyway. At the same time however I get continuously asked about posts that uses to be on my old site regarding Omnis development.

If I continue with this blog it will mostly be for things related to my profession, primarily Omnis development but also related to my other hobbies such as web development, game development and languages like C++, Obj-C, PHP, Javascript, and who knows what'll come along.