/****************************************************************************
** (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 <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlutil.h>
#include "utilemb.h"
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 *);
EXEC SQL BEGIN DECLARE SECTION;
sqlint32 prod_num;
sqlint32 sales_amt;
char sales_date[11];
char strStmt[1024];
EXEC SQL END DECLARE SECTION;
int main(int argc, char *argv[])
{
int rc = 0;
struct sqlca sqlca;
char dbAlias[SQL_ALIAS_SZ + 1];
char user[USERID_SZ + 1];
char pswd[PSWD_SZ + 1];
printf("\n THIS SAMPLE SHOWS HOW TO INSERT THROUGH A 'UNION ALL' VIEW.");
printf("\n");
/* check the command line arguments */
rc = CmdLineArgsCheck1(argc, argv, dbAlias, user, pswd);
if (rc != 0)
{
return rc;
}
/* connect to the database */
rc = DbConn(dbAlias, user, pswd);
if (rc != 0)
{
return rc;
}
/* Create tables Q1, Q2, Q3 and Q4 and add constraints to them.
Also create a view FY */
rc = CreateTablesAndView();
/* Insert some values directly into tables Q1, Q2, Q3 and Q4 */
rc = InsertInitialValuesInTables();
/* Demonstrate how to insert through a UNION ALL view */
rc = InsertUsingUnionAll();
/* Modify the constraints of table Q1 */
rc = NewConstraints();
/* Attempt to insert data through a UNION ALL view where no table
accepts the row */
rc = InsertWhenNoTableAcceptsIt();
/* Attempt to insert data through a UNION ALL view where more than
one table accepts the row */
rc = InsertWhenMoreThanOneTableAcceptsIt();
/* Drop, recreate and reinitialize the tables and view */
rc = DropTablesAndView();
rc = CreateTablesAndView();
rc = 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 = UpdateWithRowMovement();
/* Show two special cases of row migration involving tables with
overlapping constraints. */
rc = UpdateWithRowMovementSpecialCase();
/* Drop tables Q1, Q2, Q3 and Q4 and the view FY */
rc = DropTablesAndView();
/* Disconnect from the database */
rc = DbDisconn(dbAlias);
if (rc != 0)
{
return rc;
}
return 0;
} /* main */
/* Helper function: This function displays the results of the query
specified by 'querystr' */
int DisplayData(char *querystr)
{
struct sqlca sqlca;
printf("\n %s\n\n",querystr);
printf(" PRODUCT_NO SALES DATE\n");
printf(" ----------- ----------- ----------\n");
/* 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)
{
printf(" %11d", prod_num);
printf(" %11d", sales_amt);
printf(" %-11.11s", sales_date);
printf("\n");
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;
} /* DisplayData */
/* This function creates tables Q1, Q2, Q3 and Q4 and adds constraints
to them. It also creates a view FY */
int CreateTablesAndView(void)
{
struct sqlca sqlca;
printf("\n CREATE TABLES Q1,Q2,Q3 AND Q4 BY INVOKING\n");
printf(" THE STATEMENTS:\n\n");
printf(" CREATE TABLE Q1(product_no INT, sales INT, date DATE)\n");
printf(" CREATE TABLE Q2 LIKE Q1\n");
printf(" CREATE TABLE Q3 LIKE Q1\n");
printf(" CREATE TABLE Q4 LIKE Q1\n\n");
/* 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");
printf(" ADD CONSTRAINTS TO TABLES Q1, Q2, Q3 AND Q4 BY INVOKING\n");
printf(" THE STATEMENTS:\n\n");
printf(" ALTER TABLE Q1 ADD CONSTRAINT Q1_CHK_DATE");
printf(" CHECK (MONTH(date) IN (1, 2, 3))\n");
printf(" ALTER TABLE Q2 ADD CONSTRAINT Q2_CHK_DATE");
printf(" CHECK (MONTH(date) IN (4, 5, 6))\n");
printf(" ALTER TABLE Q3 ADD CONSTRAINT Q3_CHK_DATE");
printf(" CHECK (MONTH(date) IN (7, 8, 9))\n");
printf(" ALTER TABLE Q4 ADD CONSTRAINT Q4_CHK_DATE");
printf(" CHECK (MONTH(date) IN (10,11,12))\n\n");
/* 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");
printf(" CREATE A VIEW 'FY' BY INVOKING THE STATEMENT:\n\n");
printf(" CREATE VIEW FY AS\n");
printf(" SELECT product_no, sales, date FROM Q1\n");
printf(" UNION ALL\n");
printf(" SELECT product_no, sales, date FROM Q2\n");
printf(" UNION ALL\n");
printf(" SELECT product_no, sales, date FROM Q3\n");
printf(" UNION ALL\n");
printf(" SELECT product_no, sales, date FROM Q4\n\n");
/* Create the view FY which is 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;
} /* CreateTablesAndView */
/* This function inserts some values directly into tables
Q1, Q2, Q3 and Q4 */
int InsertInitialValuesInTables(void)
{
struct sqlca sqlca;
char *selectStmt = "SELECT * FROM FY ORDER BY date, product_no";
int rc = 0;
printf(" INSERT INITIAL VALUES INTO TABLES Q1, Q2, Q3, Q4 BY INVOKING\n");
printf(" THE STATEMENTS:\n\n");
printf(" INSERT INTO Q1 VALUES (5, 6, '2001-01-02'),\n");
printf(" (8, 100, '2001-02-28')\n");
printf(" INSERT INTO Q2 VALUES (3, 10, '2001-04-11'),\n");
printf(" (5, 15, '2001-05-19')\n");
printf(" INSERT INTO Q3 VALUES (1, 12, '2001-08-27')\n");
printf(" INSERT INTO Q4 VALUES (3, 14, '2001-12-29'),\n");
printf(" (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;
} /* InsertInitialValuesInTables */
/* This function drops tables Q1, Q2, Q3 and Q4 and the view FY */
int DropTablesAndView(void)
{
struct sqlca sqlca;
printf("\n DROP TABLES Q1,Q2,Q3,Q4 AND VIEW FY BY INVOKING\n");
printf(" THE STATEMENTS:\n\n");
printf(" DROP VIEW FY\n");
printf(" DROP TABLE Q1\n");
printf(" DROP TABLE Q2\n");
printf(" DROP TABLE Q3\n");
printf(" 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;
} /* DropTablesAndView */
/* This function demonstrates how to insert through a UNION ALL view */
int InsertUsingUnionAll(void)
{
struct sqlca sqlca;
char *selectStmt = "SELECT * FROM FY ORDER BY date, product_no";
int rc = 0;
printf("\n ----------------------------------------------------------");
printf("\n USE THE SQL STATEMENT:\n");
printf("\n INSERT\n");
printf("\n TO INSERT DATA THROUGH THE 'UNION ALL' VIEW.\n");
printf("\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 */
printf("\n INSERT DATA THROUGH THE 'UNION ALL' VIEW");
printf(" BY INVOKING THE STATEMENT:\n\n");
printf(" INSERT INTO FY VALUES (1, 20, '2001-06-03'),\n");
printf(" (2, 30, '2001-03-21'),\n");
printf(" (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 */
printf("\n CONTENTS OF THE TABLES Q1, Q2, Q3, AND Q4 ");
printf("AFTER INSERTING DATA:\n");
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;
} /* InsertUsingUnionAll */
/* This function modifies the constraints of table Q1 */
int NewConstraints(void)
{
struct sqlca sqlca;
printf("\n CHANGE THE CONSTRAINTS OF TABLE 'Q1' BY");
printf(" INVOKING THE STATEMENTS:\n\n");
printf(" DELETE FROM FY\n");
printf(" ALTER TABLE Q1 DROP CONSTRAINT Q1_CHK_DATE\n");
printf(" ALTER TABLE Q1 ADD CONSTRAINT Q1_CHK_DATE");
printf(" CHECK (MONTH(date) IN (4, 2, 3))\n");
/* 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;
} /* NewConstraints */
/* This function attempts to insert data through a UNION ALL view where no
table accepts the row */
int InsertWhenNoTableAcceptsIt(void)
{
struct sqlca sqlca;
printf("\n ----------------------------------------------------------\n");
printf(" USE THE SQL STATEMENT:\n\n");
printf(" INSERT\n\n");
printf(" TO ATTEMPT TO INSERT DATA THROUGH A 'UNION ALL' VIEW WHERE\n");
printf(" NO TABLE ACCEPTS THE ROW\n\n");
printf(" NO TABLE ACCEPTS A ROW WITH 'MONTH' = 1.");
printf(" AN ATTEMPT TO INSERT A ROW WITH\n");
printf(" 'MONTH' = 1, WOULD CAUSE A 'NO TARGET' ERROR TO BE RAISED");
printf("\n\n ATTEMPT TO INSERT A ROW WITH 'MONTH' = 1");
printf(" BY INVOKING THE STATEMENT:\n\n");
printf(" 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;
} /* InsertWhenNoTableAcceptsIt */
/* This function attempts to insert data through a UNION ALL view where more
than one table accepts the row */
int InsertWhenMoreThanOneTableAcceptsIt(void)
{
struct sqlca sqlca;
printf("\n ----------------------------------------------------------\n");
printf(" USE THE SQL STATEMENT:\n\n");
printf(" INSERT\n\n");
printf(" TO ATTEMPT TO INSERT DATA THROUGH A 'UNION ALL' VIEW WHERE\n");
printf(" MORE THAN ONE TABLE ACCEPTS THE ROW\n\n");
printf(" BOTH TABLES Q1 AND Q2 ACCEPT A ROW WITH 'MONTH' = 4.");
printf(" AN ATTEMPT TO\n");
printf(" INSERT A ROW WITH 'MONTH' = 4, WOULD CAUSE AN 'AMBIGUOUS");
printf(" TARGET' ERROR\n");
printf(" TO BE RAISED");
printf("\n\n ATTEMPT TO INSERT A ROW WITH 'MONTH' = 4");
printf(" BY INVOKING THE STATEMENT:\n\n");
printf(" 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;
} /* 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 UpdateWithRowMovement(void)
{
struct sqlca sqlca;
printf("\n CREATE A VIEW 'vfullyear' BY INVOKING THE STATEMENT:\n\n");
printf(" CREATE VIEW vfullyear AS\n");
printf(" SELECT product_no, sales, date FROM Q1\n");
printf(" UNION ALL\n");
printf(" SELECT product_no, sales, date FROM Q2\n");
printf(" UNION ALL\n");
printf(" SELECT product_no, sales, date FROM Q3\n");
printf(" UNION ALL\n");
printf(" SELECT product_no, sales, date FROM Q4\n");
printf(" WITH ROW MOVEMENT\n");
/* 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");
printf("\n CONTENTS OF THE TABLES Q1 AND Q2 BEFORE ROW MOVEMENT OCCURS\n");
DisplayData("SELECT * FROM Q1");
DisplayData("SELECT * FROM Q2");
printf("\n UPDATE VALUES IN VIEW vfullyear BY INVOKING\n");
printf(" THE STATEMENT:\n\n");
printf(" UPDATE vfullyear SET date = date + 2 MONTHS\n");
printf(" 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");
printf("\n\n CONTENTS OF THE TABLES Q1 AND Q2 AFTER ROW MOVEMENT OCCURS\n");
DisplayData("SELECT * FROM Q1");
DisplayData("SELECT * FROM Q2");
printf("\n DROP THE VIEW vfullyear BY INVOKING\n");
printf(" THE STATEMENT:\n\n");
printf(" DROP VIEW vfullyear\n");
EXEC SQL DROP VIEW vfullyear;
EMB_SQL_CHECK("vfullyear -- drop");
EXEC SQL COMMIT;
EMB_SQL_CHECK("commit");
return 0;
} /* 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 UpdateWithRowMovementSpecialCase(void)
{
struct sqlca sqlca;
printf("\n CREATE TABLES T1,T2 AND T3 BY INVOKING\n");
printf(" THE STATEMENTS:\n\n");
printf(" CREATE TABLE T1(name CHAR, grade INT)\n");
printf(" CREATE TABLE T2 LIKE T1\n");
printf(" CREATE TABLE T3 LIKE T1\n\n");
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");
printf(" INSERT INITIAL VALUES INTO TABLES T1, T2, T3 BY INVOKING\n");
printf(" THE STATEMENTS:\n\n");
printf(" INSERT INTO T1 VALUES ('a', 40), ('b', 55)\n");
printf(" INSERT INTO T2 VALUES ('c', 50), ('d', 75)\n");
printf(" 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");
printf("\n ADD CONSTRAINTS TO TABLES T1, T2 AND T3 BY INVOKING\n");
printf(" THE STATEMENTS:\n\n");
printf(" ALTER TABLE T1 ADD CONSTRAINT T1_CHK_GRADE\n");
printf(" CHECK (grade >= 0 AND grade <= 55)\n");
printf(" ALTER TABLE T2 ADD CONSTRAINT T2_CHK_GRADE\n");
printf(" CHECK (grade >= 50 AND grade <= 100)\n");
printf(" ALTER TABLE T3 ADD CONSTRAINT T3_CHK_GRADE\n");
printf(" CHECK (grade >= 90 AND grade <= 100)\n\n");
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");
printf(" CREATE A VIEW 'vmarks' BY INVOKING THE STATEMENT:\n\n");
printf(" CREATE VIEW vmarks AS\n");
printf(" SELECT name, grade FROM T1\n");
printf(" UNION ALL\n");
printf(" SELECT name, grade FROM T2\n");
printf(" UNION ALL\n");
printf(" SELECT name, grade FROM T3\n");
printf(" WITH ROW MOVEMENT\n");
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");
printf("\n ATTEMPT TO UPDATE THE ROW WITH grade = 90");
printf(" BY INVOKING THE STATEMENT:\n\n");
printf(" 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");
printf("\n\n ATTEMPT TO UPDATE THE ROW WITH grade = 50");
printf(" BY INVOKING THE STATEMENT:\n\n");
printf(" 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");
printf("\n DROP TABLES T1,T2,T3 AND VIEW vmarks BY INVOKING\n");
printf(" THE STATEMENTS:\n\n");
printf(" DROP VIEW vmarks\n");
printf(" DROP TABLE T1\n");
printf(" DROP TABLE T2\n");
printf(" 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;
} /* UpdateWithRowMovementSpecialCase */