Application Development

Once the engine is initialized, you need to establish a connection before your application can access any data. There are three ways to connect your application to the database, depending on your specific development requirements, the application architecture, and your application's requirements. You can connect to a database:

• Via server initialization (for JetNet/Oracle Tuxedo applications)—Set the Database Connect String option from the Server Configuration dialog in the JetNet manager.

  (Refer to "Database Connect String" in JetNet/Oracle Tuxedo Guide for details on setting server properties and database connections using the JetNet manager. For Oracle Tuxedo, refer to "Initializing Servers" in JetNet/Oracle Tuxedo Guide).

• Directly through the screen editor (or in test mode).

• Programmatically—Make runtime connections using the DBMS DECLARE CONNECTION command in a JPL procedure or C function.

In addition, the database engine must recognize your application users and grant them proper permissions, which can involve changes by your database administrator.

Connecting to the Database in the Screen Editor

While you are designing your application, you need a direct connection to the database server if you are:

• Developing a two-tier application—to import data definitions into a repository and to test your screens using "real" data.

• Developing a three-tier application—to import from the database to a repository and, for JetNet and Oracle Tuxedo applications, to test service components. (Refer to Chapter 38, "Testing Application Components," for instructions on how to test service components).

  Notes: During JetNet and Oracle Tuxedo development, making a direct connection to the database server does not affect a database connection made via server initialization.

How to Make a Direct Connection to the Database from Within the Screen Editor

1. Choose FileOpenDatabase or in test mode, choose DatabaseConnection. The Choose Engine dialog opens.
2. Select the desired engine and enter a connection name, if the default name is not appropriate.
3. For some engines, a Connect to Database dialog opens where you can enter connection options. The options vary according to the selected engine. For JDB, an engine-specific Open File dialog is displayed from which you can select the desired JDB database file.

Choose FileCloseDatabase or in test mode, choose DatabaseDisconnect. Both options close direct connections on a specified engine. They do not close connections that have been established via the application server.

Programmatically Connecting to the Database

A declared connection is a named structure describing a session about an engine. Two-tier applications contain the dbms commands to make direct connections to the database; in three-tier JetNet and Oracle Tuxedo applications, the dbms command is part of the application server initialization.

The syntax for DBMS DECLARE CONNECTION is:

DBMS [ WITH ENGINE engineName ] \
	DECLARE connectionName CONNECTION WITH \
	OPTION=argument[, OPTION=argument ...]

The information provided about the engine includes:

• A connection name—If a WITH ENGINE clause is not specified, the connection is declared for the default engine. Connection names specified in the statement are case-sensitive.

• Logon information supplied by the option arguments—Different engines support different options. Common options include USER, PASSWORD, DATABASE, and SERVER. If an option is not supported by the engine, the database driver reports an error—Bad arguments. To see a list of options for a specific engine, refer to "Database Drivers."

• Arguments that contain the value assigned to the option. The argument can be a screen variable, a JPL variable or a quoted string.

• A data structure for a default SELECT cursor.

• Pointers to other structures associated with the connection, including named cursors (thus when an application closes a connection, Panther is able to close all open cursors on the connection). For more information about database cursors, refer to "Using Database Cursors."

Once a connection is opened, the application can operate on the database tables.

A sample statement for JDB is:

DBMS DECLARE vid_conn CONNECTION WITH DATABASE="videobiz"

At runtime, the application can execute the following command for each declared connection:

DBMS CLOSE CONNECTION connectionName

A connection is always associated with an initialized engine. Setting a connection as the default or current connection also sets the default or current engine. When using multiple connections, you should set a default connection.

How to Set a Default Connection

Use the following command:

DBMS CONNECTION connectionName

Use a WITH CONNECTION clause to specify the connection to use for a single statement. For example

DBMS WITH CONNECTION oracon QUERY SELECT * FROM customers

Some database engines permit two or more simultaneous connections.

Refer to "Database Drivers" to see if this option is available for your engine.

How to Make Multiple Connections to the Database

Declare a named connection for each session on the engine. (The engine must support this feature.) For example:

DBMS ENGINE sybase
DBMS DECLARE s1 CONNECTION WITH \
	USER=:+uname, PASSWORD=:+pword, SERVER='birch'
DBMS DECLARE s2 CONNECTION WITH \
	USER=:+uname, PASSWORD=:+pword, SERVER='maple'
DBMS CONNECTION s1

