Previous Page TOC Index Next Page



USING SOLID SERVER


SOLID Server offers a number of features that make it possible to move parts of the application logic into the database. These features include

Stored Procedures

Stored procedures are simple programs, or procedures, that are executed in the server. The user can create a procedure that contains several SQL statements or a whole transaction, and execute it with a single call statement. Usage of stored procedures reduces network traffic and allows more strict control to access rights and database operations.

Interface

Creating, dropping, and Calling Procedures

Procedures are created with the

CREATE PROCEDURE name

[(parameter-name data-type
[, parameter-name data-type ... ])]
[RETURNS (parameter-name data-type
[, parameter-name data-type ... ])]
BEGIN body END;

statement and dropped with the

DROP PROCEDURE name

statement. Procedures are called with the statement

CALL name

Procedures can take several input parameters and return a single row or several rows as a result. The resulting rows are built from specified output parameters. Procedures are thus used in ODBC in the same way as the SQL SELECT statement.

Access Rights

Procedures are owned by the creator of the procedure. Specified access rights can be granted to other users. When the procedure is run, it has the creator's access rights to database objects.

Procedure Language Syntax

The stored procedure syntax is a proprietary syntax modelled from SQL3 specifications and dynamic SQL. The procedure body may contain control statements and SQL statements.

The following statements are available in the procedures:

Control statement

Description

set variable = expression

Assigns a value to a variable. The value can be either a literal value (e.g., 10 or 'text') or another variable. Parameters are considered as normal variables.

variable := expression

Alternate syntax for assigning values to variables.

while
expr
loop
statement-list
end loop

Loops while expression is true.

leave

Leaves the innermost while loop and continues executing the procedure from the next statement after the keyword end loop.

if
expr
then
statement-list1
else
statement-list2
end if

Executes statements-list1 if expression expr is true; otherwise, executes statement-list2.

if
expr1
then
statement-list1
elseif
expr2
then
statement-list2
end if

If expr1 is true, executes statement-list1. If expr2 is true, executes statement-list2. The statement can optionally contain multiple elseif statements and also an else statement.

return

Returns the current values of output parameters and exits the procedure. If a procedure has a one return row statement, return behaves like return norow.

return sqlerror of cursor-name

Returns the sqlerror associated with the cursor and exits the procedure.

return row

Returns the current values of output parameters and continues execution. (requires SOLID Server Version 2.2 or later)

return norow

Returns the end of the set and exits the procedure. (requires SOLID Server Version 2.2 or later)

All SQL DML and DDL statements can be used in procedures. Thus, the procedure can, e.g., create tables or commit a transaction. Each SQL statement in the procedure is atomic.

Preparing SQL Statements

The SQL statements in procedures are first prepared with the statement

EXEC SQL PREPARE cursor SQL-statement

The cursor specification is a cursor name that must be given. It can be any unique cursor name inside the transaction. Note that if the procedure is not a complete transaction, other open cursors outside the procedure may have conflicting cursor names.

Executing Prepared SQL Statements

The SQL statement is executed with the statement

EXEC SQL EXECUTE cursor [opt-using ][opt-into ]

The optional opt-using specification has the syntax

USING (variable-list)

where variable-list contains a list of procedure variables or parameters separated by a comma. These variables are input parameters for the SQL statement. The SQL input parameters are marked with the standard question mark syntax in the prepare statement. If the SQL statement has no input parameters, the USING specification is ignored.

The optional opt-into specification has the syntax

INTO (variable-list)

where variable-list contains the variables that the column values of the SQL SELECT statement are stored into. The INTO specification is effective only for SQL SELECT statements.

Fetching Results

Rows are fetched with the statement

EXEC SQL FETCH cursor

If the fetch completed successfully, the column values are stored into the variables defined in the opt-into specification.

Checking for Errors

The result of each EXEC SQL statement executed inside a procedure body is stored into the variable SQLSUCCESS. This variable is automatically generated for every procedure. If the previous SQL statement was successful, a value one is stored into SQLSUCCESS. After a failed SQL statement, a value zero is stored into SQLSUCCESS.

EXEC SQL WHENEVER SQLERROR [ROLLBACK [WORK],] ABORT

