Monday, 27 May 2013

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

Quit method iLastError ;; Its always a good idea to create access methods like this

;; 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
  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
  ;  Just some simple error handling, it will do for now...
  If iDefaultStatement.$nativeerrorcode=0
    Calculate iLastError as con(iDefaultStatement.$errorcode," - ",iDefaultStatement.$errortext)
    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).

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
      OK message Error {[gDatabase.$getLastError()]}
    End If

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

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:

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)

Quit method iLastError

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:

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

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

No comments:

Post a comment