StudioTips - SQL - SQL Objects (All Contents)

Tips_sql > Sqlobjects > SQL Objects (All Contents)

Session Objects

In order to begin a session with a database you need a session object.

SQL session objects enable you to log on to an SQL database and then send SQL commands to the server using an SQL statement object. SessObj is an object variable that has a subtype of an External Session Object.

In the sample code below we have a local variable SessObj, object type. The subtype was set by:

Clicking the variable Subtype droplist.
Scrolling to External Objects at the bottom of the treelist.
Opening the External Objects node and selecting the OMSQLSESS object.

You can dynamically instantiate a session object the same way you dynamically bind an object type variable to an object class on-the-fly. For example:

Do $extobjects.OMSQLDAM.$objects.OMSQLSESS.$new() Returns SessObj

This accomplishes the same thing as steps 1-3 above.

; Dynamically bind the OMSQLSESS external object to the SessionObj variable.
Do $extobjects.OMSQLDAM.$objects.OMSQLSESS.$new() Returns sessobj

; ALTERNATIVE DYNAMIC BINDING METHOD.

; This method allows you to send the DAM name as a parameter.
; The method automatically finds the first (and only) session object in the DAMs $objects group.

; Set a reference to the first external object in the $objects group of the specified DAM.
Do $extobjects.[pDAMName].$objects.$first() Returns rExtObj

; Bind an instance of the external object to the SessionObj.
Do rExtObj.$new() Returns sessobj

To find out what external objects are available to your copy of Omnis Studio:

F4 Notation Inspector > expand $extobjects node

Look for names that end with DAM. (OMSQLDAM, FRONTBASEDAM, etc.)

