The "Connect to database" Program

This program illustrates connecting to a database and retrieving data, defining variables, and passing variables between functions.

A row from the customer table of the custdemo example database is retrieved by an SQL statement and displayed to the user.

Connecting to the database

To connect to a database server, most database engines require a name to identify the server, a name to identify the database entity, a user name and a password.

Connecting through the Open Database Interface, the database can be specified directly, and the specification will be used as the data source. Or, you can define the database connection parameters indirectly in the fglprofile configuration file, and the database specification will be used as a key to read the connection information from the file. This technique is flexible; for example, you can develop your application with the database name "custdemo" and connect to the real database "custdemo1" in a production environment.

The CONNECT instruction opens a session in multi-session mode, allowing you to open other connections with subsequent CONNECT instructions (to other databases, for example). The DISCONNECT instruction can be used to disconnect from specific sessions, or from all sessions. The end of a program disconnects all sessions automatically.

The username and password can be specified in the CONNECT instruction, or defaults can be defined in the fglprofile file. Otherwise, the user name and password provided to your operating system will generally be used for authentication.

CONNECT TO "custdemo"

Variable definition

A Variable contains volatile information of a specific BDL data type. Variables must be declared before you use them in your program, using the DEFINE statement. After definition, variables have default values based on the data type.

DEFINE cont_ok INTEGER

You can use the LIKE keyword to declare a variable that has the same data type as a specified column in a database schema. A SCHEMA statement must define the database name, identifying the database schema files to be used. The column data types are read from the schema file during compilation, not at runtime. Make sure that your schema files correspond exactly to the production database.

DEFINE store_name LIKE customer.store_name

Genero BDL allows you to define structured variables as records or arrays. Examples of this are included in later chapters.

Variable scope

Variables defined in a FUNCTION , REPORT or MAIN program block have local scope (are known only within the program block). DEFINE must precede any executable statements within the same program block. A variable with local scope can have its value set and can be used only within the function in which it is defined.

A Variable defined with module scope can have its value set and can be used in any function within a single source-code module. The DEFINE statement must appear at the top of the module, before any program blocks.

A Variable defined with global scope can have its value set and can be used in any function within any modules of the same program.

For a well-structured program and ease of maintenance, we recommend that you use module variables instead of global when you need persistent data storage. You can include get/set functions in the module to make the value of the variable accessible to functions in other modules.

A compile-time error occurs if you declare the same name for two variables that have the same scope.

Passing variables

Functions can be invoked explicitly using the CALL statement. Variables can be passed as arguments to a function when it is invoked. The parameters can be variables, literals, constants, or any valid expressions. Arguments are separated by a comma. If the function returns any values, the RETURNING clause of the CALL statement assigns the returned values to variables in the calling routine. The number of input and output parameters is static.

The function that is invoked must have a RETURN instruction to transfer the control back to the calling function and pass the return values. The number of returned values must correspond to the number of variables listed in the RETURNING clause of the CALL statement invoking this function. If the function returns only one unique value, it can be used as a scalar function in an expression.

CALL myfunc()
CALL newfunc(var1) RETURNING var2, var3
LET var2 = anotherfunc(var1)
IF testfunc1(var1) == testfunc2(var1) THEN ...

Retrieving data from a database

Using Static SQL, an embedded SQL SELECT statement can be used to retrieve data from a database table into program variables. If the SELECT statement returns only one row of data, you can write it directly as a procedural instruction, using the INTO clause to provide the list of variables where the column values will be fetched. If the SELECT statement returns more than one row of data, you must declare a database cursor to process the result set.