Example: connectdb.4gl

This program connects to the custdemo database, selects the customer 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.

Program connectdb.4gl:
  1 -- connectdb.4gl
  2 
  3 SCHEMA custdemo
  4 
  5 MAIN
  6   DEFINE l_cust_name LIKE customer.cust_name
  7 
  8   CONNECT TO "custdemo"
  9 
 10   LET l_cust_name = select_name(101)
 11   DISPLAY l_cust_name
 12 
 13   DISCONNECT CURRENT
 14 
 15 END MAIN
 16 
 17 FUNCTION select_name(f_cust_num LIKE customer.cust_num) RETURNS (LIKE customer.cust_name)
 18   DEFINE f_cust_name LIKE customer.cust_name
 19 
 20   SELECT cust_name INTO f_cust_name
 21     FROM customer
 22     WHERE cust_num = f_cust_num
 23 
 24   RETURN f_cust_name
 25 
 26 END FUNCTION
Note:
  • Line 3: 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.
  • Line 6: Using DEFINE the local variable m_cust_name is declared as being LIKE the customer.cust_name column; that is, it has the same data type definition as the column in the customer table of the custdemo database.
  • Line 8: 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 SQL session is started. Any subsequent SQL statement is executed in the context of the current SQL 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_cust_name.
  • Line 11: The value of m_cust_name is displayed to the user on the standard output.
  • Line 13: The DISCONNECT instruction disconnects you from the current session. As there are no additional lines in the program block, the program terminates.
  • Line 17: 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_cust_num, defined LIKE the customer.cust_num column. The RETURNS clause indicates the type and number of values returned by the function.
  • Line 18 defines the f_cust_name local variable used in the function, that will hold the customer name fetched from the database table.
  • Lines 20 thru 22 contains the SELECT SQL statement to retrieve the customer name for the number passed as parameter. Since the customer number is unique, the WHERE clause ensures that only a single row will be returned, so there is no need for an SQL cursor here. Note the INTO clause using the local variable (this part will in fact be excluded from the real SQL statement sent to the database server). The f_cust_num local variable is used as SQL parameter in this SELECT statement (it will be converted to a ? place holder, with f_cust_num value binding when executing the statement)
  • Line 24: The RETURN statement causes the function to terminate, returning the value of the local variable f_cust_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_cust_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.

From the command line:
  1. Define the FGLDBPATH environment variable with the path to the directory containing the custdemo.sch schema file, to let fglcomp find SQL column definitions for DEFINE … LIKE instructions:
    $ FGLDBPATH=/opt/gst/gst-demo/BDLTutorial/database-sqlite
$ export FGLDBPATH
  2. Compile the single module program:
    fglcomp -M connectdb.4gl
  3. Define the DBPATH environment variable with the path to directory containing the custdemo.db SQLite database file, in order to connect to this database with fglrun:
    $ DBPATH=/opt/gst/gst-demo/BDLTutorial/database-sqlite
$ export DBPATH
  4. Execute the program:
    fglrun connectdb.42m

The program displays in a customer name.