Expand the ...DAM node of your choice. (I wasn't swearing, honest!)

Expand the $objects node and notice the object name that ends with ...SESS, this is the session object.

You add external session objects to the xcomp folder located in your Omnis Studio application folder.

After downloading and expanding the FrontBase DAM for Omnis Studio, I simply dragged file named damfrontbase.n_xcomp into my xcomp folder, then quit and restarted Omnis Studio. Once that was done the FRONTBASEDAM was present in the $extobjects group of the F4 Notation Inspector.

A session object can be a local variable, instance variable, or task variable (I'm not certain about class variables). For most applications you would use a Startup_Task task variable for the session object. By doing so, the session object stays alive the entire time the application is open. You logon to the database when the application is opened (send a $logon messaage to the session object). When the application is closed you logoff of the database (send a $logoff message to the session object from the $destruct method of the Startup_Task).

SQL Session Object Methods

If you right-click on a session object variable and select Interface Manager, you will see a list of the available session object methods. (In object-oriented programming talk these are the messages which you send to the session object.) You can drag and drop any of these methods into a Do statement in your code.

Click Run Demo in the StudioTips Browser to see a session object's methods via the Interface Manager.

The session object methods are as listed in this section with demos.

SQL Session Object Properties

The session object has both methods and properties. The Interface Manager has a Properties tab which lists all of the properties of the session object.

All properties will return the value of their current state. Some of the properties can be assigned. (e.g. $transactionmode could be changed from automatic to manual... provided the RDBMS supports it.)

The sample code below lists all the session object properites and descriptions.

Click Run Demo in the StudioTips Browser to see a session object's methods via the Interface Manager.

$logon

One of the first things you need to do is logon to the database. Once you are logged on you can communicate with the database.

$logon logs a session object on to the database

Do SessObj.$logon([cHostName][,cUserName][,cPassword][,cSessionName]) Return FlagOK

If you successfully logon, your session will show up in the SQL Browser (F2 Browser > SQL Browser). Your session will also show up in the F4 Notation Inspector > $sessions group.

The sample code/demo below takes you through logging onto an Omnis data file. (To logoff the data file you will need to go to the $logoff topic.)

; LOGON TO BOOKS DEMO OMNIS DATA FILE.

; Set a reference to the external session object.
Do $extobjects.OMSQLDAM.$objects.$first() Returns rExtObject

; Bind an instance of the external object to the sessobj task variable.
Do rExtObject.$new() Returns sessobj

; For OmnisSQL:
; Database = FilePath
; UserName and Password are not required.
; SessName = Upper case unique name. Maximum 15 characters. Underscores are OK. Avoid periods, $ characters.

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

; Logon to the database (Omnis data file)
; SessionObj.$logon(cHostname,cUsername,cPassword [,cSessName])
Do sessobj.$logon(DataFilePath,UserName,Password,cSessionName) Returns FlagOK
If not(FlagOK)
   Do sessobj.$errortext() Returns ErrorText
   OK message (Icon) {Flag false after attempting to $logon.////sessobj.$errortext = [ErrorText]}
Else

   OK message (Icon) {Logon to the books demo data file was successful.////$damname = [sessobj.$damname]////Select SQL Browser in the F2 Browser//or the//F4 Notation Inspector > $sessions group////to see that the '[cSessionName]' session is open.}
End If
Quit method FlagOK

Click the Run Demo button in the StudioTips Browser to logon to the books demo data file.

$logoff

When you are finished with a session you will need to logoff. Logging off a database is real easy.

The sample code/demo below show you how to logoff.

; Logoff the session.
Do sessobj.$logoff() Returns FlagOK
If not(FlagOK)
   Do sessobj.$errortext() Returns ErrorText
   OK message (Icon) {Flag false after attempting to $logoff.////sessobj.$errortext = "[ErrorText]'}
Else
   ; Clear the session object by setting it to null.
   Calculate sessobj as #NULL

   OK message (Icon) {Logoff to the books demo data file was successful.////F2 Browser > SQL Browser//or the//F4 Notation Inspector > $sessions group////to see that the '[cSessionName]' session is now closed.}

End If

Quit method FlagOK

Click the Run Demo button in the StudioTips Browser to logoff the books demo data file.

$selectnames

SessObj.$selectnames(list or row variable[,cTableName])

Returns a text string which is a comma delimited list of all column names from the list or row variable columns that can be used in a SELECT statement

If specified, cTableName is used to prefix all column names (i.e. cTableName.ColumnName).

If you use table classes you don't need to worry about this method. The table class's $select method does the $selectnames work for you.

; Define a row or list.
Do Row.$definefromsqlclass('sAuthor')

; SessionObj.$selectnames(list or row variable[,cTableName])
Calculate TableName as ''
Do sessobj.$selectnames(Row,TableName) Returns String

; Prepare the SELECT statement
Calculate SQLText as con("SELECT ",String," FROM ",Row.$servertablenames)

Click the Run Demo button in the StudioTips Browser to see the $selectnames result for the sAuthor schema class.

$createnames

SessObj.$createnames(list or row variable)

Returns a text string which is a comma delimited list of all column names and types from the list or row variable columns that can be used in a CREATE TABLE statement.

; Define a row or list.
Do Row.$definefromsqlclass('sAuthor')

Do sessobj.$createnames(Row) Returns String

; Prepare the CREATE TABLE statement
Calculate SQLText as con("CREATE TABLE ",Row.$servertablenames," (",String,")")

Click the Run Demo button in the StudioTips Browser to see the $createnames result for the sAuthor schema class.

$wherenames

SessObj.$wherenames(list or row variable[,cTableName] [,cComparison][,cOperator])

Returns a text string which is a comma delimited list of all column names and current column values from the list or row variable columns, that can be used in a WHERE clause.

If specified, cTableName prefixes all column names (i.e. cTableName.ColumnName)

cComparison is the comparison operator used (default =)

cOperator is the logical operator used to combine the comparisons of the column values (default AND).

; Define a row or list. (It doesn't matter how the row or list is defined.)
Do Row.$definefromsqlclass('sAuthor')
Calculate Row.Author_pkey as 1001
Calculate Row.AuthorName as 'John Smith'

Calculate TableName as Row.$servertablenames
Calculate Comparison as ''
Calculate Operator as ''
Do sessobj.$wherenames(Row,TableName,Comparison,Operator) Returns String

Click the Run Demo button in the StudioTips Browser to see the $wherenames result for the sAuthor schema class.

$insertnames

SessObj.$insertnames(list or row variable)

Returns a text string which is a comma delimited list of all column names and values from the list or row variable columns that can be used in an INSERT statement.

If you use table classes you don't need to worry about this method. The table class's $insert method does the $insertnames work for you.

; Define a row or list. (It doesn't matter how the row or list is defined.)
Do Row.$definefromsqlclass('sAuthor')

Do sessobj.$insertnames(Row) Returns String

; Prepare the INSERT statement
Calculate SQLText as con("INSERT INTO ",Row.$servertablenames," ",String)

Click the Run Demo button in the StudioTips Browser to see the $insertnames result for the sAuthor schema class.

$updatenames

SessObj.$updatenames(list or row variable)

Returns a text string which is a comma delimited list of all column names and new values from the list or row variable columns that can be used in an UPDATE statement.

If you use table classes you don't need to worry about this method. The table class's $update method does the $updatenames work for you.

; Define a row or list. (It doesn't matter how the row or list is defined.)
Do Row.$definefromsqlclass('sAuthor')

Do sessobj.$updatenames(Row) Returns String

; Prepare the UPDATE statement
Calculate SQLText as con("UPDATE ",Row.$servertablenames," ",String)

Click the Run Demo button in the StudioTips Browser to see the $updatenames result for the sAuthor schema class.

$makeschema

SessObj.$makeschema(rSchema,cTableName)

Overwrites the existing schema class rSchema with the column definitions from the database table cTableName.

; Define a row or list.
Do Row.$definefromsqlclass('sAuthor')

; Set a reference to a temporary schema class.
Calculate SchemaName as 'sTemp'
Do $clib.$schemas.$findname(SchemaName) Returns rSchema
If isnull(rSchema)

   ; If the temporary schema class doesn't exist, add it.
   Do $clib.$schemas.$add(SchemaName) Returns rSchema
End If

If not(isnull(rSchema))

   ; Make a schema class, overwriting the referenced schema class.
   Calculate TableName as 'Author'
   Do sessobj.$makeschema(rSchema,TableName) Returns FlagOK
   If not(FlagOK)
      OK message (Icon) {Flag false after attempting to $makeschema for the [TableName] table.}
   Else
      Yes/No message (Icon) {Do sessobj.$makeschema(rSchema,TableName)////replaced the '[SchemaName]' schema class.////Shall I open the '[SchemaName]' schema class?}
      If flag true
         Modify class {[rSchema.$lib().$name].[rSchema().$name]}
      End If
   End If
End If

Click the Run Demo button in the StudioTips Browser to create an sTemp schema class from the existing Author table in the data file.

$coltext

SessObj.$coltext(rVar)

Returns the SQL data type text string for the server type resulting from the mapping of the Omnis data type of rVar to its server data type.

Do sessobj.$coltext(DateTime) Returns String
OK message (Icon) {Do sessobj.$coltext(DateTime)////returns the string////[String]}

Do sessobj.$coltext(LongInteger) Returns String
OK message (Icon) {Do sessobj.$coltext(LongInteger)////returns the string////[String]}

Do sessobj.$coltext(ShortNumber_2dp) Returns String
OK message (Icon) {Do sessobj.$coltext(ShortNumber_2dp)////returns the string////[String]}

Click the Run Demo button in the StudioTips Browser to see the runt the sample code.

$newstatement

SessObj.$newstatement(cStatementName)

Creates a new statement object with name cStatementName. The resulting statement object can be used to send commands to the database and process results.

If you use table classes you don't need to worry about this method. The table class's built in methods automatically create new statement objects for you.

See the SQL Session Objects topic for more information.

Calculate SQLText as "SELECT max(Author_pkey) FROM Author"

Do sessobj.$newstatement() Returns StmntObj

Do StmntObj.$execdirect(SQLText) Returns FlagOK
If not(FlagOK)
   OK message (Icon) {Execute SQL error.////StmntObj.$nativeerrortext = [StmntObj.$nativeerrortext]}
Else

   Do StmntObj.$fetchinto(MaxValue) Returns FetchStatus
   If FetchStatus=kFetchError
      OK message (Icon) {Fetch error.////StmntObj.$nativeerrortext = [StmntObj.$nativeerrortext]}
   Else

      OK message (Icon) {The maximum Author_pkey in the Author table = [MaxValue]}

   End If
End If

Click the Run Demo button in the StudioTips Browser to run the sample code.

$clear

SessObj.$clear()

clears all statements controlled by the session object

$commit, $rollback, $begin

Commit and rollback will only work if the RDBMS you are using supports transactions and you have the session object $transactionmode set to kSessionTranManual.

The session object property $allowtransactions tells you whether or not the RDBMS you are connected to supports transactions.

The Omnis data file does not support commit and rollback.

$commit()

SessObj.$commit() commits the current transaction.
Should only be used if SessObj.$transactionmode=kSessionTranManual

$rollback()

SessObj.$commit() rolls back the current transaction.
Should only be used if SessObj.$transactionmode=kSessionTranManual

$begin()

SessObj.$begin() starts a new transaction. Should only be used if SessObj.$transactionmode=kSessionTranManual and SessObj.$autobegintran=kFalse

If you leave $autobegintrans to its default kTrue setting you don't need to worry about $begin.

$nextnativeerror

SessObj.$nextnativeerror() returns the next native session error code and message in SessObj.$nativeerrorcode and SessObj.$nativeerrortext if SessObj.$nativeerrorpending=kTrue

$rpcdefine

SessObj.$rpcdefine(cProcedureName,list variable)

Defines a remote procedure for later use in a call to StmntObj.$rpc();

cProcedureName: is the case-sensitive name of the remote procedure in the database
list variable: defines the parameters and the return value of the remote procedure

See Statement Objects > $rpc for more information on remote procedure calls.

The Omnis data file does not support stored procedures.

Statement Objects

SQL statement objects enable you to send SQL commands to the server and process results.

StmntObj is an object variable that has a subtype initially empty but contains the value returned from executing the session object $newstatement method to generate a statement object.

Do SessObj.$newstatement() Returns StmntObj

The various statement object methods are listed in this section. The information contained in this section is copied from the F1 Help.

If you are using a list variable with is bound to a table class , you can get to the table class's statement object using the notation:

; Find out the current $sqltext
Calculate #S1 as List.$statementobject().$sqltext

$execdirect

StmntObj.$execdirect([cSQLText])

Prepares and executes the SQL text cSQLText.

A statement executed directly cannot be re-executed using $execute().

$execdirect combines the $prepare and $execute statement object methods into a single method. Use $prepare and $execute if you need to execute the same SQL text just with different variable values. Putting the $prepare before the loop and then $execute inside the loop is much faster than using $execdirect inside the loop.

Calculate SQLText as "SELECT * FROM book"
Do StmntObj.$execdirect(SQLText) Returns FlagOK
If not(FlagOK)

   Do StmntObj.$nativeerrortext() Returns ErrorText
   OK message SQL Error (Icon) {$nativeerrortext = [StmntObj.$nativeerrortext]////when executing the SQL Script:////[SQLText]}

Else

   OK message (Icon) {The $execdirect ran successfully for the SQL script:////[SQLText]}

End If
Quit method FlagOK

$prepare & $execute

StmntObj.$prepare([cSQLText])

Prepares the supplied SQL text.

StmntObj.$execute()

Executes or re-executes the SQL statement previously prepared using the $prepare method. If this statement was declared using a cursor then this method opens that cursor and executes the cursor statement. Bind variable values are sent to the server with appropriate conversions where required.

Use $prepare and $execute if you need to execute the same SQL text just with different variable values. Putting the $prepare before the loop and then $execute inside the loop is much faster than using $execdirect inside the loop.

; Prepare the SQL text using bind variables pointing to columns in a list.
Calculate SQLText as "INSERT INTO Author (Author_pkey,AuthorName) VALUES( @[List.Author_pkey], @[List.AuthorName])"
Do StmntObj.$prepare(SQLText) Returns FlagOK
If FlagOK

   ; Loop through the list, inserting the new records using the $execute method.
   For List.$line from 1 to List.$linecount step 1

      Do StmntObj.$execute(SQLText) Returns FlagOK
      If not(FlagOK)
         Break to end of loop
      End If

   End For
End If
If not(FlagOK)
   Do StmntObj.$nativeerrortext() Returns ErrorText
   OK message SQL Error (Icon) {$nativeerrortext = [StmntObj.$nativeerrortext]////for the SQL Script:////[SQLText]}
End If

If FlagOK
   OK message (Icon) {The $execdirect ran successfully for the SQL script:////[SQLText]}
End If

Quit method FlagOK

$nativeerrorcode & $nativeerrortext

StmntObj.$nativeerrorcode()

returns the native error statement code.

StmntObj.$nativeerrortext() returns the native error statement text.

The native error text is useful for determining what went wrong when preparing or executing a SQL script.

If not(FlagOK)
Do StmntObj.$nativeerrorcode() Returns ErrorCode
Do StmntObj.$nativeerrortext() Returns ErrorText
End If

$fetch

StmntObj.$fetch([row or list variable] [,iRowCount=1] [,bAppend=kFalse])

Fetches one or more rows into the row or list variable.

iRowCount is the maximum number of rows to be fetched.

bAppend=kTrue indicates that the fetched rows are to be appended to the existing rows in the list; otherwise, $fetch() empties the list before fetching the rows.

Do StmntObj.$fetch(List,kFetchAll) Returns FetchStatus

If FetchStatus=kFetchError

   OK message SQL Error (Icon) {$nativeerrortext = [StmntObj.$nativeerrortext]}

Else If FetchStatus=kFetchFinished

   ; All of the selected records have been fetched into the list.

Else If FetchStatus=kFetchOk

   ; There are more selected records waiting to be fetched.

End If

$tables

StmntObj.$tables([Type-constant][,cOwner]) generates a result set containing a list of the tables or views on the server, containing columns for Owner, TableOrViewName, TableType, and Description.

The Type-constant indicates the type(s) of the objects to return:

kStatementServerAll: tables and views (default)
kStatementServerTable: tables only
kStatementServerView: views only

If specified, cOwner is used to restrict the returned tables to those owned by a particular user.

The result set may be retrieved using the $fetch() method.

$columns

StmntObj.$columns(cTableName)

generates a result set describing the columns of the specified table, containing columns for DatabaseOrCatalog, Owner, ColumnName, OmnisDataTypeText, OmnisDataType, OmnisDataSubType, SQLDataType, Length, Scale, Null, Index, PrimaryKey, Description.

Parameter cTableName may take the form [Database.][Owner.]Name depending on whether database and owner qualifiers are supported.

If only Name is specified, it returns column information for all tables with Name in all available databases.

If Owner.Name is specified then it returns column information for all tables with Name owned by Owner in all available databases.

If Database.Owner.Name is specifed then it returns column information for Name owned by Owner in Database

$indexes

StmntObj.$indexes(cTableName[,Type-constant])

generates a result set describing the indexes of the specified table, containing columns for DatabaseOrCatalog, Owner, ColumnName, IndexName, Unique, ColumnPosition.

Parameter cTableName may take the form [Database.][Owner.]Name depending on whether database and owner qualifiers are supported.

Type-constant indicates the type(s) of the indexes to return:

kStatementIndexUnique: unique indexes(default)
kStatementIndexNonUnique: non-unique indexes
kStatementIndexAll: all indexes

$clear

StmntObj.$clear()

Clears any pending results or operations on the statement object.

$results

StmntObj.$results(List variable)

populates the List variable with a description of the columns in the current result set, containing columns for ColumnName, OmnisDataTypeText, SQLDataType, Length, Scale, Null.

Calculate SQLText as "SELECT * FROM book"
Do StmntObj.$execdirect(SQLText) Returns FlagOK
If FlagOK

   Do StmntObj.$results(ResultsList) Returns FlagOK

End If
Quit method FlagOK

$rpc, $rpcprocedures, $rpcparameters

Many RDBM systems allow you to create stored procedures on the server. The $rpc methods allow you to call those stored procedures. There are two schools of thought when it comes to stored procedures.

Put all your business rules in stored procedures in the RDBMS so that no matter what software (Omnis, Java, PHP, etc.) is hitting the database the business rules will be enforced. Stored procedures are faster.
Don't use stored procedures because it makes your application dependent on the RDBMS, thereby making it impossible to switch to another RDBMS.

Whether or not you use stored procedures depends on your individual situation. As of the time of writing this I have not yet used remote (stored) procedures.

$rpc()

StmntObj.$rpc(cProcedureName,[Param1,]�[ParamN]) calls the remote procedure cProcedureName that must have previously been defined using SessObj.$rpcdefine() passing any parameters Param1� ParamN required.

$rpcprocedures()

StmntObj.$rpcprocedures([cOwner]) generates a result set describing remote procedures available on the server containing DatabaseOrCatalog,Owner,ProcedureName.

If cOwner is an empty string, omitted or not supported then this call returns all available procedures in all available databases. Otherwise cOwner takes the form [Database.]Owner.

If only Owner is specified, and this form is supported by the object, returns all available procedures owned by Owner in all available databases.

If Database.Owner is specified, and this form is supported by the object, returns all available procedures owned by Owner in Database.

$rpcparameters()

StmntObj.$rpcparameters(cProcedureName) generates a result set describing the parameters used by the remote procedure cProcedureName containing OmnisDataType,OmnisDataSubType, Length,PassType,C5(reserved for future use),C6(reserved for future use),DatabaseOrCatalog,Owner,ParameterName,OmnisDataTypeText,SQLDataType,Scale

$sqltext

The $sqltext property returns the full SQL that was last sent to the RDBMS. The $sqltext property is very useful for debugging SQL errors or SQL that just doesn't seem to be working.

If you are using a statement object you can get the value directly from the statment object.

; Find out the current $sqltext
Calculate #S1 as StmntObj.$sqltext

If you are using a list defined from a table class you can get to the $sqltext from the table class's statement object as follows:

; Find out the current $sqltext
Calculate #S1 as List.$statementobject().$sqltext

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