SQL Statements - Preparing and Executing

Client Application Variables in SQL Statements

When a client is communicating with a SQL server, you can also use variables, which:

are declared in the client application and are accessible by the SQL server and which
are used in the SQL statements sent to the server.

These variables are usually called embedded variables or host variables. They can serve e.g. for entering values written to the database, for enhancing the search criteria or for getting a query or a function result from the SQL server. Host variables affect behaviour of the statement in which they're used.

You must specify a colon before the id identifier of such variable in SQL statements. Then maximum number of valid characters in the identifier was 18 up to version 9.0, 31 in version 9.5 or higher.

You can also add a flag to the SQL statement, whether the value is passed from client to the server, from server to the client or both directions. This flag makes the data transfers between the client application and the server slightly more effective. The variable is then entered in this way:

Variable with two-way value transfer: :id or :<>id or :><id
Variable for server to client transfer only: :>id
Variable for client to server transfer only: :<id

Usage Examples:

The id_number, add, salary, name, boss, max_salary and d_area1 variables declared in a client program (a PASCAL like language) are used in the following examples.

The UPDATE statement on a selected record:

id_number := 1294;
add := 500;
SQL_execute('UPDATE Employees SET sal=sal+:<add WHERE id_pers=:<id_number');

Read the value of the SALARY column from the specified record into the salary variable:

id_number := 1294;
SQL_execute('SELECT SALARY INTO :>salary FROM Employees WHERE id_pers=:<id_number');

Insert a new record:

id_number := 1294;
salary := 12000;
name := 'GREEN';
SQL_execute(
'INSERT INTO Employees(id_pers,sal,name) VALUES(:salary,:id_number,:name)');

Call a procedure:

id_number := 1294;
SQL_execute('CALL boss_name(:<id_number,:>boss)');

Call a function:

id_number := 1294;
SQL_execute('SET :>max_salary = MAXIM_SALARY(:<id_number)');

An example of a dynamic query (the WHERE clause uses the values of the d_area1 global variable):

SELECT Area2.name,Area2.code
FROM Area2
WHERE SUBSTRING(CAST(code AS CHAR(2)) FROM 1 FOR 1) = CAST(:d_area1 AS CHAR(1))

List of topics:

External Languages Variables in SQL Statements

Ways to Use the SQL Language

SQL Statements - Preparing and Executing