/****************************************************************************
** (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: tbunion.sqC
**
** SAMPLE: How to insert through a UNION ALL view
**
** SQL STATEMENTS USED:
** DECLARE CURSOR
** SELECT
** OPEN
** FETCH
** CLOSE
** CREATE TABLE
** ALTER TABLE
** DROP TABLE
** CREATE VIEW
** DROP VIEW
** EXECUTE IMMEDIATE
** PREPARE
** INSERT
** DELETE
** UPDATE
**
**
*****************************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing embedded SQL applications see the Developing Embedded SQL Applications book.
**
** For information on using SQL statements, see the SQL Reference.
**
** For the latest information on programming, building, and running DB2
** applications, visit the DB2 application development website:
** http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
****************************************************************************/
#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
EXEC SQL BEGIN DECLARE SECTION;
sqlint32 prod_num;
sqlint32 sales_amt;
char sales_date[11];
char strStmt[1024];
EXEC SQL END DECLARE SECTION;
class TbUnion
{
public:
int CreateTablesAndView(void);
int InsertInitialValuesInTables(void);
int InsertUsingUnionAll(void);
int NewConstraints(void);
int InsertWhenNoTableAcceptsIt(void);
int InsertWhenMoreThanOneTableAcceptsIt(void);
int UpdateWithRowMovement(void);
int UpdateWithRowMovementSpecialCase(void);
int DropTablesAndView(void);
int DisplayData(char *);
};
// Helper function: This function displays the results of the query
// specified by 'querystr'
int TbUnion::DisplayData(char *querystr)
{
struct sqlca sqlca;
cout << "\n " << querystr << "\n\n";
cout << " PRODUCT_NO SALES DATE" << endl;
cout << " ----------- ----------- ----------" << endl;
// Declare a CURSOR to store the results of the query specified by
// 'querystr'
strcpy(strStmt,
querystr);
EXEC SQL PREPARE S1 FROM :strStmt;
EMB_SQL_CHECK("after prepare");
EXEC SQL DECLARE c1 CURSOR FOR s1;
EMB_SQL_CHECK("declare cursor");
EXEC SQL OPEN c1;
EMB_SQL_CHECK("cursor -- open");
// Retrieve and display the results of the query
EXEC SQL FETCH c1 INTO :prod_num, :sales_amt, :sales_date;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(11) << prod_num;
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(11) << sales_amt;
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(11) << sales_date;
cout << endl;
EXEC SQL FETCH c1 INTO :prod_num, :sales_amt, :sales_date;
EMB_SQL_CHECK("cursor -- fetch");
}
EXEC SQL CLOSE c1;
EMB_SQL_CHECK("cursor -- close");
return 0;
} // TbUnion::DisplayData
// This function creates tables Q1, Q2, Q3 and Q4 and adds constraints
// to them. It also creates a view FY which is a view over the full year.
int TbUnion::CreateTablesAndView(void)
{
struct sqlca sqlca;
cout << "\n CREATE TABLES Q1,Q2,Q3 AND Q4 BY INVOKING" << endl;
cout << " THE STATEMENTS:\n\n";
cout << " CREATE TABLE Q1(product_no INT, sales INT, date DATE)\n"
<< " CREATE TABLE Q2 LIKE Q1\n"
<< " CREATE TABLE Q3 LIKE Q1\n"
<< " CREATE TABLE Q4 LIKE Q1\n" << endl;
// Create tables Q1, Q2, Q3 and Q4
EXEC SQL CREATE TABLE Q1(product_no INT, sales INT, date DATE);
EMB_SQL_CHECK("create--table");
EXEC SQL CREATE TABLE Q2 LIKE Q1;
EMB_SQL_CHECK("create--table");
EXEC SQL CREATE TABLE Q3 LIKE Q1;
EMB_SQL_CHECK("create--table");
EXEC SQL CREATE TABLE Q4 LIKE Q1;
EMB_SQL_CHECK("create--table");
cout << " ADD CONSTRAINTS TO TABLES Q1, Q2, Q3 AND Q4 BY INVOKING\n"
<< " THE STATEMENTS:\n\n"
<< " ALTER TABLE Q1 ADD CONSTRAINT Q1_CHK_DATE"
<< " CHECK (MONTH(date) IN (1, 2, 3))\n"
<< " ALTER TABLE Q2 ADD CONSTRAINT Q2_CHK_DATE"
<< " CHECK (MONTH(date) IN (4, 5, 6))\n"
<< " ALTER TABLE Q3 ADD CONSTRAINT Q3_CHK_DATE"
<< " CHECK (MONTH(date) IN (7, 8, 9))\n"
<< " ALTER TABLE Q4 ADD CONSTRAINT Q4_CHK_DATE"
<< " CHECK (MONTH(date) IN (10,11,12))\n" << endl;
// Adds constraints to tables Q1, Q2, Q3 and Q4
EXEC SQL ALTER TABLE Q1 ADD CONSTRAINT Q1_CHK_DATE
CHECK (MONTH(date) IN (1, 2, 3));
EMB_SQL_CHECK("create--alter table");
EXEC SQL ALTER TABLE Q2 ADD CONSTRAINT Q2_CHK_DATE
CHECK (MONTH(date) IN (4, 5, 6));
EMB_SQL_CHECK("create--alter table");
EXEC SQL ALTER TABLE Q3 ADD CONSTRAINT Q3_CHK_DATE
CHECK (MONTH(date) IN (7, 8, 9));
EMB_SQL_CHECK("create--alter table");
EXEC SQL ALTER TABLE Q4 ADD CONSTRAINT Q4_CHK_DATE
CHECK (MONTH(date) IN (10, 11, 12));
EMB_SQL_CHECK("create--alter table");
cout << " CREATE A VIEW 'FY' BY INVOKING THE STATEMENT:\n\n"
<< " CREATE VIEW FY AS\n"
<< " SELECT product_no, sales, date FROM Q1\n"
<< " UNION ALL\n"
<< " SELECT product_no, sales, date FROM Q2\n"
<< " UNION ALL\n"
<< " SELECT product_no, sales, date FROM Q3\n"
<< " UNION ALL\n"
<< " SELECT product_no, sales, date FROM Q4\n" << endl;
// Create the view FY, a view over the full year.
strcpy(strStmt,
"CREATE VIEW FY AS"
" SELECT product_no, sales, date FROM Q1"
" UNION ALL"
" SELECT product_no, sales, date FROM Q2"
" UNION ALL"
" SELECT product_no, sales, date FROM Q3"
" UNION ALL"
" SELECT product_no, sales, date FROM Q4");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("view -- create");
EXEC SQL COMMIT;
EMB_SQL_CHECK("commit");
return 0;
} // TbUnion::CreateTablesAndView
// This function inserts some values directly into tables Q1, Q2, Q3 and Q4
int TbUnion::InsertInitialValuesInTables(void)
{
struct sqlca sqlca;
char *selectStmt = "SELECT * FROM FY ORDER BY date, product_no";
int rc = 0;
cout << " INSERT INITIAL VALUES INTO TABLES Q1, Q2, Q3, Q4 BY INVOKING\n"
<< " THE STATEMENTS:\n\n"
<< " INSERT INTO Q1 VALUES (5, 6, '2001-01-02'),\n"
<< " (8, 100, '2001-02-28')\n"
<< " INSERT INTO Q2 VALUES (3, 10, '2001-04-11'),\n"
<< " (5, 15, '2001-05-19')\n"
<< " INSERT INTO Q3 VALUES (1, 12, '2001-08-27')\n"
<< " INSERT INTO Q4 VALUES (3, 14, '2001-12-29'),\n"
<< " (2, 21, '2001-12-12')\n";
// Insert initial values into tables Q1, Q2, Q3 and Q4
strcpy(strStmt,
"INSERT INTO Q1 VALUES (5, 6, '2001-01-02'),"
" (8, 100, '2001-02-28')");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("insert -- Q1");
strcpy(strStmt,
"INSERT INTO Q2 VALUES (3, 10, '2001-04-11'),"
" (5, 15, '2001-05-19')");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("insert -- Q2");
strcpy(strStmt,
"INSERT INTO Q3 VALUES (1, 12, '2001-08-27')");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("insert -- Q3");
strcpy(strStmt,
"INSERT INTO Q4 VALUES (3, 14, '2001-12-29'),"
" (2, 21, '2001-12-12')");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("insert -- Q4");
// Display the view FY after inserting values into the tables
rc = DisplayData(selectStmt);
EXEC SQL COMMIT;
EMB_SQL_CHECK("commit");
return 0;
} // TbUnion::InsertInitialValuesInTables
// This function drops tables Q1, Q2, Q3 and Q4 and the view FY
int TbUnion::DropTablesAndView(void)
{
struct sqlca sqlca;
cout << "\n DROP TABLES Q1,Q2,Q3,Q4 AND VIEW FY BY INVOKING\n"
<< " THE STATEMENTS:\n\n"
<< " DROP VIEW FY\n"
<< " DROP TABLE Q1\n"
<< " DROP TABLE Q2\n"
<< " DROP TABLE Q3\n"
<< " DROP TABLE Q4\n";
EXEC SQL DROP VIEW FY;
EXEC SQL DROP TABLE Q1;
EXEC SQL DROP TABLE Q2;
EXEC SQL DROP TABLE Q3;
EXEC SQL DROP TABLE Q4;
EMB_SQL_CHECK("drop table");
EXEC SQL COMMIT;
EMB_SQL_CHECK("commit");
return 0;
} // TbUnion::DropTablesAndView
// This function demonstrates how to insert through a UNION ALL view
int TbUnion::InsertUsingUnionAll(void)
{
struct sqlca sqlca;
char *selectStmt = "SELECT * FROM FY ORDER BY date, product_no";
int rc = 0;
cout << endl;
cout << " ----------------------------------------------------------\n"
<< " USE THE SQL STATEMENT:\n\n"
<< " INSERT\n\n"
<< " TO INSERT DATA THROUGH THE 'UNION ALL' VIEW.\n";
cout << "\n CONTENTS OF THE VIEW 'FY' BEFORE INSERTING DATA:\n";
// Display the initial content of the view FY before inserting new
// rows
rc = DisplayData(selectStmt);
// INSERT data into tables Q1, Q2, Q3 and Q4 through the
// UNION ALL view FY
cout << endl;
cout << " INSERT DATA THROUGH THE 'UNION ALL' VIEW"
<< " BY INVOKING THE STATEMENT:\n\n"
<< " INSERT INTO FY VALUES (1, 20, '2001-06-03'),\n"
<< " (2, 30, '2001-03-21'),\n"
<< " (2, 25, '2001-08-30')\n";
strcpy(strStmt,
"INSERT INTO FY VALUES (1, 20, '2001-06-03'),"
" (2, 30, '2001-03-21'),"
" (2, 25, '2001-08-30')");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("insert -- FY");
// Display the final content of all tables
cout << endl
<< " CONTENTS OF THE TABLES Q1, Q2, Q3, AND Q4 AFTER INSERTING DATA:"
<< endl;
selectStmt = "SELECT * FROM Q1 ORDER BY date, product_no";
rc = DisplayData(selectStmt);
selectStmt = "SELECT * FROM Q2 ORDER BY date, product_no";
rc = DisplayData(selectStmt);
selectStmt = "SELECT * FROM Q3 ORDER BY date, product_no";
rc = DisplayData(selectStmt);
selectStmt = "SELECT * FROM Q4 ORDER BY date, product_no";
rc = DisplayData(selectStmt);
EXEC SQL ROLLBACK;
return 0;
} // TbUnion::InsertUsingUnionAll
// This function modifies the constraints of table Q1
int TbUnion::NewConstraints(void)
{
struct sqlca sqlca;
cout << endl;
cout << " CHANGE THE CONSTRAINTS OF TABLE 'Q1' BY"
<< " INVOKING THE STATEMENTS:\n\n"
<< " DELETE FROM FY\n"
<< " ALTER TABLE Q1 DROP CONSTRAINT Q1_CHK_DATE\n"
<< " ALTER TABLE Q1 ADD CONSTRAINT Q1_CHK_DATE"
<< " CHECK (MONTH(date) IN (4, 2, 3))" << endl;
// Drop the constraint Q1_CHK_DATE and add a new one
strcpy(strStmt,"DELETE FROM FY");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("delete -- FY");
strcpy(strStmt,"ALTER TABLE Q1 DROP CONSTRAINT Q1_CHK_DATE");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("alter table -- drop Q1_CHK_DATE");
strcpy(strStmt,
"ALTER TABLE Q1 ADD CONSTRAINT Q1_CHK_DATE"
" CHECK (MONTH(date) IN (4, 2, 3))");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("alter table -- add Q1_CHK_DATE");
EXEC SQL COMMIT;
EMB_SQL_CHECK("commit");
return 0;
} // TbUnion::NewConstraints
// This function attempts to insert data through a UNION ALL view where no
// table accepts the row
int TbUnion::InsertWhenNoTableAcceptsIt(void)
{
struct sqlca sqlca;
cout << endl;
cout << " ----------------------------------------------------------\n"
<< " USE THE SQL STATEMENT:\n\n"
<< " INSERT\n\n"
<< " TO ATTEMPT TO INSERT DATA THROUGH A 'UNION ALL' VIEW WHERE\n"
<< " NO TABLE ACCEPTS THE ROW\n" << endl;
cout << " NO TABLE ACCEPTS A ROW WITH 'MONTH' = 1."
<< " AN ATTEMPT TO INSERT A ROW WITH\n"
<< " 'MONTH' = 1, WOULD CAUSE A 'NO TARGET' ERROR TO BE RAISED";
cout << endl;
cout << "\n ATTEMPT TO INSERT A ROW WITH 'MONTH' = 1"
<< " BY INVOKING THE STATEMENT:\n\n"
<< " INSERT INTO FY VALUES (5, 35, '2001-01-14')\n";
// Attempt to insert a row with 'MONTH' = 1 which no table will accept
strcpy(strStmt,"INSERT INTO FY VALUES (5, 35, '2001-01-14')");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EXPECTED_ERR_CHECK("INSERT WHERE NO TABLE ACCEPTS THE ROW");
EXEC SQL ROLLBACK;
EMB_SQL_CHECK("rollback");
return 0;
} // TbUnion::InsertWhenNoTableAcceptsIt
// This function attempts to insert data through a UNION ALL view where more
// than one table accepts the row
int TbUnion::InsertWhenMoreThanOneTableAcceptsIt(void)
{
struct sqlca sqlca;
cout << endl;
cout << " ----------------------------------------------------------\n"
<< " USE THE SQL STATEMENT:\n\n"
<< " INSERT\n\n"
<< " TO ATTEMPT TO INSERT DATA THROUGH A 'UNION ALL' VIEW WHERE\n"
<< " MORE THAN ONE TABLE ACCEPTS THE ROW\n" << endl;
cout << " BOTH TABLES Q1 AND Q2 ACCEPT A ROW WITH 'MONTH' = 4."
<< " AN ATTEMPT TO\n"
<< " INSERT A ROW WITH 'MONTH' = 4, WOULD CAUSE AN 'AMBIGUOUS"
<< " TARGET' ERROR\n"
<< " TO BE RAISED";
cout << endl;
cout << "\n ATTEMPT TO INSERT A ROW WITH 'MONTH' = 4"
<< " BY INVOKING THE STATEMENT:\n\n"
<< " INSERT INTO FY VALUES (3, 30, '2001-04-21')\n";
// Attempt to insert a row with 'MONTH' = 4 which is accepted
// by both tables Q1 and Q2
strcpy(strStmt,
"INSERT INTO FY VALUES (3, 30, '2001-04-21')");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EXPECTED_ERR_CHECK("INSERT WHERE MORE THAN ONE TABLE ACCEPTS THE ROW");
EXEC SQL ROLLBACK;
EMB_SQL_CHECK("rollback");
return 0;
} // TbUnion::InsertWhenMoreThanOneTableAcceptsIt
// This function creates a new view. The new view has the WITH ROW
// MIGRATION clause in it, which enables row migration. It performs some
// updates through this view to show how row migration affects the
// underlying tables.
int TbUnion::UpdateWithRowMovement(void)
{
struct sqlca sqlca;
char *selectStmt;
int rc;
cout << endl
<< " CREATE A VIEW 'vfullyear' BY INVOKING THE STATEMENT:\n\n"
<< " CREATE VIEW vfullyear AS\n"
<< " SELECT product_no, sales, date FROM Q1\n"
<< " UNION ALL\n"
<< " SELECT product_no, sales, date FROM Q2\n"
<< " UNION ALL\n"
<< " SELECT product_no, sales, date FROM Q3\n"
<< " UNION ALL\n"
<< " SELECT product_no, sales, date FROM Q4\n"
<< " WITH ROW MOVEMENT\n" << endl;
// Create the view vfullyear, this is the same as view FY with the
// exception that it has the WITH ROW MOVEMENT clause. This additional
// clause allows updates through the view to move rows across the underlying
// tables (row migration) as necessary.
strcpy(strStmt,
"CREATE VIEW vfullyear AS"
" SELECT product_no, sales, date FROM Q1"
" UNION ALL"
" SELECT product_no, sales, date FROM Q2"
" UNION ALL"
" SELECT product_no, sales, date FROM Q3"
" UNION ALL"
" SELECT product_no, sales, date FROM Q4"
" WITH ROW MOVEMENT");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("Create -- vfullyear");
cout << " CONTENTS OF THE TABLES Q1 AND Q2 BEFORE ROW MOVEMENT OCCURS"
<< endl;
selectStmt = "SELECT * FROM Q1";
rc = DisplayData(selectStmt);
selectStmt = "SELECT * FROM Q2";
rc = DisplayData(selectStmt);
cout << endl
<< " UPDATE VALUES IN VIEW vfullyear BY INVOKING\n"
<< " THE STATEMENT:\n\n"
<< " UPDATE vfullyear SET date = date + 2 MONTHS\n"
<< " WHERE date='2001-02-28'";
// Demonstrate row movement by executing the following UPDATE statement.
// This statement causes a row to move from table Q1 to table Q2.
strcpy(strStmt,
"UPDATE vfullyear SET date = date + 2 MONTHS"
" WHERE date='2001-02-28'");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("Update -- vfullyear");
cout << endl
<< "\n CONTENTS OF THE TABLES Q1 AND Q2 AFTER ROW MOVEMENT OCCURS"
<< endl;
selectStmt = "SELECT * FROM Q1";
rc = DisplayData(selectStmt);
selectStmt = "SELECT * FROM Q2";
rc = DisplayData(selectStmt);
cout << "\n DROP THE VIEW vfullyear BY INVOKING\n"
<< " THE STATEMENT:\n\n"
<< " DROP VIEW vfullyear\n";
EXEC SQL DROP VIEW vfullyear;
EMB_SQL_CHECK("vfullyear -- drop");
EXEC SQL COMMIT;
EMB_SQL_CHECK("commit");
return 0;
} // TbUnion::UpdateWithRowMovement
// This function creates three new tables and one new view. It performs some
// updates through the view to show two special cases of row migration.
int TbUnion::UpdateWithRowMovementSpecialCase(void)
{
struct sqlca sqlca;
cout << "\n CREATE TABLES T1,T2 AND T3 BY INVOKING" << endl;
cout << " THE STATEMENTS:\n\n";
cout << " CREATE TABLE T1(name CHAR, grade INT)\n"
<< " CREATE TABLE T2 LIKE T1\n"
<< " CREATE TABLE T3 LIKE T1\n" << endl;
EXEC SQL CREATE TABLE T1(name CHAR, grade INT);
EMB_SQL_CHECK("create--table");
EXEC SQL CREATE TABLE T2 LIKE T1;
EMB_SQL_CHECK("create--table");
EXEC SQL CREATE TABLE T3 LIKE T1;
EMB_SQL_CHECK("create--table");
cout << " INSERT INITIAL VALUES INTO TABLES T1, T2, T3 BY INVOKING\n"
<< " THE STATEMENTS:\n\n"
<< " INSERT INTO T1 VALUES ('a', 40), ('b', 55)\n"
<< " INSERT INTO T2 VALUES ('c', 50), ('d', 75)\n"
<< " INSERT INTO T3 VALUES ('d', 90), ('e', 95)\n";
strcpy(strStmt,"INSERT INTO T1 VALUES ('a', 40), ('b', 55)");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("insert--table");
strcpy(strStmt,"INSERT INTO T2 VALUES ('c', 50), ('d', 75)");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("insert--table");
strcpy(strStmt,"INSERT INTO T3 VALUES ('d', 90), ('e', 95)");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("insert--table");
cout << "\n ADD CONSTRAINTS TO TABLES T1, T2 AND T3 BY INVOKING\n"
<< " THE STATEMENTS:\n\n"
<< " ALTER TABLE T1 ADD CONSTRAINT T1_CHK_GRADE\n"
<< " CHECK (grade >= 0 AND grade <= 55)\n"
<< " ALTER TABLE T2 ADD CONSTRAINT T2_CHK_GRADE\n"
<< " CHECK (grade >= 50 AND grade <= 100)\n"
<< " ALTER TABLE T3 ADD CONSTRAINT T3_CHK_GRADE\n"
<< " CHECK (grade >= 90 AND grade <= 100)\n" << endl;
EXEC SQL ALTER TABLE T1 ADD CONSTRAINT T1_CHK_GRADE
CHECK (grade >= 0 AND grade <= 55);
EMB_SQL_CHECK("table--alter");
EXEC SQL ALTER TABLE T2 ADD CONSTRAINT T2_CHK_GRADE
CHECK (grade >= 50 AND grade <= 100);
EMB_SQL_CHECK("table--alter");
EXEC SQL ALTER TABLE T3 ADD CONSTRAINT T3_CHK_GRADE
CHECK (grade >= 90 AND grade <= 100);
EMB_SQL_CHECK("table--alter");
cout << " CREATE A VIEW 'vmarks' BY INVOKING THE STATEMENT:\n\n"
<< " CREATE VIEW vmarks AS\n"
<< " SELECT name, grade FROM T1\n"
<< " UNION ALL\n"
<< " SELECT name, grade FROM T2\n"
<< " UNION ALL\n"
<< " SELECT name, grade FROM T3\n"
<< " WITH ROW MOVEMENT" << endl;
strcpy(strStmt,
"CREATE VIEW vmarks AS"
" SELECT name, grade FROM T1"
" UNION ALL"
" SELECT name, grade FROM T2"
" UNION ALL"
" SELECT name, grade FROM T3"
" WITH ROW MOVEMENT");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("Create -- vmarks");
cout << endl;
cout << " ATTEMPT TO UPDATE THE ROW WITH grade = 90"
<< " BY INVOKING THE STATEMENT:\n\n"
<< " UPDATE vmarks SET GRADE = 50 WHERE GRADE = 90\n";
// Attempt to update the row where grade = 90, which satisfies constraints
// for both tables T1 and T2. An error is raised since this update is
// ambiguous. A similar error is raised on an ambiguous insert statement.
strcpy(strStmt,
"UPDATE vmarks SET grade = 50"
" WHERE grade = 90");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EXPECTED_ERR_CHECK("UPDATE WHERE MORE THAN ONE TABLE ACCEPTS THE ROW");
cout << endl;
cout << "\n ATTEMPT TO UPDATE THE ROW WITH grade = 50"
<< " BY INVOKING THE STATEMENT:\n\n"
<< " UPDATE vmarks SET GRADE = 60 WHERE GRADE = 50\n";
// Attempt to update the row where grade = 50, which satisfies constraints
// for both tables T2 and T3. In this case no error is raised as row
// migration doesn't apply. The row does not need to be moved because it
// satisfies all constraints of the table it is already in.
strcpy(strStmt,
"UPDATE vmarks SET grade = 60"
" WHERE grade = 50");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("Update -- vmarks");
cout << "\n DROP TABLES T1,T2,T3 AND VIEW vmarks BY INVOKING\n"
<< " THE STATEMENTS:\n\n"
<< " DROP VIEW vmarks\n"
<< " DROP TABLE T1\n"
<< " DROP TABLE T2\n"
<< " DROP TABLE T3\n";
EXEC SQL DROP VIEW vmarks;
EMB_SQL_CHECK("drop--view");
EXEC SQL DROP TABLE T1;
EMB_SQL_CHECK("drop--table");
EXEC SQL DROP TABLE T2;
EMB_SQL_CHECK("drop--table");
EXEC SQL DROP TABLE T3;
EMB_SQL_CHECK("drop--table");
EXEC SQL COMMIT;
EMB_SQL_CHECK("commit");
return 0;
} // TbUnion::UpdateWithRowMovementSpecialCase
int main(int argc, char *argv[])
{
int rc = 0;
struct sqlca sqlca;
CmdLineArgs check;
DbEmb db;
TbUnion unionIns;
// check the command line arguments
rc = check.CmdLineArgsCheck1(argc, argv, db);
if (rc != 0)
{
return rc;
}
cout.setf(ios::fixed, ios::floatfield);
cout << "\n THIS SAMPLE SHOWS HOW TO INSERT THROUGH A 'UNION ALL' VIEW."
<< endl;
// Connect to the database
rc = db.Connect();
if (rc != 0)
{
return rc;
}
// Create tables Q1, Q2, Q3 and Q4 and add constraints to them.
// Also create a view FY, which is a view over the full year.
rc = unionIns.CreateTablesAndView();
// Insert some values directly into tables Q1, Q2, Q3 and Q4
rc = unionIns.InsertInitialValuesInTables();
// Demonstrate how to insert through a UNION ALL view
rc = unionIns.InsertUsingUnionAll();
// Modify the constraints of table Q1
rc = unionIns.NewConstraints();
// Attempt to insert data through a UNION ALL view where no table
// accepts the row
rc = unionIns.InsertWhenNoTableAcceptsIt();
// Attempt to insert data through a UNION ALL view where more than
// one table accepts the row
rc = unionIns.InsertWhenMoreThanOneTableAcceptsIt();
// Drop, recreate and reinitialize the tables and view
rc = unionIns.DropTablesAndView();
rc = unionIns.CreateTablesAndView();
rc = unionIns.InsertInitialValuesInTables();
// Create a new view and perform some updates through it. This shows how
// updates through a view with row migration affect the underlying
// tables.
rc = unionIns.UpdateWithRowMovement();
// Show two special cases of row migration involving tables with
// overlapping constraints.
rc = unionIns.UpdateWithRowMovementSpecialCase();
// Drop tables Q1, Q2, Q3 and Q4 and the view FY
rc = unionIns.DropTablesAndView();
// Disconnect from the database
rc = db.Disconnect();
if (rc != 0)
{
return rc;
}
return 0;
} // main