CHAPTER 3: Script Statements

call funcname ([expr {, expr}]);

1. The funcname may be a GUI function or an action.
2. If exprs are specified, they are evaluated and the results passed as arguments to the action or function.

call menu ();

call empgui_dialog_info ("message", "Hello world!");

call application appl_name ([expr {, expr}]);

1. The appl_name can be a string variable or a quoted string.
2. The expressions are evaluated and passed as arguments to the application. If the application does not take parameters, the parentheses must be empty.
3. If an argument is a string containing spaces, the string must be enclosed in additional quotation marks; otherwise, the spaces will be interpreted as argument separators.
4. The application is executed as a separate process. Both the calling application and the called application will be active.

call application "test"();

call application "test2" ("'first arg'", "'second arg'");

empgui mydb test2 'first arg' 'second arg'

check expr for attr;

1. If expr evaluates to a value which is not valid for the attribute specified, an error message is displayed in dialog box.
2. This statement will take into account the data type and any range check defined on the attribute. It also checks whether the attribute allows null values.

check "names"@"name" for "nametable"."name";

close cursor_name;

1. It closes cursors initialized with opens but does not discard the cursor declaration.
2. Since a table instance can only support one cursor, this statement frees the table instance to be used for another cursor.
3. This statement will fail giving an error if the cursor was never opened.
4. If a cursor on a table is established, and another table instance is to be opened on the table, a close should be done on the cursor first. This guarantees that any work on the cursor is written from program buffers for the new table instance to pick up.

close "namesakes";

close table tabinst {, tabinst};

1. This command cannot close a table instance currently in use in a cursor. To close such a table, first issue a close cursor and an undeclare.

close table "nametable";

commit [transaction];

1. The current transaction is committed. The transaction must have been started with a start transaction command. All database operations subsequent to the start of the transaction are committed.
2. If no transaction is in progress, an error message is reported.
3. To roll back a transaction, see the rollback command.

commit transaction;

declare cursor_name cursor for query [ with count ];

1. This statement declares a cursor without executing the SELECT statement. The actual query is executed using the open cursor statement.
2. The with count option causes the sqlcount variable to be set to the number of records selected when the cursor is opened.
3. The cursor declared with this statement can be removed with the undeclare statement.
4. Attributes are specified using the form tab_inst.attr where tab_inst is the table instance, and attr is the name of the attribute. The query can only refer to attributes which are in the table instances specified in the from clause.
5. A select_item is either an attribute or an expression. Values of expressions can be accessed using the form cursor_name."expression_n", where n is an integer identifying the expression. If the select_item has a PRINT clause, its value can also be accessed using the form cursor_name.print_item_name. The value of a select_item can also be accessed using the form cursor_name.i where i is an integer indicating the column number of the select_item.
6. The where_clause consists of conditions similar to those in Empress SQL. In addition, expressions in the where_clause can refer to objects (such as fields, radio boxes etc.). An expression will be eliminated from the where_clause if a null value (for example, if a field is empty) appears on the right side of a comparison operator opposite an attribute.
7. If a group_clause is used, the result of the query will contain an aggregate record after each group of records. The aggregate record will have the results of aggregate expressions, and will contain null values for non-aggregate select_items.
8. Refer to the Empress SQL: Reference manual for details of the SELECT statement.

The cursor c1 defines a query to retrieve records from table instance staff. Only records where attribute name matches the pattern in field name will be selected, but if field name is NULL, all records will be selected. The value of the aggregate expression can later be accessed using one of these forms:

"c1"."expression_1"

"c1"."average"

"c1".i

delete from tabinst;

1. The record deleted is the current record of the cursor generated from the tabinst. If there is no current record for a tabinst, a warning message is displayed in a dialog box.
2. The tabinst must have been opened in update or deferred mode.

delete from "nametable";

disable cursor;

