Sw4   >   Sql   >   Database Sessions

Database Sessions

In order to communicate with a database from Omnis Studio you need to open a session with the database. Omnis Studio provides a number of DAMs (Database Assess Modules) as external components. You instantiate the external component using an object or object reference datatype variable. After you instantiate the external component you open a session with the database by sending a $logon message with the appropriate parameters. Some of the DAMs require that you first set the $database or $databasename property before you send the $logon message.

This section covers the oDBSession series of object classes which make it easy for StudioWorks developers to implement and access Omnis Studio sessions.

Note

The oDBSession series of object classes were introduced to StudioWorks in 2008-04. Prior to 2008-04, the oLogon series of objects were used for database sessions. These oLogon objects were designed prior to the object reference type variables, they depended on session names, and they did not support session pools. The new oDBSession series of object were built to overcome the weaknesses of the oLogon series objects and introduce other new features. An oLogon_adapter_to_dbsess object was created to provide compatibility to code written for the oLogon object. See the topic About > Version 2008-04-30 > Upgrading from oLogon to oDBSession for more details.

oDBSession Object as Adapter

The oDBSession object is an adapter to the external DAM objects that Omnis Studio provides for communicating with various RDBMSs.

The majority of the messages (method calls) you would normally send directly to a DAM's session object, you now send to the oDBSession object.

Some of the session object methods had to be changed because Omnis Studio would not work with those method names when attempting to send them to the oDBSession.

Testing a returned session object to make sure it is valid can be difficult to test. Using a $get... method that passes the session object to the sender via field reference variable and returns flag true or false is much easier to work with your code. The same goes for statement objects. For that reason the oDBSession object uses a $getStatementObject(pfRetStatementObject) method instead of $newstatement.

Warning

Try to void using $getSessionObject in your code. Doing so means that you are be dealing directly with the session object in your code. The oDBSession object is an adapter and is therefore meant to be between your code and the actual Omnis Studio session object. If you need to $assign the $transactionmode, $commit or $rollback, or get a new statemement object($getStatementObject), then call the oDBSession methods by those names, rather than working directly with the Omnis Studio session object.

The benefit of communicating with the DAM session external object via the oDBsession object is that oDBsession can deal with the differences between the various DAMs, and it can catch and log errors for you.

One example is that if the network connection is lost, the DAM session object's $state will still report kSessionStateLoggedOn. Knowing this weakness in the DAM session object, the oDBSession object's $state method first test the connection with the database server, and if successful, it returns the $state property of the DAM session object. The $getStatementObject also tests the connection and if it fails will first attempt to reconnect the session, and if successful passes back a statement object and returns true, if it can't connect it will log an error and return false.

Note

TCPPing-ing the server can be avoided by setting pingtimeoutseconds to zero in the session row used to initialize oDBSession.

The following diagram gives you and idea the the class structure for the oDBSession series of objects.

oDBSessionClassStructure.gif

The following sample code shows how you might use the oDBsession object instantiated by the task variable db1sess. The objective of the sample code is to demonstrate how you are to communicate with the oDBSession object instance just the same as you would communicate with an actual Omnis Studio session object.

Do db1sess.$transactionmode.$assign(kSessionTranManual)

Do db1sess.$getStatementObject(StmntObj) Returns FlagOK
If FlagOK
   
   Calculate SQLText as "DELETE FROM Purchaseorder WHERE PO_Status = 'Cancelled'"
   
   Do StmntObj.$execdirect(SQLText) Returns FlagOK
   If FlagOK
      Do db1sess.$commit()
   Else
      Do db1sess.$rollback()
   End If
   
End If

Do db1sess.$transactionmode.$assign(kSessionTranAutomatic)

Quit method FlagOK

oDBSession Object as Bridge

The oDBSession object is a bridge to the various oDBSessionMethods_DAMNAME object classes. (oDBSessionMethods_MYSQLDAM, oDBSessionMethods_PGSQLDAM, oDBSessioMethods_OMSQLDAM, etc.)

