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.

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.

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.

$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

$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

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

$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,")")

$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

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

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

$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

$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]}

$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

$clear

SessObj.$clear()

$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

$nextnativeerror

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

StudioTips Documentation - Copyright 2006 Vencor Software