/****************************************************************************
** (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: tbconstr.sqc
**
** SAMPLE: How to create, use, and drop constraints
**
**
** SQL STATEMENTS USED:
** CREATE TABLE
** EXECUTE IMMEDIATE
** PREPARE
** DECLARE CURSOR
** OPEN
** FETCH
** CLOSE
** DROP TABLE
** ALTER TABLE
**
**
*****************************************************************************
**
** 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 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"
int Cn_NOT_NULL_Show(void);
int Cn_UNIQUE_Show(void);
int Cn_PRIMARY_KEY_Show(void);
int Cn_CHECK_Show(void);
int Cn_CHECK_INFO_Show(void);
int Cn_WITH_DEFAULT_Show(void);
int Cn_FK_OnInsertShow(void);
int Cn_FK_ON_UPDATE_NO_ACTION_Show(void);
int Cn_FK_ON_UPDATE_RESTRICT_Show(void);
int Cn_FK_ON_DELETE_CASCADE_Show(void);
int Cn_FK_ON_DELETE_SET_NULL_Show(void);
int Cn_FK_ON_DELETE_NO_ACTION_Show(void);
int Cn_FK_ON_DELETE_RESTRICT_Show(void);
/* support functions */
int FK_TwoTablesCreate(void);
int FK_TwoTablesDisplay(void);
int FK_TwoTablesDrop(void);
int FK_Create(char *);
int FK_Drop(void);
EXEC SQL BEGIN DECLARE SECTION;
char strStmt[384];
char deptno[3 + 1];
char deptname[20 + 1];
char empno[4 + 1];
char empname[10 + 1];
char dept_no[3 + 1];
short dept_noInd;
char firstname[10 + 1];
char lastname[10 + 1];
double salary;
EXEC SQL END DECLARE SECTION;
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];
/* check the command line arguments */
rc = CmdLineArgsCheck1(argc, argv, dbAlias, user, pswd);
if (rc != 0)
{
return rc;
}
printf("\nTHIS SAMPLE SHOWS HOW TO CREATE/USE/DROP CONSTRAINTS.\n");
/* connect to database */
rc = DbConn(dbAlias, user, pswd);
if (rc != 0)
{
return rc;
}
rc = Cn_NOT_NULL_Show();
rc = Cn_UNIQUE_Show();
rc = Cn_PRIMARY_KEY_Show();
rc = Cn_CHECK_Show();
rc = Cn_CHECK_INFO_Show();
rc = Cn_WITH_DEFAULT_Show();
printf("\n#####################################################\n"
"# Create tables for FOREIGN KEY sample functions #\n"
"#####################################################\n");
rc = FK_TwoTablesCreate();
if (rc != 0)
{
return rc;
}
rc = Cn_FK_OnInsertShow();
rc = Cn_FK_ON_UPDATE_NO_ACTION_Show();
rc = Cn_FK_ON_UPDATE_RESTRICT_Show();
rc = Cn_FK_ON_DELETE_CASCADE_Show();
rc = Cn_FK_ON_DELETE_SET_NULL_Show();
rc = Cn_FK_ON_DELETE_NO_ACTION_Show();
printf("\n########################################################\n"
"# Drop tables created for FOREIGN KEY sample functions #\n"
"########################################################\n");
rc = FK_TwoTablesDrop();
/* disconnect from the database */
rc = DbDisconn(dbAlias);
if (rc != 0)
{
return rc;
}
return 0;
} /* main */
int FK_TwoTablesCreate(void)
{
struct sqlca sqlca;
printf("\n CREATE TABLE deptmt(deptno CHAR(3) NOT NULL,\n"
" deptname VARCHAR(20),\n"
" CONSTRAINT pk_dept PRIMARY KEY(deptno))\n");
EXEC SQL CREATE TABLE deptmt(deptno CHAR(3) NOT NULL,
deptname VARCHAR(20),
CONSTRAINT pk_dept PRIMARY KEY(deptno));
EMB_SQL_CHECK("first table -- create");
printf("\n INSERT INTO deptmt VALUES('A00', 'ADMINISTRATION'),\n"
" ('B00', 'DEVELOPMENT'),\n"
" ('C00', 'SUPPORT')\n");
strcpy(strStmt, "INSERT INTO deptmt VALUES('A00', 'ADMINISTRATION'), "
" ('B00', 'DEVELOPMENT'), "
" ('C00', 'SUPPORT') ");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("first table -- insert");
printf("\n CREATE TABLE empl(empno CHAR(4),\n"
" empname VARCHAR(10),\n"
" dept_no CHAR(3))\n");
EXEC SQL CREATE TABLE empl(empno CHAR(4),
empname VARCHAR(10),
dept_no CHAR(3));
EMB_SQL_CHECK("second table -- create");
printf("\n INSERT INTO empl VALUES('0010', 'Smith', 'A00'),\n"
" ('0020', 'Ngan', 'B00'),\n"
" ('0030', 'Lu', 'B00'),\n"
" ('0040', 'Wheeler', 'B00'),\n"
" ('0050', 'Burke', 'C00'),\n"
" ('0060', 'Edwards', 'C00'),\n"
" ('0070', 'Lea', 'C00')\n");
strcpy(strStmt, "INSERT INTO empl VALUES('0010', 'Smith', 'A00'), "
" ('0020', 'Ngan', 'B00'), "
" ('0030', 'Lu', 'B00'), "
" ('0040', 'Wheeler', 'B00'), "
" ('0050', 'Burke', 'C00'), "
" ('0060', 'Edwards', 'C00'), "
" ('0070', 'Lea', 'C00') ");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("second table -- insert");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
return 0;
} /* FK_TwoTablesCreate */
int FK_TwoTablesDisplay(void)
{
struct sqlca sqlca;
printf("\n SELECT * FROM deptmt\n");
printf(" DEPTNO DEPTNAME \n");
printf(" ------- --------------\n");
strcpy(strStmt, "SELECT * FROM deptmt");
EXEC SQL PREPARE stmt1 FROM :strStmt;
EMB_SQL_CHECK("stmt1 -- prepare");
EXEC SQL DECLARE c1 CURSOR FOR stmt1;
EXEC SQL OPEN c1;
EMB_SQL_CHECK("cursor -- open");
EXEC SQL FETCH c1 INTO :deptno, :deptname;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
printf(" %-7s %-20s\n", deptno, deptname);
EXEC SQL FETCH c1 INTO :deptno, :deptname;
EMB_SQL_CHECK("cursor -- fetch");
}
EXEC SQL CLOSE c1;
EMB_SQL_CHECK("cursor -- close");
printf("\n SELECT * FROM empl\n");
printf(" EMPNO EMPNAME DEPT_NO\n");
printf(" ----- ---------- -------\n");
strcpy(strStmt, "SELECT * FROM empl");
EXEC SQL PREPARE stmt2 FROM :strStmt;
EMB_SQL_CHECK("stmt2 -- prepare");
EXEC SQL DECLARE c2 CURSOR FOR stmt2;
EXEC SQL OPEN c2;
EMB_SQL_CHECK("cursor -- open");
EXEC SQL FETCH c2 INTO :empno, :empname, :dept_no:dept_noInd;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
printf(" %-5s %-10s", empno, empname);
if (dept_noInd >= 0)
{
printf(" %-3s\n", dept_no);
}
else
{
printf(" -\n");
}
EXEC SQL FETCH c2 INTO :empno, :empname, :dept_no:dept_noInd;
EMB_SQL_CHECK("cursor -- fetch");
}
EXEC SQL CLOSE c2;
EMB_SQL_CHECK("cursor -- close");
return 0;
} /* FK_TwoTablesDisplay */
int FK_TwoTablesDrop(void)
{
struct sqlca sqlca;
printf("\n DROP TABLE deptmt\n");
EXEC SQL DROP TABLE deptmt;
EMB_SQL_CHECK("first table -- drop");
printf(" DROP TABLE empl\n");
EXEC SQL DROP TABLE empl;
EMB_SQL_CHECK("second table -- drop");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
return 0;
} /* FK_TwoTablesDrop */
int FK_Create(char *ruleClause)
{
struct sqlca sqlca;
printf("\n ALTER TABLE empl ADD CONSTRAINT fk_dept\n"
" FOREIGN KEY(dept_no)\n"
" REFERENCES deptmt(deptno)\n"
" %s\n", ruleClause);
sprintf(strStmt, "ALTER TABLE empl ADD CONSTRAINT fk_dept "
" FOREIGN KEY(dept_no) "
" REFERENCES deptmt(deptno) "
" %s ", ruleClause);
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("foreign key -- create");
printf(" COMMIT\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
return 0;
} /* FK_Create */
int FK_Drop(void)
{
struct sqlca sqlca;
printf("\n ALTER TABLE empl DROP CONSTRAINT fk_dept\n");
strcpy(strStmt, "ALTER TABLE empl DROP CONSTRAINT fk_dept ");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("foreign key -- drop");
printf(" COMMIT\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
return 0;
} /* FK_Drop */
int Cn_NOT_NULL_Show(void)
{
int rc = 0;
struct sqlca sqlca;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS:\n");
printf(" CREATE TABLE\n");
printf(" COMMIT\n");
printf(" INSERT\n");
printf(" DROP TABLE\n");
printf("TO SHOW A 'NOT NULL' CONSTRAINT.\n");
/* create table */
printf("\n CREATE TABLE empl_sal(lastname VARCHAR(10) NOT NULL,\n"
" firstname VARCHAR(10),\n"
" salary DECIMAL(7, 2))\n");
EXEC SQL CREATE TABLE empl_sal(lastname VARCHAR(10) NOT NULL,
firstname VARCHAR(10),
salary DECIMAL(7, 2));
EMB_SQL_CHECK("table -- create");
printf(" COMMIT\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
/* insert table */
printf("\n INSERT INTO empl_sal VALUES(NULL, 'PHILIP', 17000.00)\n");
strcpy(strStmt, "INSERT INTO empl_sal VALUES(NULL, 'PHILIP', 17000.00) ");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EXPECTED_ERR_CHECK("table -- insert");
/* drop table */
printf("\n DROP TABLE empl_sal\n");
EXEC SQL DROP TABLE empl_sal;
EMB_SQL_CHECK("table -- drop");
return 0;
} /* Cn_NOT_NULL_Show */
int Cn_UNIQUE_Show(void)
{
int rc = 0;
struct sqlca sqlca;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS:\n");
printf(" CREATE TABLE\n");
printf(" COMMIT\n");
printf(" INSERT\n");
printf(" ALTER TABLE\n");
printf(" DROP TABLE\n");
printf("TO SHOW A 'UNIQUE' CONSTRAINT.\n");
/* create table */
printf("\n CREATE TABLE empl_sal(lastname VARCHAR(10) NOT NULL,\n"
" firstname VARCHAR(10) NOT NULL,\n"
" salary DECIMAL(7, 2),\n"
" CONSTRAINT unique_cn UNIQUE(lastname, firstname))\n");
EXEC SQL CREATE TABLE empl_sal(lastname VARCHAR(10) NOT NULL,
firstname VARCHAR(10) NOT NULL,
salary DECIMAL(7, 2),
CONSTRAINT unique_cn UNIQUE(lastname, firstname));
EMB_SQL_CHECK("table -- create");
printf(" COMMIT\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
/* insert table */
printf("\n INSERT INTO empl_sal VALUES('SMITH', 'PHILIP', 17000.00),"
"\n ('SMITH', 'PHILIP', 21000.00) \n");
strcpy(strStmt,
"INSERT INTO empl_sal VALUES('SMITH', 'PHILIP', 17000.00), "
" ('SMITH', 'PHILIP', 21000.00) ");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EXPECTED_ERR_CHECK("table -- insert");
/* drop constraint */
printf("\n ALTER TABLE empl_sal DROP CONSTRAINT unique_cn\n");
EXEC SQL ALTER TABLE empl_sal DROP CONSTRAINT unique_cn;
EMB_SQL_CHECK("constrain -- drop");
/* drop table */
printf("\n DROP TABLE empl_sal\n");
EXEC SQL DROP TABLE empl_sal;
EMB_SQL_CHECK("table -- drop");
return 0;
} /* Cn_UNIQUE_Show */
int Cn_PRIMARY_KEY_Show(void)
{
int rc = 0;
struct sqlca sqlca;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS:\n");
printf(" CREATE TABLE\n");
printf(" COMMIT\n");
printf(" INSERT\n");
printf(" ALTER TABLE\n");
printf(" DROP TABLE\n");
printf("TO SHOW A 'PRIMARY KEY' CONSTRAINT.\n");
/* create table */
printf("\n CREATE TABLE empl_sal(lastname VARCHAR(10) NOT NULL,\n"
" firstname VARCHAR(10) NOT NULL,\n"
" salary DECIMAL(7, 2),\n"
" CONSTRAINT pk_cn PRIMARY KEY(lastname, firstname))\n");
EXEC SQL CREATE TABLE empl_sal(lastname VARCHAR(10) NOT NULL,
firstname VARCHAR(10) NOT NULL,
salary DECIMAL(7, 2),
CONSTRAINT pk_cn PRIMARY KEY(lastname, firstname));
EMB_SQL_CHECK("table -- create");
printf(" COMMIT\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
/* insert table */
printf("\n INSERT INTO empl_sal VALUES('SMITH', 'PHILIP', 17000.00),"
"\n ('SMITH', 'PHILIP', 21000.00) \n");
strcpy(strStmt,
"INSERT INTO empl_sal VALUES('SMITH', 'PHILIP', 17000.00), "
" ('SMITH', 'PHILIP', 21000.00) ");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EXPECTED_ERR_CHECK("table -- insert");
/* drop constraint */
printf("\n ALTER TABLE empl_sal DROP CONSTRAINT pk_cn\n");
EXEC SQL ALTER TABLE empl_sal DROP CONSTRAINT pk_cn;
EMB_SQL_CHECK("constraint -- drop");
/* drop table */
printf("\n DROP TABLE empl_sal\n");
EXEC SQL DROP TABLE empl_sal;
EMB_SQL_CHECK("table -- drop");
return 0;
} /* Cn_PRIMARY_KEY_Show */
int Cn_CHECK_Show(void)
{
int rc = 0;
struct sqlca sqlca;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS:\n");
printf(" CREATE TABLE\n");
printf(" COMMIT\n");
printf(" INSERT\n");
printf(" ALTER TABLE\n");
printf(" DROP TABLE\n");
printf("TO SHOW A 'CHECK' CONSTRAINT.\n");
/* create table */
printf("\n CREATE TABLE empl_sal(lastname VARCHAR(10),\n"
" firstname VARCHAR(10),\n"
" salary DECIMAL(7, 2),\n"
" CONSTRAINT check_cn CHECK(salary < 25000.00))\n");
EXEC SQL CREATE TABLE empl_sal(lastname VARCHAR(10),
firstname VARCHAR(10),
salary DECIMAL(7, 2),
CONSTRAINT check_cn CHECK(salary < 25000.00));
EMB_SQL_CHECK("table -- create");
printf(" COMMIT\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
/* insert table */
printf("\n INSERT INTO empl_sal VALUES('SMITH', 'PHILIP', 27000.00)\n");
strcpy(strStmt, "INSERT INTO empl_sal VALUES('SMITH', 'PHILIP', 27000.00)");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EXPECTED_ERR_CHECK("table -- insert");
/* drop constraint */
printf("\n ALTER TABLE empl_sal DROP CONSTRAINT check_cn\n");
EXEC SQL ALTER TABLE empl_sal DROP CONSTRAINT check_cn;
EMB_SQL_CHECK("constrain -- drop");
/* drop table */
printf("\n DROP TABLE empl_sal\n");
EXEC SQL DROP TABLE empl_sal;
EMB_SQL_CHECK("table -- drop");
return 0;
} /* Cn_CHECK_Show */
int Cn_CHECK_INFO_Show(void)
{
int rc = 0;
struct sqlca sqlca;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS:\n");
printf(" CREATE TABLE\n");
printf(" COMMIT\n");
printf(" INSERT\n");
printf(" ALTER TABLE\n");
printf(" DROP TABLE\n");
printf("TO SHOW AN 'INFORMATIONAL' CONSTRAINT.\n");
/* create table */
printf("\n CREATE TABLE empl(empno INTEGER NOT NULL PRIMARY KEY,\n"
" name VARCHAR(10),\n"
" firstname VARCHAR(20),\n"
" salary INTEGER CONSTRAINT minsalary\n"
" CHECK (salary >= 25000)\n"
" NOT ENFORCED\n"
" ENABLE QUERY OPTIMIZATION)\n");
EXEC SQL CREATE TABLE empl(empno INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(10),
firstname VARCHAR(20),
salary INTEGER CONSTRAINT minsalary
CHECK (salary >= 25000)
NOT ENFORCED
ENABLE QUERY OPTIMIZATION);
EMB_SQL_CHECK("table -- create");
printf(" COMMIT\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
/* Insert data that doesn't satisfy the constraint 'minsalary'.
Database manager does not enforce the constraint for IUD operations */
printf("\n\nTO SHOW NOT ENFORCED OPTION\n");
printf("\n INSERT INTO empl VALUES(1, 'SMITH', 'PHILIP', 1000)\n\n");
strcpy(strStmt, "INSERT INTO empl VALUES(1, 'SMITH', 'PHILIP', 1000)");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("table -- insert");
/* Alter the constraint to make it ENFORCED by database manager */
printf("Alter the constraint to make it ENFORCED by database manager\n");
printf("\n ALTER TABLE empl ALTER CHECK minsalary ENFORCED\n");
strcpy(strStmt, "ALTER TABLE empl ALTER CHECK minsalary ENFORCED");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EXPECTED_ERR_CHECK("table -- alter");
/* Delete entries from EMP Table */
printf("\n DELETE FROM empl\n");
strcpy(strStmt, "DELETE FROM empl");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("table -- alter");
/* Alter the constraint to make it ENFORCED by database manager */
printf("\n\nTO SHOW ENFORCED OPTION\n");
printf("\n ALTER TABLE empl ALTER CHECK minsalary ENFORCED\n");
strcpy(strStmt, "ALTER TABLE empl ALTER CHECK minsalary ENFORCED");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("table -- alter");
/* Insert table with data not conforming to the constraint 'minsalary'
Database manager does not enforce the constraint for IUD operations */
printf("\n INSERT INTO empl VALUES(1, 'SMITH', 'PHILIP', 1000)\n");
strcpy(strStmt, "INSERT INTO empl VALUES(1, 'SMITH', 'PHILIP', 1000)");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EXPECTED_ERR_CHECK("table -- insert");
/* drop table */
printf("\n DROP TABLE empl\n");
EXEC SQL DROP TABLE empl;
EMB_SQL_CHECK("table -- drop");
return 0;
} /* Cn_CHECK_INFO_Show */
int Cn_WITH_DEFAULT_Show(void)
{
int rc = 0;
struct sqlca sqlca;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS:\n");
printf(" CREATE TABLE\n");
printf(" COMMIT\n");
printf(" INSERT\n");
printf(" DROP TABLE\n");
printf("TO SHOW A 'WITH DEFAULT' CONSTRAINT.\n");
/* create table */
printf("\n CREATE TABLE empl_sal(lastname VARCHAR(10),\n"
" firstname VARCHAR(10),\n"
" "
"salary DECIMAL(7, 2) WITH DEFAULT 17000.00)\n");
EXEC SQL CREATE TABLE empl_sal(lastname VARCHAR(10),
firstname VARCHAR(10),
salary DECIMAL(7, 2) WITH DEFAULT 17000.00);
EMB_SQL_CHECK("table -- create");
printf(" COMMIT\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
/* insert table */
printf("\n INSERT INTO empl_sal(lastname, firstname)\n"
" VALUES('SMITH', 'PHILIP'),\n"
" ('PARKER', 'JOHN'),\n"
" ('PEREZ', 'MARIA')\n");
strcpy(strStmt, "INSERT INTO empl_sal(lastname, firstname) "
" VALUES('SMITH' , 'PHILIP'), "
" ('PARKER', 'JOHN'), "
" ('PEREZ' , 'MARIA') ");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("table -- insert");
/* display table */
printf("\n SELECT * FROM empl_sal\n");
printf(" FIRSTNAME LASTNAME SALARY \n");
printf(" ---------- ---------- --------\n");
strcpy(strStmt, "SELECT * FROM empl_sal");
EXEC SQL PREPARE stmt3 FROM :strStmt;
EMB_SQL_CHECK("stmt3 -- prepare");
EXEC SQL DECLARE c3 CURSOR FOR stmt3;
EXEC SQL OPEN c3;
EMB_SQL_CHECK("cursor -- open");
EXEC SQL FETCH c3 INTO :firstname, :lastname, :salary;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
printf(" %-10s %-10s %-7.2f\n", firstname, lastname, salary);
EXEC SQL FETCH c3 INTO :firstname, :lastname, :salary;
EMB_SQL_CHECK("cursor -- fetch");
}
EXEC SQL CLOSE c3;
EMB_SQL_CHECK("cursor -- close");
/* drop table */
printf("\n DROP TABLE empl_sal\n");
EXEC SQL DROP TABLE empl_sal;
EMB_SQL_CHECK("table -- drop");
return 0;
} /* Cn_WITH_DEFAULT_Show */
int Cn_FK_OnInsertShow(void)
{
int rc = 0;
struct sqlca sqlca;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS:\n");
printf(" ALTER TABLE\n");
printf(" COMMIT\n");
printf(" INSERT\n");
printf(" ROLLBACK\n");
printf("TO SHOW HOW A FOREIGN KEY WORKS ON INSERT.\n");
/* display initial tables content */
rc = FK_TwoTablesDisplay();
/* create foreign key */
rc = FK_Create("");
/* insert parent table */
printf("\n INSERT INTO deptmt VALUES('D00', 'SALES')\n");
strcpy(strStmt, "INSERT INTO deptmt VALUES('D00', 'SALES') ");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("parent table -- insert");
/* insert child table */
printf("\n INSERT INTO empl VALUES('0080', 'Pearce', 'E03')\n");
strcpy(strStmt, "INSERT INTO empl VALUES('0080', 'Pearce', 'E03') ");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EXPECTED_ERR_CHECK("child table -- insert");
/* display final tables content */
rc = FK_TwoTablesDisplay();
/* rollback transaction */
printf("\n ROLLBACK\n");
EXEC SQL ROLLBACK;
EMB_SQL_CHECK("transaction -- rollback");
/* drop foreign key */
rc = FK_Drop();
return 0;
} /* Cn_FK_OnInsertShow */
int Cn_FK_ON_UPDATE_NO_ACTION_Show(void)
{
int rc = 0;
struct sqlca sqlca;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS:\n");
printf(" ALTER TABLE\n");
printf(" COMMIT\n");
printf(" UPDATE\n");
printf(" ROLLBACK\n");
printf("TO SHOW AN 'ON UPDATE NO ACTION' FOREIGN KEY.\n");
/* display initial tables content */
rc = FK_TwoTablesDisplay();
/* create foreign key */
rc = FK_Create("ON UPDATE NO ACTION");
/* update parent table */
printf("\n UPDATE deptmt SET deptno = 'E01' WHERE deptno = 'A00'\n");
strcpy(strStmt, "UPDATE deptmt SET deptno = 'E01' WHERE deptno = 'A00' ");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EXPECTED_ERR_CHECK("parent table -- update");
printf("\n UPDATE deptmt SET deptno =\n"
" CASE\n"
" WHEN deptno = 'A00' THEN 'B00'\n"
" WHEN deptno = 'B00' THEN 'A00'\n"
" END\n"
" WHERE deptno = 'A00' OR deptno = 'B00'\n");
strcpy(strStmt, "UPDATE deptmt SET deptno = "
" CASE "
" WHEN deptno = 'A00' THEN 'B00' "
" WHEN deptno = 'B00' THEN 'A00' "
" END "
" WHERE deptno = 'A00' OR deptno = 'B00' ");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("parent table -- update");
/* update child table */
printf("\n UPDATE empl SET dept_no = 'G11' WHERE empname = 'Wheeler'\n");
strcpy(strStmt,
"UPDATE empl SET dept_no = 'G11' WHERE empname = 'Wheeler' ");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EXPECTED_ERR_CHECK("child table -- update");
/* display final tables content */
rc = FK_TwoTablesDisplay();
/* rollback transaction */
printf("\n ROLLBACK\n");
EXEC SQL ROLLBACK;
EMB_SQL_CHECK("transaction -- rollback");
/* drop foreign key */
rc = FK_Drop();
return 0;
} /* Cn_FK_ON_UPDATE_NO_ACTION_Show */
int Cn_FK_ON_UPDATE_RESTRICT_Show(void)
{
int rc = 0;
struct sqlca sqlca;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS:\n");
printf(" ALTER TABLE\n");
printf(" COMMIT\n");
printf(" UPDATE\n");
printf(" ROLLBACK\n");
printf("TO SHOW AN 'ON UPDATE RESTRICT' FOREIGN KEY.\n");
/* display initial tables content */
rc = FK_TwoTablesDisplay();
/* create foreign key */
rc = FK_Create("ON UPDATE RESTRICT");
/* update parent table */
printf("\n UPDATE deptmt SET deptno = 'E01' WHERE deptno = 'A00'\n");
strcpy(strStmt, "UPDATE deptmt SET deptno = 'E01' WHERE deptno = 'A00' ");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EXPECTED_ERR_CHECK("parent table -- update");
printf("\n UPDATE deptmt SET deptno =\n"
" CASE\n"
" WHEN deptno = 'A00' THEN 'B00'\n"
" WHEN deptno = 'B00' THEN 'A00'\n"
" END\n"
" WHERE deptno = 'A00' OR deptno = 'B00'\n");
strcpy(strStmt, "UPDATE deptmt SET deptno = "
" CASE "
" WHEN deptno = 'A00' THEN 'B00' "
" WHEN deptno = 'B00' THEN 'A00' "
" END "
" WHERE deptno = 'A00' OR deptno = 'B00' ");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EXPECTED_ERR_CHECK("parent table -- update");
/* update child table */
printf("\n UPDATE empl SET dept_no = 'G11' WHERE empname = 'Wheeler'\n");
strcpy(strStmt,
"UPDATE empl SET dept_no = 'G11' WHERE empname = 'Wheeler' ");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EXPECTED_ERR_CHECK("child table -- update");
/* display final tables content */
rc = FK_TwoTablesDisplay();
/* rollback transaction */
printf("\n ROLLBACK\n");
EXEC SQL ROLLBACK;
EMB_SQL_CHECK("transaction -- rollback");
/* drop foreign key */
rc = FK_Drop();
return 0;
} /* Cn_FK_ON_UPDATE_RESTRICT_Show */
int Cn_FK_ON_DELETE_CASCADE_Show(void)
{
int rc = 0;
struct sqlca sqlca;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS:\n");
printf(" ALTER TABLE\n");
printf(" COMMIT\n");
printf(" DELETE\n");
printf(" ROLLBACK\n");
printf("TO SHOW AN 'ON DELETE CASCADE' FOREIGN KEY.\n");
/* display initial tables content */
rc = FK_TwoTablesDisplay();
/* create foreign key */
rc = FK_Create("ON DELETE CASCADE");
/* delete parent table */
printf("\n DELETE FROM deptmt WHERE deptno = 'C00'\n");
strcpy(strStmt, "DELETE FROM deptmt WHERE deptno = 'C00' ");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("parent table -- delete");
/* display tables content */
rc = FK_TwoTablesDisplay();
/* delete child table */
printf("\n DELETE FROM empl WHERE empname = 'Wheeler'\n");
strcpy(strStmt, "DELETE FROM empl WHERE empname = 'Wheeler' ");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("child table -- delete");
/* display final tables content */
rc = FK_TwoTablesDisplay();
/* rollback transaction */
printf("\n ROLLBACK\n");
EXEC SQL ROLLBACK;
EMB_SQL_CHECK("transaction -- rollback");
/* drop foreign key */
rc = FK_Drop();
return 0;
} /* Cn_FK_ON_DELETE_CASCADE_Show */
int Cn_FK_ON_DELETE_SET_NULL_Show(void)
{
int rc = 0;
struct sqlca sqlca;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS:\n");
printf(" ALTER TABLE\n");
printf(" COMMIT\n");
printf(" DELETE\n");
printf(" ROLLBACK\n");
printf("TO SHOW AN 'ON DELETE SET NULL' FOREIGN KEY.\n");
/* display initial tables content */
rc = FK_TwoTablesDisplay();
/* create foreign key */
rc = FK_Create("ON DELETE SET NULL");
/* delete parent table */
printf("\n DELETE FROM deptmt WHERE deptno = 'C00'\n");
strcpy(strStmt, "DELETE FROM deptmt WHERE deptno = 'C00' ");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("parent table -- delete");
/* display tables content */
rc = FK_TwoTablesDisplay();
/* delete child table */
printf("\n DELETE FROM empl WHERE empname = 'Wheeler'\n");
strcpy(strStmt, "DELETE FROM empl WHERE empname = 'Wheeler' ");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("child table -- delete");
/* display final tables content */
rc = FK_TwoTablesDisplay();
/* rollback transaction */
printf("\n ROLLBACK\n");
EXEC SQL ROLLBACK;
EMB_SQL_CHECK("transaction -- rollback");
/* drop foreign key */
rc = FK_Drop();
return 0;
} /* Cn_FK_ON_DELETE_SET_NULL_Show */
int Cn_FK_ON_DELETE_NO_ACTION_Show(void)
{
int rc = 0;
struct sqlca sqlca;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS:\n");
printf(" ALTER TABLE\n");
printf(" COMMIT\n");
printf(" DELETE\n");
printf(" ROLLBACK\n");
printf("TO SHOW AN 'ON DELETE NO ACTION' FOREIGN KEY.\n");
/* display initial tables content */
rc = FK_TwoTablesDisplay();
/* create foreign key */
rc = FK_Create("ON DELETE NO ACTION");
/* delete parent table */
printf("\n DELETE FROM deptmt WHERE deptno = 'C00'\n");
strcpy(strStmt, "DELETE FROM deptmt WHERE deptno = 'C00' ");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EXPECTED_ERR_CHECK("parent table -- delete");
/* delete child table */
printf("\n DELETE FROM empl WHERE empname = 'Wheeler'\n");
strcpy(strStmt, "DELETE FROM empl WHERE empname = 'Wheeler' ");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("child table -- delete");
/* display final tables content */
rc = FK_TwoTablesDisplay();
/* rollback transaction */
printf("\n ROLLBACK\n");
EXEC SQL ROLLBACK;
EMB_SQL_CHECK("transaction -- rollback");
/* drop foreign key */
rc = FK_Drop();
return 0;
} /* Cn_FK_ON_DELETE_NO_ACTION_Show */