/****************************************************************************
** (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: dbinline.sqC
**
** SAMPLE: How to use inline SQL Procedure Language
**
** This sample demonstrates how to use Inline SQL Procedure Language.
** The program uses an existing database to create various tables,
** functions and triggers to show three sample usages. The first
** example is a scalar function. The second example is a table-level
** function and the third example demonstrates a pair of triggers.
**
** SQL STATEMENTS USED:
** INCLUDE
** CREATE TABLE
** CREATE FUNCTION (SQL Scalar, Table or Row)
** CREATE TRIGGER
** INSERT
** DECLARE CURSOR
** OPEN
** FETCH
** CLOSE
** BEGIN ATOMIC
** LEAVE
** IF statement
** WHILE statement
** FOR statement
** RETURN
** SELECT
** UPDATE
** DROP FUNCTION
** DROP TABLE
** DROP TRIGGER
**
** STRUCTURES USED:
** sqlca
**
**
*****************************************************************************
**
** 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 <string.h>
#include <sqlenv.h>
#include <sqlcodes.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 INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
char strStmt[1000];
short input;
short output;
short key;
short value;
char status[8 + 1];
char part_no[5 + 1];
sqlint32 amount;
sqlint32 reorder_no;
sqlint32 stock_change;
char action[3 + 1];
char timestamp [25 + 1];
short transaction_no;
EXEC SQL END DECLARE SECTION;
class DbInl
{
public:
int ScalarFunction();
int TableFunction();
int Triggers();
};
// ScalarFunction
// This function shows BEGIN ATOMIC, DECLARE variable, IF, WHILE <label>,
// SET and LEAVE. It consists of a function that calculates the sum of even
// numbers from zero up to and including an input integer, which may be
// positive or negative.
int DbInl::ScalarFunction()
{
cout << "\n----------------------------------------------------" <<endl;
cout << "\nSCALAR FUNCTION EXAMPLE" << endl;
cout << "------------------------------------------------------" << endl;
cout << " Function which calculates the sum of even numbers up to and ";
cout << "including \n an input integer, which may be positive or ";
cout << "negative\n" << endl;
cout << " CREATE FUNCTION Even_sum(input INT)" << endl;
cout << " RETURNS int" << endl;
cout << " BEGIN ATOMIC" << endl;
cout << " DECLARE to_add INT DEFAULT 0;" << endl;
cout << " DECLARE result INT DEFAULT 0;" << endl;
cout << " DECLARE direction INT DEFAULT 0;" << endl;
cout << " IF (input < 0) THEN" << endl;
cout << " SET direction = -1;" << endl;
cout << " END IF;" << endl;
cout << " summing_loop:" << endl;
cout << " WHILE (1 = 1) DO" << endl;
cout << " IF (direction = 0) THEN" << endl;
cout << " SET to_add = to_add + 2;" << endl;
cout << " IF (to_add > input) THEN" << endl;
cout << " LEAVE summing_loop;" << endl;
cout << " END IF;" << endl;
cout << " ELSE" << endl;
cout << " SET to_add = to_add -2;" << endl;
cout << " IF (to_add < input) THEN" << endl;
cout << " LEAVE summing_loop;" << endl;
cout << " END IF;" << endl;
cout << " END IF;" << endl;
cout << " SET result = result + to_add;" << endl;
cout << " END WHILE summing_loop;" << endl;
cout << " RETURN result;" << endl;
cout << " END" << endl;
strcpy(strStmt, "CREATE FUNCTION Even_sum(input INT) "
"RETURNS int "
"BEGIN ATOMIC "
"DECLARE to_add INT DEFAULT 0; "
"DECLARE result INT DEFAULT 0; "
"DECLARE direction INT DEFAULT 0; "
"IF (input < 0) THEN "
"SET direction = -1; "
"END IF; "
"summing_loop: "
"WHILE (1 = 1) DO "
"IF (direction = 0) THEN "
"SET to_add = to_add + 2; "
"IF (to_add > input) THEN "
"LEAVE summing_loop; "
"END IF; "
"ELSE "
"SET to_add = to_add -2; "
"IF (to_add < input) THEN "
"LEAVE summing_loop; "
"END IF; "
"END IF; "
"SET result = result + to_add; "
"END WHILE summing_loop; "
"RETURN result; "
"END");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("CREATE SCALAR FUNCTION");
cout << "\n Table to hold results generated by the function Even_sum";
cout << "\n\n CREATE TABLE Even_sum_result(input INT, output INT)";
// table to hold the results demonstrating the function Even_sum
EXEC SQL CREATE TABLE Even_sum_result(input INT, output INT);
EMB_SQL_CHECK("CREATE TABLE EVEN_SUM_RESULT");
cout << "\n\n INSERT INTO even_sum_result" << endl;
cout << " VALUES (10, Even_sum(10))" << endl;
cout << " (-5, Even_sum(-5))" << endl;
cout << " (-10, Even_sum(-10))" << endl;
strcpy(strStmt, "INSERT INTO even_sum_result VALUES "
"(10, Even_sum(10)), "
"(-5, Even_sum(-5)), "
"(-10, Even_sum(-10))");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("INSERT INTO even_sum_result");
cout << "\n SELECT * FROM even_sum_result ORDER BY input";
cout << "\n INPUT OUTPUT" << endl;
cout << " ----- ------" << endl;
strcpy(strStmt, "SELECT * FROM even_sum_result ORDER BY input");
EXEC SQL PREPARE S1 FROM :strStmt;
EMB_SQL_CHECK("PREPARE S1");
EXEC SQL DECLARE C1 CURSOR FOR S1;
EXEC SQL OPEN C1;
EMB_SQL_CHECK("OPEN CURSOR");
EXEC SQL FETCH C1 INTO :input, :output;
EMB_SQL_CHECK("FETCH CURSOR");
while (sqlca.sqlcode != SQL_RC_W100)
{
cout << setw(9) << input << " " << setw(6) << output << endl;
EXEC SQL FETCH C1 INTO :input, :output;
EMB_SQL_CHECK("FETCH CURSOR");
}
EXEC SQL CLOSE C1;
EMB_SQL_CHECK("CLOSE CURSOR");
cout << "\n DROP TABLE even_sum_result" << endl;
EXEC SQL DROP TABLE even_sum_result;
EMB_SQL_CHECK("DROP TABLE EVEN_SUM_RESULT");
cout << "\n DROP FUNCTION Even_sum" << endl;
EXEC SQL DROP FUNCTION Even_sum;
EMB_SQL_CHECK("DROP FUNCTION EVEN_SUM");
return 0;
} // ScalarFunction
// TableFunction:
// Demonstrates a table function. No new statements introduced.
// This function cleans a table by removing all values outside
// the range between 0 and 1000, and aligns the remaining values
// to the closest multiple of 10.
int DbInl::TableFunction()
{
cout << "\n-----------------------------------------------------";
cout << "\nTABLE FUNCTION EXAMPLE" << endl;
cout << "-------------------------------------------------------" << endl;
cout << "Function which cleans a table by removing all values outside";
cout << "\nthe range between 0 and 1000, and aligns the remaining values";
cout << "\nto the closest multiple of 10\n" << endl;
cout << " CREATE FUNCTION Discretize(raw INTEGER) RETURNS INTEGER\n";
cout << " RETURN CASE" << endl;
cout << " WHEN raw < 0 THEN CAST(NULL AS INTEGER)" << endl;
cout << " WHEN raw > 1000 THEN NULL" << endl;
cout << " ELSE (((raw + 5) / 10) * 10) END" << endl;
strcpy(strStmt, "CREATE FUNCTION Discretize(raw INTEGER) RETURNS INTEGER "
"RETURN CASE "
"WHEN raw < 0 THEN CAST(NULL AS INTEGER) "
"WHEN raw > 1000 THEN NULL "
"ELSE (((raw + 5) / 10) * 10) END");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("CREATE TABLE FUNCTION");
cout << "\n Table to be cleaned by the function Discretize";
cout << "\n\n CREATE TABLE source(key INT NOT NULL PRIMARY KEY,";
cout << " value INT)" << endl;
// table to be cleaned by the function Discretize
EXEC SQL CREATE TABLE source(key INT NOT NULL PRIMARY KEY, value INT);
EMB_SQL_CHECK("CREATE TABLE SOURCE");
cout << "\n INSERT INTO source" << endl;
cout << " VALUES (1, -5)," << endl;
cout << " (2, 8)," << endl;
cout << " (3, 1200)," << endl;
cout << " (4, 23)," << endl;
cout << " (5, 10)," << endl;
cout << " (6, 876)" << endl;
strcpy(strStmt, "INSERT INTO source VALUES "
"(1, -5), "
"(2, 8), "
"(3, 1200), "
"(4, 23), "
"(5, 10), "
"(6, 876)");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("INSERT INTO SOURCE");
cout << "\n SELECT * FROM source ORDER BY key";
cout << "\n KEY VALUE" << endl;
cout << " --- -----" << endl;
strcpy(strStmt, "SELECT * FROM source ORDER BY key");
EXEC SQL PREPARE S2 FROM :strStmt;
EMB_SQL_CHECK("PREPARE S2");
EXEC SQL DECLARE C2 CURSOR FOR S2;
EXEC SQL OPEN C2;
EMB_SQL_CHECK("OPEN CURSOR");
EXEC SQL FETCH C2 INTO :key, :value;
EMB_SQL_CHECK("FETCH CURSOR");
while (sqlca.sqlcode != SQL_RC_W100)
{
cout << setw(7) << key << " " << setw(5) << value << endl;
EXEC SQL FETCH C2 INTO :key, :value;
EMB_SQL_CHECK("FETCH CURSOR");
}
EXEC SQL CLOSE C2;
EMB_SQL_CHECK("CLOSE CURSOR");
cout << "\n Table to hold the values cleaned by the function Discretize";
cout << "\n\n CREATE TABLE table_result(key INT NOT NULL PRIMARY KEY,";
cout << " value INT)" << endl;
// table to hold the values cleaned by the function Discretize
EXEC SQL CREATE TABLE table_result(key INT NOT NULL PRIMARY KEY,
value INT);
EMB_SQL_CHECK("CREATE TABLE TABLE_RESULT");
cout << "\n Table to hold the values outside of range";
cout << "\n\n CREATE TABLE outside_range(key INT NOT NULL PRIMARY KEY,\n";
cout << " value INT)" << endl;
// table to hold the values outside of range
EXEC SQL CREATE TABLE outside_range(key INT NOT NULL PRIMARY KEY,
value INT);
EMB_SQL_CHECK("CREATE TABLE OUTSIDE_RANGE");
cout << "\n BEGIN ATOMIC" << endl;
cout << " FOR row AS" << endl;
cout << " SELECT key, value, Discretize(value) AS d FROM source\n";
cout << " DO" << endl;
cout << " IF row.d is NULL THEN" << endl;
cout << " INSERT INTO outside_range VALUES(row.key, row.value);\n";
cout << " ELSE" << endl;
cout << " INSERT INTO result VALUES(row.key, row.d);" << endl;
cout << " END IF;" << endl;
cout << " END FOR;" << endl;
cout << " END" << endl;
strcpy(strStmt, "BEGIN ATOMIC "
"FOR row AS "
"SELECT key, value, Discretize(value) AS d FROM source "
"DO "
"IF row.d is NULL THEN "
"INSERT INTO outside_range VALUES(row.key, row.value); "
"ELSE "
"INSERT INTO table_result VALUES(row.key, row.d); "
"END IF; "
"END FOR; "
"END");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("EXECUTE SCRIPT");
cout << "\n SELECT * FROM outside_range ORDER BY key";
cout << "\n KEY VALUE" << endl;
cout << " --- -----" << endl;
strcpy(strStmt, "SELECT * FROM outside_range ORDER BY key");
EXEC SQL PREPARE S3 FROM :strStmt;
EMB_SQL_CHECK("PREPARE S3");
EXEC SQL DECLARE C3 CURSOR FOR S3;
EXEC SQL OPEN C3;
EMB_SQL_CHECK("OPEN CURSOR");
EXEC SQL FETCH C3 INTO :key, :value;
EMB_SQL_CHECK("FETCH CURSOR");
while (sqlca.sqlcode != SQL_RC_W100)
{
cout << setw(7) << key << " " << setw(5) << value << endl;
EXEC SQL FETCH C3 INTO :key, :value;
EMB_SQL_CHECK("FETCH CURSOR");
}
EXEC SQL CLOSE C3;
EMB_SQL_CHECK("CLOSE CURSOR");
cout << "\n SELECT * FROM table_result ORDER BY key";
cout << "\n KEY VALUE" << endl;
cout << " --- -----" << endl;
strcpy(strStmt, "SELECT * FROM table_result ORDER BY key");
EXEC SQL PREPARE S4 FROM :strStmt;
EMB_SQL_CHECK("PREPARE S4");
EXEC SQL DECLARE C4 CURSOR FOR S4;
EXEC SQL OPEN C4;
EMB_SQL_CHECK("OPEN CURSOR");
EXEC SQL FETCH C4 INTO :key, :value;
EMB_SQL_CHECK("FETCH CURSOR");
while (sqlca.sqlcode != SQL_RC_W100)
{
cout << setw(7) << key << " " << setw(5) << value << endl;
EXEC SQL FETCH C4 INTO :key, :value;
EMB_SQL_CHECK("FETCH CURSOR");
}
EXEC SQL CLOSE C4;
EMB_SQL_CHECK("CLOSE CURSOR");
cout << "\n DROP TABLE source" << endl;
EXEC SQL DROP TABLE source;
EMB_SQL_CHECK("DROP TABLE SOURCE");
cout << "\n DROP TABLE table_result" << endl;
EXEC SQL DROP TABLE table_result;
EMB_SQL_CHECK("DROP TABLE TABLE_RESULT");
cout << "\n DROP TABLE outside_range" << endl;
EXEC SQL DROP TABLE outside_range;
EMB_SQL_CHECK("DROP TABLE OUTSIDE_RANGE");
cout << "\n DROP FUNCTION Discretize" << endl;
EXEC SQL DROP FUNCTION Discretize;
EMB_SQL_CHECK("DROP FUNCTION DISCRETIZE");
return 0;
} // TableFunction
// Triggers:
// Demonstrates Inline SQL PL in triggers. This shows DECLARE <condition>,
// SIGNAL and GET DIAGNOSTICS.
int DbInl::Triggers()
{
cout << "\n-----------------------------------------------------";
cout << "\nTRIGGER EXAMPLE" << endl;
cout << "-------------------------------------------------------" << endl;
cout << " Table to record amount of inventory on hand using part number";
cout << ", amount,\n an amount in stock level upon which the given part";
cout << " will be reordered\n and a status variable stating whether the ";
cout << "amount of a given part in\n stock is sufficient or a reorder ";
cout << "is required\n" << endl;
cout << " CREATE TABLE in_stock(part_no VARCHAR(5) NOT NULL, amount INT,";
cout << "\n reorder_no INT, status VARCHAR(8))\n";
// table records amount of inventory on hand: part number, amount, an
// amount in stock number upon which the given part will be reordered and
// a status variable stating whether the amount of a given part in stock
// is sufficient or a reorder is required
EXEC SQL CREATE TABLE in_stock(part_no VARCHAR(5) NOT NULL, amount INT,
reorder_no INT, status VARCHAR(8));
EMB_SQL_CHECK("CREATE TABLE IN_STOCK");
cout << "\n INSERT INTO in_stock" << endl;
cout << " VALUES('10001', 50, 30, 'fine')" << endl;
cout << " ('10002', 30, 10, 'fine')" << endl;
strcpy(strStmt, "INSERT INTO in_stock VALUES "
"('10001', 50, 30, 'fine'), "
"('10002', 30, 10, 'fine')");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("INSERT INTO IN_STOCK");
cout << "\n SELECT * FROM in_stock ORDER BY part_no";
cout << "\n PART_NO AMOUNT REORDER_NO STATUS" << endl;
cout << " ------- ------ ---------- -------" << endl;
strcpy(strStmt, "SELECT * FROM in_stock ORDER BY part_no");
EXEC SQL PREPARE S66 FROM :strStmt;
EMB_SQL_CHECK("PREPARE S66");
EXEC SQL DECLARE C66 CURSOR FOR S66;
EXEC SQL OPEN C66;
EMB_SQL_CHECK("OPEN CURSOR");
EXEC SQL FETCH C66 INTO :part_no, :amount, :reorder_no, :status;
EMB_SQL_CHECK("FETCH CURSOR");
while (sqlca.sqlcode != SQL_RC_W100)
{
cout << setw(11) << part_no
<< " " << setw(6) << amount
<< " " << setw(10) << reorder_no
<< " " << setw(7) << status
<< endl;
EXEC SQL FETCH C66 INTO :part_no, :amount, :reorder_no, :status;
EMB_SQL_CHECK("FETCH CURSOR");
}
EXEC SQL CLOSE C66;
EMB_SQL_CHECK("CLOSE CURSOR");
cout << "\n This trigger updates the STATUS variable of the in_stock ";
cout << "table if\n the new amount is less than or equal to the reorder ";
cout << "number" << endl;
// This trigger updates the STATUS variable of the in_stock
// table if the new amount is less than or equal to the
// reorder number.
cout << "\n CREATE TRIGGER stock_status" << endl;
cout << " NO CASCADE BEFORE UPDATE OF amount ON IN_STOCK" << endl;
cout << " REFERENCING NEW AS n" << endl;
cout << " FOR EACH ROW MODE DB2SQL" << endl;
cout << " BEGIN ATOMIC" << endl;
cout << " IF (n.amount > n.reorder_no) THEN" << endl;
cout << " SET n.status = 'fine';" << endl;
cout << " ELSE" << endl;
cout << " SET n.status = 'reorder';" << endl;
cout << " END IF;" << endl;
cout << " END" << endl;
strcpy(strStmt, "CREATE TRIGGER stock_status "
"NO CASCADE BEFORE UPDATE OF amount ON IN_STOCK "
"REFERENCING NEW AS n "
"FOR EACH ROW MODE DB2SQL "
"BEGIN ATOMIC "
"IF (n.amount > n.reorder_no) THEN "
"SET n.status = 'fine'; "
"ELSE "
"SET n.status = 'reorder'; "
"END IF; "
"END");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("CREATE TRIGGER STOCK_STATUS");
cout << "\n Table to record stock transactions using part number, amount";
cout << " by which\n the given part changed, an action code (ADD for ";
cout << "adding to stock,\n SUB for removing from stock) and the time ";
cout << "the transaction took place\n" << endl;
cout << " CREATE TABLE stock_history(part_no VARCHAR(5) NOT NULL,\n";
cout << " stock_change INT," << endl;
cout << " action VARCHAR(3)," << endl;
cout << " timestamp TIMESTAMP)" << endl;
// table records stock transactions: part number, amount by which the
// given part changed, an action code (ADD for adding to stock, SUB for
// removing from stock) and the time the transaction took place
EXEC SQL CREATE TABLE stock_history(part_no VARCHAR(5) NOT NULL,
stock_change INT, action VARCHAR(3),
timestamp TIMESTAMP);
EMB_SQL_CHECK("CREATE TABLE STOCK_HISTORY");
cout << "\n This trigger is activated when AMOUNT changes in the table ";
cout << "in_stock\n to record details of the transaction into the ";
cout << "table stock_history" << endl;
// This trigger is activated when AMOUNT changes in the table
// in_stock to record the transaction into the table
// stock_history.
cout << "\n CREATE TRIGGER history" << endl;
cout << " AFTER UPDATE OF amount ON IN_STOCK" << endl;
cout << " REFERENCING NEW as n OLD AS o" << endl;
cout << " FOR EACH ROW MODE DB2SQL" << endl;
cout << " BEGIN ATOMIC" << endl;
cout << " DECLARE change INT;" << endl;
cout << " DECLARE type VARCHAR(3);" << endl;
cout << " DECLARE no_change CONDITION FOR SQLSTATE '80001';\n";
cout << " IF (n.amount > o.amount) THEN" << endl;
cout << " SET change = n.amount - o.amount;" << endl;
cout << " SET type = 'ADD';" << endl;
cout << " ELSEIF (n.amount < o.amount) THEN" << endl;
cout << " SET change = o.amount - n.amount;" << endl;
cout << " SET type = 'SUB';" << endl;
cout << " ELSE" << endl;
cout << " SIGNAL no_change SET MESSAGE_TEXT = ";
cout << "'Identical amount';" << endl;
cout << " END IF;" << endl;
cout << " INSERT INTO stock_history" << endl;
cout << " VALUES(n.part_no," << endl;
cout << " change," << endl;
cout << " type," << endl;
cout << " TIMESTAMP(generate_unique()));" << endl;
cout << " END" << endl;
strcpy(strStmt, "CREATE TRIGGER history "
"AFTER UPDATE OF amount ON IN_STOCK "
"REFERENCING NEW as n OLD AS o "
"FOR EACH ROW MODE DB2SQL "
"BEGIN ATOMIC "
"DECLARE change INT; "
"DECLARE type VARCHAR(3); "
"DECLARE no_change CONDITION FOR SQLSTATE '80001'; "
"IF (n.amount > o.amount) THEN "
"SET change = n.amount - o.amount; "
"SET type = 'ADD'; "
"ELSEIF (n.amount < o.amount) THEN "
"SET change = o.amount - n.amount; "
"SET type = 'SUB'; "
"ELSE "
"SIGNAL no_change SET MESSAGE_TEXT = 'Identical amount'; "
"END IF; "
"INSERT INTO stock_history "
"VALUES(n.part_no, "
"change, "
"type, "
"TIMESTAMP(generate_unique())); "
"END");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("CREATE TRIGGER HISTORY");
cout << "\n UPDATE in_stock SET amount = 25 WHERE part_no = '10001'\n";
strcpy(strStmt, "UPDATE in_stock SET amount = 25 "
"WHERE part_no = '10001'");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("FIRST UPDATE OF IN_STOCK");
cout << "\n SELECT status FROM in_stock WHERE part_no = '10001'";
cout << "\n STATUS" << endl;
cout << " -------" << endl;
strcpy(strStmt, "SELECT status FROM in_stock WHERE part_no = '10001'");
EXEC SQL PREPARE S5 from :strStmt;
EMB_SQL_CHECK("PREPARE S5");
EXEC SQL DECLARE C5 CURSOR FOR S5;
EXEC SQL OPEN C5;
EMB_SQL_CHECK("OPEN CURSOR");
EXEC SQL FETCH C5 INTO :status;
EMB_SQL_CHECK("FETCH CURSOR");
while (sqlca.sqlcode != SQL_RC_W100)
{
cout << setw(11) << status << endl;
EXEC SQL FETCH C5 INTO :status;
EMB_SQL_CHECK("FETCH CURSOR");
}
EXEC SQL CLOSE C5;
EMB_SQL_CHECK("CLOSE CURSOR");
cout << "\n Updating in_stock with an identical amount is not possible ";
cout << "in this\n example to demonstrate DECLARE <condition> and ";
cout << "SIGNAL" << endl;
cout << "\n UPDATE in_stock SET amount = 20 WHERE part_no = '10001'";
strcpy(strStmt, "UPDATE in_stock SET amount = 20 "
"WHERE part_no = '10001'");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("SECOND UPDATE OF IN_STOCK");
cout << "\n UPDATE in_stock SET amount = 20 WHERE part_no = '10001'\n";
strcpy(strStmt, "UPDATE in_stock SET amount = 20 WHERE part_no = '10001'");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EXPECTED_ERR_CHECK("UPDATE OF IN_STOCK - FAILS BECAUSE AMOUNT IDENTICAL");
cout << "\n Populate stock_history with more entries" << endl;
cout << "\n UPDATE in_stock SET amount = 40 WHERE part_no = '10002'";
strcpy(strStmt, "UPDATE in_stock SET amount = 40 "
"WHERE part_no = '10002'");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("FOURTH UPDATE OF IN_STOCK");
cout << "\n UPDATE in_stock SET amount = 10 WHERE part_no = '10002'";
strcpy(strStmt, "UPDATE in_stock SET amount = 10 "
"WHERE part_no = '10002'");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("FIFTH UPDATE OF IN_STOCK");
cout << "\n UPDATE in_stock SET amount = 25 WHERE part_no = '10002'";
strcpy(strStmt, "UPDATE in_stock SET amount = 25 "
"WHERE part_no = '10002'");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("SIXTH UPDATE OF IN_STOCK");
cout << "\n\n SELECT * FROM in_stock ORDER BY part_no";
cout << "\n PART_NO AMOUNT REORDER_NO STATUS" << endl;
cout << " ------- ------ ---------- -------" << endl;
strcpy(strStmt, "SELECT * FROM in_stock ORDER BY part_no");
EXEC SQL PREPARE S6 FROM :strStmt;
EMB_SQL_CHECK("PREPARE S6");
EXEC SQL DECLARE C6 CURSOR FOR S6;
EXEC SQL OPEN C6;
EMB_SQL_CHECK("OPEN CURSOR");
EXEC SQL FETCH C6 INTO :part_no, :amount, :reorder_no, :status;
EMB_SQL_CHECK("FETCH CURSOR");
while (sqlca.sqlcode != SQL_RC_W100)
{
cout << setw(11) << part_no
<< " " << setw(6) << amount
<< " " << setw(10) << reorder_no
<< " " << setw(7) << status
<< endl;
EXEC SQL FETCH C6 INTO :part_no, :amount, :reorder_no, :status;
EMB_SQL_CHECK("FETCH CURSOR");
}
EXEC SQL CLOSE C6;
EMB_SQL_CHECK("CLOSE CURSOR");
cout << "\n SELECT * FROM stock_history ORDER BY timestamp";
cout << "\n PART_NO STOCK_CHANGE ACTION TIMESTAMP" << endl;
cout << " ------- ------------ ------ ----------------------" << endl;
strcpy(strStmt, "SELECT * FROM stock_history ORDER BY timestamp");
EXEC SQL PREPARE S7 FROM :strStmt;
EMB_SQL_CHECK("PREPARE S7");
EXEC SQL DECLARE C7 CURSOR FOR S7;
EXEC SQL OPEN C7;
EMB_SQL_CHECK("OPEN CURSOR");
EXEC SQL FETCH C7 INTO :part_no, :stock_change, :action, :timestamp;
EMB_SQL_CHECK("FETCH CURSOR");
while (sqlca.sqlcode != SQL_RC_W100)
{
cout << setw(11) << part_no
<< " " << setw(12) << stock_change
<< " " << setw(6) << action
<< " " << setw(26) << timestamp
<< endl;
EXEC SQL FETCH C7 INTO :part_no, :stock_change, :action, :timestamp;
EMB_SQL_CHECK("FETCH CURSOR");
}
EXEC SQL CLOSE C7;
EMB_SQL_CHECK("CLOSE CURSOR");
cout << "\n Function Transaction_number determines the number of ";
cout << "transactions\n associated with a particular part number ";
cout << "in stock_history" << endl;
// Function transaction_number determines number of transactions
// associated with particular part number in stock_history.
cout << "\n CREATE FUNCTION Transaction_number(part VARCHAR(5))" << endl;
cout << " RETURNS INT" << endl;
cout << " BEGIN ATOMIC" << endl;
cout << " DECLARE a INT DEFAULT 0;" << endl;
cout << " SELECT * FROM stock_history WHERE part_no = part;" << endl;
cout << " GET DIAGNOSTICS a = ROW_COUNT;" << endl;
cout << " RETURN a;" << endl;
cout << " END" << endl;
strcpy(strStmt, "CREATE FUNCTION Transaction_number(part VARCHAR(5)) "
"RETURNS INT "
"BEGIN ATOMIC "
"DECLARE a INT DEFAULT 0; "
"SELECT * FROM stock_history WHERE part_no = part; "
"GET DIAGNOSTICS a = ROW_COUNT; "
"RETURN a; "
"END");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("CREATE FUNCTION TRANSACTION_NUMBER");
cout << "\n Table to hold each part number which occurs in the ";
cout << "stock_history\n table and show the number of times it ";
cout << "appears\n" << endl;
cout << " CREATE TABLE transaction_record(part_no VARCHAR(5) NOT NULL,\n";
cout << " transaction_no INT)" << endl;
// table to hold each part number which occurs in the stock_history
// table and the number of times it appears
EXEC SQL CREATE TABLE transaction_record(part_no VARCHAR(5) NOT NULL,
transaction_no INT);
EMB_SQL_CHECK("CREATE TABLE TRANSACTION_RECORD");
// routine to fill transaction_record
cout << "\n BEGIN ATOMIC" << endl;
cout << " FOR row AS" << endl;
cout << " SELECT DISTINCT part_no, Transaction_number(part_no) AS p";
cout << "\n FROM stock_history GROUP BY part_no" << endl;
cout << " DO" << endl;
cout << " INSERT INTO transaction_record VALUES ";
cout << "(row.part_no, row.p);" << endl;
cout << " END FOR;" << endl;
cout << " END" << endl;
strcpy(strStmt, "BEGIN ATOMIC "
"FOR row AS "
"SELECT DISTINCT part_no, Transaction_number(part_no) "
"AS p FROM stock_history GROUP BY part_no "
"DO "
"INSERT INTO transaction_record "
"VALUES(row.part_no, row.p); "
"END FOR; "
"END");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("EXECUTE SCRIPT");
cout << "\n SELECT * FROM transaction_record ORDER BY part_no";
cout << "\n PART_NO TRANSACTION_NO" << endl;
cout << " ------- --------------" << endl;
strcpy(strStmt, "SELECT * FROM transaction_record ORDER BY part_no");
EXEC SQL PREPARE S8 FROM :strStmt;
EMB_SQL_CHECK("PREPARE S8");
EXEC SQL DECLARE C8 CURSOR FOR S8;
EXEC SQL OPEN C8;
EMB_SQL_CHECK("OPEN CURSOR");
EXEC SQL FETCH C8 INTO :part_no, :transaction_no;
EMB_SQL_CHECK("FETCH CURSOR");
while (sqlca.sqlcode != SQL_RC_W100)
{
cout << setw(11) << part_no
<< " " << setw(14) << transaction_no
<< endl;
EXEC SQL FETCH C8 INTO :part_no, :transaction_no;
EMB_SQL_CHECK("FETCH CURSOR");
}
EXEC SQL CLOSE C8;
EMB_SQL_CHECK("CLOSE CURSOR");
cout << "\n DROP FUNCTION Transaction_number" << endl;
EXEC SQL DROP FUNCTION Transaction_number;
EMB_SQL_CHECK("DROP FUNCTION TRANSACTION_NUMBER");
cout << "\n DROP TRIGGER stock_status" << endl;
EXEC SQL DROP TRIGGER stock_status;
EMB_SQL_CHECK("DROP TRIGGER STOCK_STATUS");
cout << "\n DROP TRIGGER history" << endl;
EXEC SQL DROP TRIGGER history;
EMB_SQL_CHECK("DROP TRIGGER HISTORY");
cout << "\n DROP TABLE in_stock" << endl;
EXEC SQL DROP TABLE in_stock;
EMB_SQL_CHECK("DROP TABLE IN_STOCK");
cout << "\n DROP TABLE stock_history" << endl;
EXEC SQL DROP TABLE stock_history;
EMB_SQL_CHECK("DROP TABLE STOCK_HISTORY");
cout << "\n DROP TABLE transaction_record" << endl;
EXEC SQL DROP TABLE transaction_record;
EMB_SQL_CHECK("DROP TABLE TRANSACTION_HISTORY");
return 0;
} // Triggers
int main(int argc, char *argv[])
{
int rc = 0;
CmdLineArgs check;
DbEmb db;
DbInl dbinline;
// check the command line arguments
rc = check.CmdLineArgsCheck1(argc, argv, db);
if(rc != 0)
{
return rc;
}
cout << "\nTHIS SAMPLE SHOWS HOW TO USE INLINE SQL PROCEDURE LANGUAGE.\n";
// connect to database
rc = db.Connect();
if(rc != 0)
{
return rc;
}
dbinline.ScalarFunction();
dbinline.TableFunction();
dbinline.Triggers();
// disconnect from the database
rc = db.Disconnect();
if(rc != 0)
{
return rc;
}
return 0;
} // main