helpinghand
search
needassistance
 
How To
Get Current Values of Sequences
Categories: Programming Examples, SQL
Introduction

Versions 8.2 and later of Mimer SQL support sequences. A sequence is an entity that is used to automatically generate a sequence of integers. It is primarily used to automatically generate a new unique primary key.

A common problem is that a database application is unaware of the generated primary key. Sometimes the application wants to know what the value of a sequence is for further processing. This article describes ways of determining the value of a sequence without incurring significant performance costs.

Introduction to Sequences

When a sequence is created it is not associated with a column. A sequence is a database object in its own right and can be used in any combination and in any circumstance. The most common usage is to use a sequence to generate a primary key. See the database schema below for an example on how to create a table to use this feature.

As a sequence is an entity in itself, we can query it any time. A common scenario will be to query it just after we have inserted a new row using the sequence to generate the primary key.
An example of such a query is the following statement:
select current_value of MY_SEQUENCE from MIMER.ONEROW
The MIMER.ONEROW table is a system table that always contains one row, but any table would suffice.

Transactions and Sequences

Sequences are stable within a transaction. This means that no matter how many times other transactions increment the sequence during your transaction, you will always see the current value of the sequence as it was after your last incrementation.

However, if you increment the sequence several times in the same transaction, the numbers you get may not be contiguous, as someone else might have slipped in and incremented the sequence.

Implementations

For performance reasons, we don’t want to do run a separate query to find the current value of the sequence. Separate queries always generate overhead, both when transferring the query to the server and when sending the results back. But there is also some overhead for the server each time it receives a query, and also for the client when it sends the query to the server.

Depending on our requirements and the interface we are working with, we can choose from three implementations:
  • The first and easiest is to embed both the INSERT and SELECT statements for the current sequence value in a transaction
  • The second way, which is a little better, is to use a batch to package the INSERT and SELECT within one network request
  • Thirdly, you can embed both the INSERT and the SELECT in a procedure.
The table below shows us which methods we can use with the interfaces to solve our particular problem.


 Two StatementsBatchesProcedures
Embedded SQLX X
ODBCXXX
JDBCX X


Example

Example File

You can download full versions of the examples used in this section here. (Zip file)

To create the databank, sequence and table used in the following examples, run:
create databank MY_DB;
create unique sequence MY_SEQUENCE;
create table MY_TABLE (
NUMBER integer default next_value of MY_SEQUENCE,
NAME char(20)
) in MY_DB;

ODBC Example

The example below connects to the data source SAMPLE with the username SYSADM and password SYSADM. It inserts a new name into the database, and retrieves the number it got.

Note that only the segment clarifying the main point is shown here. For the full example, see the download link above. CHECK is a C-macro which takes care of the error handling.
int main(int argc,char *argv[])
{
char new_name[100] = "default_name";
char dsn[200],uid[200],pwd[200];
int value;

lines omitted

CHECK(SQLPrepare(hstmt,"insert into MY_TABLE(NAME) values (?);
select current_value of MY_SEQUENCE from MIMER.ONEROW",
SQL_NTS));
CHECK(SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,
0,0,new_name,sizeof(new_name),NULL));
CHECK(SQLExecute(hstmt));
CHECK(SQLMoreResults(hstmt));
CHECK(SQLFetch(hstmt));
CHECK(SQLGetData(hstmt,1,SQL_C_SLONG,&value,0,NULL));
CHECK(SQLCloseCursor(hstmt));

printf("The new person (%s) got number %d.\n",new_name,value);

return 0;
}
What we see above is an SQLPrepare call which prepares two statements in a batch. One INSERT that inserts the new person into the database, and one SELECT which queries for the primary key assigned to the new record. Both of these statements are sent to the server in a single network package.

After the SQLExecute we are ready to take care of whatever the statements returned. First, we are positioned at the INSERT statement. Since we are not interested in the row count of the INSERT we move directly to the next statement, SQLMoreResults. As that was a SELECT statement, we expect a result set which we start fetching from. With the SQLGetData call, we know what primary key was assigned to the new record.

Embedded SQL

