/******************************************************************************
**
** Source File Name = trigsql.sqc
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 2000
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
** PURPOSE : an example of using advanced SQL statements, to show the
** use of TRIGGERs within a database.
** New tables are created for this example.
** (currentquote, quotehistory)
**
** STRUCTURES USED :
** sqlca
** sqlda
**
** APIs USED :
**
** FUNCTIONS DECLARED :
** 'C' COMPILER LIBRARY :
** stdio.h - printf
** string.h - strncpy
**
** OTHER :
** external : [in the file UTIL.C]
** check_error : Checks for SQLCODE error, and prints out any
** related information available.
**
** EXTERNAL DEPENDANCIES :
** - Ensure existence of database for precompile purposes.
** - Precompile with the SQL precompiler (PREP in DB2)
** - Bind to a database (BIND in DB2)
** - Compile and link with the IBM Cset++ compiler (AIX and OS/2)
** or the Microsoft Visual C++ compiler (Windows)
** or the compiler supported on your platform.
**
** For more information about these samples see the README file.
**
** For more information on programming in C, see the:
** - "Programming in C and C++" section of the Application Development Guide
** For more information on Building C Applications, see the:
** - "Building C Applications" section of the Application Building Guide.
**
** For more information on the SQL language see the SQL Reference.
**
*******************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlda.h>
#include "utilemb.h"
EXEC SQL INCLUDE SQLCA;
int output_statement (char *);
int main(int argc, char *argv[]) {
EXEC SQL BEGIN DECLARE SECTION;
char userid[9];
char passwd[19];
char st[80];
char st2[80];
char symbol[3];
double currquote;
struct cs {
short length;
char name[15];
} currstatus;
char timestamp[26];
EXEC SQL END DECLARE SECTION;
double quoteupdates[6] = {68.25, 68.75, 68.5, 68.5, 68.62, 68};
short idx;
printf( "\nSample C program: TRIGSQL\n");
if (argc == 1) {
EXEC SQL CONNECT TO sample; /* :rk.5:erk. */
EMB_SQL_CHECK("CONNECT TO SAMPLE"); /* :rk.6:erk. */
}
else if (argc == 3) {
strcpy (userid, argv[1]);
strcpy (passwd, argv[2]);
EXEC SQL CONNECT TO sample USER :userid USING :passwd; /* :rk.5:erk. */
EMB_SQL_CHECK("CONNECT TO SAMPLE"); /* :rk.6:erk. */
}
else {
printf ("\nUSAGE: trigsql [userid passwd]\n\n");
return 1;
} /* endif */
EXEC SQL DROP TABLE currentquote;
EXEC SQL DROP TABLE quotehistory;
EXEC SQL DROP TRIGGER stock_status;
EXEC SQL DROP TRIGGER record_history;
EXEC SQL CREATE TABLE currentquote (symbol CHAR(3) NOT NULL,
quote DECIMAL(6,2),
status VARCHAR(8));
EMB_SQL_CHECK("CREATE TABLE CURRENTQUOTE");
EXEC SQL CREATE TABLE quotehistory (symbol CHAR(3) NOT NULL,
quote DECIMAL(6,2), timestamp TIMESTAMP);
EMB_SQL_CHECK("CREATE TABLE QUOTEHISTORY");
strcpy (st, "INSERT INTO currentquote VALUES ('IBM', 68.5, NULL)");
EXEC SQL EXECUTE IMMEDIATE :st;
EMB_SQL_CHECK("INSERT INTO CURRENTQUOTE");
EXEC SQL CREATE TRIGGER STOCK_STATUS
NO CASCADE BEFORE UPDATE OF QUOTE ON CURRENTQUOTE
REFERENCING NEW AS NEWQUOTE OLD AS OLDQUOTE
FOR EACH ROW MODE DB2SQL
SET NEWQUOTE.STATUS =
CASE
WHEN NEWQUOTE.QUOTE >=
(SELECT MAX(QUOTE) FROM QUOTEHISTORY
WHERE SYMBOL = NEWQUOTE.SYMBOL
AND YEAR(TIMESTAMP) = YEAR(CURRENT DATE) )
THEN 'High'
WHEN NEWQUOTE.QUOTE <=
(SELECT MIN(QUOTE) FROM QUOTEHISTORY
WHERE SYMBOL = NEWQUOTE.SYMBOL
AND YEAR(TIMESTAMP) = YEAR(CURRENT DATE) )
THEN 'Low'
WHEN NEWQUOTE.QUOTE > OLDQUOTE.QUOTE
THEN 'Rising'
WHEN NEWQUOTE.QUOTE < OLDQUOTE.QUOTE
THEN 'Dropping'
WHEN NEWQUOTE.QUOTE = OLDQUOTE.QUOTE
THEN 'Steady'
END;
EMB_SQL_CHECK("CREATE TRIGGER STOCK_STATUS");
EXEC SQL CREATE TRIGGER RECORD_HISTORY
AFTER UPDATE OF QUOTE ON CURRENTQUOTE
REFERENCING NEW AS NEWQUOTE
FOR EACH ROW MODE DB2SQL
INSERT INTO QUOTEHISTORY
VALUES (NEWQUOTE.SYMBOL,NEWQUOTE.QUOTE,CURRENT TIMESTAMP);
EMB_SQL_CHECK("CREATE TRIGGER RECORD_HISTORY");
strcpy (st, "UPDATE currentquote SET quote=? WHERE symbol='IBM'");
strcpy (st2, "SELECT * FROM currentquote");
EXEC SQL PREPARE QUPDATE FROM :st;
for (idx=0; idx < 6; idx++) {
currquote = quoteupdates[idx];
EXEC SQL EXECUTE QUPDATE USING :currquote;
EXEC SQL PREPARE S1 FROM :st2;
EXEC SQL DECLARE C1 CURSOR FOR S1;
EXEC SQL OPEN C1;
EXEC SQL FETCH C1 INTO :symbol, :currquote, :currstatus;
EXEC SQL CLOSE C1;
printf ("%3.3s %10.2f %.*s\n", symbol, currquote, currstatus.length,
currstatus.name);
} /* endfor */
printf ("\nContents of the 'quotehistory' table after updates\n");
strcpy (st, "SELECT * FROM quotehistory");
EXEC SQL PREPARE S2 FROM :st;
EXEC SQL DECLARE C2 CURSOR FOR S2;
EXEC SQL OPEN C2;
do {
EXEC SQL FETCH C2 INTO :symbol, :currquote, :timestamp;
if (SQLCODE != 0) break;
printf ("%3.3s %10.2f %s\n", symbol, currquote, timestamp);
} while ( 1 );
EXEC SQL CLOSE C2;
/* Drop the tables and triggers */
EXEC SQL DROP TABLE currentquote;
EMB_SQL_CHECK("DROP TABLE currentquote");
EXEC SQL DROP TABLE quotehistory;
EMB_SQL_CHECK("DROP quotehistory");
EXEC SQL DROP TRIGGER stock_status;
EMB_SQL_CHECK("DROP TRIGGER stock_status");
EXEC SQL DROP TRIGGER record_history;
EMB_SQL_CHECK("DROP TRIGGER record_history");
EXEC SQL CONNECT RESET;
EMB_SQL_CHECK("CONNECT RESET");
return 0;
}