The $initialize method of oDBSession instantiates the appropriate oDBSessionMethods_DAMNAME object class for the DAM specified in the SessionRow that is used to initialize the oDBSession object.

This makes it easy to introduce new DAMs and to override and modify code for specific DAMs. StudioWorks developers can even override the StudioWorks oDBSessionMethod_DAMNAME objects by subclassing any or all of them to their main library and then overriding method(s) in their main library subclass object.

oDBSession forwards the $initialize message to the oDBSessionMethods_DAMNAME object. The oDBSessionMethods_DAMNAME object has an ioSessionObjRef ivar that is an Object Reference datatype. The ioSessionObjRef ivar is bound to the appropriate DAM external component using $newref. This creates a stable instance of the external component object in memory which ioSessionObjRef points to.

All of this is really behind the scenes and transparent to your application code. As long as your code sends its requests for new statement objects and session objects to oDBSession you won't need to know or worry about what goes on behind the scenes.

oDBSession can be instantiated by a task, instance, or local variable. In StudioWorks the main session is instantiated by the db1sess object reference task variable of the main library's Startup_Task. An object reference variable can be passed and copied all over the place. Every object reference points to the exact instance of the object in memory. That makes it very easy to pass around the db1sess object reference of main session's oDBSession object instance.

In StudioWorks the db1sess object reference is copied to object reference ivars in oSQLLists, oRefs, and oErrorHandler. As long as all of those objects ask their ivar object reference (which points to the db1sess tvar's oDBSession instance in memory) for new statement objects or session objects each time they need one, behind the scenes we could reconnect to the database with a different session name, change from sessions to session pools, and even connect to a different database. All of this could be done without needing to notify the other objects, and our code would hold together.

The following diagram shows how various classes can point to the same session object via the ioSessionObj ivar of oDBSessionMethods_DAMNAME by getting a session object reference via the oDBSession's $getSessionObject method.

oDBSessionClassStructure.jpg

Messages sent to oDBSession are forwarded to the instantiated oDBSessionMethods_DAMNAME object.

You should instantiate and initialize one oDBSession object for each session you wish to open with a database.

If your application needs to communicate with more databases you can add an appropriately named task variable for each additional database or database session. You point each task variable to the oDBSession object.

  1. db1sess - the main session to the main database.
  2. db2sess - a session to a second database
  3. db3sess - a session to a third database
  4. db3sess2 - a second session to the third database

You do not have to use task variables to instantiate the oDBSession object. In some cases it will make more sense to use an instance variable or even a local variable.

You do not have to use object reference datatype variables to instantiate oDBSession, in fact in most cases you would use an object datatype.

oSQLLists and Database Sessions

In Omnis Studio a list variable that is defined from a SQL class, either a schema class or a query class, can be used to select, fetch, insert, update, and delete records in a database.

The list must be defined using the $definefromsqlclass method and the list's $sessionobject property must be set to use a session that has been opened with the database which you want to select, fetch, insert, update, or delete records.

The following shows how you might accomplish this in Omnis Studio.

; Define the list.
Do List.$definefromsqlclass('myAppModule.sCountry')

; Set the session object.
Do List.$sessionobject.$assign($sessions.MYAPPMAIN_1.$sessionobject)

; Select and fetch records.
Do List.$select() Returns FlagOK
Do List.$fetch(kFetchAll) Returns FetchStatus

The oSQLLists object in StudioWorks is instantiated using the lsts startup task variable. oSQLLists is command central for all things SQL list related. The $retDefinedList method returns a list that has been defined using $definefromsqlclass and has the $sessionobject set to the session object which has been opened by dbsess.

The following shows how you would accomplish the same thing in StudioWorks as was done in the previous code sample.

; Get a defined list bound to table class tBase, with the session object assigned
Do lsts.$retDefinedList('sCountry') Returns List

; Get all the records.
Do List.$getAllRecords() Returns FlagOK

In StudioWorks after the user successfully signs in via the Sign-In window, oSQLLists is sent a $:DefaultDBSessionObjRef(db1sess1) message thereby pointing it to the db1sess1 startup task variable instance of oDBSession.

Each time oSQLLists is asked to $retDefinedList is sends a $getSessionObjectRef message to db1sess and assigns the session object passed back to the SQL defined list before returning it to the sender. (The $getSessionObjRef method checks to make sure the connection to the database is still valid and will attempt to reconnect if the connection has been lost.)

Some developers may need to work with more than one database, having some of the SQL defined lists pointing to one database and the rest pointing to a different database. To accomplish this you will need to set need to do the following:

  1. Add a db2sess task variable to the main library startup task.
  2. Set the datatype to Object Reference.
  3. Point the task variable to oDBSession in your main library.
  4. Add a method called openSession_db2sess to your main library startup task.
  5. Add something like the following code to the method:

    ; Prepare a session row.
    Do oSessionsManager.$:DefaultSessionRow_PGSQLDAM() Returns Row
    If Row.$colcount
       
       Calculate Row.sessionname as 'DB2SESS'
       Calculate Row.hostname as '192.168.1.23'
       Calculate Row.databasename as 'CorporateData'
       Calculate Row.tablesownername as 'dbadmin'
       Calculate Row.tablesownerpassword as 'secret'
       Calculate Row.tablesownerlogon as kTrue
       
       ; Initialize the object.
       Do db2sess.$initialize(Row) Returns FlagOK
       If FlagOK
          
          ; Logon
          Do db2sess.$logon() Returns FlagOK
          If FlagOK
             
             ; Prepare a list of all of the SQL classes in the vsCorporate module.
             Do lsts.$:DatabaseQueriesSchemasViewsList() Returns List
             If List.$colcount=0
                Calculate FlagOK as kFalse
             Else
                
                Do List.$search($ref.sqlclasslibname='vsCorporate')
                Do List.$remove(kListKeepSelected)
                
                ; Set the dbsessionobjectref property for each of the SQL classes in the vsCorporate module to use the db2sess.
                For List.$line from 1 to List.$linecount step 1
                   
                   Do lsts.$setTableDBSessionObjectRef(db2sess) Returns FlagOK
                   If not(FlagOK)
                      Break to end of loop
                   End If
                   
                End For
                
             End If
          End If
       End If
    End If
    Quit method FlagOK

  6. Call the openSession_db2sess method from the end of the $signInOKContinue main library startup task method or at whatever point is appropriate for your application.

db2sess

The StartNewApp main library Startup_Task includes a db2sess object reference task variable which can be used for opening a session with a second database.

A couple situations where you might use the db2sess variable are:

  1. Fetching records from an old database which you are converting.
  2. Fetching records from a database which you have read-only access to. (e.g. The university's students database.)

In both of these situations you normally do NOT want all of the SQL classes (schema and query classes) which are related to the old database or the read-only database to be in your SQL Meta-Data and other StudioWorks related cached lists. If there are lots of SQL classes related to the db2sess, they will bog down and clutter up your StudioWorks related cached lists.

A trick you can use to keep these db2sess SQL classes out of your StudioWorks cached lists is to put all of those SQL classes in a separate library and put that library inside of a db2sess folder which you create inside your APP/libraries/ folder.

By default the main library will only open the libraries inside the APP/libraries/modules/ folder, ignoring the APP/libraries/db2sess/ folder.

StudioWorks only includes libraries which are inside the modules folders as being part of the StudioWorks application, therefore the SQL classes inside the db2sess folder will be ignored.

You need to then separately open the library located inside the db2sess folder, and you use a special oSQLList_db2sess object to $retDefinedList, and a special tBase_db2sess to $getAllRecords, $getWhere, $selectWhere, $fetchBatch.

The oSQLLists_db2sess object class and tBase_db2sess table class are located in the myOldDataConverterDemo library include with StartNewApp.

StudioWorks Documentation - Copyright 2005 Vencor Software