/****************************************************************************
** (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: tbsavept.sqC
**
** SAMPLE: How to use external savepoints
** Also demonstrates how to change the default value of a column
**
** SQL STATEMENTS USED:
** ALTER TABLE
** CLOSE
** COMMIT
** CREATE TABLE
** CREATE USER TEMPORARY TABLESPACE
** DECLARE GLOBAL TEMPORARLY TABLE
** DECLARE CURSOR
** DROP TABLE
** DROP TABLESPACE
** EXECUTE IMMEDIATE
** FETCH
** INCLUDE
** INSERT
** OPEN
** PREPARE
** RELEASE SAVEPOINT
** ROLLBACK TO SAVEPOINT
** SAVEPOINT
** SELECT
** SET INTEGRITY
**
** STRUCTURES USED:
** sqlca
**
**
*****************************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing C applications, see the Application
** Development Guide.
**
** For information on using SQL statements, see the SQL Reference.
**
** For the latest information on programming, building, and running DB2
** applications, visit the DB2 Information Center:
** http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
****************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlutil.h>
#include "utilemb.h"
#if ((__cplusplus >= 199711L) && !defined DB2HP && !defined DB2AIX) || \
(DB2LINUX && (__LP64__ || (__GNUC__ >= 3)) )
#include <iomanip>
#include <iostream>
using namespace std;
#else
#include <iomanip.h>
#include <iostream.h>
#endif
#ifndef TRUE
#define TRUE 1
#define FALSE 0
#endif
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
char firstName[20 + 1];
char lastName[20 + 1];
char major[20 + 1];
char university[20 + 1];
char mystmnt[150 + 1];
char tablename[20 + 1];
char status[5 + 1];
float GPA;
short stuID;
short empID;
EXEC SQL END DECLARE SECTION;
class TbSavePt
{
public:
int DropTablespaceAndTables(void);
int CreateTablespaceAndTablesWithData(void);
int TbContentDisplay(char, char, char, char, char, char);
int TbIntegrityInfoDisplay();
int SavepointWithAlterTable(void);
int SavepointWithGlobalTemporaryTable(void);
int NestedSavepoints(void);
int SavepointWithSetIntegrity(void);
int SavepointWithNotLoggedInitiallyTable(void);
};
int main(int argc, char *argv[])
{
int rc = 0;
char dbAlias[SQL_ALIAS_SZ + 1];
char user[USERID_SZ + 1];
char pswd[PSWD_SZ + 1];
TbSavePt savept;
CmdLineArgs check;
DbEmb db;
/* Clear host variables */
memset(firstName, '\0', 21);
memset(lastName, '\0', 21);
memset(major, '\0', 21);
memset(university,'\0', 21);
memset(mystmnt, '\0', 151);
memset(tablename, '\0', 21);
memset(status, '\0', 6);
/* Check command line arguments */
rc = check.CmdLineArgsCheck1(argc, argv, db);
if (rc != 0)
{
return rc;
}
cout << endl << "THIS SAMPLE SHOWS HOW TO USE EXTERNAL SAVEPOINTS" << endl;
/* Connect to sample database */
rc = db.Connect();
if (rc != 0)
{
return rc;
}
/* Set up the initial state of the database */
savept.CreateTablespaceAndTablesWithData();
savept.SavepointWithAlterTable();
savept.SavepointWithGlobalTemporaryTable();
savept.NestedSavepoints();
savept.SavepointWithSetIntegrity();
savept.SavepointWithNotLoggedInitiallyTable();
/* Drop the tables and temporary table space */
rc = savept.DropTablespaceAndTables();
if (rc != 0)
{
return rc;
}
/* Disconnect from sample database */
rc = db.Disconnect();
if (rc != 0)
{
return rc;
}
return rc;
} /* main */
/*** Helper functions ***/
/* The following function drops the temporary tablespace as well as the
tables created and used by this sample. */
int TbSavePt::DropTablespaceAndTables(void)
{
cout << endl << "-----------------------------------------------------------" << endl;
cout << "USE THE SQL STATEMENTS:" << endl;
cout << " DROP TABLE" << endl;
cout << " DROP TABLESPACE" << endl;
cout << "TO DROP TABLE AND TABLE SPACE" << endl;
cout << endl << " DROP TABLESPACE tempspace" << endl;
EXEC SQL DROP TABLESPACE tempspace;
EMB_SQL_CHECK("Tablespace: drop");
cout << endl << " DROP TABLE students" << endl;
EXEC SQL DROP TABLE students;
EMB_SQL_CHECK("Table: drop");
cout << endl << " DROP TABLE studentrecords" << endl;
EXEC SQL DROP TABLE studentrecords;
EMB_SQL_CHECK("Table: drop");
return 0;
} /* TbSavePt::DropTablespaceAndTables */
/* The following function creates a temporary tablespace as well as two
tables, studentrecords and students. The tables are populated with
initial values. */
int TbSavePt::CreateTablespaceAndTablesWithData(void)
{
cout << endl << "-----------------------------------------------------------" << endl;
cout << "USE THE SQL STATEMENTS:" << endl;
cout << " COMMIT" << endl;
cout << " CREATE TABLE" << endl;
cout << " CREATE USER TEMPORARY TABLESPACE" << endl;
cout << " INSERT" << endl;
cout << "TO CREATE TABLES WITH DATA AND A USER TEMPORARY TABLE SPACE FOR" << endl;
cout << "THE TEMP TABLE" << endl;
/* Create the tables and temporary table space */
/* Create a temporary table space */
cout << endl << " CREATE USER TEMPORARY TABLESPACE tempspace" << endl;
cout << " MANAGED BY SYSTEM USING('temp')" << endl;
EXEC SQL
CREATE USER TEMPORARY TABLESPACE tempspace
MANAGED BY SYSTEM USING('temp');
EMB_SQL_CHECK("Temporary Table Space: create");
/* Create studentrecords table */
cout << endl << " CREATE TABLE studentrecords(" << endl;
cout << " studentid SMALLINT" << endl;
cout << " gpa FLOAT(3)" << endl;
cout << " university CHAR(20))" << endl << endl;
EXEC SQL CREATE TABLE studentrecords(
studentid smallint,
gpa float(3),
university char(20));
EMB_SQL_CHECK("Table: create");
/* Create students table */
cout << " CREATE TABLE students(" << endl;
cout << " firstname CHAR(20)" << endl;
cout << " lastname CHAR(20)" << endl;
cout << " studentid SMALLINT" << endl;
cout << " employeeid SMALLINT)" << endl;
cout << " NOT LOGGED INITIALLY" << endl << endl;
EXEC SQL CREATE TABLE students(
firstname char(20),
lastname char(20),
studentid smallint,
employeeid smallint)
NOT LOGGED INITIALLY;
EMB_SQL_CHECK("Table: create");
/* This is for deactivating NOT LOGGED INITIALLY mode for the table
STUDENTS. */
cout << " COMMIT" << endl;
EXEC SQL COMMIT;
EMB_SQL_CHECK("Commit");
/* Populate the tables just created */
/* Insert 2 records into students table */
cout << endl << " INSERT INTO students(firstname, lastname, studentid,";
cout << " emplyeeid)" << endl;
cout << " VALUES('Jack', 'Hill', 1, 3)," << endl;
cout << " ('Jill', 'Hill', 5, 7)" << endl << endl;
strcpy(mystmnt, "INSERT INTO students(firstname, lastname, studentid,");
strcat(mystmnt, " employeeid) VALUES('Jack', 'Hill', 1, 3),");
strcat(mystmnt, " ('Jill', 'Hill', 5, 7)");
EXEC SQL EXECUTE IMMEDIATE :mystmnt;
EMB_SQL_CHECK("Table: insert");
/* Insert 2 records into studentrecords table */
cout << " INSERT INTO studentrecords(studentid, gpa, university)" << endl;
cout << " VALUES(1, 2.5, 'Crown')," << endl;
cout << " (5, 3.99, 'Tumbling')" << endl;
strcpy(mystmnt, "INSERT INTO studentrecords(studentid, gpa, university)");
strcat(mystmnt, " VALUES(1, 2.5, 'Crown'),");
strcat(mystmnt, " (5, 3.99, 'Tumbling')");
EXEC SQL EXECUTE IMMEDIATE :mystmnt;
EMB_SQL_CHECK("Table: insert");
/* Prepare SELECT statements */
strcpy(mystmnt, "SELECT * FROM studentrecords");
EXEC SQL PREPARE selectstmnt1 FROM :mystmnt;
EMB_SQL_CHECK("Prepare");
strcpy(mystmnt, "SELECT * FROM students");
EXEC SQL PREPARE selectstmnt2 FROM :mystmnt;
EMB_SQL_CHECK("Prepare");
strcpy(mystmnt, "SELECT TABNAME, STATUS");
strcat(mystmnt, " FROM syscat.tables WHERE TABNAME='STUDENTRECORDS'");
EXEC SQL PREPARE selectstmnt3 FROM :mystmnt;
EMB_SQL_CHECK("Prepare");
/* Declare cursors for the prepared SELECT statements */
EXEC SQL DECLARE stuRecCursor CURSOR FOR selectstmnt1;
EMB_SQL_CHECK("Cursor: declare");
EXEC SQL DECLARE studentsCursor CURSOR FOR selectstmnt2;
EMB_SQL_CHECK("Cursor: declare");
EXEC SQL DECLARE syscatCursor CURSOR FOR selectstmnt3;
EMB_SQL_CHECK("Cursor: declare");
/* Display the contents of the tables to show that the records were
inserted successfully */
TbContentDisplay(FALSE, TRUE, TRUE, FALSE, TRUE, TRUE);
return 0;
} /* TbSavePt::CreateTablespaceAndTablesWithData */
/* The following function displays data in two tables, studentrecords
and students. The parameters passed to this function should be either
TRUE or FALSE. If the parameter is true, a corresponding section of
code is executed. For example:
TbContentDisplay(TRUE, TRUE, TRUE, FALSE, TRUE, TRUE);
will show the major column, show the data in both tables, will not expect
failure, and will open and close all cursors used for displaying the
tables. */
int TbSavePt::TbContentDisplay(char useMajor,
char showSTUDENTRECORDS,
char showSTUDENTS,
char expectStuRecFail,
char openCursors,
char closeCursors)
{
/* If the cursors are to be opened, open them */
if(openCursors)
{
EXEC SQL OPEN stuRecCursor;
EMB_SQL_CHECK("Cursor: open");
EXEC SQL OPEN studentsCursor;
EMB_SQL_CHECK("Cursor: open");
}
/* Display the contents of the studentrecords table */
if (showSTUDENTRECORDS)
{
cout << endl << " SELECT * FROM studentrecords" << endl;
/* Print out header for the student record table */
if(useMajor)
{
cout << " Student ID GPA University Major" << endl;
cout << " ---------- ---- -------------------- ";
cout << "--------------------" << endl;
}
else
{
cout << " Student ID GPA University " << endl;
cout << " ---------- ---- --------------------" << endl;
}
/* Display all of the records in the studentrecords table */
while(sqlca.sqlcode != 100)
{
/* Check whether to retrieve the 'major' column */
if(useMajor)
{
EXEC SQL FETCH stuRecCursor INTO :stuID, :GPA, :university, :major;
if (sqlca.sqlcode == 0)
cout << setw(14) << stuID << " " << setw(4) << GPA
<< " " << setw(4) << university << " " << major << endl;
}
else
{
EXEC SQL FETCH stuRecCursor INTO :stuID, :GPA, :university;
if (sqlca.sqlcode == 0)
cout << setw(14) << stuID << " " << setw(4) << GPA/1.00
<< " " << setw(4) << university << endl;
}
/* Check whether an error has occured before reaching the end of
the table */
if (sqlca.sqlcode != 100)
{
/* Check whether the failure of the fetch is expected */
if (expectStuRecFail)
{
EXPECTED_ERR_CHECK("Cursor:fetch");
break;
}
else
{
EMB_SQL_CHECK("Cursor:fetch");
}
}
}
}
/* Display the contents of the students table */
if (showSTUDENTS)
{
cout << endl << " SELECT * FROM students" << endl;
/* Print out header for the student record table */
cout << " First Name Last Name ";
cout << "Student ID Employee ID" << endl;
cout << " -------------------- -------------------- ";
cout << "---------- -----------" << endl;
/* Fetch the first record. This is to reset the value of the sqlcode */
EXEC SQL FETCH studentsCursor INTO :firstName, :lastName, :stuID, :empID;
/* Print out all of the records in the studentrecords table */
while(sqlca.sqlcode != 100)
{
/* Check whether an error has occured before reaching the end of
the table */
if (sqlca.sqlcode != 100)
EMB_SQL_CHECK("Cursor:fetch");
if (sqlca.sqlcode == 0)
cout << setw(24) << firstName << " " << setw(5) << lastName
<< " " << setw(10) << stuID << " " << setw(11) << empID
<< endl;
EXEC SQL FETCH studentsCursor INTO :firstName, :lastName, :stuID, :empID;
}
}
/* Check whether to close the cursors */
if (closeCursors)
{
EXEC SQL CLOSE stuRecCursor;
EMB_SQL_CHECK("Cursor: close");
EXEC SQL CLOSE studentsCursor;
EMB_SQL_CHECK("Cursor: close");
}
return 0;
} /* TbSavePt::TbContentDisplay */
/* The following function displays the integrity status for the
studentrecords table. */
int TbSavePt::TbIntegrityInfoDisplay()
{
/* Get the status of the studentrecords table from syscat.tables */
cout << " SELECT tabname, status" << endl;
cout << " FROM syscat.tables WHERE tabname='STUDENTRECORDS'" << endl;
EXEC SQL OPEN syscatCursor;
EMB_SQL_CHECK("Cursor: open");
EXEC SQL FETCH syscatCursor INTO :tablename, :status;
EMB_SQL_CHECK("Cursor: fetch");
EXEC SQL CLOSE syscatCursor;
EMB_SQL_CHECK("Cursor: close");
/* Print out the status for the studentrecords table */
cout << endl << " Integrity status:" << endl;
cout << " Table Status" << endl;
cout << " -------------------- ------" << endl;
cout << setw(22) << tablename << " " << setw(5) << status << endl;
return 0;
} /* TbSavePt::TbIntegrityInfoDisplay */
/*** Main functions ***/
/* The following function demonstrates the use of a savepoint. A savepoint
is set, after which the studentrecords table is modified. A rollback is
issued, invalidating the cursor for the studentrecords table. All
changes that occurred after the savepoint was set are also undone. */
int TbSavePt::SavepointWithAlterTable()
{
cout << endl << "-----------------------------------------------------------" << endl;
cout << "USE THE SQL STATEMENTS:" << endl;
cout << " ALTER TABLE" << endl;
cout << " INSERT" << endl;
cout << " RELEASE SAVEPOINT" << endl;
cout << " ROLLBACK TO SAVEPOINT" << endl;
cout << " SAVEPOINT" << endl;
cout << " SELECT" << endl;
cout << "TO ADD A 'MAJOR' COLUMN TO THE STUDENTRECORDS TABLE" << endl;
cout << "AND ATTEMPT TO SELECT ROWS FROM THE STUDENTRECORDS TABLE " << endl;
cout << "BEFORE AND AFTER ROLLING BACK TO THE SAVEPOINT." << endl;
/* Set savepoint */
cout << endl << " SAVEPOINT sp ON ROLLBACK RETAIN CURSORS" << endl;
EXEC SQL SAVEPOINT sp ON ROLLBACK RETAIN CURSORS;
EMB_SQL_CHECK("Savepoint: set");
/* Add a column to the studentrecords table */
cout << endl << " ALTER TABLE studentrecords" << endl;
cout << " ADD COLUMN major char(20) WITH DEFAULT 'Computer Science'" << endl;
strcpy(mystmnt, "ALTER TABLE studentrecords");
strcat(mystmnt, " ADD COLUMN major char(20) WITH DEFAULT 'Computer Science'");
EXEC SQL EXECUTE IMMEDIATE :mystmnt;
EMB_SQL_CHECK("Alter table: add column");
/* Show that the column has successfully been added to the studentrecords
table, and that the cursors successfully retrieve the records from the
studentrecords and students tables. */
TbContentDisplay(TRUE, TRUE, TRUE, FALSE, TRUE, TRUE);
cout << endl << " NOTE:" << endl;
cout << " The 'major' column has successfully been added to the" << endl;
cout << " studentrecords table. As well, cursors for the" << endl;
cout << " studentrecords and students tables were able to" << endl;
cout << " successfully retrieve their records." << endl;
/* Update the default value of the new column to 'Computer Science' */
cout << endl << " ALTER TABLE studentrecords" << endl;
cout << " ALTER COLUMN major SET DEFAULT \'English Lit\'" << endl;
strcpy(mystmnt, "ALTER TABLE studentrecords");
strcat(mystmnt,
" ALTER COLUMN major SET DEFAULT 'English Lit'");
EXEC SQL EXECUTE IMMEDIATE :mystmnt;
EMB_SQL_CHECK("Alter table: alter default value");
/* Insert a row to the table with the new default value */
cout << endl << " INSERT INTO students(firstname, lastname, studentid,";
cout << " emplyeeid)" << endl;
cout << " VALUES('Rolling', 'Hill', 9, 22))" << endl;
strcpy(mystmnt, "INSERT INTO students(firstname, lastname, studentid,");
strcat(mystmnt, " employeeid) VALUES('Rolling', 'Hill', 9, 22)");
EXEC SQL EXECUTE IMMEDIATE :mystmnt;
EMB_SQL_CHECK("Table: insert");
/* Insert the new record into studentrecords table */
cout << " INSERT INTO studentrecords(studentid, gpa, university, major)" << endl;
cout << " VALUES(9, 3.0, 'After', default))" << endl;
strcpy(mystmnt, "INSERT INTO studentrecords(studentid, gpa, university, major)");
strcat(mystmnt, " VALUES(9, 3.0, 'After', default)");
EXEC SQL EXECUTE IMMEDIATE :mystmnt;
EMB_SQL_CHECK("Table: insert");
/* Display the results, to see that only the new column has the */
/* modified default value */
TbContentDisplay(TRUE, TRUE, TRUE, FALSE, TRUE, FALSE);
cout << endl << " NOTE:" << endl;
cout << " The 'major' column has successfully been altered " << endl;
cout << " for the record just inserted. Old rows retained their " << endl;
cout << " original values" << endl;
/* Rollback to the savepoint */
cout << endl << " ROLLBACK TO SAVEPOINT sp" << endl;
EXEC SQL ROLLBACK TO SAVEPOINT sp;
EMB_SQL_CHECK("Savepoint: rollback");
/* Show that the cursor for the studentrecords table has been invalidated
while the cursor for the students table is still valid. */
TbContentDisplay(FALSE, TRUE, TRUE, TRUE, FALSE, TRUE);
cout << endl << " NOTE:" << endl;
cout << " Due to the ROLLBACK TO SAVEPOINT, the ALTER TABLE" << endl;
cout << " statement applied to the studentrecords table has been" << endl;
cout << " undone and it's cursor has been invalidated, thus the" << endl;
cout << " SELECT statement for the studentrecords table fails." << endl;
cout << " However, the student table was not modified within" << endl;
cout << " this savepoint, so it's cursor is valid and it's SELECT" << endl;
cout << " statement is successful." << endl;
/* Set the savepoint */
cout << endl << " RELEASE SAVEPOINT sp" << endl;
EXEC SQL RELEASE SAVEPOINT sp;
EMB_SQL_CHECK("Savepoint: point");
return 0;
} /* TbSavePt::SavepointWithAlterTable */
/* The following function demonstrates the use of a savepoint. A savepoint
is set, after which a global temporary table is declared. A rollback is
issued, implicitly dropping the global temporary table. */
int TbSavePt::SavepointWithGlobalTemporaryTable()
{
cout << endl << "-----------------------------------------------------------" << endl;
cout << "USE THE SQL STATEMENTS:" << endl;
cout << " DECLARE GLOBAL TEMPORARY TABLE" << endl;
cout << " RELEASE SAVEPOINT" << endl;
cout << " ROLLBACK TO SAVEPOINT" << endl;
cout << " SAVEPOINT" << endl;
cout << " SELECT" << endl;
cout << "TO CREATE AND INSERT DATA INTO A DECLARED GLOBAL TEMPORARY" << endl;
cout << "TABLE, WITH THE SAME COLUMNS AS THE STUDENTS TABLE, WITHIN" << endl;
cout << "A SAVEPOINT AND SHOW THAT THE TEMPORARY TABLE IS REMOVED AFTER" << endl;
cout << "ROLLING BACK TO THE SAVEPOINT." << endl;
/* Set savepoint */
cout << endl << " SAVEPOINT sp ON ROLLBACK RETAIN CURSORS" << endl;
EXEC SQL SAVEPOINT sp ON ROLLBACK RETAIN CURSORS;
/* Create a temporary table */
cout << endl << " DECLARE GLOBAL TEMPORARY TABLE temptbl" << endl;
cout << " LIKE students NOT LOGGED" << endl;
EXEC SQL DECLARE GLOBAL TEMPORARY TABLE temptbl
LIKE students NOT LOGGED;
EMB_SQL_CHECK("Temporary Table: declare");
/* Insert a record into the temporary table
using the required "session" qualifier */
cout << endl << " INSERT INTO session.temptbl" << endl;
cout << " (SELECT firstname, lastname, studentid, employeeid" << endl;
cout << " FROM students)" << endl;
EXEC SQL INSERT INTO session.temptbl
(SELECT firstname, lastname, studentid, employeeid FROM students);
EMB_SQL_CHECK("Temporary Table: insert");
/* Rollback to the savepoint */
cout << endl << " ROLLBACK TO SAVEPOINT sp" << endl;
EXEC SQL ROLLBACK TO SAVEPOINT sp;
EMB_SQL_CHECK("Savepoint: rollback");
/* Attempt to retrieve records from the temporary table after rolling
back to the savepoint. */
cout << endl << " SELECT * FROM session.temptbl" << endl;
EXEC SQL DECLARE tmpselect CURSOR FOR SELECT * FROM session.temptbl;
EMB_SQL_CHECK("Cursor: declare");
EXEC SQL OPEN tmpselect;
EXPECTED_ERR_CHECK("Cursor: open");
cout << endl << " NOTE:" << endl;
cout << " The temporary table that was created within this savepoint" << endl;
cout << " was implicitly dropped during the ROLLBACK TO SAVEPOINT." << endl;
cout << " So an attempt to select some records from the temporary" << endl;
cout << " table fails." << endl;
/* Release the savepoint */
cout << endl << " RELEASE SAVEPOINT sp" << endl;
EXEC SQL RELEASE SAVEPOINT sp;
EMB_SQL_CHECK("Savepoint: release");
return 0;
} /* TbSavePt::SavepointWithGlobalTemporaryTable */
/* The following function demonstrates nested savepoints. */
int TbSavePt::NestedSavepoints()
{
cout << endl << "-----------------------------------------------------------" << endl;
cout << "USE THE SQL STATEMENTS:" << endl;
cout << " SAVEPOINT sp" << endl;
cout << " INSERT" << endl;
cout << " SAVEPOINT nestedsp" << endl;
cout << " INSERT" << endl;
cout << " SELECT" << endl;
cout << " ROLLBACK TO SAVEPOINT nestedsp" << endl;
cout << " SELECT" << endl;
cout << " INSERT" << endl;
cout << " ROLLBACK TO SAVEPOINT sp" << endl;
cout << " SELECT" << endl;
cout << " RELEASE SAVEPOINT nestedsp" << endl;
cout << " RELEASE SAVEPOINT sp" << endl;
cout << "TO ATTEMPT TO CREATE A SAVEPOINT WITHIN A SAVEPOINT" << endl;
cout << "(NESTED SAVEPOINTS) TO SHOW SAVEPOINTS CAN BE NESTED." << endl;
/* Set savepoint */
cout << endl << " SAVEPOINT sp ON ROLLBACK RETAIN CURSORS" << endl;
EXEC SQL SAVEPOINT sp ON ROLLBACK RETAIN CURSORS;
EMB_SQL_CHECK("Savepoint");
/* Insert 2 records into students table */
cout << endl << " INSERT INTO students(firstname, lastname, studentid,"
<< " emplyeeid)" << endl;
cout << " VALUES('Evelyn', 'Moore', 10, 30)," << endl;
cout << " ('Jamie', 'Mak', 7, 75)" << endl;
strcpy(mystmnt, "INSERT INTO students(firstname, lastname, studentid,");
strcat(mystmnt, " employeeid) VALUES('Evelyn', 'Moore', 10, 30),");
strcat(mystmnt, " ('Jamie', 'Mak', 7, 75)");
EXEC SQL EXECUTE IMMEDIATE :mystmnt;
EMB_SQL_CHECK("Table: insert");
/* Set a nested savepoint */
cout << endl << " SAVEPOINT nestedsp ON ROLLBACK RETAIN CURSORS" << endl;
EXEC SQL SAVEPOINT nestedsp on rollback retain cursors;
EMB_SQL_CHECK("Savepoint");
/* Insert 2 records into students table */
cout << endl << " INSERT INTO students(firstname, lastname, studentid,";
cout << " emplyeeid)" << endl;
cout << " VALUES('Aaron', 'Johnson', 11, 31)," << endl;
cout << " ('Karl', 'Johnson', 8, 76)" << endl;
strcpy(mystmnt, "INSERT INTO students(firstname, lastname, studentid,");
strcat(mystmnt, " employeeid) VALUES('Aaron', 'Johnson', 11, 31),");
strcat(mystmnt, " ('Karl', 'Johnson', 8, 76)");
EXEC SQL EXECUTE IMMEDIATE :mystmnt;
EMB_SQL_CHECK("Table: insert");
/* Show that the record has been added to the students table */
TbContentDisplay(FALSE, TRUE, TRUE, FALSE, TRUE, TRUE);
/* Rollback the nested savepoint */
cout << endl << " RELEASE SAVEPOINT nestedsp" << endl;
EXEC SQL ROLLBACK TO SAVEPOINT nestedsp;
EMB_SQL_CHECK("Savepoint: rollback");
/* Show that the records inserted to the students table after savepoint nestedsp was rollback */
TbContentDisplay(FALSE, TRUE, TRUE, FALSE, TRUE, TRUE);
strcpy(mystmnt, "INSERT INTO students(firstname, lastname, studentid,");
strcat(mystmnt, " employeeid) VALUES('Erin', 'Tory', 11, 31)");
EXEC SQL EXECUTE IMMEDIATE :mystmnt;
EMB_SQL_CHECK("Table: insert");
/* Rollback to the savepoint */
cout << endl << " ROLLBACK TO SAVEPOINT sp" << endl;
EXEC SQL ROLLBACK TO SAVEPOINT sp;
EMB_SQL_CHECK("Savepoint: rollback");
/* Show that the records inserted to the students table after savepoint sp was rollback */
TbContentDisplay(FALSE, TRUE, TRUE, FALSE, TRUE, TRUE);
/* Release the nested savepoint */
cout << endl << " RELEASE SAVEPOINT nestedsp" << endl;
EXEC SQL RELEASE SAVEPOINT nestedsp;
EXPECTED_ERR_CHECK("Savepoint: release");
cout << endl << " NOTE:" << endl;
cout << " Since the savepoint nestedsp is started after savepoint sp, the" << endl;
cout << " rollback of savepoint sp has removed the savepoints nested inside." << endl;
/* Release the nested savepoint */
cout << endl << " RELEASE SAVEPOINT sp" << endl;
EXEC SQL RELEASE SAVEPOINT sp;
EMB_SQL_CHECK("Savepoint: release");
return 0;
} /* TbSavePt::NestedSavepoints */
/* The following function demonstrates that table integrity is retained by a
savepoint. If a ROLLBACK TO SAVEPOINT is issued and a savepoint exists
before a table's integrity is modified, changes to the table's integrity
are rolled back. */
int TbSavePt::SavepointWithSetIntegrity()
{
cout << endl << "-----------------------------------------------------------" << endl;
cout << "USE THE SQL STATEMENTS:" << endl;
cout << " RELEASE SAVEPOINT" << endl;
cout << " ROLLBACK TO SAVEPOINT" << endl;
cout << " SAVEPOINT" << endl;
cout << " SELECT" << endl;
cout << " SET INTEGRITY" << endl;
cout << "WITHIN A SAVEPOINT, TO SET INTEGRITY FOR STUDENTRECORDS" << endl;
cout << "(CHANGE THE STATUS OF THE STUDENTRECORDS TABLE, IN" << endl;
cout << "SYSCAT.TABLES, FROM 'N', FOR NORMAL to 'C' FOR CHECK PENDING" << endl;
cout << "STATE), THEN ROLLBACK TO THE SAVEPOINT AND SHOW THAT THE" << endl;
cout << "STATUS OF THE STUDENTRECORDS TABLE IS RETURNED TO 'N'" << endl;
cout << " (NORMAL STATE)." << endl;
/* Set savepoint */
cout << endl << " SAVEPOINT sp ON ROLLBACK RETAIN CURSORS" << endl << endl;
EXEC SQL SAVEPOINT sp ON ROLLBACK RETAIN CURSORS;
EMB_SQL_CHECK("Savepoint");
/* Print out the current integrity status for the studentrecords table */
TbIntegrityInfoDisplay();
/* Set the integrity for the studentrecords table to CHECK PENDING
NO ACCESS state, and IMMEDIATELY CASCADE. */
cout << endl << " SET INTEGRITY FOR studentrecords" << endl;
cout << " OFF NO ACCESS CASCADE IMMEDIATE" << endl << endl;
strcpy(mystmnt,
"SET INTEGRITY FOR studentrecords OFF NO ACCESS CASCADE IMMEDIATE");
EXEC SQL EXECUTE IMMEDIATE :mystmnt;
EMB_SQL_CHECK("Set integrity");
/* Print out the update integrity status for the studentrecords table */
TbIntegrityInfoDisplay();
/* Rollback to the savepoint */
cout << endl << " ROLLBACK TO SAVEPOINT sp" << endl << endl;
EXEC SQL ROLLBACK TO SAVEPOINT sp;
EMB_SQL_CHECK("Savepoint: rollback");
/* Print out the current integrity status for the studentrecords table to
show it has been reset to it's original status. */
TbIntegrityInfoDisplay();
/* Release the savepoint */
cout << endl << " RELEASE SAVEPOINT sp" << endl;
EXEC SQL RELEASE SAVEPOINT sp;
EMB_SQL_CHECK("Savepoint: release");
return 0;
} /* TbSavePt::SavepointWithSetIntegrity */
/* The following function demonstrates a special case of savepoints. If a
table is set to NOT LOG INITIALLY mode and a new row is inserted, rolling
back to a savepoint fails. This failure causes the entire unit of work
to rollback. This means that the initially created savepoint is no
longer available. */
int TbSavePt::SavepointWithNotLoggedInitiallyTable()
{
cout << endl << "-----------------------------------------------------------" << endl;
cout << "USE THE SQL STATEMENTS:" << endl;
cout << " ALTER TABLE" << endl;
cout << " COMMIT" << endl;
cout << " INSERT" << endl;
cout << " RELEASE SAVEPOINT" << endl;
cout << " ROLLBACK TO SAVEPOINT" << endl;
cout << " SAVEPOINT" << endl;
cout << " SELECT" << endl;
cout << "TO ALTER THE STUDENTS TABLE TO BE NOT LOGGED INITIALLY," << endl;
cout << "SET A SAVEPOINT, INSERT A RECORD INTO THE STUDENTS" << endl;
cout << "TABLE, THEN ATTEMPT TO ROLLBACK TO THE SAVEPOINT" << endl;
/* Prevent the loss of the earlier work done by this sample */
cout << endl << " COMMIT" << endl;
EXEC SQL COMMIT;
EMB_SQL_CHECK("Commit");
/* Activate the students table's NOT LOGGED INITIALLY mode */
cout << endl << " ALTER TABLE students ACTIVATE NOT LOGGED INITIALLY" << endl;
strcpy(mystmnt, "ALTER TABLE students ACTIVATE NOT LOGGED INITIALLY");
EXEC SQL EXECUTE IMMEDIATE :mystmnt;
EMB_SQL_CHECK("Alter table: not logged initially");
/* Set savepoint */
cout << endl << " SAVEPOINT sp ON ROLLBACK RETAIN CURSORS" << endl;
EXEC SQL SAVEPOINT sp ON ROLLBACK RETAIN CURSORS;
EMB_SQL_CHECK("Savepoint");
/* Insert another record into the students table */
cout << endl << " INSERT INTO students(firstname, lastname, studentid,";
cout << " emplyeeid)" << endl;
cout << " VALUES('Jack', 'Benimble', 9, 11)" << endl;
strcpy(mystmnt, "INSERT INTO students(firstname, lastname, studentid,");
strcat(mystmnt, " employeeid) VALUES('Jack', 'Benimble', 9, 11)");
EXEC SQL EXECUTE IMMEDIATE :mystmnt;
EMB_SQL_CHECK("Table: insert");
/* Show that the record has been added to the students table */
TbContentDisplay(TRUE, FALSE, TRUE, FALSE, TRUE, TRUE);
/* Rollback to the savepoint */
cout << endl << " ROLLBACK TO SAVEPOINT sp" << endl;
EXEC SQL ROLLBACK TO SAVEPOINT sp;
EXPECTED_ERR_CHECK("Savepoint: rollback");
cout << endl << " NOTE:" << endl;
cout << " Since the students table is in NOT LOGGED INITIALLY" << endl;
cout << " mode rolling back to a savepoint fails with an error. The" << endl;
cout << " result is the entire unit of work being rolled back and" << endl;
cout << " the students table being put into a 'drop only' state." << endl;
/* Release the savepoint */
cout << endl << " RELEASE SAVEPOINT sp" << endl;
EXEC SQL RELEASE SAVEPOINT sp;
EXPECTED_ERR_CHECK("Savepoint: release");
cout << endl << " NOTE:" << endl;
cout << " Since the entire unit of work has been rolled back, the" << endl;
cout << " savepoint no longer exists. So the RELEASE SAVEPOINT" << endl;
cout << " statement fails." << endl;
return 0;
} /* TbSavePt::SavepointWithNotLoggedInitiallyTable */