Sample connecting to a database

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 SELECT statement and the fetched customer name 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 source 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 SQL connections with subsequent CONNECT instructions (to other databases, for example). The DISCONNECT instruction can be used to disconnect from specific SQL sessions, or from all SQL 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.

CONNECT TO "custdemo" -- USER "dbuser" USING "dbpswd"

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, or with the VAR statement in subsequent code-blocks. 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 .sch schema file during compilation, not at runtime. Make sure that your schema files correspond exactly to the production database.

SCHEMA custdemo
...
DEFINE cust_name LIKE customer.cust_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 declared with the VAR instruction has the scope of the code block where 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.

Module variables can be shared with other modules, by specifying the PUBLIC keyword before DEFINE. This allows you to organize your source modules properly, by grouping shared module variables in the module they belong to, instead of grouping all shared variables in a globals file.

Passing variables to and returning values from functions

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. If the function returns a single value, it can be used in an expression.

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 ... END IF

Retrieving data from a database

Using Static SQL syntax, an 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.

SELECT cust_name INTO rec.cust_name FROM customer
   WHERE cust_num = rec.cust_num

Static SQL syntax is the essence of the BDL programming language, as it allows to write SQL statements directly in the source code and increases code readability. However, not all possible SQL syntax is allowed. If you need to use specific SQL syntax, use dynamic SQL, for example with the PREPARE and EXECUTE instructions:

PREPARE s1 FROM "SELECT cust_name FROM customer WHERE cust_num = ?"
EXECUTE s1 USING rec.cust_num INTO rec.cust_name