- 37

- Connecting to Database Sources Through Sessions

- Overview of the Session Object
- Session Properties

- ConnectType
- DesignConnect
- DesignRunConnect
- Name
- RunConnect

- Session Methods on the Property Sheet

- CommitWork()
- Connect()
- Disconnect()
- IsConnected()
- IsWorkPending()
- RollbackWork()

- Creating Session Objects
- Connecting the Session Object to a Database Source
- Session Object Components
- Migrating Data Between Database Sources
- Using a Session to Generate Forms and Reports
- Summary

#
37

## Connecting to Database Sources Through Sessions

One of the most integral parts of OPO application development is the session object. This chapter introduces the use of session objects and their importance within OPO. Further, sessions will be explained in terms of what they represent, their properties and methods, and how they are used to manage data and build applications.

### Overview of the Session Object

A session object represents a connection between an OPO application and a database. Through this connection, OPO communicates with the database and performs all data operations. Database objects, including tables, views, indexes, sequences, and synonyms, are represented as graphical icons in the session's window. Whether a session is connected to a Blaze database, an Oracle database, or any other database, the session interacts with the database and displays the database objects identically. This is important because it allows OPO to seamlessly connect to any database through sessions. Figure 37.1 illustrates the relationship between a session object and the various database back ends that OPO can connect to.

Figure 37.1. A session's graphical interface to different databases.

Through the session's graphical interface, developers can efficiently maintain database objects. Tables, views, indexes, sequences, and synonyms can be created, modified, deleted, or populated from a session with the ease of point-and-click. Additionally, database objects are used to automate the task of form and report building. For example, dropping a table onto a form automatically populates the form with text fields corresponding to each identified column in that table.

Another important feature of OPO is that it allows multiple active sessions. This feature permits OPO applications to interact with any number of database connections simultaneously. On any given form of an application, data can be displayed from one or more Blaze, Oracle, and Sybase sessions. As well, master-detail or any other relationship can be made between the database objects from different databases.

OPO also provides full transaction processing for each session. From the time a session is connected, a transaction is opened recording any changes made to data through the session. The data is not actually saved to the database until the user chooses to commit the changes. Alternatively, the user can choose to rollback or revert the changes. Once the user commits or reverts the changes, a new transaction is started.

### Session Properties

The properties of a session identify when a session should connect to a database and to which database the connection should be made.

#### ConnectType

The ConnectType property tells an application when to activate a session. The possible values for the ConnectType property include Connect on Startup, Connect on Demand, and Connect Manually. Table 37.1 contains possible ConnectType values:

Table 37.1. ConnectType values.

    Connect Type
    When an Application Activates a Session
Connect on Startup | When the Application starts
Connect on Demand | Not until an object references the session
Connect Manually | Not until the user manually activates the session through its Connect method

#### DesignConnect

The DesignConnect property specifies the connect string used by the session when it attempts to connect to a database during application development from the OPO Designer.

#### DesignRunConnect

The DesignRunConnect property specifies the connect string used by the session when it attempts to connect to a database when testing or running an application from the OPO Designer. If the DesignRunConnect property is left empty, then the session will use the value of the DesignConnect property as the connect string.

#### Name

The Name property provides a reference to a session. Objects that require session information can refer to a session through its name. For example, a form's RecSrcSession property can be set to the name of a session. This allows a form to equate that any database activity will be done through the specified session's database connection.

#### RunConnect

The RunConnect property specifies the connect string used by the session when it attempts to connect to a database when the application is executed from the OPO Runtime. If the RunConnect property is left empty, then the session will use the value of the DesignConnect property as the connect string.

### Session Methods on the Property Sheet

The methods of a session are used to perform session connections, status querying, and transaction processing.

#### CommitWork()

The CommitWork method commits or saves any changes made to data within the current transaction for the given session. After CommitWork is called, a new transaction is started.

#### Connect()

The Connect method attempts to connect the session to the database specified by the appropriate connect string property.

#### Disconnect()

The Disconnect method disconnects the session from the currently connected database.

#### IsConnected()

The IsConnected method evaluates whether the session is currently connected to a database. True is returned if the session is connected, otherwise false is returned.

#### IsWorkPending()

The IsWorkPending method evaluates whether the session has any outstanding changes made to data. True is returned if the session has outstanding data changes, otherwise false is returned.

#### RollbackWork()

The RollbackWork method rolls back or reverts the values of any changes made to data within the current transaction for the given session. After RollbackWork is called, a new transaction is started.

### Creating Session Objects

New sessions are created from the OPO Designer by clicking on the toolbar's New Session button, or by selecting New Session from the File menu. The Create Session dialog appears and requests the database type and connect string for the new session as shown in Figure 37.2.

Figure 37.2. Create Session dialog box.

Using the stand-alone version of OPO, the database type can be Blaze or Oracle. The stand-alone version only connects to local Oracle, Personal Oracle7. For the client/server version of OPO, the database type can be Blaze, Oracle, or Sybase where Oracle is local or remote and Sybase represents Sybase SQL Server or Microsoft SQL Server. In future releases of OPO, sessions will also be able to connect to any ODBC (Open Database Connectivity) compliant database.

If you wish to create a session for a new Blaze database, you must first create the new Blaze database by selecting New Blaze Database from the File menu.

The connect string identifies database specific information such as username, password, and database/host name. Table 37.2 is a list illustrating possible connect strings for Blaze and Oracle:

