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.
- Compile the single module
program:
fglcomp connectdb.4gl
- Execute the program:
fglrun connectdb.42m