Opening a database connection

A database connection identifies the SQL database server and the database entity the program connects to, in order to execute SQL statements.

To connect to a database server, the database driver needs to be loaded, and the SQL data source most be provided. Additionally, user authentication with user name / password may also be needed. All these parameters define connection information.

There are different ways to give connection information, and it is possible to mix the different methods to specify connection parameters. However, if provided, the database user name and password have to be specified together with the same method.

A database connection is performed in programs with the DATABASE or CONNECT TO instruction:

CONNECT TO dbspec [USER username USING password]

DATABASE dbspec

Prefer the CONNECT TO instruction, as it allows to specify a user name and password.

For portability reasons, it is not recommended that you use database vendor specific syntax (such as 'dbname@dbserver') in the DATABASE or CONNECT TO instructions: Connections must be identified in programs by a single name, while connection parameters are provided in external files.

Indirect database specification uses entries in the FGLPROFILE configuration file: When a DATABASE or CONNECT TO instruction is executed with the parameter dbspec, the runtime system first looks into FGLPROFILE for entries starting with dbi.database.dbspec, and uses these connection parameters if found. Otherwise, the runtime system will do direct database specification, by using the dbspec string to connect to the server.

Important: When using FGLPROFILE entries for database connection parameters, keep in mind that entries must be written in lowercase.

Use a string variable with the DATABASE or CONNECT TO statement, in order to specify the database source at runtime. This solution gives you the best flexibility.

The string variable can be set from your own configuration file, from a program argument or from an environment variable.

MAIN
  DEFINE db, us, pwd CHAR(50)
  LET db = fgl_getenv("MYDBSOURCE")
  LET us = arg_val(2)
  LET pwd = arg_val(3)
  CONNECT TO db USER us USING pwd
  ...
END MAIN