Database Connections

Types of Connections

Legacy Progress 4GL applications use both implicit and explicit database connections. An application's primary databases are configured/started such that an explicit connection action is not necessary before data access statements are executed. From the perspective of a developer, such a database is, whether by implicit or permanent connection, always available.

For example, consider an application which uses the physical database primary.db for most of its work. The Progress server for this application most likely is launched with this database connected by default. Thus, 4GL code which accesses this database can either explicitly prepend the primary qualifier to the names of this database's objects (tables, indexes, etc.), or in cases where such an object's name is unambiguous with any other database the application uses, the code may simply omit the database qualifier altogether. Either way, references to objects in the primary database will be properly resolved by the Progress runtime.

An application also may use a database which was not connected by default at the time the application's server was launched. This is done by connecting to the target database explicitly via the CONNECT language statement. When the database is no longer needed, the DISCONNECT language statement is used. For data access statements to work properly in such a scenario, the CONNECT statement must be executed in one program, then data access work with that database is done in a separate program. Data access statements may reference the connected database using a logical database name (or an alias), which is established in the first program at the time the database is connected (shortly thereafter in the case of an alias).

For the following examples, assume we have the following databases:

Physical Name Table(s) Connected by Default?
primary.db customer yes
secondary.db inventory no

Consider connect1.p below. The primary database is connected by default. The program connects to the secondary database explicitly, calls the program worker1.p to do some work, then disconnects from the secondary database. Here is connect1.p:

CONNECT -db secondary.db -ld otherdb -H localhost -S 55000.
RUN worker1.p.

The logical database name otherdb is established in the CONNECT statement above and is used to reference the connected database in the called program worker1.p below:

/* Do some work with primary database */
FOR EACH primary.customer:
  DISPLAY primary.customer.

/* Do some work with secondary database */
FOR EACH otherdb.inventory:
  DISPLAY otherdb.inventory.

Since worker1.p executes within the context of the explicit connection to the secondary database, conversion of this program requires some special preparation.

Schema Configuration

Conversion must be configured to know about the full set of schemas used by all programs which comprise the application, whether the corresponding databases are connected by default (for all programs to be converted), or connected by explicit CONNECT statements (for a subset of the programs to be converted). This list of schemas is specified in cfg/p2j.cfg.xml, within the schema XML element.

For example, to support the situation described above (i.e., primary database connected by default, secondary database connected explicitly by one or more programs), the schema node might look like this:

<schema metadata="standard">
      xmlFile="data/namespace/standard_91c.dict" />
      default="true" />
      xmlFile="data/namespace/secondary.dict" />

The important portions of the above example for purposes of this discussion are the namespace nodes with the names primary and secondary. Their sibling node with the name standard is necessary, but is not relevant to this discussion. Please refer to the Project Setup chapter (Schema Loading section) for an explanation of that node's role in configuration.

Second, we may need to provide conversion hints or possibly make 4GL source code changes, depending upon the options specified as part of the CONNECT statements in 4GL code which explicitly connect additional databases.

Conversion Hints for Database References

Early in the conversion process, the FWD parser scans each 4GL source file in your project. It attempts to identify each symbol it encounters, be it a language keyword, reference to a database object, operator, variable name, and so on. In order for the parser to identify database objects like fields, tables, indexes, and physical database names, the parser utilizes a dictionary of these references, created in an earlier step in the conversion.

This schema dictionary alone is not always enough to get the job done, however. Logical database names and aliases in particular present a problem. A logical name or an alias is a construct which creates a temporary, runtime association between an arbitrary symbol (the name/alias) and a particular, physical database. More importantly for the parser's purposes, this association implicitly determines which database schema defines that physical database.

As discussed above, a logical name is created (in one program) when the database is connected. The name then represents that database for some finite period at runtime (in a different program which is called by the first program). Finally, the association is discarded (after the called program returns control to the first program) when the database is disconnected, since the logical name has served its purpose and is now out of scope. Aliases are used in a similar way.

Because the FWD parser is not actually executing the programs, it does not inherently have all the state information which would normally be available to the Progress runtime environment to manage the necessary associations between logical names/aliases and their corresponding, physical databases. The parser scans each program file independently of all others, so it may well encounter the called program which uses a logical database name before it has scanned the program which connects and disconnects the database and establishes the logical name. Unless these associations are provided by some other means, the parser will encounter such symbols and it will not be able to determine that they represent references to a specific database schema. This will cause conversion to halt with an error.

To address this issue, FWD uses conversion hints; namely, the alias and database hints. For each 4GL source file which contains references to an alias or to the logical name of a database connected explicitly using a CONNECT statement in a calling program, a hint specifies a schema known to conversion (from the list provided in cfg/p2j.cfg.xml - see above), and optionally, its corresponding, logical database name or alias name, such that the parser can recognize these symbols as schema references within 4GL source code. Such hints may be specified for a single program or for a set of programs that are grouped together within the file system.

If a single 4GL program requires such a hint, the best way to provide it is with a hints file which corresponds with the program in which the logical name or alias is used. The hints file must be named the same as the corresponding 4GL source file, but with an additional .hints extension. So, for the worker1.p example program above, a hints file named worker1.p.hints would be created.

If all 4GL programs in a particular file system subdirectory require the same set of hints, a better way to provide them is by creating a directory.hints file containing those hints, in the target subdirectory. The hints will be used for all 4GL programs in that subdirectory and in its children, unless overridden by individual (program-specific) hints files, or by a directory.hints file residing in a child subdirectory. Please refer to the chapter Specifying Hints for additional details on the use of directory-specific versus program-specific hints files.

Whether a hint is specified at the directory or at the program level, the syntax of the hint is the same. A hint which tells the parser that the otherdb logical database name in the worker1.p example above should be recognized as a reference to the physical database secondary would look something like this:

   <alias name="otherdb" database="secondary" />

Note that the meaning of the alias attribute above is broader than in the Progress sense. This attribute is used to specify either a logical database name or an alias. If multiple Progress aliases represent the same physical database (or multiple databases with the same backing schema), multiple database hints would be defined in the same hints file. That is, a separate hint should be provided for each Progress alias or logical database name used in the program(s) expected to be supported by the enclosing hints file.

Now let's consider a slightly different case, continuing with the assumption that primary.db remains connected by default. Here is connect2.p:

CONNECT -db secondary.db -H localhost -S 55000.
RUN worker2.p.
DISCONNECT secondary.

This time, no explicit logical database name is given, so the logical name implicitly assigned to secondary.db is secondary. Here is worker2.p:

/* Do some work with inventory table in secondary database */
FOR EACH inventory:
  DISPLAY inventory.

The inventory table exists in the secondary database (and only there), so even though the table name is unqualified by a logical database name or by an alias in worker2.p, the 4GL runtime environment will resolve the reference to this table properly, since connect2.p connects the secondary database explicitly before worker2.p is run, and the reference to the inventory table is unambiguous in this context.

However, as noted previously, the FWD parser does not have the benefit of knowing the secondary database has been connected when the worker2.p program is executed. So, unless a conversion hint is provided, the parser will attempt to find a table named inventory in the primary database's schema dictionary (i.e., the only database it is aware of at the time this file is parsed). Since no such table exists, conversion will halt with an error.

In this case, the conversion hint which solves this problem does not need to specify an alias. It exists only to ensure that the secondary schema is loaded into the schema dictionary when worker2.p is parsed, so that the inventory symbol can be recognized as a valid reference to a database table:

   <database name="secondary" />

As before, this hint can be program-specific (stored in a file named worker2.p.hints) or directory-specific (stored in directory.hints in the enclosing subdirectory).