is used to decrease the need for IF NOT SQLSUCCESS THEN tests after every executed SQL statement.

This statement can be used with SOLID Server Version 2.2 or later.

Using Transactions

EXEC SQL {COMMIT | ROLLBACK} WORK

is used to terminate transactions.

EXEC SQL SET TRANSACTION {READ ONLY | READ WRITE}

is used to control the type of transactions.

Using Sequencer Objects and Event Alerts

See the discussion about sequencer objects and event alerts later in this chapter.

Example 1

-- A simple calculator that also tests some control 

-- statements.

"create procedure test1(i1 integer, op char(1),
i2 integer)
returns (result varchar)
begin
declare i integer;

set i = 10;
i := 10;

if i <> 10 then
result := 'Error in first set';
return;
end if

while i < 15 loop
i := i + 1;
end loop

if i <> 15 then
result := 'Error in loop1';
return;
end if

while i < 100 loop
if i = 20 then
leave;
end if
i := i + 1;
end loop
if i <> 20 then
result := 'Error in loop2';
return;
end if

if op = '+' then
result := i1 + i2;
elseif op = '-' then
result := i1 - i2;
elseif op = '*' then
result := i1 * i2;
elseif op = '/' then
result := i1 / i2;
else
result := 'Error: illegal op';
end if
end"; -- Call and result call test1(1, '+', 4); -- result: 5 call test1; -- result: Error: illegal op

Example 2

-- Returns the count from the system table where 

-- the table id
-- is greater than the procedure input parameter
-- value.

"create procedure test2(tableid integer)
returns (cnt integer)
begin
exec sql prepare c1 select count(*)
from sys_tables where id > ?;
exec sql execute c1 using (tableid) into (cnt);
exec sql fetch c1;
end"; -- Call and result call test2(0); -- result: 24 call test2(10000); -- result: 0

Example 3

-- Simple insert


"create procedure test3(empid integer, empname varchar)
returns (succ integer)
begin
exec sql prepare c1 insert into employee
values ( ?, ?);
exec sql execute c1 using (empid, empname);

if not sqlsuccess then
succ := 0;
else
succ := 1;
end if
end"; -- Call and result call test3(1, 'Donald Duck'); -- result: 1

Example 4

create table employee (id integer, name varchar);

create table employee2 (id integer, name varchar); -- inserts a row in table employee2 "create procedure test4(empid integer, empname varchar)
returns (succ integer)
begin
exec sql prepare c4 insert into employee2
values(?, ?);
exec sql execute c4 using (empid, empname);

if not sqlsuccess then
succ := 0;
else
succ := 1;
end if
end";

-- inserts every row found in table employee also in
-- table employee2 "create procedure test5(empid integer, empname varchar)
returns (result integer)
begin
declare rowcount integer;
declare r1 integer;
declare r2 varchar;
declare a1 integer;

exec sql prepare c1 select count(*) from employee
where id = ? or name = ?;
exec sql execute c1 using (empid, empname)
into ( rowcount);
exec sql fetch c1;

exec sql prepare c2 select id, name from employee
where id = ? or name = ?;
exec sql execute c2 using (empid, empname)
into ( r1, r2);

while
rowcount > 0
loop
exec sql fetch c2;
exec sql prepare c3 call test4(?, ?);
exec sql execute c3 using (r1, r2) into (a1);
if a1 = 0 then
exec sql rollback work;
leave;
end if
rowcount := rowcount -1;
end loop

if rowcount = 0 then
exec sql commit work;
end if
result := rowcount;
end"; -- Call and result call test5(1, 'Donald Duck'); -- result: 0

Event Alerts

Event alerts are used to signal an event in the database. Events are simple objects with a name. The use of event alerts removes resource consuming database polling from applications.

The system does not automatically generate events, they must be triggered by stored procedures. Similarly the events are received in stored procedures. When an application calls a stored procedure that waits for a specific event to happen, the application is blocked until the event is triggered and received. In multithreaded environments separate threads and connections can be used to access the database during the event standstill.

Interface

An event has a name that identifies it and a set of parameters. The name can be any user-specified alphanumeric string. An event object is created with the SQL statement

