//***************************************************************************
// (c) Copyright IBM Corp. 2007 All rights reserved.
//
// The following sample of source code ("Sample") is owned by International
// Business Machines Corporation or one of its subsidiaries ("IBM") and is
// copyrighted and licensed, not sold. You may use, copy, modify, and
// distribute the Sample in any form without payment to IBM, for the purpose of
// assisting you in the development of your applications.
//
// The Sample code is provided to you on an "AS IS" basis, without warranty of
// any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
// IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
// MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
// not allow for the exclusion or limitation of implied warranties, so the above
// limitations or exclusions may not apply to you. IBM shall not be liable for
// any damages you suffer as a result of using, copying, modifying or
// distributing the Sample, even if IBM has been advised of the possibility of
// such damages.
//***************************************************************************
//
// SOURCE FILE NAME: DbSeq.java
//
// SAMPLE: How to create, alter and drop a sequence in a database
//
// This sample demonstrates how to create, alter and drop a
// sequence object. It also demonstrates how to use 'next value'
// and 'previous value' with a sequence object.
//
// SQL STATEMENTS USED:
// CREATE SEQUENCE
// ALTER SEQUENCE
// DROP SEQUENCE
// INSERT
// SELECT
// COMMIT
// ROLLBACK
//
// Classes used from Util.java are:
// Db
// JdbcException
//
//
// Output will vary depending on the JDBC driver connectivity used.
//***************************************************************************
//
// For more information on the sample programs, see the README file.
//
// For information on developing Java applications see the Developing Java Applications book.
//
// For information on using SQL statements, see the SQL Reference.
//
// For the latest information on programming, compiling, and running DB2
// applications, visit the DB2 Information Center at
// http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
//**************************************************************************/
import java.sql.*;
import java.lang.*;
class DbSeq
{
public static void main(String argv[])
{
try
{
Db db = new Db(argv);
System.out.println();
System.out.println(
"THIS SAMPLE SHOWS HOW TO USE A SEQUENCE IN A DATABASE.");
// connect to the 'sample' database
db.connect();
createSequence(db.con);
// The following code demonstrates how to GRANT the usage permission
// on the sequence 'id_seq' to a user, Tom, from Bob. Comment out the
// following and replace 'Tom' with the user you want to grant usage
// permission to.
// Statement grantstmt = con.createStatement();
// grantstmt.executeUpdate("GRANT USAGE ON SEQUENCE id_seq TO Tom");
// grantstmt.close();
// The following code demonstrates how to REVOKE the usage permission
// on the sequence 'id_seq' from Tom by Bob. Comment out the
// following, replace 'Bob' with your user name and 'Tom' with the
// user you want to revoke usage permission from.
// Statement revostmt = con.createStatement();
// revostmt.executeUpdate(
// "REVOKE USAGE ON SEQUENCE id_seq FROM Tom BY Bob");
// revostmt.close();
nextValSeq(db.con);
prevValSeq(db.con);
dropSequence(db.con);
// disconnect from the 'sample' database
db.disconnect();
}
catch (Exception e)
{
JdbcException jdbcExc = new JdbcException(e);
jdbcExc.handle();
}
} //main
// Helping function: This function is used to display the contents of
// the tables created in this sample program.
static void tbContentDisplay(Connection con, String tableName)
{
try
{
int empNo;
String info = null;
String column = "Name";
System.out.println("\n SELECT * FROM " + tableName);
if (tableName.equalsIgnoreCase("emp_location"))
{
column="Location";
}
System.out.println(" EmpNo " + column);
System.out.println(" ----- ----------");
PreparedStatement pstmt = con.prepareStatement(
"SELECT * FROM " + tableName);
ResultSet rs = pstmt.executeQuery();
while (rs.next())
{
info = rs.getString(2);
empNo = rs.getInt(1);
System.out.println(" "+empNo + " " + info);
}
rs.close();
pstmt.close();
}
catch (Exception e)
{
JdbcException jdbcExc = new JdbcException(e, con);
jdbcExc.handle();
}
} // tbContentDisplay
// This function shows how to create a table and a sequence in a database.
static void createSequence(Connection con)
{
try
{
System.out.println(
"\n---------------------------------------------------\n"+
"USE THE SQL STATEMENT:\n" +
" CREATE SEQUENCE\n" +
"TO CREATE A SEQUENCE.");
// Create a sequence object called 'id_seq' that generates the
// employee's ID number.
System.out.println();
System.out.println(
" CREATE SEQUENCE id_seq\n" +
" AS INTEGER\n" +
" START WITH 400\n" +
" INCREMENT BY 10\n" +
" NO MINVALUE\n" +
" MAXVALUE 430\n" +
" NO CYCLE\n" +
" NO CACHE");
Statement stmt = con.createStatement();
stmt.executeUpdate(
"CREATE SEQUENCE id_seq AS INTEGER START WITH 400 " +
"INCREMENT BY 10 NO MINVALUE MAXVALUE 430 NO CYCLE " +
"NO CACHE");
stmt.close();
}
catch (Exception e)
{
JdbcException jdbcExc = new JdbcException(e, con);
jdbcExc.handle();
}
} // createTbAndSeq
// This function shows how to drop a table and a sequence object in a
// database.
static void dropSequence(Connection con)
{
try
{
System.out.println();
System.out.println(
"------------------------------------------------\n"+
"USE THE SQL STATEMENT:\n" +
" DROP SEQUENCE\n" +
"TO DROP A SEQUENCE.");
// drop a sequence object called 'id_seq'
System.out.println();
System.out.println(" DROP SEQUENCE id_seq RESTRICT");
Statement drop = con.createStatement();
drop.executeUpdate("DROP SEQUENCE id_seq RESTRICT");
drop.close();
}
catch (Exception e)
{
JdbcException jdbcExc = new JdbcException(e, con);
jdbcExc.handle();
}
} // dropTbAndSeq
// This function shows how to alter a sequence object.
static void alterSeq(Connection con)
{
try
{
System.out.println(
"USE THE SQL STATEMENTS:\n" +
" ALTER\n" +
"TO ALTER A SEQUENCE");
// Alter the sequence to restart from 430 with a range of 400 to 430
// inclusively while incrementing by -10 (decrementing by 10) with
// no maximum value.
System.out.println();
System.out.println(
" ALTER SEQUENCE id_seq\n" +
" RESTART WITH 430\n" +
" INCREMENT BY -10\n" +
" MINVALUE 400\n" +
" NO MAXVALUE\n" +
" CYCLE\n" +
" CACHE 10");
Statement altstmt = con.createStatement();
altstmt.executeUpdate(
"ALTER SEQUENCE id_seq RESTART WITH 430 INCREMENT BY -10" +
" MINVALUE 400 NO MAXVALUE CYCLE CACHE 10");
altstmt.close();
}
catch (Exception e)
{
JdbcException jdbcExc = new JdbcException(e, con);
jdbcExc.handle();
}
} // alterSeq
// This function shows how to use a sequence with 'NEXT VALUE' to insert
// table data and generate a gap.
static void nextValSeq(Connection con)
{
try
{
System.out.println();
System.out.println(
"---------------------------------------------------\n"+
"USE THE SQL STATEMENTS:\n" +
" INSERT\n" +
"TO INSERT TABLE DATA USING A SEQUENCE WITH 'NEXT VALUE'");
// create a table called 'contract_emp'
System.out.println();
System.out.println(
" CREATE TABLE contract_emp(empNo INTEGER, name CHAR(10))");
Statement stmt = con.createStatement();
stmt.executeUpdate(
"CREATE TABLE contract_emp(empNo INTEGER, name CHAR(10))");
stmt.close();
// insert table data using 'NEXT VALUE'
System.out.println();
System.out.println(
" INSERT INTO contract_emp\n" +
" VALUES(NEXT VALUE FOR id_seq, 'shameem')\n");
Statement stmt1 = con.createStatement();
stmt1.executeUpdate(
"INSERT INTO contract_emp VALUES(NEXT VALUE FOR id_seq, 'shameem')");
stmt1.close();
System.out.println(" COMMIT\n");
con.commit();
// display the content of the 'contract_emp' table
tbContentDisplay(con, "contract_emp");
// insert table data using 'NEXT VALUE'
System.out.println(
" INSERT INTO contract_emp\n" +
" VALUES(NEXT VALUE FOR id_seq, 'mohammed')\n");
Statement stmt2 = con.createStatement();
stmt2.executeUpdate(
"INSERT INTO contract_emp VALUES(NEXT VALUE FOR id_seq, 'mohammed')");
stmt2.close();
// display the content of the 'contract_emp' table
tbContentDisplay(con, "contract_emp");
System.out.println(" ROLLBACK\n");
con.rollback();
// display the content of the 'contract_emp' table
tbContentDisplay(con, "contract_emp");
// insert table data using 'NEXT VALUE'
System.out.println();
System.out.println(
" INSERT INTO contract_emp\n" +
" VALUES(NEXT VALUE FOR id_seq, 'sunny')\n");
Statement stmt3 = con.createStatement();
stmt3.executeUpdate(
"INSERT INTO contract_emp VALUES(NEXT VALUE FOR id_seq,'sunny')");
stmt3.close();
// display the content of the 'contract_emp' table
tbContentDisplay(con, "contract_emp");
System.out.println();
System.out.println(
" Note:\n"+
" The new insertion has EmpNo 420. Note the gap in the\n"+
" EmpNo. This shows numbers generated by SEQUENCE are\n"+
" independent of the status of the transaction that\n"+
" generated the previous value in the sequence.\n");
System.out.println(
" Altering the sequence to show overlap can be generated by\n"+
" a sequence object.\n");
alterSeq(con);
// insert table data using 'NEXT VALUE' after altering the sequence
System.out.println();
System.out.println(
" INSERT INTO contract_emp\n" +
" VALUES(NEXT VALUE FOR id_seq, 'saba')\n"+
" (NEXT VALUE FOR id_seq, 'repeat')");
Statement stmt4 = con.createStatement();
stmt4.executeUpdate(
"INSERT INTO contract_emp VALUES(NEXT VALUE FOR " +
"id_seq,'saba'), (NEXT VALUE FOR id_seq, 'repeat')");
stmt4.close();
// display the content of the 'contract_emp' table after altering the
// sequence
tbContentDisplay(con, "contract_emp");
System.out.println();
System.out.println(
" Note:\n"+
" One of the new insertions has EmpNo 420 which\n" +
" already exists. This happened because the new altered range\n" +
" of sequence overlaps the old one. It is the responsibility\n" +
" of the programmer to ensure that these overlaps do not\n" +
" occur if they are not wanted.\n");
// drop the table 'emp_location'
System.out.println(" DROP TABLE contract_emp");
Statement drop = con.createStatement();
drop.executeUpdate("DROP TABLE contract_emp");
drop.close();
}
catch (Exception e)
{
JdbcException jdbcExc = new JdbcException(e, con);
jdbcExc.handle();
}
} // nextValSeq
// This function shows how to use a sequence with 'PREVIOUS VALUE' to insert
// the last generated sequence value into a table.
static void prevValSeq(Connection con)
{
try
{
System.out.println();
System.out.println(
"---------------------------------------------------\n"+
"USE THE SQL STATEMENTS:\n" +
" INSERT INTO PREVIOUS VALUE\n" +
"TO INSERT DATA INTO A TABLE USING A SEQUENCE WITH 'PREVIOUS VALUE'");
// create a table called 'emp_location'
System.out.println();
System.out.println(
" CREATE TABLE emp_location(empNo INTEGER, city CHAR(10))\n");
Statement stmt = con.createStatement();
stmt.executeUpdate(
"CREATE TABLE emp_location(empNo INTEGER, city CHAR(10))");
stmt.close();
// insert table data using 'PREVIOUS VALUE'
System.out.println(
" INSERT INTO emp_location\n" +
" VALUES(PREVIOUS VALUE FOR id_seq, 'repeat')");
Statement stmt1 = con.createStatement();
stmt1.executeUpdate(
"INSERT INTO emp_location VALUES(PREVIOUS VALUE FOR id_seq, 'repeat')");
stmt1.close();
// display the content of the 'emp_location' table
tbContentDisplay(con, "emp_location");
System.out.println();
System.out.println(
" Note:\n"+
" By using SEQUENCE with 'PREVIOUS VALUE', you can insert\n"+
" into a different table with the last generated\n"+
" EmpNo.\n");
// drop the 'emp_location' table
System.out.println(" DROP TABLE emp_location");
Statement drop = con.createStatement();
drop.executeUpdate("DROP TABLE emp_location");
drop.close();
}
catch (Exception e)
{
JdbcException jdbcExc = new JdbcException(e, con);
jdbcExc.handle();
}
} // prevValSeq
} // DbSeq