Unfortunately, batches aren’t allowed in Embedded SQL. The recommended approach in Embedded SQL is to embed the SQL statements in a procedure. This is not complicated but requires that the queries and statements be defined and set.
To query for the primary key using embedded SQL, we create the following procedure:
create procedure MY_PROC(in P_NAME char(20),out P_NR integer)
modifies sql data
begin
insert into MY_TABLE(NAME) values (P_NAME);
select current_value of MY_SEQUENCE into P_NR from MIMER.ONEROW;
end
Calling this procedure from Embedded SQL is surprisingly easy.
For clarity, only the important parts of the sample are included, see the download link above for the full example.
int main(int argc,char *argv[])
{
exec sql BEGIN DECLARE SECTION;
char new_name[100] = "default_name";
char dsn[200],uid[200],pwd[200];
int value;
exec sql END DECLARE SECTION;

exec sql WHENEVER SQLERROR goto error_exit;

if (argc>1)
strcpy(new_name,argv[1]);

get_connect_info(dsn,uid,pwd);

exec sql CONNECT TO :dsn USER :uid USING :pwd;

exec sql CALL MY_PROC(:new_name,:value);

printf("The new person (%s) got number %d.\n",new_name,value);

exec sql COMMIT;

exec sql DISCONNECT ALL;

return 0;

error_exit:
print_sqlerror();

exec sql WHENEVER SQLERROR CONTINUE;

exec sql DISCONNECT;
exit(0);
return 0;
}
Of course, this procedure can be used from ODBC as well. See the ODBC reference on advice how to call procedures using ODBC.

JDBC example

Like ODBC, JDBC supports batches, but, although the terminology used is the same, JDBC batches are a little different.

First and foremost, batches in JDBC are specifically intended only for DML statements returning an update count. This is a limitation compared to ODBC as ODBC allows a mix of DML-statements and queries.

Secondly, JDBC will not allow programmers to compile a sequence of statements in advance and use parameter markers with them. Using JDBC, we have to use the PreparedStatement object to prepare statements with parameter markers, but the PreparedStatement can only batch parameter arrays to a single statement.

In the example below we use the most straightforward method - with separate statements.
import java.io.*;
import java.sql.*;

class sample3 {
public static void main(String[] arg) {
try {
Connection con;
PreparedStatement pstmt;
Statement stmt;
String url;
String name = "default_name";
int value;

BufferedReader in =
new BufferedReader(new InputStreamReader(System.in));

System.out.print("Enter URL of server: ");
url = in.readLine();

if (arg.length>0)
name = arg[0];

Class.forName("com.mimer.jdbc.Driver");
con = DriverManager.getConnection(url);
con.setAutoCommit(false);

pstmt = con.prepareStatement(
"insert into MY_TABLE(NAME) values (?)");
pstmt.setString(1,name);
pstmt.executeUpdate();
pstmt.close();

stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(
"select current_value of MY_SEQUENCE from MIMER.ONEROW");
rs.next();
value = rs.getInt(1);

rs.close();
stmt.close();
con.commit();

System.out.println(
"The new person ("+name+") got number "+value+".\n");

con.close();
} catch (SQLException e) {
System.out.println("Database ERROR! "+e.getMessage());
} catch (Exception e) {
System.out.println("ERROR! "+e.getMessage());
}
}
}
Naturally, JDBC users can also use the MY_PROC procedure, which will be more efficient than the example above. See any JDBC API reference on how to call procedures.

Benefits

Mimer SQL supports auto-incrementing column values through sequences. However, auto-incrementing column values or sequences is not included in the SQL-99 standard. Mimer SQL supports this implementation for your convenience.

Links

For details on ODBC, please refer to http://www.microsoft.com/ (search for 'odbc').

Java 1.3 SDK API Reference.

Mimer SQL Documentation Set, html navigation (see the Reference Manual an the Programmer's Manual).

Mimer SQL Documentation Set, PDF-file (see the Reference Manual and the Programmer's Manual).

Last updated: 2002-10-23

 

Powered by Mimer SQL

Powered by Mimer SQL