Table 37.2. Connect string examples.

    Database
    Connect String
Blaze | Blaze:[username/password@]filename
| Blaze:blazedb.blz
| Blaze:dba/dba@blazedb.blz
Oracle | Oracle:username/password[@host]
| Oracle:scott/tiger
| Oracle:scott/tiger@x:IPXServerName
| Oracle:scott/tiger@t:123.456.789.123

After specifying the database type and connect string, OPO pops up a file dialog requesting a filename for the session. The filename you specify will also be the initial name of the session which can be changed through the Name property.

After the session has been created, a window representing the session appears. Initially, the session is disconnected and there is only a single object in the session window. This object is the session's connector icon. When the session is disconnected, the connector icon appears to be disconnected (the plug is not connected to the cylinder). Alternatively, when the session is connected, the connector icon appears to be connected (the plug is connected to the cylinder).

### Connecting the Session Object to a Database Source

Connecting a session to a database is referred to as activating the session. When a session is activated, it attempts to connect or log in to the database specified in the connect string.

In the OPO Designer, double-clicking a session's ÒconnectorÓ icon activates a session using the value of the DesignConnect property as the connect string. Once connected, the session will display the database's data objects. The session is now considered active. Double-clicking an active session's connector icon will disconnect the session from the database and remove the database's data objects from display.

While running a form or application from the OPO Designer, a session will attempt to connect to the database using the value of the DesignRunConnect property as the connect string.

While running an application from the OPO Runtime, a session will attempt to connect to the database using the value of the RunConnect property as the connect string.

As shown above, OPO has maximized connectivity options by providing separate connect strings for each of its modes—designer, design run-time, and run-time. This allows development, testing and deployment to connect to different databases. However, it's not necessary to enter values for all connect properties. Both DesignRunConnect and RunConnect will default to the DesignConnect value if they are empty when the session attempts to connect.

Additionally, an application can change a session's connect string during run-time as long as the session is not active. A session's connect string is set during run-time by either setting the RunConnect property through method code, or by prompting the user for the value of the connect string. To prompt the user for the connect string during application run-time, set the value of the RunConnect property to ? then call the session's Connect method. If the session is already connected to a database, it is necessary to call the session's Disconnect method before calling the Connect method. After calling the session's Connect method, the Database Login dialog will appear and prompt the user for the desired connect string as illustrated in Figure 37.3.

Figure 37.3. Database Login dialog box.

### Session Object Components

Once connected, the session displays most of the database objects contained in the database. The database objects shown in the session window consist of the following: tables, views, indexes, sequences, and synonyms. Illustrated in Figure 37.4 are the database objects that appear in an active session's window:

Figure 37.4. A session's database objects.

These objects can be created and deleted from the database through the session interface using toolbar or menu commands. Tables and views are editable through the Table Editor and View Editor, which are activated by double-clicking a table or view database object. Currently, sessions are unable to display public or granted privilege database objects from other database connections. In future releases, database objects with granted privileges and other database objects, such as stored procedures and triggers, should be displayed in the session window.

### Migrating Data Between Database Sources

An important feature of OPO is the ability to graphically migrate data between active sessions. Through drag-and-drop, a database object can be migrated or copied from one session to another. This allows developers to easily upsize or downsize application databases by quickly moving their database objects to alternate databases.

Easily migrating data between OPO sessions is enabled through the session metaphor. By the same measure that enables OPO to display various databases seamlessly as sessions, OPO can translate database structures and data between these databases. However, some limitations or exceptions do exist. Some of these exceptions are as follows: Oracle and Sybase store dates and times in slightly different formats; Oracle permits a table to have dual primary keys where Blaze does not; Blaze does not fully support the full range of number formats supported by Oracle. If one of these exceptions is encountered while migrating data, OPO either converts the data as best it can or it notifies the developer that some rows were not migrated.

### Using a Session to Generate Forms and Reports

One of the most powerful advantages of using OPO to develop database applications is its unique ability to automatically create forms and reports based on database objects. By dragging-and-dropping a table or view onto a form or report, OPO populates that form or report with application objects corresponding to the identified columns of the table or view. Figure 37.5 illustrates dragging-and-dropping a table onto a form:

Figure 37.5. Generating forms through drag-and-drop.

Below is list of events that occur when a table is dropped onto a form and the form is generated from the tables data definition.

1. The form's RecordSource property is set to the name of the table.
2. The form's RecSrcSession property is set to the name of the session that contains the table.
3. For each column of the table, a label (static text field) and data-entry (text edit field) objects is created on the form.
4. Each of the label objects displays the name of a corresponding column.
5. Each of the data-entry objects is bound to a corresponding column by setting the DataSize, DataSource, and DataType properties according to the column's data definition.

Additionally, dropping a single column from a table or view onto an object, such as a text field or pop-up list, sets the necessary properties of that object to bind it to the identified column. Sequences can also be dropped onto text fields that represent a counter for that table. Dropping a sequence onto an application object causes that object to receive the appropriate sequence number whenever a new record is created.

The ability to automatically create forms through drag-and-drop allows developers to build applications efficiently. Further, developers are able to concentrate on application design rather than the database interface.

### Summary

As illustrated, sessions are one of the most powerful components of OPO. They provide a transparent interface to multiple databases simultaneously. Sessions enable developers to easily view the database objects within a database and efficiently create database applications.