StudioTips - SQL - Session Pools

Session Pools

At the time of writing this tip, to the best of my knowledge, session pools are only included with the Omnis Web Development license.

Pools of session instances can be created when Omnis starts up and made available to any methods running within Omnis. They are designed to be used by the multi-threaded server and allow client methods to quickly obtain SQL sessions without the burden of constructing their own instances.

There is a notation group $sessionpools that contains the current session pools. Normally the session pools are created by the startup task and exist until Omnis is shut down.

When a session is required in order to perform a SQL query, it is obtained from the pool using an object variable and a statement object created in the normal way in order to execute the query and fetch any results. When the session object variable is destroyed the session instance is returned to the pool for later reuse.

Session Pool Notation

The group $sessionpools has the usual $findname, $first, $next and $makelist methods. The $remove() method is also implemented but not $add() since $makepool() is used to create a session pool.

A pool has the $name, $poolsize and $inuse properties. The $poolsize property is the number of instances stored in the pool and $inuse is the number of these which are currently assigned out. If you increase the value of $poolsize the new session instances are immediately constructed and if the hostname, username and password parameters were specified at $makepool, they are also logged on.

An alternative form of the $new() method is poolone.$new(pWaitSeconds) which is designed to be used in client methods running on the multi-threaded server. If there are no sessions currently available in the pool this waits for the specified number of seconds. Whilst it is waiting other threads are allowed to run and if a session is returned to the pool it will be used by the waiting method. At the end of the time period NULL is returned if no session has become available. Note that this waiting time period should be treated as approximate.

$makepool

Session pools are created using the $makepool() method. The call to $makepool() will only be successful if used with an external object that is a session object or an object class that has a session object as its superclass. The $makepool() method returns a reference to the pool if it is successfully created and the required number of session instances are constructed, otherwise it returns NULL. Once a session pool has been created there is no need to maintain it further during the time that the library is open, however it is possible to change the number of available instances using notation.

Pooled sessions do not appear in the SQL Object Browser!

You can create a session pool using an external object with a method call of the form:

Do $extobjects.DAMobject.$objects.SessObj.$makepool(pName, [pSize,pHostName,pUserName,pPassword]) Returns FlagOK

DAMobject is the name of the external component. You can find out what DAM objects are available on your workstation by examining the $extobjects branch of the notation tree using the F4 Notation Inspector. SessObj is the name of the session object. You can find out what session object is available by expanding the $objects group for a particular DAM object using the F4 Notation Inspector. The pName parameter is the name of the pool and must be unique amongst session pools and the pSize parameter is the number of object instances to be initially contained in the pool. The other three parameters are optional and if specified are passed to the $logon() method for the instance, if they are not specified the instance is constructed but not logged on.

