This section explains in detail how to use SQL stored procedures. In the beginning of this section, the general concepts of using the procedures are explained. Later sections go more in-depth and describe the actual syntax of different statements in the procedures. The end of this section discusses transaction management, sequences and other advanced stored procedure features.
Basic procedure structure
A stored procedure is a standard solidDB® database object that can be manipulated using standard DDL statements CREATE and DROP.
In its simplest form a stored procedure definition looks like:
"CREATEPROCEDUREprocedure_name parameter_section BEGIN declare_section_local_variables procedure_body END";
The following example creates a procedure called TEST:
"CREATEPROCEDUREtest BEGIN END"
Procedures can be run by issuing a CALL statement followed by the name of the procedure to be invoked:
CALLtest
Naming procedures
Procedure names have to be unique within a database schema.
All the standard naming restrictions applicable to database objects, like using reserved words and identifier lengths, apply to stored procedure names. For an overview and complete list of reserved words, see Reserved words.
Parameter section
A stored procedure communicates with the calling program using parameters. solidDB® supports two methods to return values to the calling program. The first method is the standard SQL-99 method, which uses parameters, and the other is a solidDB® proprietary method, RETURNS, which uses result sets.
Using parameters: Parameters are the standard SQL-99 method to return data in stored procedures. The solidDB® stored procedures accept three types of parameters:
▪Input parameters (IN), which are used as input to the procedure. Parameters are input parameters by default. Thus, the keyword IN is optional in parameter definitions.
▪Output parameters (OUT), which are returned values from the procedure.
▪Input/output parameters (INOUT), which pass values into the procedure and return a value back to the calling procedure.
When you declare input parameters in the procedure heading, you can access their values inside the procedure by referring to the parameter name. The parameter data type must also be declared.
The parameter declaration in the stored procedure definition uses the following syntax
parameter_definition ::= [parameter_mode] parameter_name data_type parameter_mode ::= IN | OUT | INOUT
There can be any number of parameters. When you call the procedure, you typically supply the input parameters in the same order as they are defined in the procedure.
You can specify default values to the parameters in the procedure definition. When you declare the parameter, add an equals character (=) and the default value after the parameter data type. For example:
"CREATE PROCEDURE participants( adults integer = 1, children integer = ’0’, pets integer = ’0’) BEGIN END"
When you call a procedure that has default values for parameters, you do not have to give values for all the parameters. To use default values for all parameters, you can omit the parameters. For example:
CALL participants()
To give a value to a parameter in the procedure call, you have two options:
Named parameters
Use the parameter name in the CALL statement and assign the parameter value by using the equals (=) character.
For example, to use default values for parameters adults and pets, and value 2 for children, use the following statement:
CALL participants(children = 2)
If a name is given to a parameter, all parameters following it must also have a name.
For example, the following statement returns an error because a parameter name is not defined for value 2, which is a parameter in the procedure definition that follows the parameter adults. The default value is used for parameter pets.
CALL participants(adults = 7,2)
The following statement succeeds because the unnamed parameter value 7 precedes the named parameter value children = 2. The default value is used for parameter pets.
CALL participants(7,children = 2)
Positional parameters
Supply the input parameters in the same order as they are defined in the procedure.
For example, to specify value 7 for adults, 3 for children, and 5 for pets, use the following statement:
CALL participants(7,3,5)
You can omit positional parameters only in reverse order (starting from the last positional parameter) and only if the parameters have default values.
For example, you can use the following statement to specify value 7 for adults and default values for children and pets:
You can use stored procedures to return a result set table with several rows of data in separate columns. This is a solidDB® proprietary method to return data and it is performed by using the RETURNS structure.
When you use the RETURNS structure, you must separately declare result set column names for the output data rows. There can be any number of result set column names. The result set column names are declared in the RETURNS section of the procedure definition:
By default, the procedure only returns one row of data containing the values as they were at the moment when the stored procedure was run or was forced to exit. However, it is also possible to return result sets from a procedure using the following syntax:
return row;
Every RETURN ROW call adds a new row into the returned result set where column values are the current values of the result set column names.
The following statement creates a procedure that has two input parameters and two result set column names for output rows:
"CREATE PROCEDURE PHONEBOOK_SEARCH (IN FIRST_NAME VARCHAR, LAST_NAME VARCHAR) RETURNS (PHONE_NR NUMERIC, CITY VARCHAR) BEGIN -- procedure_body END";
This procedure should be called by using two input parameter of data type VARCHAR. The procedure returns an output table consisting of two columns named PHONE_NR of type NUMERIC and CITY of type VARCHAR.
For example:
call phonebook_search (’JOHN’,’DOE’);
The result looks as follows (when the procedure body has been programmed):
PHONE_NR CITY 3433555 NEW YORK 2345226 LOS ANGELES
The following statement creates a calculator procedure:
"create procedure calc(i1 float, op char(1), i2 float) returns (calcresult float) begin declare i integer;
if op = ’+’ then calcresult := i1 + i2; elseif op = ’-’ then calcresult := i1 - i2; elseif op = ’*’ then calcresult := i1 * i2; elseif op = ’/’ then calcresult := i1 / i2; else calcresult := ’Error: illegal op’; end if end";
You can test the calculator with the command:
call calc(1,’/’,3);
With RETURNS, select statements can also be wrapped into database procedures. The following statement creates a procedure that uses a select statement to return backups created from the database:
"create procedure show_backups returns (backup_number varchar, date_created varchar) begin -- First set action for failing statements. exec sql whenever sqlerror rollback, abort;
-- Prepare and execute the select statement exec sql prepare sel_cursor select replace(property, ’backup ’, ’’), substring(value_str, 1, 19) from sys_info where property like ’backup %’; exec sql execute sel_cursor into (backup_number, date_created);
-- Fetch first row; exec sql fetch sel_cursor; -- Loop until end of table while sqlsuccess loop -- Return the fetched row return row; -- Fetch next exec sql fetch sel_cursor; end loop; end";
Declare section
Local variables that are used inside the procedure for temporary storage of column and control values are defined in a separate section of the stored procedure directly following the BEGIN keyword.
The syntax of declaring a variable is:
DECLAREvariable_namedatatype;
Note that every declare statement should be ended with a semicolon (;).
The variable name is an alphanumeric string that identifies the variable. The data type of the variable can be any valid SQL data type supported. For supported data types, see Data types.
For example:
"CREATE PROCEDURE PHONEBOOK_SEARCH (FIRST_NAME VARCHAR, LAST_NAME VARCHAR) RETURNS (PHONE_NR NUMERIC, CITY VARCHAR) BEGIN DECLARE i INTEGER;
DECLARE dat DATE;
END";
Note that input and output parameters are treated like local variables within a procedure with the exception that input parameters have a preset value and output parameter values are returned or can be appended to the returned result set.
Procedure body
The procedure body contains the actual stored procedure program based on assignments, expressions, and SQL statements.
Any type of expression, including scalar functions, can be used in a procedure body. For valid expressions, see expression.
Assignments
To assign values to variables either of the following syntax is used:
SETvariable_name=expression;
or
variable_name:=expression;
Example:
SET i = i + 20 ; i := 100;
Scalar functions with assignments
A scalar function is an operation denoted by a function name followed by a pair of parentheses enclosing zero or more specified arguments. Each scalar function returns one value. Note that scalar functions can be used with assignments, as in:
"CREATE PROCEDURE scalar_sample RETURNS (string_var VARCHAR(20)) BEGIN -- CHAR(39) is the single quotation mark/apostrophe string_var := ’Joe’ + {fn CHAR (39)} + ’s Garage’; END";
The result of this stored procedure is the output:
Joe’s Garage
For a list of solidDB®-supported scalar functions (SQL-92), see solidDB® SQL statements. The solidDB® Programmer Guide describes ODBC scalar functions, which contain some differences for SQL-92.
Variables, constants, and parameters in assignments
Variables and constants are initialized every time a procedure is executed. By default, variables are initialized to NULL. Unless a variable has been explicitly initialized, its value is NULL, as the following example shows:
BEGIN
DECLARE total INTEGER;
...
total := total + 1; -- assigns a null to total
...
Therefore, a variable should never be referenced before it has been assigned a value.
The expression following the assignment operator can be arbitrarily complex, but it must yield a data type that is the same as or convertible to the data type of the variable.
When possible, solidDB® procedure language can provide conversion of data types implicitly. This makes it possible to use literals, variables and parameters of one type where another type is expected.
Implicit conversion is not possible if:
▪information would be lost in the conversion
▪a string to be converted to an integer contains non-numeric data
Single quotation marks and apostrophes in string assignments
Strings are delimited by single quotation marks. If you want to have a single quotation mark within a string, then you can put two single quotation marks (''), side by side, to produce one quotation mark in your output. This is commonly known as an "escape sequence." Following is a stored procedure that uses this technique:
Note that in the last example there are five single quotation marks in a row at the end of the string. The last of these is the delimiter (the closing quotation mark); the preceding four are part of the data. The four quotation marks are treated as two pairs of quotation marks, and each pair of quotation marks is treated as an escape sequence representing one single quotation mark.
Expressions
Comparison operators:
Comparison operators compare one expression to another. The result is always TRUE, FALSE, or NULL. Typically, comparisons are used in conditional control statements and allow comparisons of arbitrarily complex expressions.
Operator
Meaning
—
is equal to
<>
is not equal to
<
is less than
>
is greater than
<=
is less than or equal to
>=
is greater than or equal to
!=
is not equal to
!= notation cannot be used inside a stored procedure, use the ANSI-SQL compliant <> instead.
Logical operators
The logical operators can be used to build more complex queries. The logical operators AND, OR, and NOT operate according to the tri-state logic illustrated by the truth tables shown below. AND and OR are binary operators; NOT is a unary operator.
Logical operators: NOT
NOT
true
false
null
false
true
null
Logical operators: AND
AND
true
false
null
true
true
false
null
false
false
false
false
null
null
false
null
Logical operators: OR
OR
true
false
null
true
true
true
true
false
true
false
null
null
true
null
null
As the truth tables show, AND returns the value TRUE only if both its operands are true. On the other hand, OR returns the value TRUE if either of its operands is true. NOT returns the opposite value (logical negation) of its operand. For example, NOT TRUE returns FALSE.
NOT NULL returns NULL because nulls are indeterminate.
When not using parentheses to specify the order of evaluation, operator precedence determines the order.
Note that 'true' and 'false' are not literals accepted by SQL parser but values. Logical expression value can be interpreted as a numeric variable:
false = 0 or NULL
true = 1 or any other numeric value
Example:
IF expression = TRUE THEN
can be simply written
IF expression THEN
IS NULL operator
The IS NULL operator returns the Boolean value TRUE if its operand is null, or FALSE if it is not null. Comparisons involving nulls always yield NULL. To test whether a value is NULL, do not use the expression,
IF variable = NULL THEN...
because it never evaluates to TRUE.
Instead, use the following statement:
IFvariableISNULLTHEN...
Note that when using multiple logical operators in solidDB® stored procedures the individual logical expressions should be enclosed in parentheses like:
((A >= B) AND (C = 2)) OR (A = 3)
Control structures
The following sections describe the statements that can be used in the procedure body, including branch and loop statements.
IF statement
Often, it is necessary to take alternative actions depending on circumstances. The IF statement executes a sequence of statements conditionally. There are three forms of IF statements: IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSEIF.
IF-THEN
The simplest form of IF statement associates a condition with a statement list enclosed by the keywords THEN and END IF (not ENDIF), as follows:
IF condition THEN statement_list; END IF
The sequence of statements is executed only if the condition evaluates to TRUE. If the condition evaluates to FALSE or NULL, the IF statement does nothing. In either case, control passes to the next statement. An example follows:
IF sales > quota THEN SET pay = pay + bonus; END IF
IF-THEN-ELSE
The second form of IF statement adds the keyword ELSE followed by an alternative statement list, as follows:
The statement list in the ELSE clause is executed only if the condition evaluates to FALSE or NULL. Thus, the ELSE clause ensures that a statement list is executed. In the following example, the first or second assignment statement is executed when the condition is true or false, respectively:
IF trans_type = ’CR’ THEN SET balance = balance + credit; ELSE SET balance = balance - debit; END IF
THEN and ELSE clauses can include IF statements. That is, IF statements can be nested, as the following example shows:
IF trans_type = ’CR’ THEN SET balance = balance + credit ; ELSE IF balance >= minimum_balance THEN SET balance = balance - debit ; ELSE SET balance = minimum_balance; END IF END IF
IF-THEN-ELSEIF
Occasionally it is necessary to select an action from several mutually exclusive alternatives. The third form of IF statement uses the keyword ELSEIF to introduce additional conditions, as follows:
If the first condition evaluates to FALSE or NULL, the ELSEIF clause tests another condition. An IF statement can have any number of ELSEIF clauses; the final ELSE clause is optional. Conditions are evaluated one by one from top to bottom. If any condition evaluates to TRUE, its associated statement list is executed and the rest of the statements (inside the IF-THEN-ELSEIF) are skipped. If all conditions evaluate to FALSE or NULL, the sequence in the ELSE clause is executed. Consider the following example:
IF sales > 50000 THEN bonus := 1500; ELSEIF sales > 35000 THEN bonus := 500; ELSE bonus := 100; END IF
If the value of "sales" is more than 50000, the first and second conditions are true. Nevertheless, "bonus" is assigned the proper value of 1500 since the second condition is never tested. When the first condition evaluates to TRUE, its associated statement is executed and control passes to the next statement following the IF-THEN-ELSEIF.
When possible, use the ELSEIF clause instead of nested IF statements. That way, the code will be easier to read and understand. Compare the following IF statements:
These statements are logically equivalent, but the first statement obscures the flow of logic, whereas the second statement reveals it.
Use of parentheses in IF-THEN statements
Parentheses can be used in IF-THEN statements with the following rules:
▪A single logical expression in an IF condition can use parentheses, but they are not required.
Example: parentheses used
IF (x > 0) THEN x := x - 1; END IF;
Example: parentheses not used
IF x > 0 THEN x := x - 1; END IF;
▪If there are multiple expressions inside a logical condition, parentheses are required around each subexpression.
IF (x > 0) AND (y > 0) THEN x := x - 1; END IF;
Example 1
This example shows use of valid logical conditions in IF statements.
"CREATE PROCEDURE sample_if_conditions BEGIN DECLARE x INT; DECLARE y INT; x := 2; y := 2;
Example 2
This example shows use of additional parentheses around the entire expression.
IF ((x > 0) AND (y > 0)) THEN x := x - 1; END IF;
WHILE-LOOP
The WHILE-LOOP statement associates a condition with a sequence of statements enclosed by the keywords LOOP and END LOOP, as follows:
WHILEconditionLOOP statement_list; ENDLOOP
Before each iteration of the loop, the condition is evaluated. If the condition evaluates to TRUE, the statement list is executed, then control resumes at the top of the loop. If the condition evaluates to FALSE or NULL, the loop is bypassed and control passes to the next statement. An example follows:
WHILE total <= 25000 LOOP ... total := total + salary; END LOOP
The number of iterations depends on the condition and is unknown until the loop completes. Since the condition is tested at the top of the loop, the sequence might execute zero times. In the latter example, if the initial value of "total" is greater than 25000, the condition evaluates to FALSE and the loop is bypassed altogether.
Loops can be nested. When an inner loop is finished, control is returned to the next loop. The procedure continues from the next statement after END LOOP.
Leaving loops
It may be necessary to force the procedure to leave a loop prematurely. This can be implemented using the LEAVE keyword:
WHILE total < 25000 LOOP total := total + salary; IF exit_condition THEN LEAVE; END IF END LOOP statement_list2
Upon successful evaluation of the exit_condition the loop is left, and the procedure continues at the statement_list2.
Note Although solidDB® databases support the ANSI-SQL CASE syntax, the CASE construct cannot be used inside a stored procedure as a control structure.
Use of parentheses in WHILE loops
The following code illustrates the rules for using parentheses in WHILE loops. Refer also to the release notes for additional information about using parentheses in WHILE loops.
--- This piece of code shows examples of valid logical conditions --- in WHILE loops. "CREATE PROCEDURE sample_while_conditions BEGIN DECLARE x INT; DECLARE y INT; x := 2; y := 2;
--- As shown below, a single logical expression in a --- WHILE condition --- may use parentheses. WHILE (x > 0) LOOP x := x - 1; END LOOP;
--- As shown below, although a single logical expression in a WHILE --- condition may use parentheses, the parentheses are not required. WHILE x > 0 LOOP x := x - 1; END LOOP; --- As shown below, if there are multiple expressions inside a --- logical condition, then you need parentheses around EACH --- individual expression. WHILE (x > 0) AND (y > 0) LOOP x := x - 1; y := y - 1; END LOOP;
--- The example below is the same as the preceding example, --- except that this has additional parentheses around the --- entire expression.
WHILE ((x > 0) AND (y > 0)) LOOP x := x - 1; y := y - 1; END LOOP;
NOT operator
Applying the logical operator NOT to a null yields NULL. Thus, the following two statements are not always equivalent:
IF x > y THEN IF NOT (x >y)THEN high:=x; high:=y; ELSE ELSE high:=y; high:=x; ENDIF END IF
The sequence of statements in the ELSE clause is executed when the IF condition evaluates to FALSE or NULL. If either or both "x" and "y" are NULL, the first IF statement assigns the value of "y" to "high", but the second IF statement assigns the value of "x" to "high". If neither "x" nor "y" is NULL, both IF statements assign the corresponding value to "high".
Handling nulls
Nulls can cause confusing behavior. To avoid some common errors, observe the following rules:
▪comparisons involving nulls always yield NULL
▪applying the logical operator NOT to a null yields NULL
▪in conditional control statements, if the condition evaluates to NULL, its associated sequence of statements is not executed
In the example below, you might expect the statement list to execute because "x" and "y" seem unequal. Remember though that nulls are indeterminate. Whether "x" is equal to "y" or not is unknown. Therefore, the IF condition evaluates to NULL and the statement list is bypassed.
x := 5; y := NULL; ... IF x <> y THEN -- evaluates to NULL, not TRUE statement_list; -- not executed END IF
In the next example, one might expect the statement list to execute because "a" and "b" seem equal. But, again, this is unknown, so the IF condition evaluates to NULL and the statement list is bypassed.
a := NULL; b := NULL; ... IF a = b THEN -- evaluates to NULL, not TRUE statement_list; -- not executed END IF
Zero-length strings
Zero length strings are treated by a solidDB® server like they are a string of zero length, instead of a null. NULL values should be specifically assigned as in the following:
SET a = NULL;
This also means that checking for NULL values will return FALSE when applied to a zero-length string.
Exiting a procedure
A procedure may be exited prematurely by issuing thekeyword
RETURN;
at any location. After this keyword, control is directly handed to the program calling the procedure returning the values bound to the result set column names as indicated in the RETURNS section of the procedure definition.
Returning data
You can return data with the OUT parameter mode, which is a standard SQL-99 method of returning data. This method allows you to pass data back to the program from the procedure. For syntax information, refer to solidDB® SQL statements.
The OUT parameter mode has the following characteristics:
▪The OUT parameter mode allows you to pass data back to the calling program from the procedure. Inside the calling program, the OUT parameter acts like a variable. That means you can use an OUT parameter as if it were a local variable. You can change its value or reference the value in any way.
▪The actual parameter that corresponds to an OUT parameter must be a variable; it cannot be a constant or an expression.
▪Like variables, OUT parameters are initialized to NULL.
Before exiting a procedure, you must explicitly assign values to all OUT parameters. Otherwise, the corresponding actual parameters will be null. If you exit successfully, solidDB® assigns values to the actual parameters. However, if you exit with an unhandled exception, solidDB® does not assign values to the actual parameters.
For a solidDB® proprietary method of returning data, see Using RETURNS.
Example stored procedure
Following is an example of a simple procedure that determines whether a person is an adult on the basis of a birthday as input parameter.
Note the usage of {fn ...} on scalar functions, and semicolons to end assignments.
"CREATE PROCEDURE grown_up (birth_date DATE) RETURNS (description VARCHAR) BEGIN DECLARE age INTEGER; -- determine the number of years since the day of birth age := {fn TIMESTAMPDIFF(SQL_TSI_YEAR, birth_date, now())}; IF age >= 18 THEN --If age is at least 18, then it’s an adult description := ’ADULT’; ELSE -- otherwise it’s still a minor description := ’MINOR’; END IF END";
SQL in a stored procedure
Using SQL statements inside a stored procedure is somewhat different from issuing SQL directly from tools like solidDB® SQL Editor (solsql) – a special syntax is required.
There are two ways to execute SQL statements inside a procedure:
▪Use EXECDIRECT syntax to execute a statement.
▪Treat the SQL statement as a "cursor".
EXECDIRECT
The EXECDIRECT syntax is particularly appropriate for statements where there is no result set, and where you do not have to use any variable to specify a parameter value. For example, the following statement inserts a single row of data:
Cursors are appropriate for statements where there is a result set, or where you want to repeat a single basic statement but use different values from a local variable as a parameter (for example, in a loop).
A cursor is a specific allocated part of the server process memory that keeps track of the statement being processed. Memory space is allocated for holding one row of the underlying statement, together with some status information about the current row (in SELECTS) or the number of rows affected by the statement (in UPDATES, INSERTS and DELETES).
In this way query results are processed one row at a time. The stored procedure logic should take care of the actual handling of the rows, and the positioning of the cursor on the required row(s).
There are five basic steps in handling a cursor:
1 Preparing the cursor - the definition
2 Executing the cursor - executing the statement
3 Fetching on the cursor (for select procedure calls) - getting the results row by row
4 Closing the cursor after use - still enabling it to re-execute
5 Dropping the cursor from memory - removing it
Preparing the cursor
A cursor is defined (prepared) using the following syntax:
EXEC SQL PREPARE cursor_name SQL_statement;
By preparing a cursor, memory space is allocated to accommodate one row of the result set of the statement, and the statement is parsed and optimized.
A cursor name given for the statement must be unique within the connection. This means procedures that contain cursors cannot be called recursively (at least not from a statement that is after a PREPARE CURSOR and before the corresponding DROP CURSOR). When a cursor is prepared, a solidDB® server checks that no other cursor of this name is currently open. If there is one, error number 14504 is returned.
Note that statement cursors can also be opened using the ODBC API. These cursor names need to be different from the cursors opened from procedures.
Example:
EXEC SQL PREPARE sel_tables SELECT table_name FROM sys_tables WHERE table_name LIKE ’SYS%’;
This statement will prepare the cursor named sel_tables, but will not execute the statement that it contains.
Executing the cursor
After a statement has been successfully prepared it can be executed. An execute binds possible input and output variables to it and runs the actual statement.
The optional section INTO binds result data of the statement to variables.
Variables listed in parentheses after the INTO keyword are used when running a SELECT or CALL statement. The resulting columns of the SELECT or CALL statement are bound to these variables when the statement is executed. The variables are bound starting from the leftmost column listed in the statement. Binding of variables continues to the following column until all variables in the list of variables have been bound. For example to extend the sequence for the cursor sel_tables that was prepared earlier we need to run the following statements:
EXEC SQL PREPARE sel_tables SELECT table_name FROM sys_tables WHERE table_name LIKE ’SYS%’
EXEC SQL EXECUTE sel_tables INTO (tab);
The statement is now executed and the resulting table names will be returned into variable tab in the subsequent Fetch statements.
Fetching on the cursor
When a SELECT or CALL statement has been prepared and executed, it is ready for fetching data from it. Other statements (UPDATE, INSERT, DELETE, DDL) do not require fetching as there will be no result set. Fetching results is done using the fetch syntax:
EXEC SQL FETCH cursor_name;
This command fetches a single row from the cursor to the variables that were bound with INTO keyword when the statement was executed.
To complete the previous example to actually get result rows back, the statements will look like:
EXEC SQL PREPARE sel_tables SELECT table_name FROM sys_tables WHERE table_name LIKE ’SYS%’ EXEC SQL EXECUTE sel_tables INTO (tab); EXEC SQL FETCH sel_tables;
After this the variable tab will contain the table name of the first table found conforming to the WHERE clause.
Subsequent calls to fetch on the cursor sel_tables will get the next row(s) if the select found more than one.
To fetch all table names a loop construct may be used:
WHILE expression LOOP EXEC SQL FETCH sel_tables; END LOOP
Note that after the completion of the loop, the variable tab will contain the last fetched table name.
Closing the cursor
Cursors may be closed by issuing the statement
EXEC SQL CLOSE cursor_name;
This will not remove the actual cursor definition from memory; it may be re-executed when the need arises.
Dropping the cursor
Cursors may be dropped from memory, releasing all resources by the statement:
EXEC SQL DROP cursor_name;
Parameter markers in cursors
In order to make a cursor more dynamic, a SQL statement can contain parameter markers that indicate values that are bound to the actual parameter values at execute time. The '?' symbol is used as a parameter marker.
Syntax example:
EXEC SQL PREPARE sel_tabs SELECT table_name FROM sys_tables WHERE table_name LIKE ? AND table_schema LIKE ?;
The execution statement is adapted by including a USING keyword to accommodate the binding of a variable to the parameter marker.
EXEC SQL EXECUTE sel_tabs USING ( var1, var2 ) INTO (tabs);
In this way a single cursor can be used multiple times without having to re-prepare the cursor. As preparing a cursor involves also the parsing and optimizing of the statement, significant performance gains can be achieved by using reusable cursors.
Note that the USING list only accepts variables; data can not be directly passed in this way. So if for example an insert into a table should be made, one column value of which should always be the same (status = 'NEW') then the following syntax would be wrong: EXEC SQL EXECUTE ins_tab USING (nr, desc, dat, ’NEW’);
The correct way would be to define the constant value in the prepare section:
Note that variables can be used multiple times in the using list.
The parameters in an SQL statement have no intrinsic data type or explicit declaration. Therefore, parameter markers can be included in an SQL statement only if their data types can be inferred from another operand in the statement.
For example, in an arithmetic expression such as ? + COLUMN1, the data type of the parameter can be inferred from the data type of the named column represented by COLUMN1. A procedure cannot use a parameter marker if the data type cannot be determined.
The following table describes how a data type is determined for several types of parameters.
Location of Parameter
Assumed Data Type
One operand of a binary arithmetic or comparison operator
Same as the other operand
The first operand in a BETWEEN clause
Same as the other operand
The second or third operand in a BETWEEN clause
Same as the first operand
An expression used with IN
Same as the first value or the result column of the subquery
A value used with IN
Same as the expression
A pattern value used with LIKE
VARCHAR
An update value used with UPDATE
Same as the update column
An application cannot place parameter markers in the following locations:
▪As an SQL identifier (name of a table, name of a column and so on)
▪In a SELECT list.
▪As both expressions in a comparison-predicate.
▪As both operands of a binary operator.
▪As both the first and second operands of a BETWEEN operation.
▪As both the first and third operands of a BETWEEN operation.
▪As both the expression and the first value of an IN operation.
▪As the operand of a unary + or - operation.
▪As the argument of a set-function-reference.
For more information, see the ANSI SQL-92 specification.
In the following example, a stored procedure will read rows from one table and insert parts of them in another, using multiple cursors:
"CREATE PROCEDURE tabs_in_schema (schema_nm VARCHAR) RETURNS (nr_of_rows INTEGER) BEGIN DECLARE tab_nm VARCHAR; EXEC SQL PREPARE sel_tab SELECT table_name FROM sys_tables WHERE table_schema = ?; EXEC SQL PREPARE ins_tab INSERT INTO my_table (table_name, schema) VALUES (?,?);
nr_of_rows := 0;
EXEC SQL EXECUTE sel_tab USING (schema_nm) INTO (tab_nm); EXEC SQL FETCH sel_tab; WHILE SQLSUCCESS LOOP nr_of_rows := nr_of_rows + 1; EXEC SQL EXECUTE ins_tab USING(tab_nm, schema_nm); IF SQLROWCOUNT <> 1 THEN RETURN SQLERROR OF ins_tab; END IF EXEC SQL FETCH sel_tab; END LOOP END";
Error handling
SQLSUCCESS: The return value of the latest EXEC SQL statement executed inside a procedure body is stored into variable SQLSUCCESS. This variable is automatically generated for every procedure. If the previous SQL statement was successful, the value 1 is stored into SQLSUCCESS. After a failed SQL statement, a value 0 is stored into SQLSUCCESS.
The value of SQLSUCCESS may be used, for instance, to determine when the cursor has reached the end of the result set as in the following example:
EXEC SQL FETCH sel_tab; -- loop as long as last statement in loop is successful WHILE SQLSUCCESS LOOP -- do something with the results, for example, return a row EXEC SQL FETCH sel_tab; END LOOP
SQLERRNUM: This variable contains the error code of the latest SQL statement executed. It is automatically generated for every procedure. After successful execution, SQLERRNUM contains zero (0).
SQLERRSTR: This variable contains the error string from the last failed SQL statement.
SQLROWCOUNT: After the execution of UPDATE, INSERT and DELETE statements, an additional variable is available to check the result of the statement. Variable SQLROWCOUNT contains the number of rows affected by the last statement.
SQLERROR: To generate user errors from procedures, the SQLERROR variable may be used to return an actual error string that caused the statement to fail to the calling application. The syntax is:
SQLERROR OF cursorname: For error checking of EXEC SQL statements, the SQLSUCCESS variable may be used as described under SQLSUCCESS in the beginning of this section. To return the actual error that caused the statement to fail to the calling application, the following syntax may be used:
EXEC SQL PREPARE cursornamesql_statement; EXEC SQL EXECUTE cursorname; IF NOT SQLSUCCESS THEN RETURN SQLERROR OF cursorname; END IF END IF
Processing will stop immediately when this statement is executed and the procedure return code is SQLERROR. The actual database error can be returned using the SQLError function:
When this statement is included in a stored procedure, all return values of executed SQL statements are checked for errors. If a statement execution returns an error, the procedure is automatically aborted and SQLERROR of the last cursor is returned. Optionally the transaction can also be rolled back.
The statement should be included before any EXEC SQL statements, directly following the DECLARE section of variables.
Below is an example of a complete procedure returning all table names from SYS_TABLES that start with 'SYS':
"CREATE PROCEDURE sys_tabs RETURNS (tab VARCHAR) BEGIN -- abort on errors EXEC SQL WHENEVER SQLERROR ROLLBACK, ABORT; -- prepare the cursor EXEC SQL PREPARE sel_tables SELECT table_name FROM sys_tables WHERE table_name LIKE ’SYS%’; -- execute the cursor EXEC SQL EXECUTE sel_tables INTO (tab); -- loop through rows EXEC SQL FETCH sel_tables; WHILE sqlsuccess LOOP RETURN ROW; EXEC SQL FETCH sel_tables; END LOOP -- close and drop the used cursors EXEC SQL CLOSE sel_tables; EXEC SQL DROP sel_tables; END";
Example: stored procedure with EXECDIRECT and cursors: The following stored procedure uses EXECDIRECT in one place and uses a cursor in another place.
"CREATE PROCEDURE p2 BEGIN -- This variable holds an ID that we insert into the table. DECLARE id INT; -- Here are simple examples of EXECDIRECT. EXEC SQL EXECDIRECT create table table1 (id_col INT); EXEC SQL EXECDIRECT insert into table1 (id_col) values (1); -- Here is an example of a cursor. EXEC SQL PREPARE cursor1 INSERT INTO table1 (id_col) values (?); id := 2; WHILE id <= 10 LOOP EXEC SQL EXECUTE cursor1 USING (id); id := id + 1; END LOOP; EXEC SQL CLOSE cursor1; EXEC SQL DROP cursor1; END";
Calling other procedures
As calling a procedure forms a part of the supported SQL syntax, a stored procedure may be called from within another stored procedure. The default limit for levels of nested procedures is 16. When the maximum is exceeded, the transaction fails. The maximum nesting level is set in the MaxNestedProcedures parameter in the solid.ini configuration file. For details, see "Configuration Parameters" in solidDB® Administration Guide.
Like all SQL statements, a cursor should be prepared and executed like:
If procedure myproc returns one or more values, then subsequently a fetch should be done on the cursor cp to retrieve those values:
EXEC SQL PREPARE cp call myproc(?,?); EXEC SQL EXECUTE cp USING (var1, var2) INTO (ret_var1, ret_var2); EXEC SQL FETCH cp;
Note that if the called procedure uses a return row statement, the calling procedure should utilize a WHILE LOOP construct to fetch all results.
Recursive calls are possible, but discouraged because cursor names are unique at connection level.
Positioned updates and deletes
In solidDB® procedures it is possible to use positioned updates and deletes. This means that an update or delete will be done to a row, where a given cursor is currently positioned. The positioned updates and deletes can also be used within stored procedures using the cursor names used within the procedure.
The following syntax is used for positioned updates:
UPDATE table_name SET column = value WHERE CURRENT OF cursor_name
and for deletes
DELETE FROM table_name WHERE CURRENT OF cursor_name
In both cases the cursor_namerefers to a statement doing a SELECT on the table that is to be updated/deleted from.
Positioned cursor update is a semantically suspicious concept in SQL standard that may cause peculiarities also with a solidDB® server.
Below is an example written with pseudo code that will cause an endless loop with a solidDB® server (error handling, binding variables and other important tasks omitted for brevity and clarity):
"CREATE PROCEDURE ENDLESS_LOOP BEGIN EXEC SQL PREPARE MYCURSOR SELECT * FROM TABLE1; EXEC SQL PREPARE MYCURSOR_UPDATE UPDATE TABLE1 SET COLUMN2 = ’new data’; WHERE CURRENT OF MYCURSOR;” EXEC SQL EXECUTE MYCURSOR; EXEC SQL FETCH MYCURSOR; WHILE SQLSUCCESS LOOP EXEC SQL EXECUTE MYCURSOR_UPDATE; EXEC SQL COMMIT WORK; EXEC SQL FETCH MYCURSOR; END LOOP END";
The endless loop is caused by the fact that when the update is committed, a new version of the row becomes visible in the cursor and it is accessed in the next FETCH statement. This happens because the incremented row version number is included in the key value and the cursor finds the changed row as the next greater key value after the current position. The row gets updated again, the key value is changed and again it will be the next row found.
In the above example, the updated COLUMN2 is not assumed to be part of the primary key for the table, and the row version number was the only part of the
index entry that changed. However, if a column value is changed that is part of the index through which the cursor has searched the data, the changed row may jump further forward or backward in the search set.
For these reasons, using positioned update is not recommended in general and searched update should be used instead whenever possible. However, sometimes the update logic may be too complex to be expressed in SQL WHERE clause and in such cases positioned update can be used as follows:
Positioned cursor update works deterministically in solidDB®, when the WHERE clause is such that the updated row does not match the criteria and therefore does not reappear in the fetch loop. Constructing such a search criteria may require using additional column only for this purpose.
Note that in an open cursor, user changes do not become visible unless they are committed within the same database session.
Transactions
Stored procedures use transactions like any other interface to the database uses transactions. A transaction may be committed or rolled back either inside the procedure or outside the procedure. Inside the procedure a commit or roll back is done using the following syntax:
EXEC SQL COMMIT WORK; EXEC SQL ROLLBACK WORK;
These statements end the previous transaction and start a new one.
If a transaction is not committed inside the procedure, it may be ended externally using:
▪solidDB® SA
▪Another stored procedure
▪By autocommit, if the connection has AUTOCOMMIT switch set to ON
When a connection has autocommit activated, it does not force autocommit inside a procedure. The commit is done when the procedure exits.
Default cursor management
By default, when a procedure exits, all cursors opened in a procedure are closed. Closing cursors means that cursors are left in a prepared state and can be re-executed.
After exiting, the procedure is put in the procedure cache. When the procedure is dropped from the cache, all cursors are finally dropped.
The number of procedures kept in cache is determined by the solid.ini file setting:
[SQL] ProcedureCache = nbr_of_procedures
This means that, as long as the procedure is in the procedure cache, all cursors can be reused as long as they are not dropped. A solidDB® server itself manages the procedure cache by keeping track of the cursors declared, and notices if the statement a cursor contains has been prepared.
As cursor management, especially in a heavy multi-user environment, can use a considerable amount of server resources, it is good practice to always close cursors
immediately and preferably also drop all cursors that are no longer used. Only the most frequently used cursors may be left non-dropped to reduce the cursor preparation effort.
Note that transactions are not related to procedures or other statements. Commit or rollback therefore does NOT release any resources in a procedure.
Notes on SQL:
▪There is no restriction on the SQL statements used. Any valid SQL statement can be used inside a stored procedure, including DDL and DML statements.
▪Cursors may be declared anywhere in a stored procedure. Cursors that are certainly going to be used are best prepared directly following the declare section.
▪Cursors that are used inside control structures, and are therefore not always necessary, are best declared at the point where they are activated, to limit the amount of open cursors and hence the memory usage.
▪The cursor name is an undeclared identifier, not a variable; it is used only to reference the query. You cannot assign values to a cursor name or use it in an expression.
▪Cursors may be re-executed repeatedly without having to re-prepare them. Note that this can have a serious influence on performance; repetitively preparing cursors on similar statements may decrease the performance by around 40% in comparison to re-executing already prepared cursors!
▪Any SQL statement will have to be preceded by the keywords EXEC SQL.
Functions for procedure stack viewing
The following functions may be included in stored procedures to analyze the current contents of the procedure stack:
▪PROC_COUNT ( )
This function returns the number of procedures in the procedure stack, including the current procedure.
▪PROC_NAME (N)
This function returns the Nth procedure name in the stack. The first procedure is in position zero.
▪PROC_SCHEMA (N)
This function returns the schema name of the Nth procedure in the procedure stack.
These functions allow for stored procedures that behave differently depending on whether they are called from an application or from a procedure.
Procedure privileges
Stored procedures are owned by the creator, and are part of the creator's schema. Users who need to run stored procedures in other schemas need to be granted EXECUTE privilege on the procedure:
GRANT EXECUTE ON procedure_name TO { USER | ROLE };
This function returns the schema name of the Nth procedure in the procedure stack.
All database objects accessed within the granted procedure, even subsequently called procedures, are accessed according to the rights of the owner of the procedure. No special grants are necessary.
Since the procedure is run with the privileges of the creator, the procedure not only has the creator's rights to access objects such as tables, but also uses the creator's schema and catalog. For example, suppose that user 'Sally' runs a procedure named 'Proc1' created by user 'Jasmine'. Suppose also that both Sally and Jasmine have a table named 'table1'. By default, the stored procedure Proc1 will use the table1 that is in Jasmine's schema, even if Proc1 was called by user Sally.
See also ACCESS RIGHTS for more information about privileges and remote stored procedure calls.
Remote stored procedures in advanced replication configurations
In advanced replication configurations, stored procedures can be called locally orremotely. Remote procedures are stored procedures that one database server callsfrom another database server. Remote stored procedure calls use the followingsyntax:
CALL procedure_name AT node-ref;
where
node-refindicates which database server the remote stored procedure is on.
Remote stored procedures calls can only be made between two solidDB® servers that have a master/replica relationship. The calls can be made in either direction; the master may call a stored procedure on the replica, or the replica may call a stored procedure on the master.
A remote stored procedure can be called from any context that allows a local procedure call. Thus, for example, you can call a remote stored procedure directly by using a CALL statement, or you can call the remote procedure from within a trigger, another stored procedure, or a START AFTER COMMIT statement.
A remotely-called stored procedure can contain any command that any other stored procedure may contain. All stored procedures are created using the same syntax rules. A single stored procedure may be called both locally and remotely at different times.
When called remotely, the stored procedure accepts parameters from the caller as if the call was local. However, a remote stored procedure cannot return a result set; it can only return an error code.
Both local and remote stored procedure calls are synchronous; regardless of whether the procedure is called locally or remotely, the caller waits until the value is returned; the caller does not continue on while the stored procedure executes in the background. If the stored procedure is called from inside a START AFTER COMMIT, the stored procedure call itself is synchronous, but the START AFTER COMMIT was not synchronous, so the stored procedure will execute as an asynchronous background process.
Transaction handling for remote stored procedures is different from transaction handling for local stored procedures. When a stored procedure is called remotely, the execution of the stored procedure is not a part of the transaction that contained the call. Therefore, you cannot roll back a stored procedure call by rolling back the transaction that called it.
CALL syntax for remote stored procedures
The full syntax of the command to call a remote stored procedure is:
DEFAULT is used only with the START AFTER COMMIT statement.
For example:
CALL MyProc(’Smith’, 750) AT replica1; CALL MyProcWithoutParameters AT replica2;
Note You can only list one node definition per CALL. If you want to notify multiple replicas, for example, then you must call each of them separately. You can, however, create a stored procedure that contains multiple CALL statements, and then simply make a single call to that procedure.
Creating remote stored procedures
The remote stored procedure is always created on the server that executes the procedure, not on the server that calls the procedure. For example, if the master is going to call procedure foo() to execute on replica1, the procedure foo() must have been created on replica1. The master does not know the content of the stored procedure that it calls remotely. In fact, the master does not know anything about the stored procedure other than the information specified in the CALL statement itself.
For example, the following CALL statement includes the procedure's name, some parameter values, and the name of the replica on which the procedure is to be executed:
CALL foo(param1, param2) AT replica1
The stored procedure is not registered with the caller. This means that the caller calls the procedure blindly, without even knowing if it exists. If the caller tries to call a procedure that does not exist, the caller will get an error message.
Dynamic parameter binding is supported. For example, the following is legal:
CALL MYPROC(?, ?) AT MYREPLICA1;
Calls to the stored procedure are not buffered or queued. If you call the stored procedure and the procedure does not exist, the call does not persist, waiting until the stored procedure appears. Similarly, if the procedure exists, but the server that has that procedure is shut down or is disconnected from the network or inaccessible for any other reason, the call is not held open and retried when the server becomes accessible again. This is important to know when using the "Sync Pull Notify" (push synchronization) feature.
ACCESS RIGHTS
To call a stored procedure, the caller must have EXECUTE privilege on that procedure. (This is true for any stored procedure, whether it is called locally or remotely.)
When a procedure is called locally, it is executed with the privileges of the caller. When a procedure is called remotely, it may be executed either with the privileges of a specified user on the remote server, or with the privileges of the remote user who corresponds to the local caller. (The replica and master users must already be mapped to each other before the stored procedure is called. For more information about mapping replica users to master users, see solidDB®Advanced Replication User Guide.)
If a remote stored procedure was called from the replica (and is to be executed on the master), then you have the option of specifying which master user's privileges you would like the procedure to be executed with.
If the remote stored procedure was called from the master (and is to be executed on the replica), or if you do not specify which user's privileges to use, then the calling server will figure out which user's privileges should be used, based on which user called the stored procedure and the mapping between replica and master users.
These possibilities are explained in more detail below.
1 If the procedure was called from a replica (and will be executed on the master), then you may execute the SET SYNC USER statement to specify which master user's privileges to use. You must execute SET SYNC USER on the local server before calling the remote stored procedure. Once the sync user has been specified on the calling server, the calling server will send the user name and password to the remote server (the master server) each time a remote stored procedure is called. The remote server will try to execute the procedure using the user id and password that were sent with the procedure call. The user id and password must exist in the remote server, and the specified user must have appropriate access rights to the database and EXECUTE privilege on the called procedure.
The SET SYNC USER statement is valid only on a replica, so you can only specify the sync user when a replica calls a stored procedure on a master.
2 If the caller is a master, or if the call was made from a replica and you did not specify a sync user before the call, then the servers will attempt to determine which user on the remote server corresponds to the user on the local server.
If the calling server is a replica (R → M)
The calling server sends the following information to the remote server when calling a remote procedure:
Name of the master (SYS_SYNC_MASTERS.NAME).
Replica id (SYS_SYNC_MASTERS.REPLICA_ID).
Master user id (This master user id is the master user id that corresponds to the user id of the local user who called the procedure. Obviously, this local user must already be mapped to the corresponding master user.)
Note that this method of selecting the master user id is the same as the method used when a replica refreshes data — the replica looks up in the SYS_SYNC_USERS table to find the master user who is mapped to the current local replica user.
If the calling server is a master (M → R)
The calling server sends the following information to the remote server when calling a remote procedure:
Name of the master (SYS_SYNC_REPLICAS.MASTER_NAME).
Replica id (SYS_SYNC_REPLICAS.ID).
User name of the caller.
User id of the caller.
When the replica receives the master user id, the replica looks up the local user who is mapped to that master id. Since more than one replica user may be mapped to a single master user, the server will use the first local user it finds who is mapped to the specified master user and who has the privileges required to execute this stored procedure.
Before a master server can call a stored procedure on a replica server, the master must know the connect string of the replica. If a replica allows calls from a master, then the replica should define its own connect string information in the solid.ini file. This information is provided to the master (the replica includes a copy when it forwards any message to master). When the master receives the connect string from the replica, the master replaces the previous value (if the new value differs).
It is also possible to inform the master of the replica's connect string by using the statement:
SET SYNC CONNECT <connect-info> TO REPLICA <replica-name>
This is useful if the master needs to call the replica but the replica has not yet provided its connect string to the master (i.e. has not yet forwarded any message to the master).
Deferred procedures in advanced replication configurations
Deferred procedures are stored procedures that are called after commit has been processed.
In advanced replication configurations, at the end of a committed transaction, you might want to perform a specific action. For example, if the transaction updated some data in a "master" publication, you might want to notify a replica that the master data was updated. solidDB®Y allows the START AFTER COMMIT statement to specify an SQL statement that will be executed when the current transaction is committed. The specified SQL statement is called the "body" of the START AFTER COMMIT. The body is executed asynchronously in a separate connection.
For example, if you would like to call a stored procedure named my_proc() when the transaction commits, you would write:
START AFTER COMMIT NONUNIQUE CALL my_proc;
This statement may appear anywhere inside the transaction; it may be the first statement, the last statement, or any statement in between. Regardless of where the START AFTER COMMIT statement itself appears within the transaction, the body (the call to my_proc) will be executed only when the transaction is committed. In the example above, the body is on a separate line, but that is not required syntactically.
Because the body of the statement is not executed at the same time as the START AFTER COMMIT statement itself, there are two different phases to the START AFTER COMMIT command: the definition phase and the execution phase.
▪In the definition phase of START AFTER COMMIT, you specify the body but do not execute it. The definition phase can occur anywhere inside a transaction; in other words, the statement "START AFTER COMMIT ..." can be placed in any order relative to other SQL statements in the same transaction.
▪In the execution phase, the body of the START AFTER COMMIT statement is actually executed. The execution phase occurs when the COMMIT WORK statement for the transaction is executed. It is also possible to execute a START AFTER COMMIT in autocommit mode, but there is rarely a reason to do this.
The following example shows the use of a START AFTER COMMIT statement inside a transaction.
-- Any valid SQL statement(s)... ... -- Creation phase. The function my_proc() is not actually called here. START AFTER COMMIT NONUNIQUE CALL my_proc(x, y); ... -- Any valid SQL statement(s)... -- Execution phase: This ends the transaction and starts execution -- of the call to my_proc(). COMMIT WORK;
A START AFTER COMMIT does not execute unless and until the transaction is successfully committed. If the transaction containing the START AFTER COMMIT is rolled back, the body of the START AFTER COMMIT is not executed. If you want to propagate the updated data from a replica to a master, this is an advantage because you only want the data propagated if it is committed. If you were to use triggers to start the propagation, the data would be propagated before it was committed.
The START AFTER COMMIT command applies only to the current transaction, that is, the one that the START AFTER COMMIT command was issued inside. It does not apply to subsequent transactions, or to any other transactions that are currently open in other connections.
The START AFTER COMMIT command allows you to specify only one SQL statement to be executed when the COMMIT occurs. However, that one SQL statement may be a call to a stored procedure, and that stored procedure can have many statements, including calls to other stored procedures. Furthermore, you can have more than one START AFTER COMMIT command per transaction. The body of each of these START AFTER COMMIT statements will be executed when the transaction is committed. However, these bodies will run independently and asynchronously; they will not necessarily execute in the same order as their corresponding START AFTER COMMIT statements, and they are likely to have overlapping execution (there is no guarantee that one will finish before the next one starts).
A common use of START AFTER COMMIT is to help implement "Sync Pull Notify" ("Push Synchronization") functionality in advanced replication setups.
If the body of your START AFTER COMMIT is a call to a stored procedure, that procedure can be local or it may be remote on one remote replica (or master).
If you are using Sync Pull Notify, you might want to call the same procedure on many replicas. To do this, you must use a slightly indirect method. The simplest method is to write one local procedure that calls many procedures on replicas. For example, if the body of the START AFTER COMMIT statement is "CALL my_proc", you could write my_proc to be similar to the following:
CREATE PROCEDURE my_proc BEGIN CALL update_inventory(x) AT replica1; CALL update_inventory(x) AT replica2; CALL update_inventory(x) AT replica3; END;
This approach works fine if your list of replicas is static. However, if you expect to add new replicas in the future, you might find it more convenient to update"groups" of replicas based on their properties. This allows you to add new replicas with specific properties and then have existing stored procedures operate on those new replicas. This is done by making use of two features: the FOR EACH REPLICA clause in START AFTER COMMIT, and the DEFAULT clause in remote stored procedure calls.
If the FOR EACH REPLICA clause is used in START AFTER COMMIT, the statement will be executed once for each replica that meets the conditions in the WHERE clause. The statement is executed once FOR each replica, not once ON each replica. If there is no "AT node-ref" clause in the CALL statement, the stored procedure is called locally, that is, on the same server as the START AFTER COMMIT was executed on. To make sure that a stored procedure is called once on each replica, you must use the DEFAULT clause. The typical way to do this is to create a local stored procedure that contains a remote procedure calling that uses the DEFAULT clause.
For example, suppose that my_local_proc contains the following:
CALL update_sales_statistics AT DEFAULT;
The START AFTER COMMIT statement is the following:
START AFTER COMMIT FOR EACH REPLICA WHERE region = ’north’ UNIQUE CALL my_local_proc;
The WHERE clause is the following:
WHERE region = ’north’
Therefore, for each replica that has the properties region = ’north’, you will call the stored procedure named my_local_proc.
That local procedure, in turn, executes the following:
CALL update_sales_statistics() AT DEFAULT
The keyword DEFAULT is resolved as the name of the replica. Each time that my_local_proc is called from inside the body of the START AFTER COMMIT, the DEFAULT keyword is the name of a different replica that has the property "region = 'north'".
Note In this example:
▪It is possible that not all replicas will have a procedure namedupdate_sales_statistics(). If this is the case, the procedure will only be executed on those replicas that have the procedure. The master will not send each replica a copy of the procedure; the master only calls existing procedures.)
▪It is possible that not all replicas that have a procedure namedupdate_sales_statistics() will have the SAME procedure. Each replica may have its own custom version of the procedure.
Before executing each statement on each replica, a connection to the replica is established.
When the START AFTER COMMIT command is used to call multiple replicas, this enables the use of the optional keyword "DEFAULT" in the syntax of the CALL command. For example, suppose that you use the following:
START AFTER COMMIT FOR EACH REPLICA WHERE location = ’India’ UNIQUE CALL push;
Then in the local procedure 'push', you can use the keyword "DEFAULT", which acts as a variable that contains the name of the replica in question.
CREATE PROCEDURE push BEGIN EXEC SQL EXECDIRECT CALL remoteproc AT DEFAULT; END
Procedure 'push' will be called once for each replica that has a property named 'location' with value 'India'. Each time the procedure is called, "DEFAULT" will be set to the name of that replica. Thus, the following statement will call the procedure on that particular replica:
CALL remoteproc AT DEFAULT;
You can set the replica properties in the master with the statement:
SET SYNC PROPERTY propname = ’value’ FOR REPLICA replica_name;
For example
SET SYNC PROPERTY location = ’India’ FOR REPLICA asia_hq;
The statement specified in START AFTER COMMIT is executed as an independent transaction. It is not part of the transaction that contained the START AFTER COMMIT command. This independent transaction is run as though autocommit mode were on; in other words, you do not need an explicit COMMIT WORK to commit the work done in this statement.
In other respects, however, the execution of the statement is not much like a transaction. First, there is no guarantee that the statement will execute to completion. The statement is launched as an independent background task. If the server crashes, or if for some other reason the statement cannot be executed, then the statement disappears without being completely executed.
Second, because the statement is executed as a background task, there is no mechanism for returning an error. Third, there is no way to roll back the statement; if the statement execution is completed, the "transaction" statement is autocommitted regardless of whether any errors were detected. If the statement is a procedure call, then the procedure itself may contain COMMIT and ROLLBACK commands.
You can use the "RETRY" clause to try executing the statement more than once if it fails. The RETRY clause allows you to specify the number of times the server should attempt to retry the failed statement. You must specify the number of seconds to wait between each retry.
If you do not use the RETRY clause, the server attempts only once execute the statement, then the statement is discarded. If, for example, the statement tries to call a remote procedure, and if the remote server is down (or cannot be contacted due to a network problem), then the statement will not be executed and you will not get any error message.
Any statement, including the statement specified in a START AFTER COMMIT, executes in a certain "context". The context includes such factors as the default
catalog, the default schema, and so on. For a statement executed from within a START AFTER COMMIT, the statement's context is based on the context at the time that the START AFTER COMMIT is executed, not on the context at the time of the COMMIT WORK that actually causes the statement inside START AFTER COMMIT to run. In the example below, 'CALL FOO_PROC' is executed in the catalog foo_cat and schema foo_schema, not bar_cat and bar_schema.
SET CATALOG FOO_CAT; SET SCHEMA FOO_SCHEMA; START AFTER COMMIT UNIQUE CALL FOO_PROC; ... SET CATALOG BAR_CAT; SET SCHEMA BAR_SCHEMA; COMMIT WORK;
The UNIQUE/NONUNIQUE keywords determine whether the server tries to avoid issuing the same command twice.
The UNIQUE keyword before <stmt> defines that the statement is executed only if there isn't identical statement under execution or "pending" for execution. Statements are compared with simple string compare. So for example 'call foo(1)' is different from 'call foo(2)'. Replicas are also taken into account in the comparison; in other words, UNIQUE does not prevent the server from executing the same trigger call on different replicas. Note that "unique" only blocks overlapping execution of statements; it does not prevent the same statement from being executed again later if it is called again after the current invocation has finished running.
NONUNIQUE means that duplicate statements can be executed simultaneously in the background.
Examples: The following statements are all considered different and are thus executed even though each contains the UNIQUE keyword. (Name is a unique property of replica.)
START AFTER COMMIT UNIQUE call myproc; START AFTER COMMIT FOR EACH REPLICA WHERE name=’R1’ UNIQUE call myproc; START AFTER COMMIT FOR EACH REPLICA WHERE name=’R2’ UNIQUE call myproc; START AFTER COMMIT FOR EACH REPLICA WHERE name=’R3’ UNIQUE call myproc;
But if the following statement is executed in the same transaction as the previous ones and if some of the replicas R1, R2, and R3 have the property "color='blue'", then the call is not executed for those replicas again.
START AFTER COMMIT FOR EACH REPLICA WHERE color=’blue’ UNIQUE call myproc;
Uniqueness does not prevent "automatic" execution from overlapping "manual" execution. For example, if you manually execute a command to refresh from a particular publication, and if the master also calls a remote stored procedure to refresh from that publication, the master will not "skip" the call because a manual refresh is already running. Uniqueness applies only to statements started by START AFTER COMMIT.
The START AFTER COMMIT statement can be used inside a stored procedure. For example, suppose that you want to post an event if and only if a transaction completed successfully. You could write a stored procedure that would execute a START AFTER COMMIT statement that would post the event if the transaction was committed (but not if it was rolled back). Your code might look similar to the following:
This sample also contains an example of "receiving" and then using an event parameter. See the stored procedure named "wait_on_event_e" in script #1.
-- To run this demo properly, you will need two users/connections. -- This demo contains 5 separate "scripts", which must be executed -- in the order shown below: -- User1 executes the first script. -- User2 executes the second script. -- User1 executes the third script. -- User2 executes the fourth script. -- User1 executes the fifth script. -- You may notice that there are some COMMIT WORK statements -- in surprising places. These are to ensure that each user sees the -- most recent changes of the other user. Without the COMMIT WORK -- statements, in some cases one user would see an out-of-date -- "snapshot" of the database. -- -- Set autocommit off for both users/connections! SCRIPT 1 (USER 1) CREATE EVENT e (i int); CREATE TABLE table1 (a int); -- This inserts a row into table1. The value inserted into the is copied -- from the parameter to the procedure. "CREATE PROCEDURE inserter(i integer) BEGIN EXEC SQL PREPARE c_inserter INSERT INTO table1 (a) VALUES (?); EXEC SQL EXECUTE c_inserter USING (i); EXEC SQL CLOSE c_inserter; EXEC SQL DROP c_inserter; END"; -- This posts the event named "e". "CREATE PROCEDURE post_event(i integer) BEGIN POST EVENT e(i); END"; -- This demonstrates the use of START AFTER COMMIT inside a -- stored procedure. After you call this procedure and -- call COMMIT WORK, the server will post the event. "CREATE PROCEDURE sac_demo BEGIN DECLARE MyVar INT; MyVar := 97; EXEC SQL PREPARE c_sacdemo START AFTER COMMIT NONUNIQUE CALL post_event(?); EXEC SQL EXECUTE c_sacdemo USING (MyVar); EXEC SQL CLOSE c_sacdemo; EXEC SQL DROP c_sacdemo; END"; -- When user2 calls this procedure, the procedure will wait until -- the event named "e" is posted, and then it will call the -- stored procedure that inserts a record into table1. "CREATE PROCEDURE wait_on_event_e BEGIN -- Declare the variable that will be used to hold the event parameter. -- Although the parameter was declared when the event was created, you -- still need to declare it as a variable in the procedure that receives -- that event. DECLARE i INT; WAIT EVENT WHEN e (i) BEGIN -- After we receive the event, insert a row into the table. EXEC SQL PREPARE c_call_inserter CALL inserter(?); EXEC SQL EXECUTE c_call_inserter USING (i); EXEC SQL CLOSE c_call_inserter; EXEC SQL DROP c_call_inserter; END EVENT END WAIT END"; COMMIT WORK; SCRIPT 2 (USER 2) -- Make sure that user2 sees the changes that user1 made. COMMIT WORK; -- Wait until user1 posts the event. CALL wait_on_event_e; -- Do not commit work again (yet). SCRIPT 3 (USER 1) COMMIT WORK; -- User2 should be waiting on event e, and should see the event after -- we execute the stored procedure named sac_demo and then commit work. -- Note that since START AFTER COMMIT statements are executed -- asynchronously, there may be a slight delay between the COMMIT WORK -- and the associated POST EVENT. CALL sac_demo; COMMIT WORK; SCRIPT 4 (USER 2) -- Commit the INSERT that we did earlier when we called inserter() -- after receiving the event COMMIT WORK; SCRIPT 5 (USER 1) -- Ensure that we see the data that user2 inserted. COMMIT WORK; -- Show the record that user2 inserted. SELECT * FROM table1; COMMIT WORK;
Important considerations on START AFTER COMMIT
▪When the body of the deferred procedure call (START AFTER COMMIT) is executed, it runs asynchronously in the background. This allows the server to immediately start executing the next SQL command in your program without waiting for the deferred procedure call statement to finish. It also means that you do not have to wait for completion before disconnecting from the server. In most situations, this is an advantage. However, in a few situations this may be a disadvantage. For example, if the body of the deferred procedure call locks records that are needed by subsequent SQL commands in your program, you may not appreciate having the body of the deferred procedure call run in the background while your next SQL command runs in the foreground and has to wait to access those same records.
▪The statement to be executed will only be executed if the transaction is completed with a COMMIT, not a ROLLBACK. If the entire transaction isexplicitly rolled back, or if the transaction is aborted and thus implicitly rolled back (due to a failed connection, for example), then the body of the START AFTER COMMIT will not be executed.
▪Although the transaction in which the deferred procedure call occurs can be rolled back (thus preventing the body of the deferred procedure call from running), the body of the deferred procedure call cannot itself be rolled back if it has executed. Because it runs asynchronously in the background, there is no mechanism for cancelling or rolling back the body once it starts executing.
▪The statement in the deferred procedure call is not guaranteed to run until completion or to be run as an "atomic" transaction. For example, if your server crashes, then the statement will not resume executing the next time that the server starts, and any actions that were completed before the server crashed may be kept. To prevent inconsistent data in this type of situation, you must program carefully and make proper use of features like referential constraints to ensure data integrity.
▪If you execute a START AFTER COMMIT statement in autocommit mode, then the body of the START AFTER COMMIT will be executed "immediately" (i.e. as soon as the START AFTER COMMIT is executed and automatically committed). At first, this might seem useless — why not just execute the body of the START AFTER COMMIT directly? There are a few subtle differences, however. First, a direct call to my_proc is synchronous; the server will not return control to you until the stored procedure has finished executing. If you call my_proc as the body of a START AFTER COMMIT, however, then the call is asynchronous; the server does not wait for the end of my_proc before allowing you to execute the next SQL statement. In addition, because START AFTER COMMIT statements are not truly executed "immediately" (i.e. at the time that the transaction is committed) but may instead be delayed briefly if the server is busy, you might or might not actually start running your next SQL statement before my_proc even starts executing. It is rare for this to be desirable behavior. However, if you truly want to launch an asynchronous stored procedure that will run in the background while you continue onward with your program, it is valid to do START AFTER COMMIT in autocommit mode.
▪If more than one deferred procedure call was executed in the same transaction, then the bodies of all the START AFTER COMMIT statements will run asynchronously. This means that they will not necessarily run in the same order as you executed the START AFTER COMMIT statements within the transaction.
▪The body of a START AFTER COMMIT must contain only one SQL statement. That one statement may be a procedure call, however, and the procedure may contain multiple SQL statements, including other procedure calls.
▪The START AFTER COMMIT statement applies only to the transaction in which it is defined. If you execute START AFTER COMMIT in the current transaction, the body of the deferred procedure call will be executed only when the current transaction is committed; it will not be executed in subsequent transactions, nor will it be executed for transactions done by any other connections. START AFTER COMMIT statements do not create "persistent" behavior. If you would like the same body to be called at the end of multiple transactions, then you will have to execute a "START AFTER COMMIT ... CALL my_proc" statement in each of those transactions.
▪The "result" of the execution of the body of the deferred procedure call (START AFTER COMMIT) statement is not returned in any way to the connection that ran the deferred procedure call. For example, if the body of the deferred procedure call returns a value that indicates whether an error occurred, that value will be discarded.
▪Almost any SQL statement may be used as the body of a START AFTER COMMIT statement. Although calls to stored procedures are typical, you may also use UPDATE, CREATE TABLE, or almost anything else. (We do not advise putting another START AFTER COMMIT statements inside a START AFTER COMMIT, however.) Note that a statement like SELECT is generally useless inside an deferred procedure call because the result is not returned.
▪Because the body is not executed at the time that the START AFTER COMMIT statement is executed inside the transaction, START AFTER COMMIT statements rarely fail unless the deferred procedure call itself or the body contains a syntax error or some other error that can be detected without actually executing the body.
What if you do not want the next SQL statement in your program to run until deferred procedure call statement has finished running? Here's a workaround:
1 At the end of the deferred procedure call statement (for example, at the end of the stored procedure called by the deferred procedure call statement), post an Event. (See solidDB® Programmer Guide for a description of events.)
2 Immediately after you commit the transaction that specified the deferred procedure call, call a stored procedure that waits on the event.
3 After the stored procedure call (to wait on the event), put the next SQL statement that your program wants to execute.
For example, your program might look like the following:
The stored procedure wait_for_sac_completion would wait for the event that myproc will post. Therefore, the UPDATE statement will not run until after the deferred procedure call statement finishes.
However, this workaround is slightly risky. Since deferred procedure call statements are not guaranteed to execute until completion, there is a chance that the stored procedure wait_for_sac_completion will never get the event that it is waiting for.
Why would anyone design a command that may or may not run to completion? The answer is that the primary purpose of the START AFTER COMMIT feature is to support "Sync Pull Notify". The Sync Pull Notify feature allows a master server to notify its replica(s) that data has been updated and that the replicas may request refreshes to get the new data. If this notification process fails for some reason, it would not result in data corruption; it would simply mean that there would be a longer delay before the replica refreshes the data. Since a replica is always given all the data since its last successful refresh operation, a delay in receipt of data does not cause the replica to permanently miss any data. For more details, see “Introduction to Sync Pull Notify” in the solidDB® Advanced Replication User Guide.
Note The statement inside the body of the START AFTER COMMIT may be any statement, including SELECT. Remember, however, that the body of the START AFTER COMMIT does not return its results anywhere, so a SELECT statement is generally not useful inside a START AFTER COMMIT.
Note If you are in auto-commit mode and execute START AFTER COMMIT..., then the given statement is started immediately in the background. "Immediately" here actually means "as soon as possible", because it's still executed asynchronously when the server has time to do it.
This example showcases how to implement Sync Pull Notify (Master notifying all relevant Replicas that there is new data that they can request a refresh of) using the START and CALL statements. This example also uses triggers.
Consider a scenario where there is a Master M1 and Replicas R1 and R2.
To carry out Sync Pull Notify, follow the steps listed below:
1 Define a Procedure Pm1 in Master M1. In Procedure Pm1, include the statements:
EXECDIRECT CALL Pr1 AT R1; EXECDIRECT CALL Pr1 AT R2;
You will have one call for each interested Replica. Even though the replica name changes, typically the procedure name is the same on each replica.
2 Define a Procedure Pr1 in Replica R1. If a master is to invoke the Pr1 in more than one replica, then Pr1 should be defined for every replica that is of interest. See the replica procedure example in the example section below.
3 Define a Trigger for all relevant DML operations, such as
▪INSERT
▪UPDATE
▪DELETE
4 In each trigger body, embed the statement
EXECDIRECT START [UNIQUE] CALL Pm1;
5 Grant EXECUTE authority to the appropriate user on each replica. (A user Ur1 on the replica should already be mapped to a corresponding user Um1 on the master. The user Um1 must execute the EXECDIRECT START [UNIQUE] CALL Pm1;
When Um1 calls the procedure remotely, the call will actually execute with the privileges of Ur1 when the call is executed on the replica.)
Example: Sliced replicas
In this example, a sales application has a table named CUSTOMER, which has a column named SALESMAN. The master database contains information for all salespersons. Each salesperson has her own replica database, and that replica has only a slice of the master's data; specifically, each salesperson's replica has the slice
of data for that salesperson. For example, salesperson Smith's replica has only the data for salesperson Smith. If the salesperson assigned to a particular customer changes, then the correct replicas should be notified. If XYZ corporation is reassigned from salesperson Smith to salesperson Jones, then salesperson Jones's replica database should add the data related to XYZ corporation, and salesperson Smith's replica should delete the data related to XYZ corporation. Here is the code to update both replica databases:
-- If a customer is reassigned to a different salesman, then we -- must notify both the old and new salesmen. -- NOTE: This sample shows only the "UPDATE" trigger, but in -- reality, you’d also need to define INSERT and DELETE triggers. CREATE TRIGGER T_CUST AFTERUPDATE ON CUSTOMER AFTER UPDATE REFERENCING NEW SALESMAN AS NEW_SALESMAN, REFERENCING OLD SALESMAN AS OLD_SALESMAN BEGIN IF NEW_SALESMAN <> OLD_SALESMAN THEN EXEC SQL EXECDIRECT START AFTER COMMIT FOR EACH REPLICA WHERE NAME=OLD_SALESMAN UNIQUE CALL CUST(OLD_SALESMAN); EXEC SQL EXECDIRECT START AFTER COMMIT FOR EACH REPLICA WHERE NAME=NEW_SALESMAN UNIQUE CALL CUST(NEW_SALESMAN); ENDIF END;
Suppose that in the application, the user assigns all customers in sales area 'CA' to salesperson Mike.
UPDATE CUSTOMER SET SALESMAN=’Mike’ WHERE SALES_AREA=’CA’; COMMIT WORK;
The Master server has the following procedure:
CREATE PROCEDURE CUST(salesman VARCHAR) BEGIN EXEC SQL EXECDIRECT CALL CUST(salesman) AT salesman; COMMIT WORK; END
Each replica has the following procedure:
CREATE PROCEDURE CUST(salesman VARCHAR) BEGIN MESSAGE s BEGIN; MESSAGE s APPEND REFRESH CUSTS(salesman); MESSAGE s END; COMMIT WORK; MESSAGE s FORWARD TIMEOUT FOREVER; COMMIT WORK; END
In the procedure CUST(), we force the salesperson's replica to refresh from the data in the master. This procedure CUST() is defined on all the replicas. If we call the procedure on both the replica that the customer was reassigned to, and the replica that the customer was reassigned from, then the procedure updates both those replicas. Effectively, this will delete the out-of-date data from the replica that no longer has this customer, and will insert the data to the replica that is now responsible for this customer. If the publication and its parameters are properly defined, we do not need to write additional detailed logic to handle each possible operation, such as reassigning a customer from one salesperson to another; instead, we simply tell each replica to refresh from the most current data.
Note
▪It is possible to implement a Sync Pull Notify without triggers. The application may call appropriate procedures to implement SyncPull. Triggers are a way to achieve Sync Pull Notify in conjunction with the statement START AFTER COMMIT and remote procedure calls.
▪Sometimes, in the Sync Pull Notify process, it is possible that a replica may have to exchange one extra round trip of messages unnecessarily. This could happen if the master invoked procedure tries to send a message to the replica that just sent the changes to the master, and that causes a change in the "hot data" in the master. But this can be avoided with careful usage of the START AFTER COMMIT statement. Be careful not to create an "infinite loop", where each update on the master leads to an immediate update on the replica, which leads to an immediate update on the master... The best way to avoid this is to be careful when creating triggers on the replica that might "immediately" send updated data to the master, which in turn "immediately" notifies the replica to refresh again.
Tracing the execution of background jobs: The START AFTER COMMIT statement returns a result-set with one INTEGER column. This integer is a unique "job" id that can be used to query the status of statements that failed to start for one reason or another (invalid SQL statement, no access rights, replica not available, and so on).
If a maximum number of uncommitted deferred procedure call statements is reached, then an error is returned when the deferred procedure call is issued. The maximum number is configurable in solid.ini. See solidDB® Administrator Guide.
If a statement cannot be started, the reason is logged to the system table SYS_BACKGROUNDJOB_INFO.
SYS_BACKGROUNDJOB_INFO ( ID INTEGER NOT NULL, STMT WVARCHAR NOT NULL, USER_ID INTEGER NOT NULL, ERROR_CODE INTEGER NOT NULL, ERROR_TEXT WVARCHAR NOT NULL, PRIMARY KEY(ID) );
Only failed START AFTER COMMIT statements are logged into this table. If the statement (for example, a procedure call) starts successfully, no information is stored into the system tables.
User can retrieve the information from the table SYS_BACKGROUNDJOB_INFO using either SQL SELECT-query or calling a system procedure SYS_GETBACKGROUNDJOB_INFO. The input parameters is the jobID. The returned values are: ID INTEGER, STMT WVARCHAR, USER_ID INTEGER, ERROR_CODE INTEGER, ERROR_TEXT INTEGER.
Also an event SYS_EVENT_SACFAILED is posted when a statement fails to start.
The NUMDATAINFO field contains the jobID. The application can wait for this event and use the jobID to retrieve the reason from the system table SYS_BACKGROUNDJOB_INFO.
The system table SYS_BACKGROUNDJOB_INFO can be emptied with the admin command cleanbgjobinfo. You need DBA privileges to execute this command, which means that only a DBA can delete the rows from the table.
Controlling background tasks: Background tasks can be controlled with the SSC API and admin commands (see solidDB® Shared Memory Access and Linked Library Access User Guidefor details on the SSC API). The server uses the task type SSC_TASK_BACKGROUND for the tasks that execute statements started with START AFTER COMMIT. Note that there may be several of these tasks, but you cannot control them individually.
