Working With Sequences


Title Working With Sequences
Author Hughes Technologies
Date 10 Feb 1999
Specifics mSQL 2.0 or greater
Abstract A tutorial covering the use of unique, server generated sequences as identifiers within an SQL database. The general concept of atomic sequences and their application in a working example are provided.


Introduction

Many database applications require the use of a unique value to identify a particular database record. A classic example of this requirement is an employee database in which each employee is assigned a unique employee number. Often, the client application is responsible for the maintenance of a simple counter value, stored in the database or an external file, that it uses to identify the last allocated employee number. When adding a new employee to the database, the application would simply load the counter value, increment it, store the new value in the counter file and insert the new employee record into the database using the new value as the employee number. This approach would be fine if there was no chance of more than one person adding an employee to the database at the same time. Multiple simultaneous insertions could result in the following situation

This is a very bad situation and the integrity of the database is now compromised as the employee ID value no longer identifies a unique employee. The problem is cause by the lack of an atomic operation that both retrieves the counter value and increments it before another request for the counter value is processed. mSQL 2.0 introduces support for server managed sequences to the mSQL database engine to overcome this problem. By allowing the server to manage sequential values, the uniqueness and integrity of the values can be ensured. The database server allocates and increments the sequence value using a single, atomic operation each time it is requested by a client application.


Using Sequences

A sequence in mSQL is directly associated with a database table and is implemented as a system variable of that table. It is created by sending a query containing a "CREATE SEQUENCE" construct to the server. The initial value of the sequence and the size of the incremental change in the value (the step value) can be set when the sequence is created. Issuing the query 
		CREATE SEQUENCE ON emp_details

would create a sequence associated with the emp_details table with an initial value of 1 and a step of 1 (i.e. it would increase by 1 each time it was accessed). The initial value of 1 and the step of 1 are the defaults if no other values are provided. Both values may be set in the CREATE query by using 

		CREATE SEQUENCE ON emp_details VALUE intVal STEP intVal

where intVal is replaced by the desired integer value. For example, a sequence with an initial value of 100 that changes by 5 each time it is accessed could be created using 

		CREATE SEQUENCE ON emp_details VALUE 100 STEP 5

If a negative value is specified as the step value then the sequence will decrease by the specified amount each time it is accessed. The sequence value itself is implemented as an unsigned integer so it can itself never contain a negative value. If the value passed below zero the value wraps. The maximum value of a sequence is 4,294,967,295.

To retrieve the sequence value, the client application simply submits a SELECT query on the sequence system variable. The database server will return the current value of the sequence and modify the stored value by the step value specified when the sequence was created. The next time the sequence is retrieve the client will be sent the new value. The retrieval and update of the value is performed as a single atomic operation and can never be interrupted, ensuring the integrity of the sequence value. An example of retrieving the sequence value is shown below. 

		SELECT _seq FROM emp_details


Working Example
Below is a working example based on the fictitious employee database that has been used as an illustration during this tutorial. The employee table is defined as follows 
		CREATE TABLE emp_details (
			emp_id INT,
			first_name CHAR(15),
			last_name CHAR(25),
			dept CHAR(10),
			start_date DATE )

		CREATE SEQUENCE ON emp_details VALUE 100 STEP 1

As a client application will probably use the emp_id field to retrieve data from the table so it is a prime candidate for an index. Defining an index on the emp_id field will ensure that lookups on the table using the emp_id as the search condition are performed as quickly as possible. Because it is based on the value of a sequence we are assured it is a unique value so a unique index could be used 

		CREATE UNIQUE INDEX idx1 ON emp_details ( emp_id )

A new employee can be added to the database via a web based application using w3-msql enhanced web pages as follows. The first page, add.html, is a simple HTML form that collects the required data while the second page, add_emp.msql, performs the insertion

File Name : add.html

 
<HTML>
<HEAD><TITLE>Add Employee Record<TITLE><HEAD>
<BODY>
<CENTER>
<H2>Add Employee Record<H2>
<FORM ACTION=add_emp.msql METHOD=POST>
<TABLE>
<TR>
	<TD>First Name<TD>
	<TD><INPUT NAME=first_name><TD>
<TR>
<TR>
	<TD>Last Name<TD>
	<TD><INPUT NAME=last_name><TD>
<TR>
<TR>
	<TD>Department<TD>
	<TD><SELECT NAME=dept>
		<OPTION VALUE=sales>Sales
		<OPTION VALUE=dev>Development
		<OPTION VALUE=admin>Administration
		<OPTION VALUE=cs>Customer Support
	<SELECT><TD>
<TR>
<TABLE>
<INPUT TYPE=SUBMIT>
<FORM>
<BODY>
<HTML>

File Name : add_emp.msql

 


<HEAD><TITLE>Add Employee Record</TITLE></HEAD>
<BODY>
<CENTER>
<H2>Add Employee Record</H2>
<!
	/* 
	** Connect to the database and grab the sequence value
	*/
	$sock = msqlConnect();
	if ($sock < 0) {
		fatal("Can't connect to database server : $ERRMSG");
	}
	if (msqlSelectDB($sock, "staff") < 0) {
		fatal("Can't select staff database : $ERRMSG");
	}
	if (msqlQuery($sock, "select _seq from emp_details") < 0) {
		fatal("Query failed : $ERRMSG");
	}
	$res = msqlStoreResult();
	$row = msqlFetchRow($res);
	$sequence = (int)$row\[0\];
	msqlFreeResult($res);

	/*
	** Grab the date for inclusion in the employee record
	*/
	$curTime = time();
	$curDate = strftime($time, "d-b-Y");

	/*
	** Insert the record
	*/
	$q = "insert into emp_details values ($sequence, '$first_name',
		'$last_name', '$dept', '$curDate')";
	if (msqlQuery($sock, $q) < 0) {
		fatal("Add employee failed : $ERRMSG");
	}
	msqlClose($sock);
>

New employee '$first_name $last_name' added to database with employee
ID '$sequence'

</BODY>
</HTML>


Copyright © 2001, Hughes Technologies Pty Ltd. All Rights Reserved.
Last updated 18 Jan 2002.