home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
OS/2 Shareware BBS: Product
/
Product.zip
/
DBDEMO.ZIP
/
DEMODB.ZIP
/
D30ACBB1.C
< prev
next >
Wrap
Text File
|
1991-07-03
|
42KB
|
1,730 lines
static unsigned char sqla_program_id[40] =
{111,65,65,66,65,67,66,67,84,71,82,65,89,32,32,32,68,51,48,65,
67,66,66,49,80,65,99,71,74,68,72,72,32,32,32,32,32,32,32,32};
#ifdef LINT_ARGS
short far pascal sqlaaloc(unsigned short,
unsigned short,
unsigned short,
void far *);
short far pascal sqlacall(unsigned short,
unsigned short,
unsigned short,
unsigned short,
void far *);
short far pascal sqladloc(unsigned short,
void far *);
short far pascal sqlasets(unsigned short,
unsigned char far *,
void far *);
short far pascal sqlasetv(unsigned short,
unsigned short,
unsigned short,
unsigned short,
void far *,
void far *,
void far *);
short far pascal sqlastop(void far *);
short far pascal sqlastrt(void far *,
void far *,
struct sqlca far *);
short far pascal sqlausda(unsigned short,
struct sqlda far *,
void far *);
#else
short far pascal sqlaaloc( );
short far pascal sqlacall( );
short far pascal sqladloc( );
short far pascal sqlasets( );
short far pascal sqlasetv( );
short far pascal sqlastop( );
short far pascal sqlastrt( );
short far pascal sqlausda( );
#endif
/*************************************** ****************/
/* */
/* 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;
*/
/* SQL Communication Area - SQLCA - structures and constants */
#ifndef SQLCODE
/* SQL Communication Area - SQLCA */
struct sqlca
{
unsigned char sqlcaid[8]; /* Eyecatcher = 'SQLCA ' */
long sqlcabc; /* SQLCA size in bytes = 136 */
long sqlcode; /* SQL return code */
short sqlerrml; /* Length for SQLERRMC */
unsigned char sqlerrmc[70]; /* Error message tokens */
unsigned char sqlerrp[8]; /* Diagnostic information */
long sqlerrd[6]; /* Diagnostic information */
unsigned char sqlwarn[8]; /* Warning flags */
unsigned char sqlext[8]; /* Reserved */
};
#define SQLCODE sqlca.sqlcode
#define SQLWARN0 sqlca.sqlwarn[0]
#define SQLWARN1 sqlca.sqlwarn[1]
#define SQLWARN2 sqlca.sqlwarn[2]
#define SQLWARN3 sqlca.sqlwarn[3]
#define SQLWARN4 sqlca.sqlwarn[4]
#define SQLWARN5 sqlca.sqlwarn[5]
#define SQLWARN6 sqlca.sqlwarn[6]
#define SQLWARN7 sqlca.sqlwarn[7]
#endif
struct sqlca 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);
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,1,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
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';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,2,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
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';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,3,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN CUSTOMER.CU_NAME
IS 'customer name';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,4,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN CUSTOMER.CU_ADDR1
IS 'street address';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,5,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN CUSTOMER.CU_ADDR2
IS 'city / state';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,6,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN CUSTOMER.CU_ZIP
IS 'zip code - no extensions allowed';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,7,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*****************************************************************************/
/* 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);
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,8,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
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';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,9,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
strcpy(rte_label,"newcust col cmt"); /* define comments for columns */
/*
EXEC SQL COMMENT ON COLUMN NEWCUST.SSN
IS 'newcust social security number: table key';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,10,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN NEWCUST.NAME
IS 'newcust name';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,11,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN NEWCUST.ADDR1
IS 'street address';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,12,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN NEWCUST.ADDR2
IS 'city / state';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,13,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN NEWCUST.ZIP
IS 'zip code - no extensions allowed';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,14,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*************************************** ******************/
/*****************************************************************************/
/* 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) );
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,15,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
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';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,16,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
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';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,17,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN BOX.B_SIZE
IS 'this is the size of the box: S, M, or L';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,18,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN BOX.B_RENT
IS 'box rental amount per month';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,19,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN BOX.B_SSN
IS 'this is the associated customer ssn if the box is rented';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,20,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN BOX.B_PAYUP
IS 'shows what month the box is paid up for if rented';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,21,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*************************************** ******************/
/*****************************************************************************/
/* 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) );
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,22,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
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';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,23,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
strcpy(rte_label,"chkg col commts"); /* define column comments */
/*
EXEC SQL COMMENT ON COLUMN CHECKING.C_ACCNUMB
IS 'checking account number: table key';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,24,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN CHECKING.C_STARTBAL
IS 'starting balance of the checking account';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,25,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN CHECKING.C_ENDBAL
IS 'ending balance of the checking account';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,26,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN CHECKING.C_SSN1
IS 'primary customer ssn associated with this checking account';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,27,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN CHECKING.C_SSN2
IS 'optional: secondary customer ssn associated with this account';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,28,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*************************************** ******************/
/*****************************************************************************/
/* 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);
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,29,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
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';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,30,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
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';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,31,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN CREDITS.CR_TRANDATE
IS 'credit transaction date';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,32,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN CREDITS.CR_DEPAMT
IS 'amount of deposit made to the checking account';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,33,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*************************************** ******************/
/*****************************************************************************/
/* 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);
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,34,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
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';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,35,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
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';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,36,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN DEBITS.DB_TRANDATE
IS 'debit transaction date';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,37,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN DEBITS.DB_AMT
IS 'amount of the debit made from checking account';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,38,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN DEBITS.DB_CHECKNO
IS 'check number associated with the debit';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,39,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*************************************** ******************/
/*****************************************************************************/
/* 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);
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,40,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
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';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,41,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
strcpy(rte_label,"loan col commts"); /* define column comments */
/*
EXEC SQL COMMENT ON COLUMN LOAN.L_NUMB
IS 'loan number: table key';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,42,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN LOAN.L_TYPE
IS 'type of loan: M, A, P, H, or O';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,43,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN LOAN.L_RATE
IS 'loan interest rate';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,44,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN LOAN.L_TERM
IS 'term of loan in months';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,45,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN LOAN.L_PAYMT
IS 'amount of loan payment per month';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,46,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN LOAN.L_BAL
IS 'remaining balance of the loan';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,47,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN LOAN.L_INTODAT
IS 'interest paid to date';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,48,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN LOAN.L_SSN
IS 'associated customer ssn';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,49,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN LOAN.L_DESCRIP
IS 'description of the specific loan';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,50,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN LOAN.L_DATE
IS 'date the loan was initiated';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,51,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*************************************** ******************/
/*****************************************************************************/
/* 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);
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,52,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/***********************************************************************/
/* 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);
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,53,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
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 );
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,54,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
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';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,55,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
strcpy(rte_label,"savg col commts"); /* define column comments */
/*
EXEC SQL COMMENT ON COLUMN SAVINGS.S_ACCNUMB
IS 'savings account number: table key';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,56,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN SAVINGS.S_IRATE
IS 'rate of interest paid';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,57,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN SAVINGS.S_INTTOD
IS 'interest earned to date';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,58,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN SAVINGS.S_BAL
IS 'savings account balance';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,59,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*
EXEC SQL COMMENT ON COLUMN SAVINGS.S_SSN
IS 'associated customer.ssn';
*/
{
sqlastrt(sqla_program_id,0L,&sqlca);
sqlacall((unsigned short)24,60,0,0,0L);
if (sqlca.sqlcode < 0)
{
sqlastop(0L);
goto stop_datab;
}
sqlastop(0L);
}
/*****************************************************************************/
/*************************************** ******************/
/*****************************************************************************/
/* 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 */
}