This example declares two connections on the sybase engine and sets the default connection to be s1. Panther gets the values for USER and PASSWORD from the variables uname and pword at runtime.

If you execute an additional connection statement for an engine supporting multiple connections, the support routine opens the additional connection and Panther keeps a count of the number of active connections for the engine.

If the engine does not support multiple connections or if the connection name is not unique, Panther returns the error DM_ALREADY_ON.

How to Close All Connections on an Engine

Executing the following command:

DBMS [ WITH ENGINE engineName ] CLOSE_ALL_CONNECTIONS

If a two-tier application uses two or more database engines, a connection must be declared for each. You can then set a default connection. For example:

DBMS WITH ENGINE sybase DECLARE sybcon CONNECTION WITH \
	USER=:+uname, PASSWORD=:+pword, SERVER='birch'
DBMS WITH ENGINE oracle DECLARE oracon CONNECTION WITH \
	USER=:+uname, PASSWORD=:+pword
DBMS CONNECTION sybcon
DBMS QUERY SELECT * FROM titles WHERE title_id = :+title_id

In the example, connections are declared on the engines sybase and oracle. Panther gets the values for USER and PASSWORD from the variables uname and pword at runtime. The connection sybcon is identified as the default engine. Therefore, Panther performs the SQL SELECT on connection sybcon and uses the support routine associated with the sybcon engine to execute the query.

In three-tier applications, connections to multiple database engines is usually handled by having a different application server for each database engine.

Checking the Status of Connections

Check whether a database connection is open using the library function dm_is_connection. For example:

// This procedure finds out if the connection is 
// open and if not, declares the connection.

proc check_connect
vars retcode
retcode=dm_is_connection("app_connect")
if retcode == 0
{
	DBMS DECLARE app_connect CONNECTION WITH ...
}
return

Find out which database connection is assigned to a database cursor using the library function dm_cursor_connection. For example:

// This procedure finds the connection for a cursor 
// and makes it the default connection.

proc check_cursor
vars retcode
retcode=dm_cursor_connection("select_data")
DBMS CONNECTION :retcode
return

In order to interface with some database engine programs, you need information about the Panther database connection structure. Use the library function dm_get_db_conn_handle to obtain this information.

Verifying Database Access

Depending on your application architecture, you need to verify that users, processes, and services have database access and appropriate permissions on database tables or views.

UNIX

For two-tier UNIX applications, verify that:

• The user specified in the DBMS DECLARE CONNECTION statement is configured for database access.

• The owner of the application process sets the environment variables needed for database access.

For two-tier UNIX web applications, verify that:

• The user specified in the DBMS DECLARE CONNECTION statement is configured for database access.

• The web application's initialization file contains the environment variables needed for database access. (At runtime, the web application runs under an http process.)

For three-tier JetNet and Oracle Tuxedo UNIX applications, verify that:

• The user specified in the DBMS DECLARE CONNECTION statement (part of the proserv configuration or JPL initialization) is configured for database access.

• The owner of the application process sets the environment variables needed for database access.

Windows

On Windows, you can install applications as a service. Windows assigns that service a user (owner) name; that user name must be configured for database access. Verify that the service has database access by:

• Logging into the machine as the user (owner) of the service.

• Starting a database vendor's program.

Some database engines have special installation instructions. For example, Informix requires that you run setnet32 to add the service user and then run the demo login program to check that the user was added correctly.

For two-tier Windows applications, verify that:

• The user specified in the DBMS DECLARE CONNECTION statement is configured for database access.

• The PC has the database vendor's client program installed.

For two-tier Windows web applications, verify that:

• The user of the web application's service is configured for database access. That user can be specified as part of the monitor command that installs the application as an service.

• The user specified in the DBMS DECLARE CONNECTION statement is configured for database access.

• The PC has the database vendor's client program installed.

• The web application's initialization file contains the environment variables needed for database access.

For three-tier JetNet and Oracle Tuxedo Windows applications, the application uses the TUX IPC Helper service. Verify that:

• The user that the TUX IPC Helper service runs under is configured for database access.

• The user specified in the DBMS DECLARE CONNECTION statement (part of the proserv configuration or JPL initialization) is configured for database access.

If the three-tier Windows application includes a web application client, the web application can be installed as a service. Verify that:

• The user of the web application's service is configured for database access. That user can be specified as part of the monitor command that installs the application as a service.