home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
OS/2 Shareware BBS: Product
/
Product.zip
/
DBDEMO.ZIP
/
DEMODB.ZIP
/
D30ACBB1.SQC
< prev
next >
Wrap
Text File
|
1991-06-03
|
28KB
|
673 lines
/*************************************** ****************/
/* */
/* APPLICATION PROGRAM NAME: D30ACBB1.SQC of D30ACB01 */
/* */
/* PROGRAM LANGUAGE: 'C' Language */
/* */
/* PROGRAM DESCRIPTION: D30ACBB1.SQC is the second program of the */
/* D30ACB01 creation application. */
/* */
/* This program will create the 7 tables of the */
/* BANK data base: */
/* */
/* Customer */
/* Box */
/* Checking */
/* Credits */
/* Debits */
/* Loan */
/* Savings */
/* */
/* An additional table will also be created that */
/* will be identical to the Customer table. This */
/* table will be used with the import through a */
/* view (Replace option) test. It will be named: */
/* */
/* Newcust */
/* */
/* AUTHOR: D. J. Grant DATE: 04/22/87 */
/* */
/* DATABASE: Gemstone BANK database */
/* */
/* INPUT FILES: None */
/* */
/* OUTPUT FILES: */
/* */
/* D30ACBB1.ERR - this is a file, written to disk, only */
/* when some type of error condition is returned from */
/* SQL. It serves as an error log, recording the error */
/* code, its corresponding error message, and any other */
/* useful information which the program might be able */
/* to give. */
/* */
/* DEPENDENCIES: */
/* */
/* GEORGETOWN, Gemstone DIAMOND data base kernel, */
/* creation of Gemstone BANK data base, */
/* pre-compilation with Gemstone SQLPREP, */
/* binding to data base with SQLPREP or SQLBIND, */
/* compilation with IBM C/2, using compiler options: */
/* "/AL /Gs /DLINT_ARGS /DSQL_REL_10 /W3 /Od /FPa" */
/* */
/*** Prologue continued on next page *************************************/
/****************************************** *************/
/* */
/* FUNCTION CALLS: */
/* */
/* COMPILER LIBRARY */
/* stdio.h - fopen,fclose,fprintf,printf */
/* string.h - strcpy */
/* */
/* GEMSTONE LIBRARY */
/* sql.h - sqlaintp */
/* sqlenv.h - sqlestrd,sqlestpd */
/* - sqleisig (ctrl-break processing) */
/* sqlca.h - sqlca.sqlcode */
/* */
/* OTHER */
/* none */
/* */
/* ERROR CONDITIONS: */
/* */
/* NORMAL - specifically tested for and handled by the ap- */
/* application program: (exit_normal) */
/* - DOS return code = 0. (errorlvl) */
/* */
/* */
/* ABNORMAL - unexpected and resulting in program termination: */
/* (exit_error) */
/* - 1015 : start database failed, restart database */
/* - DOS return code = 2. (errorlvl) */
/* */
/* MODIFICATIONS: */
/* */
/* Date Author Description */
/* */
/* 09/15/87 D. J. Grant - Update prologue. */
/* - Put on every */
/* page. */
/* - Add code to pass return codes to */
/* DOS. */
/* - Add code to enhance exit_error. */
/* - Made references to sqlca lower */
/* case. */
/* */
/* 10/05/87 D. J. Grant - change L_DATE type in LOAN table. */
/* - change rc compare in exit_error */
/* to rc > 0. */
/* 10/26/87 D.J.Grant Added SQLEISIG function call, and */
/* enhances exit_error to also print */
/* SQLERRD[0] - [5] and SQL_ERRP. */
/* 03/08/88 D.J.Grant - took out some compiler options. */
/* - add the create Newcust table and */
/* updated the prologue accordingly. */
/* 02/03/89 W.B.Brown - upgraded for Rel 3.0 */
/******* End of prologue *************************************************/
/******************************* ************************/
/* SET UP INCLUDES */
/*****************************************************************************/
#include <stdio.h>
#include <sql.h>
#include <string.h>
#include <sqlenv.h>
EXEC SQL INCLUDE sqlca;
/*****************************************************************************/
/* DEFINITIONS AND DECLARATIONS */
/*****************************************************************************/
unsigned char sqlerrp_value[9] = " "; /* error value */
/*******************************/
/* Set up error message buffer */
/*******************************/
unsigned char msgbuf[512];
/********************************************/
/* Declarations for D30ACBB1.ERR error file */
/********************************************/
unsigned char errorfle[]="D30ACBB1.ERR";
unsigned char tcaseid[]="Test Case Id : D30ACBB1.SQC";
unsigned char descript[]="BANK: base table creation";
/***********************************/
/* Declarations for BANK data base */
/***********************************/
unsigned char database[]="bank"; /* establish database name */
unsigned char password[]=""; /* set up null password */
/***********************************************/
/* Set up variable to pass return codes to DOS */
/***********************************************/
short errorlvl = 0; /* variable used to pass return codes */
/* to DOS */
/*****************************************************************************/
int main (void);
main ()
{
/* declare local program variables */
unsigned char rte_label[16]; /* program progress marker */
short rc = 0; /* variable for return code */
/* declare output file */
FILE *ptr_output; /* pointer to output file */
/*************************************** ******************/
/* ERROR HANDLING FOR SQL STATEMENTS */
/*****************************************************************************/
EXEC SQL
WHENEVER SQLERROR GOTO stop_datab;
EXEC SQL
WHENEVER SQLWARNING CONTINUE;
EXEC SQL
WHENEVER NOT FOUND CONTINUE;
/*****************************************************************************/
strcpy(rte_label,"instal sqleisig");
sqleisig(&sqlca); /* ctrl-break processing */
if (sqlca.sqlcode != 0)
{
goto exit_error;
}
/***************************************************************************/
/*****************************************************************************/
/* START USING DATABASE */
/*****************************************************************************/
strcpy(rte_label,"start database ");
sqlestrd (database,'S',&sqlca); /* no password, and shared */
if (sqlca.sqlcode == -1015)
{
strcpy(rte_label,"restart databas");
sqlerest (database,&sqlca); /* restart database if need*/
if (sqlca.sqlcode != 0)
{
goto exit_error;
}
strcpy(rte_label,"start dbase 2nd");
sqlestrd (database,'S',&sqlca); /* second start attempt */
}
if (sqlca.sqlcode != 0)
{
goto exit_error; /* exit the program on error */
}
/*************************************** ******************/
/*****************************************************************************/
/* Create the CUSTOMER base table */
/*****************************************************************************/
strcpy(rte_label,"create customer");
EXEC SQL CREATE TABLE CUSTOMER
(CU_SSN CHAR (11) NOT NULL PRIMARY KEY,
CU_NAME CHAR (30) NOT NULL,
CU_ADDR1 CHAR (25) NOT NULL,
CU_ADDR2 CHAR (25) NOT NULL,
CU_ZIP CHAR (5) NOT NULL);
strcpy(rte_label,"cust tbl commt "); /* define table comment */
EXEC SQL COMMENT ON TABLE CUSTOMER
IS 'This is the customer table of the BANK data base';
strcpy(rte_label,"cust col commts"); /* define comments for columns */
EXEC SQL COMMENT ON COLUMN CUSTOMER.CU_SSN
IS 'customer social security number: table key';
EXEC SQL COMMENT ON COLUMN CUSTOMER.CU_NAME
IS 'customer name';
EXEC SQL COMMENT ON COLUMN CUSTOMER.CU_ADDR1
IS 'street address';
EXEC SQL COMMENT ON COLUMN CUSTOMER.CU_ADDR2
IS 'city / state';
EXEC SQL COMMENT ON COLUMN CUSTOMER.CU_ZIP
IS 'zip code - no extensions allowed';
/*****************************************************************************/
/* Create the NEWCUST view import table */
/*****************************************************************************/
strcpy(rte_label,"create newcust ");
EXEC SQL CREATE TABLE NEWCUST
(SSN CHAR (11) NOT NULL,
NAME CHAR (30) NOT NULL,
ADDR1 CHAR (25) NOT NULL,
ADDR2 CHAR (25) NOT NULL,
ZIP CHAR (5) NOT NULL);
strcpy(rte_label,"newcust tbl cmt"); /* define table comment */
EXEC SQL COMMENT ON TABLE NEWCUST
IS 'This is the newcust table of the BANK data base';
strcpy(rte_label,"newcust col cmt"); /* define comments for columns */
EXEC SQL COMMENT ON COLUMN NEWCUST.SSN
IS 'newcust social security number: table key';
EXEC SQL COMMENT ON COLUMN NEWCUST.NAME
IS 'newcust name';
EXEC SQL COMMENT ON COLUMN NEWCUST.ADDR1
IS 'street address';
EXEC SQL COMMENT ON COLUMN NEWCUST.ADDR2
IS 'city / state';
EXEC SQL COMMENT ON COLUMN NEWCUST.ZIP
IS 'zip code - no extensions allowed';
/*************************************** ******************/
/*****************************************************************************/
/* Create the BOX base table */
/*****************************************************************************/
strcpy(rte_label,"create box tbl ");
EXEC SQL CREATE TABLE BOX
(B_NUMB CHAR (3) NOT NULL,
B_SIZE CHAR (1) NOT NULL,
B_RENT DECIMAL (3,2) NOT NULL,
B_SSN CHAR (11) ,
B_PAYUP CHAR (3) );
strcpy(rte_label,"box tbl comment"); /* define box table comment */
EXEC SQL COMMENT ON TABLE BOX
IS 'This is the box table of the BANK data base';
strcpy(rte_label,"box col comment"); /* define box column comments */
EXEC SQL COMMENT ON COLUMN BOX.B_NUMB
IS 'this is the safety deposit box number: table key';
EXEC SQL COMMENT ON COLUMN BOX.B_SIZE
IS 'this is the size of the box: S, M, or L';
EXEC SQL COMMENT ON COLUMN BOX.B_RENT
IS 'box rental amount per month';
EXEC SQL COMMENT ON COLUMN BOX.B_SSN
IS 'this is the associated customer ssn if the box is rented';
EXEC SQL COMMENT ON COLUMN BOX.B_PAYUP
IS 'shows what month the box is paid up for if rented';
/*************************************** ******************/
/*****************************************************************************/
/* Create the CHECKING base table */
/*****************************************************************************/
strcpy(rte_label,"create chk tbl ");
EXEC SQL CREATE TABLE CHECKING
(C_ACCNUMB CHAR (8) NOT NULL PRIMARY KEY,
C_STARTBAL DECIMAL (9,2) NOT NULL,
C_ENDBAL DECIMAL (9,2) NOT NULL,
C_SSN1 CHAR (11) NOT NULL,
C_SSN2 CHAR (11) );
strcpy(rte_label,"chkg tbl commt "); /* define table comment */
EXEC SQL COMMENT ON TABLE CHECKING
IS 'This is the checking table of the BANK data base';
strcpy(rte_label,"chkg col commts"); /* define column comments */
EXEC SQL COMMENT ON COLUMN CHECKING.C_ACCNUMB
IS 'checking account number: table key';
EXEC SQL COMMENT ON COLUMN CHECKING.C_STARTBAL
IS 'starting balance of the checking account';
EXEC SQL COMMENT ON COLUMN CHECKING.C_ENDBAL
IS 'ending balance of the checking account';
EXEC SQL COMMENT ON COLUMN CHECKING.C_SSN1
IS 'primary customer ssn associated with this checking account';
EXEC SQL COMMENT ON COLUMN CHECKING.C_SSN2
IS 'optional: secondary customer ssn associated with this account';
/*************************************** ******************/
/*****************************************************************************/
/* Create the CREDITS base table */
/*****************************************************************************/
strcpy(rte_label,"create cred tbl");
EXEC SQL CREATE TABLE CREDITS
(CR_ACCNUMB CHAR (8) NOT NULL,
CR_TRANDATE CHAR (8) NOT NULL,
CR_DEPAMT DECIMAL (8,2) NOT NULL);
strcpy(rte_label,"cred tbl commt "); /* define table comment */
/* define table comment */
EXEC SQL COMMENT ON TABLE CREDITS
IS 'This is the credits table of the BANK data base';
strcpy(rte_label,"cred col commts");
/* define comments for columns */
EXEC SQL COMMENT ON COLUMN CREDITS.CR_ACCNUMB
IS 'associated checking account number: table key';
EXEC SQL COMMENT ON COLUMN CREDITS.CR_TRANDATE
IS 'credit transaction date';
EXEC SQL COMMENT ON COLUMN CREDITS.CR_DEPAMT
IS 'amount of deposit made to the checking account';
/*************************************** ******************/
/*****************************************************************************/
/* Create the DEBITS base table */
/*****************************************************************************/
strcpy(rte_label,"create debs tbl");
EXEC SQL CREATE TABLE DEBITS
(DB_ACCNUMB CHAR (8) NOT NULL,
DB_TRANDATE CHAR (8) NOT NULL,
DB_AMT DECIMAL (8,2) NOT NULL,
DB_CHECKNO CHAR (5) NOT NULL);
strcpy(rte_label,"debs tbl commt "); /* define table comment */
EXEC SQL COMMENT ON TABLE DEBITS
IS 'This is the debits table of the BANK data base';
strcpy(rte_label,"debs col commts"); /* define comments for columns */
EXEC SQL COMMENT ON COLUMN DEBITS.DB_ACCNUMB
IS 'associated checking account number: table key';
EXEC SQL COMMENT ON COLUMN DEBITS.DB_TRANDATE
IS 'debit transaction date';
EXEC SQL COMMENT ON COLUMN DEBITS.DB_AMT
IS 'amount of the debit made from checking account';
EXEC SQL COMMENT ON COLUMN DEBITS.DB_CHECKNO
IS 'check number associated with the debit';
/*************************************** ******************/
/*****************************************************************************/
/* Create the LOAN base table */
/*****************************************************************************/
strcpy(rte_label,"create loan tbl");
EXEC SQL CREATE TABLE LOAN
(L_NUMB CHAR (8) NOT NULL,
L_TYPE CHAR (1) NOT NULL,
L_RATE DECIMAL (4,2) NOT NULL,
L_TERM SMALLINT NOT NULL,
L_PAYMT DECIMAL (7,2) NOT NULL,
L_BAL DECIMAL (8,2) NOT NULL,
L_INTODAT DECIMAL (8,2) ,
L_SSN CHAR (11) NOT NULL,
L_DESCRIP CHAR (25) NOT NULL,
L_DATE DATE NOT NULL);
strcpy(rte_label,"loan tbl commt "); /* define table comment */
EXEC SQL COMMENT ON TABLE LOAN
IS 'This is the loan table of the BANK data base';
strcpy(rte_label,"loan col commts"); /* define column comments */
EXEC SQL COMMENT ON COLUMN LOAN.L_NUMB
IS 'loan number: table key';
EXEC SQL COMMENT ON COLUMN LOAN.L_TYPE
IS 'type of loan: M, A, P, H, or O';
EXEC SQL COMMENT ON COLUMN LOAN.L_RATE
IS 'loan interest rate';
EXEC SQL COMMENT ON COLUMN LOAN.L_TERM
IS 'term of loan in months';
EXEC SQL COMMENT ON COLUMN LOAN.L_PAYMT
IS 'amount of loan payment per month';
EXEC SQL COMMENT ON COLUMN LOAN.L_BAL
IS 'remaining balance of the loan';
EXEC SQL COMMENT ON COLUMN LOAN.L_INTODAT
IS 'interest paid to date';
EXEC SQL COMMENT ON COLUMN LOAN.L_SSN
IS 'associated customer ssn';
EXEC SQL COMMENT ON COLUMN LOAN.L_DESCRIP
IS 'description of the specific loan';
EXEC SQL COMMENT ON COLUMN LOAN.L_DATE
IS 'date the loan was initiated';
/*************************************** ******************/
/*****************************************************************************/
/* Create the SAVINGS base table */
/*****************************************************************************/
strcpy(rte_label,"create savg tbl");
EXEC SQL CREATE TABLE SAVINGS
(S_ACCNUMB CHAR (8) NOT NULL,
S_IRATE DECIMAL (4,2) NOT NULL,
S_INTTOD DECIMAL (7,2) ,
S_BAL DECIMAL (9,2) NOT NULL,
S_SSN CHAR (11) NOT NULL);
/***********************************************************************/
/* CUSTOMER SAVINGS LOANS CREDITS AND BOX are combined to form another */
/* table PERFDAT3 */
/***********************************************************************/
printf("\n creating table PERFDAT3 \n");
EXEC SQL
CREATE TABLE PERFDAT3
(CU_SSN CHAR(11) NOT NULL, CU_NAME CHAR(30) NOT NULL, CU_ADDR1 CHAR(25) NOT NULL, CU_ADDR2 CHAR(25) NOT NULL,
CU_ZIP CHAR(5) NOT NULL,L_NUMB CHAR(8) NOT NULL, L_TYPE CHAR(1) NOT NULL, L_RATE DECIMAL(5,2) NOT NULL,
L_TERM SMALLINT NOT NULL,L_PAYMT DECIMAL (7,2) NOT NULL, L_BAL DECIMAL(9,2) NOT NULL, L_INTODAT DECIMAL(9,2),
L_DESCRIP CHAR(25) NOT NULL, L_DATE DATE, S_ACCNUMB CHAR(8) NOT NULL, S_IRATE DECIMAL(5,2) NOT NULL,
S_INTTOD DECIMAL(7,2), S_BAL DECIMAL(9,2) NOT NULL, B_NUMB CHAR(3) NOT NULL, B_SIZE CHAR(1) NOT NULL,
B_RENT DECIMAL(3,2) NOT NULL, B_PAYUP CHAR(3), C_ACCNUMB CHAR(8) NOT NULL, C_STARTBAL DECIMAL(9,2) NOT NULL,
C_ENDBAL DECIMAL(9,2) NOT NULL);
printf("\n creating table PERFDAt4 \n");
EXEC SQL
CREATE TABLE PERFDAt4
(CU_SSN CHAR(11) NOT NULL, CU_NAME CHAR(30) NOT NULL, CU_ADDR1 CHAR(25) NOT NULL, CU_ADDR2 CHAR(25) NOT NULL,
CU_ZIP CHAR(5) NOT NULL,L_NUMB CHAR(8) NOT NULL, L_TYPE CHAR(1) NOT NULL, L_RATE DECIMAL(5,2) NOT NULL,
L_TERM SMALLINT NOT NULL,L_PAYMT DECIMAL (7,2) NOT NULL, L_BAL DECIMAL(9,2) NOT NULL, L_INTODAT DECIMAL(9,2),
L_DESCRIP CHAR(25) NOT NULL, L_DATE DATE, S_ACCNUMB CHAR(8) NOT NULL, S_IRATE DECIMAL(5,2) NOT NULL,
S_INTTOD DECIMAL(7,2), S_BAL DECIMAL(9,2) NOT NULL, B_NUMB CHAR(3) NOT NULL, B_SIZE CHAR(1) NOT NULL,
B_RENT DECIMAL(3,2) NOT NULL, B_PAYUP CHAR(3), C_ACCNUMB CHAR(8) NOT NULL, C_STARTBAL DECIMAL(9,2) NOT NULL,
C_ENDBAL DECIMAL(9,2) NOT NULL );
strcpy(rte_label,"savg tbl commt "); /* define table comment */
EXEC SQL COMMENT ON TABLE SAVINGS
IS 'This is the savings table of the BANK data base';
strcpy(rte_label,"savg col commts"); /* define column comments */
EXEC SQL COMMENT ON COLUMN SAVINGS.S_ACCNUMB
IS 'savings account number: table key';
EXEC SQL COMMENT ON COLUMN SAVINGS.S_IRATE
IS 'rate of interest paid';
EXEC SQL COMMENT ON COLUMN SAVINGS.S_INTTOD
IS 'interest earned to date';
EXEC SQL COMMENT ON COLUMN SAVINGS.S_BAL
IS 'savings account balance';
EXEC SQL COMMENT ON COLUMN SAVINGS.S_SSN
IS 'associated customer.ssn';
/*****************************************************************************/
/*************************************** ******************/
/*****************************************************************************/
/* STOP USING DATABASE AND EXIT PROGRAM */
/*****************************************************************************/
stop_datab:
strcpy(rte_label,"stop using dbas");
if (sqlca.sqlcode != 0)
{
goto exit_error; /* exit the program due to error */
}
sqlestpd (&sqlca); /* stop using the database */
goto exit_normal; /* terminate normally */
/*****************************************************************************/
exit_error:
errorlvl = 2; /* set return code - 2 (error)*/
rc = sqlaintp (msgbuf,512,0,&sqlca);
if (rc > 0)
{
/* Set up error file processing */
if ((ptr_output = fopen(errorfle,"w")) == NULL) /* open the error file*/
{
printf ("\nUNABLE TO OPEN D30ACBB1.ERR"); /* display on screen */
printf ("\nSQLCODE IS %ld",sqlca.sqlcode);
goto exit_normal;
}
/* print error information */
fprintf (ptr_output,"%s\n%s\n",tcaseid,descript);
fprintf (ptr_output,"%s\n",rte_label);
fprintf (ptr_output,"SQLCODE is %ld.\n",sqlca.sqlcode);
fprintf (ptr_output,msgbuf);
/* SQL miscellaneous error info*/
fprintf(ptr_output,"\nSQLERRD[0] is %ld",sqlca.sqlerrd[0]);
fprintf(ptr_output,"\nSQLERRD[1] is %ld",sqlca.sqlerrd[1]);
fprintf(ptr_output,"\nSQLERRD[2] is %ld",sqlca.sqlerrd[2]);
fprintf(ptr_output,"\nSQLERRD[3] is %ld",sqlca.sqlerrd[3]);
fprintf(ptr_output,"\nSQLERRD[4] is %ld",sqlca.sqlerrd[4]);
fprintf(ptr_output,"\nSQLERRD[5] is %ld",sqlca.sqlerrd[5]);
memcpy (sqlerrp_value, sqlca.sqlerrp,8);
fprintf(ptr_output,"\nSQLERRP is %s",sqlerrp_value);
fclose(ptr_output); /* close the output file */
sqlestpd (&sqlca); /* stop using the database */
}
/*****************************************************************************/
exit_normal:
return (errorlvl); /* DOS error return code */
}