Try OpenEdge Now
skip to main content
SQL Development
Performing Multi-database Queries : Connecting to multiple databases : Using properties files to enable multiple database connections : Creating a properties file
 
Creating a properties file
The properties file must follow a specific format and contain information that appropriately defines the auxiliary databases. The following example provides an example of a multi-database connection properties file.
Example: Multi-database connection properties file
[sql-configuration]
configuration-names-list=NortheastSales, ALBSales
[configuration.NortheastSales]
database-id-list=MA, NH
[database.MA]
Name=Mass
Catalog=Mass
Location=/usr1/kjain/States/Mass.db
[database.NH]
Name=NewHampshire
Catalog=NH
Location=/usr1/kjain/States/NewHamp.db
[configuration.ALBSales]
database-id-list=Ny, Ma1
[database.Ny]
Name= NewYork
Catalog=Lions
Location=/usr1/kjain/States/NewYork.db

[database.Ma1]
Name= Mass
Catalog=Bears
Location=/usr1/kjain/States/Mass.db
The name of the multi-database connection properties file has the following format:
<database>.oesql.properties
SQL configuration properties are found after the [sql-configuration] directive in the OpenEdge SQL properties file. Property names and values are separated by an equal sign. For example, catalog=auto. The following table describes SQL configuration properties:
Table 14. SQL configuration properties and their values
Property
Type and length
configuration-names-list
Character [1024]
The following specifics pertain to the SQL properties:
*The SQL properties specifies a comma-separated list of configuration names.
*The configuration name must match the configuration name specified in the properties section.
*Each configuration must have a unique name.
*The name cannot contain a hyphen (-).
*This property is used to list all available configurations.
*This property is case-insensitive.
Configuration properties are found after the configuration.configuration-name directive. The value of the configuration-name must exactly match one of the names specified for the configuration-names-list property under the sql-configuration directive. If you need two configurations, you need a configuration.configuration-name directive for each of the configurations. The following table describes the configuration properties:
Table 15. Configuration properties and their values
Property
Type and length
database-id-list
Character [1024]
The following specifics pertain to the configuration properties:
*Specifies a list of database identifiers.
*Database identifiers need not be the name of the database. It is merely an identifier which must match the database name specified in the database properties section.
*Each database identifier must be unique. This property is used to list the database identifier sections available under this configuration. This property is case-insensitive.
Database properties are found after the database.database-identifier directive. The value of the database identifier must exactly match one of the names specified for the database-id-list property under the configuration.configuration-name directive. If you want three auxiliary databases under a certain configuration, you need a database.database-id directive for each of the databases. The following table describes database properties:
Table 16. Database properties and their values
Property
Type and length
Description
Name
Character [32]
This is the name of the physical database whose properties will follow. This property is case-insensitive.
catalog
Character [32]
This is the catalog name under which the database will be available in the multi-database environment. This property is case-insensitive.
location
Character [300]
This is the full/relative path of the database's .db file including the file name and its .db extension. In case it is a relative path, it should be relative to the location of this properties files, which is the same as the location of the primary database. This property is case-sensitive.
The following are the characteristics of using a multi-database connection properties file:
*The properties file should have the same name as the primary database, without the database extension followed by the oesql.properties extension. It should be available in the same directory as the database .db file. For example, the properties file for /usr1/sales.db should be available in the /usr1 directory and it should be named sales.oesql.properties.
*The properties file is optional. The absence of a properties file indicates a single database connection environment. Similarly, an empty properties file with the correct name results a single database connection environment.
*It is important to name the file appropriately with the correct extension for each primary database that will support the automatic connection feature in a multi-database setting.
*The properties file may contain the same database in multiple configurations with different catalog names. For example, the database named Massachusetts appear in the properties file in the following example in two configurations with two different catalog names. Duplicate versions of the same physical database cannot exist within a single configuration.
*The properties file should contain only the list of databases to be connected as auxiliary connections. It should not contain the primary database. The primary connection would already have been established before the properties file is read.
*If the properties file contains invalid/junk data or if the data is not in the correct format, a connection.log file is created, in the folder where the database is created, and this information is logged in that file.
*Once the primary connections is made, OpenEdge SQL Server will check for the presence of the properties file having the same name as the database followed by the required extension. If it cannot find the file, then a single database environment will result. If the file is found, the contents of the file will be parsed to create a list of database objects on the server and the appropriate configuration will be chosen to establish the connections to auxiliary databases.
*The complete properties file will be read at once after the primary connection is made.
*The auxiliary connections will be made in an all-or-none manner and in one configuration only. For example, from the properties file in the following example, if "ALB-sales" is chosen as the configuration, then connection to both "New York" and "Massachusetts" will be made as auxiliary connections. If an error occurs while making connections to any of the databases, then all other successful connections will be disconnected.
*The configuration file can be specified as part of the connection URL to the primary database. The file is named within square brackets after the primary database, as shown in the following example.
jdbc:datadirect:openedge://localhost:6790;databaseName=empty[-mdbq:Alb Sales]
Similarly, the properties file can be specified when identifying the DSN used by an ODBC client, as shown in the following example.
Data Source Name: AlbTeams
Description: ALB Teams Databases
Host Name: albhost
Port Number: 3535
Database Name: empty[-mdbq:AlbSales]
User ID: bjones
Notes
*Once the properties file is read and the list of database objects is constructed, the database objects in the configuration is filtered on the basis of the configuration specified by the user in the connection URL. The connections to those databases are then established as auxiliary connections.
*A configuration specified in the connection URL that does not exist in the properties file will result in an appropriate error returned to the application and only the primary connection will exist. A message is also logged to the database log file (.lg).
*If a configuration is not specified on the connection URL, the primary database operates in single-database environment.
*The auxiliary database object list that is the parsed contents of the properties file will be maintained internally by the OpenEdge SQL Server and is shared process-wide. Each time a new client connects to the server, the server will check the file modification timestamp. If the file has not been changed since the last time the properties file was read and the DB object list was created, the server will use that list with appropriate mutexing to read the list concurrently. Otherwise, the server will refresh the list with the properties file contents appropriately mutexed to prevent readers from accessing the list when it is being updated.
*Once the application/client disconnects from the server, all auxiliary connections initiated on its behalf by the server through the properties file, will be disconnected in an orderly manner.