CREATE EVENT event-name

[(parameter-name data-type
[parameter-name data-type ...])]

The parameter list specifies parameter names and parameter types. The parameter types are normal SQL types. Events are dropped with the SQL statement

DROP EVENT event-name

Events are triggered and received inside stored procedures. Special stored procedure statements are used to trigger and receive events.

The event is triggered with the stored procedure statement

POST EVENT event-name (parameters)

Event parameters must be local variables or parameters in the stored procedure where the event is triggered. All clients that are waiting for the posted event will receive the event.

To make a procedure wait for an event to happen, the WAIT EVENT construct is used in the stored procedure:

wait-event-statement ::=

WAIT EVENT
[event-specification ...]
END WAIT event-specification ::=
WHEN event-name (parameters) BEGIN
statements
END EVENT

Access Rights

The creator of an event or the database administrator can grant and revoke access rights to an event. Access rights can be granted to users and roles. The select access right gives waiting access to an event. The insert access right gives triggering access to an event.

Examples

-- Create test event

create event test1;

-- Create test procedure that waits events
"create procedure event_wait
returns (result varchar)
begin
wait event
when test1 begin
result := 'receive';
return;
end event

end wait
end";

-- Create test procedure that generates
-- events
"create procedure event_send
returns (result varchar)
begin
result := 'send';
post event test1;
end"; -- client 1 calls event_wait procedure
call event_wait;
-- and starts waiting for the event test1

-- client 2 calls event_send procedure
call event_send;

-- client 2 returns
send
-- and posts event test1

-- client 1 receives event test1 and
-- returns
receive

Sequencer Objects

A sequencer object is an object that can be used to get sequence numbers. Depending on how the sequence is created, there may or may not be holes in the sequence (the sequence can be sparse or dense).

Dense sequences guarantee that there are no holes in the sequence numbers. The sequence number allocation is bound to the current transaction. If the transaction rolls back, also the sequence number allocations are rolled back. The drawback of dense sequences is that the sequence is locked out from other transactions until the current transaction ends.

If there is no need for dense sequences, a sparse sequence can be used. A sparse sequence guarantees uniqueness of the returned values, but it is not bound to the current transaction. If a transaction allocates a sparse sequence number and later rolls back, the sequence number is simply lost.

A sequence object can be used, for example, to generate invoice numbers. The advantage of using a sequence object instead of a separate table is that the sequence object is specifically fine-tuned for fast execution and requires less overhead than normal update statements.

Both dense and sparse sequence numbers start from 1.

Interface

Sequences are created using the CREATE SEQUENCE statement:

CREATE [DENSE] SEQUENCE sequence-name

By default, the created sequence is sparse, unless the keyword DENSE is given.

Sequences are dropped using the DROP SEQUENCE statement:

DROP SEQUENCE sequence-name

Sequences are accessed from stored procedures. The current sequence value can be retrieved using the following stored procedure statement:

EXEC SEQUENCE sequence-name.CURRENT INTO variable

The new sequence value can be retrieved using the following stored procedure statement:

EXEC SEQUENCE <name>.NEXT INTO <variable>

Sequence values can be set with the following stored procedure statement:

EXEC SEQUENCE sequence-name SET VALUE USING variable

Access Rights

Select access rights are required to retrieve the current sequence value. Update access rights are required to allocate new sequence values. Sequence access rights are granted and revoked in the same way as table access rights.

Examples

-- Create a dense sequence, the keyword DENSE is

-- required
CREATE DENSE SEQUENCE SPTEST6_DENSE;
-- Create a sparse sequence, by default
-- sequences are sparse
CREATE SEQUENCE SPTEST6_SPARSE;

-- Create a procedure that retuns new
-- seqeunce values
"create procedure sptest6
returns (denseval integer, sparseval integer)
begin
exec sequence sptest6_dense.next into denseval;
exec sequence sptest6_sparse.next into sparseval;
end"; -- Call and result

call sptest6; -- returns: 1 1 call sptest6; -- returns: 2 2

Previous Page TOC Index Next Page

Copyright © 1992-1997 Solid Information Technology Ltd All rights reserved.