1. The disable command effectively disestablishes the current record without losing its position. The lock on the record is released, and attempts to update or delete the record will fail.
2. The cursor is disabled until a current record is established with a fetch or enable statement.

disable "namesakes";

enable cursor;

1. If the cursor was disabled, enable re-establishes the current record if it still exists or is not locked by another user.
2. If a multilist has been associated to the cursor with the function empgui_multirecord_define(), enable causes the highlighted row in the multilist to become the current record.

enable "namesakes";

1. Whenever you exit a module, the module exit script is executed.
2. If the confirm option is specified, the user will be prompted for exit confirmation with a "Confirm exit? (y/n)" message.
3. If the script option is specified, the current script action is exited, but the module is still running. If the script action was called by another action, control is not returned to that action.

exit;

exit script;

1. The cursor must be opened before a fetch can be performed.
2. The next option specifies that the row to be retrieved is the one after the current row in the cursor.
3. The prior option specifies that the row to be retrieved is the one before the current row in the cursor.
4. The first option specifies that the row to be retrieved is the first one in the cursor.
5. The last option specifies that the row to be retrieved is the last one in the cursor.
6. The relative option specifies that the row to be retrieved is n rows away from the current row in the cursor. If n is 0, the current row is fetched again. Note that n can be a negative integer.
7. The default option is next.
8. A successful fetch automatically enables the cursor, establishes a current row, and initializes each attribute of the table instances in the cursor with the values of the selected row's attributes. The system variable sqlresult is 1 after a successful fetch.
9. If there was no such row, sqlresult is 0. For example, if the current row is the last one, and a fetch next if performed, sqlresult returns 0, and there is no current row.
10. If a row cannot be fetched because of locks, sqlresult is set to -1, and the row becomes current, although the values of its attributes could not be read.

fetch next from "c1"; 
switch sqlresult 
  case 0: 
     call empgui_dialog_info ("Next", "No more row"); 
  case 1: 
     call empgui_objects_set ("win", "c1"); 
  case -1: 
     fetch relative 0 from "c1"; 
     if (sqlresult = 1) 
        call empgui_objects_set ("win", "c1"); 
     else 
        call empgui_dialog_info ("Next", "Locked row"); 
     end; 
end; 

1. The cursor must be opened before a fetch can be performed.
2. If the query for which the cursor was declared does not have a group_clause, all the rows selected are considered to be part of the same group.
3. The next option specifies that the group to be retrieved is the one after the current group in the cursor.
4. The prior option specifies that the group to be retrieved is the one before the current group in the cursor.
5. The first option specifies that the group to be retrieved is the first group in the cursor.
6. The last option specifies that the group to be retrieved is the last one in the cursor.
7. The relative option specifies that the group to be retrieved is n groups away from the current group in the cursor. If n is 0, the current group is fetched again. Note that n can be a negative integer.
8. The default option is next.
9. A successful fetch group advances the current row pointer to the aggregate row of the specified group. The system variable sqlresult is 1 after a successful fetch group.
10. If there was no such group, sqlresult is 0. For example, if the current group is the last one, and a fetch group next if performed, sqlresult returns 0, and there is no current row.

fetch group next from "c1"; 
switch sqlresult 
  case 0: 
    call empgui_dialog_info ("Next", "No more group"); 
  case 1: 
    call empgui_multirecord_display ("c1"); 
end;  

global type variable {; type variable} ; end;

1. The named variables are declared to be global to the application and of the given types. See Chapter 1.2 - "Variables".
2. A global variable may only be declared in the global script action of the module.
3. The variable may be an array available, in which case the number of elements must be specified on an integer constant between square brackets after the variable name.

global 
   char name; 
   integer age; 
   char children [5]; 
end; 

where elseif_clause has the format:

elseif condition [then] 
{statement;} 
[|else{statement;|] 
 |elseif_clause  | 

and condition may be one of the following:

TRUE
FALSE
(condition)
condition or condition
condition and condition
not condition

exprrange value[|exclusive|]to value[|exclusive|] 
                |inclusive|          |inclusive| 
exprbetween value[|exclusive|]and value[|exclusive|] 
                  |inclusive|           |inclusive| 
expr[|= |] null 
     |!=| 
     |~=| 
null expr 

expr |=       | expr 
     |<       | 
     |>       | 
     |<=      | 
     |>=      | 
     |!=      | 
     |<>      | 
     |~=      | 
     |like    | 
     |not like| 
     |match   | 
     |!match  | 
     |smatch  | 
     |!smatch | 

where:

(condition)	guarantees precedence.
or	is an or conjunction which returns true if either condition is true.
and	is an and conjunction which returns true if both conditions are true.
range between	is a range or between condition which returns true if the value falls in the given range. The exclusive keyword means that the boundary values are not included in the range. The inclusive keyword means that the boundary values are included in the range. The keyword excl is a synonym for exclusive and incl is a synonym for inclusive. The default is inclusive.
Patterns	are operators like, match, smatch (single-case pattern match), !match and !smatch providing pattern matching. Pattern matching is as for Empress Query Language WHERE clauses. The interpretation of patterns is in the following table.

Pattern Matching Characters
Character	Use	Example
?	Matches any character in the position.
*	Matches 0 or more occurrences of any character.
[...]	Matches any of a set of characters in the position.	[abc] matches "a", "b", "c"
{...}	Matches 0 or more occurrences of a fixed length pattern.	{[a-z]} matches any string of lower case letters
[.-.]	Matches any range of characters in the position.	[a-cf-i] matches "a", "b", "c", "f", "g", "h", "i"
[^...]	Matches anything but a set or range of characters in the position.	[^123] matches everything but 1, 2, and3; [^a-d] matches anything but "a", "b", "c", and "d"
...|...	Requires a match on either side of the bar.	"ab|cd" requires a match on "ab" or "cd"
...&...	Requires match on both sides of the ampersand.	[a-z]&[^x] matches any letter except "x"
\	Placed before any of the special characters "?", "*", "|", "&", "{,}", "[,]", and "\", causes the character to be interpreted as an ordinary character.

1. If the condition is true, the statements after the then are executed. If the condition is false, the statements following the else or elseif are executed.
2. In a condition that compares two expressions, neither expression should evaluate to null, else an error is flagged. To prevent this from occurring, test for null explicitly before using an expression that may have a null value.
3. Attempting to compare a string and a number results in an expression failure. An error message will be generated.

if "names"@"name" = "stop" then 
   exit; 
end;

1. If the field is empty prompt for a value.
2. If the value is stop exit the application.
3. If there is a value and it is not stop call the script get_details.

if "names"@"name" = null then 
  call empgui_dialog_info ("Name missing", "Please enter a name"); 
elseif "names"@"name" = "stop" then 
  exit; 
else 
  call get_details (); 
end;

insert into tab_inst;

1. The values of the attributes in the table instance are used to create a new record in the table associated with the table instance.
2. The table instance must have been opened in update or deferred mode.
3. Inserting a record does not affect the position of the current row of a cursor (if any) associated with the table instance. In other words, the record inserted does not become the current record. To prevent confusion on the part of the user, the cursor should be disabled immediately after an insert.

let "staff"."name" = "win"@"name_field"; 
let "staff"."dept" = "win"@"dept_radio_box"; 
let "staff"."salary" = "win"@"salary_scale"; 
insert into "staff"; 

1. Remember that an object is usually cited as window@object. A window is the name of a window or bin. An object_name is the name of an object.

let "names"@"address" = "nametable"."address";

let i = 0; 
while (i<20) 
   let i = i+1; 
   fetch next from "names_cursor"; 
   let names[i] = "nametable"."name"; 
end; 

local type variable {; type variable} ; end;

1. The named variables are declared to be local to the script and of the given types. See Chapter 1.2 - "Variables".
2. A local variable may have the same name as a global variable created in the global script. Within the current script the local variable is recognized, while outside the script the global variable is recognized.
3. There may be only one local statement in a script. It must follow the parameters statement, if any, and precede all other statements.
4. The variable may be an array variable, in which case the number of elements must be specified as an integer between square brackets after the variable name.

local
   char name; 
   integer age; 
   char children [3]; 
end; 

1. The lock statement locks the table known by the table name. It is only effective during a transaction.
2. A table may be locked in exclusive or share modes. An exclusive lock gives exclusive access to the table. No one else may select from or update the table and no other locks may be placed on it concurrently. A share-mode lock guarantees only that you will be able to select from the table and precludes update operations from others.
3. Several share-mode locks can be placed on a table at one time. If there is more than one share-mode lock on a given table it may not be updated. If you have placed the only share-mode lock on a table you may perform updates on that table. Records so updated will not be accessible to others for the remainder of the transaction.
4. The lock command will only have effect if some level of locking has been set for the table using the Empress LOCK LEVEL Query Language command.

lock "nametable" in exclusive mode;

where:

function	is an Empress GUI Builder function or a user-defined action
num	is an integer identifying the exception condition

1. An event is a minor exception condition that is not reported by default. A warning is an exception that would normally cause a message to be displayed in a pop-up dialog box. A database error is an error related to a database command. An error is a serious exception.
2. The call function (...) option allows an action or GUI function to be called with the appropriate arguments. These arguments may not reference local variables.
3. The display option causes a message related to the exception condition to be displayed in a dialog box. This is the default for most exceptions except events.
4. The ignore option causes the exception condition to be ignored. This is the default for events.
5. If one or more num codes are specified, only exceptions with those codes will be trapped. Otherwise, all exceptions of the specified class are trapped.

open cursor_name;

1. The open statement effectively executes the SELECT statement defined by the cursor declaration, and initializes or re-initializes the cursor with records. There is no current record immediately after an open.
2. A cursor can be re-opened without explicitly closing it first.
3. If the cursor is associated with a multilist with the function empgui_multirecord_define(), opening the cursor automatically causes the multilist to be updated.

declare "namesakes" cursor for select from "nametable"

where "nametable"."name" = "names"@"name";

open "namesakes";

where:

tablename	is the name of an Empress table.
tabinst	are the "instance names" by which the table is to be known and referenced.

1. The tablename may be specified using database:tablename where database is the database containing the table and tablename is the name of the table in the database. The database may be other than the current user database.
2. tabinsts are names by which the tables are known in the application.
3. If no tabinst is specified, the value of tablename is taken as the "instance name".
4. Tables may be opened in one of several modes. If a table is opened in read mode no update type operations can be done on it. With record level locking defined for the table, a read lock is in effect for each record while it is current.
5. If a table is opened in update mode records may be inserted, updated and deleted from it. With record-level locking defined for the table an update lock (i.e., exclusive access lock) is in effect for each record while it is current.
6. If a table is opened in deferred mode records may also be inserted, updated and deleted from it. However, if locking for the table is set at the record level only a read lock is placed on each record when it becomes current. The read lock is exchanged for an update lock only when an update type operation occurs. The update lock lasts for the duration of the update type operation, then it is replaced with a read lock again. The read lock is removed when the record is no longer current. Opening a table in deferred mode thus keeps the table in a shareable state more of the time (given record level locking on the table).
7. Locking levels are described in the Empress SQL: Reference under the LOCK LEVEL command.
8. Update type operations on a table via any instance name affect the records accessed through all the instance names.
9. The Empress variables MSIAEXCLRETRY, MSIAEXCLSLEEP, MSIALOCKRETRY and MSIALOCKSLEEP are used when attempting to place locks on a table.
10. The time required to open a large number of tables in the same database can be reduced if the opens are issued consecutively in a script.

open table "nametable" update;

open table "nametable" update as "exampletable";

parameters type variable {; type variable} ; end;

1. Parameters are values that are passed to a script action when that action is run. The values are stored in the named variable and must be of the given types. See Chapter 1.2 - "Variables".
2. A variable defined as a parameter is known only in the script action where it is so defined, i.e., it is local in scope.
3. Parameters are initialized when the action is invoked with arguments. The parameters assume the values of the arguments. Empress reports an error if a script is invoked with a different number of arguments than the number of parameters defined in it.
4. There can be only one parameters statement in an action and it must precede all other statements.
5. The variable may be an array variable, in which case the variable name is followed by open and close square brackets, without specifying the dimension. Arrays are passed by reference.

parameters 
   char name; 
   integer age; 
   char children [ ]; 
end;

You could then execute the statements:

let offsprings[1] = "Jimmy";
let offsprings[2] = "Betsy";
let offsprings[3] = "Lilian";
call family ("John", 30, offsprings);

The family script action would be invoked, the name parameter would have the value John, the age parameter the value 30, and children would be an array of three elements containing values Jimmy, Betsy and Lilian.

return [expr];

1. Control returns to the script that invoked the script with the return statement.

return result;

let store_result = calculation (argument1, argument2);

1. This rolls back a transaction. A savepoint (given as a string starting with a letter) may be specified to roll back to the start of that nested transaction.
2. If no savepoint is specified, the entire transaction is rolled back.
3. A transaction is started with a start transaction. A save point is set with a savepoint statement. All database operations subsequent to the start of the transaction (or the save point) are rolled back.
4. If no transaction is in progress, an error message is reported.
5. To commit a transaction, see commit.

start transaction;

...

savepoint t1;

...

rollback transaction to t1;

savepoint savepoint;

1. A transaction must be in progress for the statement to succeed. A save point is marked in the transaction, starting a nested transaction.
2. A rollback transaction to savepoint statement can be used to rollback the transaction to any defined save point. A commit transaction statement will commit the entire transaction.
3. If the application ends before the transaction is explicitly committed or rolled back, it is committed by default.

start transaction;

...

savepoint t1;

...

rollback transaction to t1;

commit transaction;

1. A transaction is started. Any database operation on any database subsequent to this statement is part of the transaction until the transaction is either committed or rolled back.
2. If a transaction is already in progress, a savepoint can be used to roll back to later.
3. To commit a transaction, see commit. To roll back a transaction, see rollback.
4. If the application ends before the transaction is explicitly committed or cancelled, it is committed by default.
5. When one application is called by another, the transaction in the called application can be started and ended without affecting the calling application.

start transaction;

...

savepoint t1;

...

rollback transaction to t1;

commit transaction;

where:

expr	is an expression that evaluates to a string, an integer, or a decimal.
constant	is a constant of the same data type as expr.
statement	is the statement to be executed on a match.

1. The default case is chosen if the expression does not match any constant.

let name = "window1"@"radiobox"; 
switch name 
   case "Jim": call empgui_dialog_info ("Name", "It's Jim"); 
   case "Joe": call empgui_dialog_info ("Name", "It's Joe"); 
   case "Ann": call empgui_dialog_info ("Name", "It's Ann"); 
   default: call empgui_dialog_info ("Name", "Neither Jim, 
            Joe nor Ann"); 
end; 

undeclare cursor_name;

1. This statement will fail giving an error if the cursor was never established.

undeclare "namesakes";

update tab_inst;

1. The values of the attributes in the table instance are used to update the current record in the table associated with the table instance.
2. The table instance must have been opened in update or deferred mode.

let "staff"."salary" = "win"@"salary_scale";
update "staff";

while condition {statement;} end;

1. The statements are performed as long as the condition is true. See the if statement for the list of allowable conditions.

let number = 1;
while number < 100
   let number = number + 1;
end;