602SQL Documentation Index  

Client Application Variables in SQL Statements

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

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:

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';
'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

List of topics: