Tips_todo > Sql > 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 "SessionObject", object type. The subtype was set by:
1. Clicking the variable Subtype droplist.
2. Scrolling to "External Objects" at the bottom of the treelist.
3. 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 $root.$extobjects.OMSQLDAM.$objects.OMSQLSESS.$new() Returns SessObj
This accomplishes the same thing as steps 1-3 above.
To find out what external objects are available to your copy of Omnis Studio:
1. F4 Notation Inspector > expand $extobjects node
2. Look for names that sound end with "DAM". (OMSQLDAM, FRONTBASEDAM, etc.)
3. Expand the "...DAM" node of your choice. (I wasn't swearing, honest!)
4. 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 "FrontBase DAM (Mac OS X)" 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).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.
NOTE: 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
NOTE: If you leave $autobegintrans to its default kTrue setting you don't need to worry about $begin.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.
NOTE: 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.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.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()
SessObj.$logon([cHostName][,cUserName][,cPassword][,cSessionName]) logs session object SessObj on to the database
If you successfully logon, your session will show up in the SQL Browser (Tools menu > select 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.)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.
NOTE: 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.SessObj.$rpcdefine(cProcedureName,list variable) defines a remote procedure for later use in a call to StatementObj.$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
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).
NOTE: 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.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.
NOTE: 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.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").If you right-click on a session object variable > select Interface Manager, you will see a list of the available session object methods. (In OO 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" to see a session object's methods via the Interface Manager.
The session object methods are as listed in this section with demos.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 the "Run Demo" button below to see all the properties of the OMSQLSESS external object.