; Get the path to the books demo data file.
Do pths.$:BooksDemoDataFile Returns DataFilePath
If len(DataFilePath)

   ; Set a reference to the external session object.
   Do $extobjects.OMSQLDAM.$objects.$findname('OMSQLSESS') Returns rExtSessObj

   ; Send a $makepool message to the external session object.
   Do rExtSessObj.$makepool(cSessionPoolName,5,DataFilePath,UserName,Password) Returns rSessionPool
   If isnull(rSessionPool)

      OK message (Icon) {Creation of the session pool failed.////The problem could be related to your Omnis Studio developer license level.}

   Else

      OK message (Icon) {The session pool '[cSessionPoolName]' has been created.////F4 Notation Inspector > open $sessionpools node, to see the session pool.}

   End If
End If

Alternatively $makepool() may be used with an object class with a method call of the form

Do $objects.ObjectClass.$makepool(pName,[pSize,pUserName,pPassword]) Returns FlagOK

ObjectClass is the name of an object class that must have a session object as its superclass. You can achieve this by selecting the object class in the browser and clicking on the $superclass in the F6 Property Manager, click the arrow and select External Objects and double-click on the required session object.

$remove Session Pool

To remove a session pool you set a reference to the session pool and then use $remove to remove it from the $sessionpools group.

; Set a reference to the session pool.
Do $sessionpools.$findname(cSessionPoolName) Returns rSessionPool
If rSessionPool

   ; Send a $remove message to the $sessionpools group.
   Do $sessionpools.$remove(rSessionPool) Returns FlagOK

End If

$new Session Pool Session Object

The $new() method is used to assign a session instance from a pool.

; Get a session object from the session pool.
Do $sessionpools.[cSessionPoolName].$new() Returns lv_SessObj
If isnull(lv_SessObj)
OK message (Icon) {Creating a new session object instance failed. :-(}
Else
OK message (Icon) {The session object instance has been successfully created.}
End If

If successful the session instance assigned from the pool belongs to SessObj and is normally returned to the pool when SessObj is destroyed (for example, if SessObj is a local variable the session is returned to the pool when the method containing SessObj terminates). Alternatively the session can be manually returned to the pool by assigning some other object or zero to SessObj. The $new method returns NULL if all instances contained in the pool have already been assigned out.

Once you have a session object you can use the $newstatement() method with the session object to create a statement to execute SQL in the normal way.

$newref Session Pool Session ObjectRef

The $newref() method is used to assign a session instance from a pool to an Object Reference type variable.

; Get a session object from the session pool.
Do $sessionpools.[cSessionPoolName].$newref() Returns SessObjRef
If isnull(SessObjRef)
OK message (Icon) {Creating a new session object reference instance failed. :-(}
Else
OK message (Icon) {The session object reference instance has been successfully created.}
End If

If successful the session instance assigned from the pool belongs to SessObjRef.

Once you have a session object you can use the $newstatement() method with the session object to create a statement to execute SQL in the normal way.

In order to return the session to the pool you must manually delete the object reference using $deleteref.

; Test if the session object reference is valid.
Do SessObjRef.$validref() Returns bValid
If bValid

   ; Destroy the session object reference.
   Do SessObjRef.$deleteref() Returns FlagOK
   Do SessObjRef.$validref() Returns bValid
End If

Session Pool Gotcha - SessionName

Working with Omnis Studio version 4.3 I ran into some session pool gotchas that kept me chasing gremlins for a while.

If you $logon and include a SessionName parameter Omnis Studio creates a session in the $sessions group even though you are using a session pool session object.

; Get a session object from the session pool.
Do $sessionpools.[cSessionPoolName].$new() Returns SessObj1

; Logon with the session pool session object, and include a session name.
Calculate SessionName as 'SessionPoolSession1'
Do SessObj1.$logon(DataFilePath,UserName,Password,SessionName) Returns FlagOK

; Including the session name causes a new session to be added to the $sessions group.

; Get a session object reference from the session pool.
Do $sessionpools.[cSessionPoolName].$newref() Returns SessObjRef

; Logon with the session pool session object, and include a session name.
Calculate SessionName as 'SessionPoolSession2'
Do SessObjRef.$logon(DataFilePath,UserName,Password,SessionName) Returns FlagOK

; Including the session name causes a new session to be added to the $sessions group.

Session Pool Gotcha - $sessionobject

Working with Omnis Studio version 4.3 I ran into some session pool gotchas that kept me chasing gremlins for a while.

If you get a session object reference from the session pool using $newref you can not get the session object reference's $sessionobject property for setting a SQL defined list. You must directly assign the session object reference to the SQL defined list's $sessionobject property.

Do $sessionpools.[cSessionPoolName].$newref() Returns SessionObjRef

; In the $sessions group, you can get the $sessionobject from a session object reference.
Do SessionObjRef.$sessionobject Returns SessionObj
; But you can not do this with a session pool session object reference.
; The SessionObj variable will be 'empty'.

; Surprisingly you can assign a session object reference to a SQL defined list's $sessionobject property.
Do Row.$sessionobject.$assign(SessionObjRef)