Example: connectdb.4gl

This program connects to the custdemo database, selects the store name from the customer table and displays it to the user.

Note: The line numbers shown in the examples in this tutorial are not part of the BDL code; they are used here so specific lines can be easily referenced. The BDL keywords are shown in uppercase, as a convention only.
Program connectdb.4gl:
01 -- connectdb.4gl 
02 SCHEMA custdemo 
03
04 MAIN
05   DEFINE 
06    m_store_name LIKE customer.store_name 
07
08   CONNECT TO "custdemo"
09
10   CALL select_name(101) 
11       RETURNING m_store_name
12   DISPLAY m_store_name 
13  
14   DISCONNECT CURRENT
15
16 END MAIN
17
18 FUNCTION select_name(f_store_num)
19   DEFINE 
20    f_store_num  LIKE customer.store_num,
21    f_store_name LIKE customer.store_name 
22
23   SELECT store_name INTO f_store_name 
24      FROM customer 
25      WHERE store_num = f_store_num 
26
27   RETURN f_store_name 
28
29 END FUNCTION  -- select_name
Note:
  • Line 02 The SCHEMA statement is used to define the database schema files to be used as custdemo. The LIKE syntax has been used to define variables in the module.
  • Lines 05 and 06 Using DEFINE the local variable m_store_name is declared as being LIKE the store_name column; that is, it has the same data type definition as the column in the customer table of the custdemo database.
  • Line 08 A connection in multi-session mode is opened to the custdemo database, with connection parameters defined in the fglprofile configuration file. Once connected to the database server, a current database session is started. Any subsequent SQL statement is executed in the context of the current database session.
  • Line10 The select_name function is called, passing the literal value 101 as an argument. The function returns a value to be stored in the local variable m_store_name.
  • Line 12 The value of m_store_name is displayed to the user on the standard output.
  • Line 14 The DISCONNECT instruction disconnects you from the current session. As there are no additional lines in the program block, the program terminates.
  • Line 18 Beginning of the definition of the function select_name. The value "101" that is passed to the function will be stored in the local variable f_store_num.
  • Lines 19 thru 21 Defines multiple local variables used in the function, separating the variables listed with a comma. Notice that a variable must be declared with the same name and data type as the parameter listed within the parenthesis in the function statement, to accept the passed value.
  • Lines 23 thru 25Contains the embedded SELECT ... INTO SQL statement to retrieve the store name for store number 101. The store name that is retrieved will be stored in the f_store_name local variable. Since the store number is unique, the WHERE clause ensures that only a single row will be returned.
  • Line 27 The RETURN statement causes the function to terminate, returning the value of the local variable f_store_name. The number of variables returned matches the number declared in the RETURNING clause of the CALL statement invoking the function. Execution of the program continues with line 12.

The database schema file

This program requires a database schema file because of the use of the LIKE keyword when defining the variable m_store_name. The database schema contains the definition of the database tables and columns and is used to centralize column data types to define program variables. The schema file for the BDLTutorial has already been extracted from the custdemo database and is used at compile time.

To learn more about database schema files see Database schema in the Genero Business Development Language User Guide.

Compiling and executing the program

You can compile and execute the connectdb application using the Execute option in the Project view of Genero Studio or use the command line options.

  1. Compile the single module program:
    fglcomp connectdb.4gl
  2. Execute the program:
    fglrun connectdb.42m