Liren Large Software Subsidy 5

home *** CD-ROM | disk | FTP | other *** search

/ Liren Large Software Subsidy 5 / 05.iso / a / a524 / 42.ddi / demo / samplec.lis < prev next >

Wrap

File List | 1991-03-04 | 16.6 KB | 423 lines

ORACLE RDBMS V6.0.27.9.2, transaction processing option - Production PL/SQL V1.0.30.0.1 - Production/ORACLE Precompiler 1.3.15 Sat Dec 15 06:20:25 1990 Page 1 Options: include=/usr/user1/c/lib ireclen=132 oreclen=132 rebind=no select_error=no sqlcheck=limited 1 #ifdef RCSID 2 static char *RCSid = 3 "$Header: samplec.pc,v 6.0 88/06/16 19:25:58 rchoplin Exp $ samplec.pc Copyr (c) 1986 Oracle"; 4 #endif /* RCSID */ 5 6 /* 7 * $Date: 88/06/16 19:25:58 $ $Revision: 6.0 $ samplec.pc Copyr (c) 1986 Oracle 8 */ 9 10 /* VOID sample 11 12 sample is a simple example program which adds new employee 13 records to the personnel data base. Checking 14 is done to insure the integrity of the data base. 15 The employee numbers are automatically selected using 16 the current maximum employee number + 10. 17 18 The program queries the user for data as follows: 19 20 Enter employee name: 21 Enter employee job: 22 Enter employee salary: 23 Enter employee dept: 24 25 The program terminates if EOF (end of file) or a null 26 string (<return> key) is entered when the employee name 27 is requested. 28 29 If the record is successfully inserted, the following 30 is printed: 31 32 ename added to department dname as employee # nnnnnn 33 34 To build and run SAMPLE (assumes that SQLCA file is in "SQLCA.H"): 35 36 $ pcc iname=samplec.pc prog=xxx$sample userid=scott/tiger 37 $ cc -O -c samplec.c 38 $ cc -o samplec samplec.o -lsql -lhlic -lhli 39 $ samplec 40 41 where "xxx" is your logon id. E.g.: 42 43 $ pcc iname=samplec prog=oracle$sample userid=scott/tiger 44 45 The idea here is to pick a unique name for the program, so that 46 anyone precompiling pgms into "scott/tiger" will have unique names 47 for their pgms (e.g., if I have a prog=sample userid=scott/tiger; 48 and you have a prog=sample userid=scott/tiger, then we have a 49 conflict...). 50 */ 51 #include <stdio.h> 52 #include <ctype.h> 53 54 EXEC SQL BEGIN DECLARE SECTION; 55 VARCHAR uid[20]; 56 /* username */ 57 VARCHAR pwd[20]; 58 /* password */ 59 ORACLE RDBMS V6.0.27.9.2, transaction processing option - Production PL/SQL V1.0.30.0.1 - Production/ORACLE Precompiler 1.3.15 Sat Dec 15 06:20:25 1990 Page 2 Options: include=/usr/user1/c/lib ireclen=132 oreclen=132 rebind=no select_error=no sqlcheck=limited 60 int empno; /* employee number */ 61 VARCHAR ename[15]; 62 /* employee name */ 63 int deptno; /* department number */ 64 VARCHAR dname[15]; 65 /* department name */ 66 67 VARCHAR job[15]; 68 /* employee job */ 69 int sal; /* employee salary */ 70 EXEC SQL END DECLARE SECTION; 71 EXEC SQL INCLUDE sqlca.h; 1 /* 2 * $Header: sqlca.h,v 10301.3 89/02/22 11:07:56 nsalah Exp $ sqlca.h Copyr (c) 1987 Oracle 3 */ 4 5 /* v1pcc,10301,v1pcc$src:[defs.10301] */ 6 /* Copyright (c) 1985,1986 by Oracle Corporation. */ 7 8 /* 9 NAME 10 SQLCA : SQL Communications Area. 11 FUNCTION 12 Contains no code. Oracle fills in the SQLCA with status info 13 during the execution of a SQL stmt. 14 NOTES 15 If the symbol SQLCA_STORAGE_CLASS is defined, then the SQLCA 16 will be defined to have this storage class. For example: 17 18 #define SQLCA_STORAGE_CLASS extern 19 20 will define the SQLCA as an extern. 21 22 If the symbol SQLCA_INIT is defined, then the SQLCA will be 23 statically initialized. Although this is not necessary in order 24 to use the SQLCA, it is a good pgming practice not to have 25 unitialized variables. However, some C compilers/OS's don't 26 allow automatic variables to be init'd in this manner. Therefore, 27 if you are INCLUDE'ing the SQLCA in a place where it would be 28 an automatic AND your C compiler/OS doesn't allow this style 29 of initialization, then SQLCA_INIT should be left undefined -- 30 all others can define SQLCA_INIT if they wish. 31 32 MODIFIED 33 Clare 12/06/84 - Ch SQLCA to not be an extern. 34 Clare 10/21/85 - Add initialization. 35 Bradbury 01/05/86 - Only initialize when SQLCA_INIT set 36 Clare 06/12/86 - Add SQLCA_STORAGE_CLASS option. 37 */ 38 39 #ifndef SQLCA 40 #define SQLCA 1 41 42 struct sqlca 43 { 44 /* ub1 */ char sqlcaid[8]; 45 /* b4 */ long sqlabc; 46 /* b4 */ long sqlcode; 47 struct ORACLE RDBMS V6.0.27.9.2, transaction processing option - Production PL/SQL V1.0.30.0.1 - Production/ORACLE Precompiler 1.3.15 Sat Dec 15 06:20:25 1990 Page 3 Options: include=/usr/user1/c/lib ireclen=132 oreclen=132 rebind=no select_error=no sqlcheck=limited 48 { 49 /* ub2 */ unsigned short sqlerrml; 50 /* ub1 */ char sqlerrmc[70]; 51 } sqlerrm; 52 /* ub1 */ char sqlerrp[8]; 53 /* b4 */ long sqlerrd[6]; 54 /* ub1 */ char sqlwarn[8]; 55 /* ub1 */ char sqlext[8]; 56 }; 57 58 #ifdef SQLCA_STORAGE_CLASS 59 SQLCA_STORAGE_CLASS struct sqlca sqlca 60 #else 61 struct sqlca sqlca 62 #endif 63 64 #ifdef SQLCA_INIT 65 = { 66 {'S', 'Q', 'L', 'C', 'A', ' ', ' ', ' '}, 67 sizeof(struct sqlca), 68 0, 69 { 0, {0}}, 70 {'N', 'O', 'T', ' ', 'S', 'E', 'T', ' '}, 71 {0, 0, 0, 0, 0, 0}, 72 {0, 0, 0, 0, 0, 0, 0, 0}, 73 {0, 0, 0, 0, 0, 0, 0, 0} 74 } 75 #endif 76 ; 77 78 #endif 79 80 /* end SQLCA */ 72 73 main() 74 { 75 76 /* ----------------------------------------------------------------------------- 77 logon to ORACLE, and open the cursors. The program exits if any errors occur. 78 ----------------------------------------------------------------------------- */ 79 80 strcpy(uid.arr,"SCOTT"); 81 uid.len = strlen(uid.arr); 82 strcpy(pwd.arr,"TIGER"); 83 pwd.len = strlen(pwd.arr); 84 85 EXEC SQL WHENEVER SQLERROR GOTO errexit; 86 EXEC SQL CONNECT :uid IDENTIFIED BY :pwd; 87 88 /* ----------------------------------------------------------------------------- 89 Retrieve the current maximum employee number 90 ----------------------------------------------------------------------------- */ 91 92 EXEC SQL SELECT NVL(MAX(EMPNO),0) + 10 93 INTO :empno 94 FROM EMP; 95 96 /* ----------------------------------------------------------------------------- 97 read the user's input from STDIN. If the employee name is ORACLE RDBMS V6.0.27.9.2, transaction processing option - Production PL/SQL V1.0.30.0.1 - Production/ORACLE Precompiler 1.3.15 Sat Dec 15 06:20:25 1990 Page 4 Options: include=/usr/user1/c/lib ireclen=132 oreclen=132 rebind=no select_error=no sqlcheck=limited 98 not entered, exit. 99 Verify that the entered department number is valid and echo the 100 department's name 101 ----------------------------------------------------------------------------- */ 102 103 for( ; ; empno+=10 ) 104 { 105 int l; 106 107 /* Get employee name to be inserted. 108 109 IMPORTANT NOTE: beware of coding as follows (I got burnt, 1st time): 110 111 ename.len = asks("Enter employee name : ", ename.arr); 112 if ( ename.len <= 0 ) 113 ..etc.. 114 115 In the above, asks() returns an int, but ename.len is an unsigned 116 short (see SQLCA). Therefore, the "if" fails for <EOF> (which returns 117 -1) because, by definition, the unsigned short can't be negative. 118 */ 119 l = asks("Enter employee name : ",ename.arr); 120 121 if ( l <= 0 ) 122 break; 123 124 ename.len = l; 125 126 job.len = asks("Enter employee job : ",job.arr); 127 askn("Enter employee salary: ",&sal); 128 for ( ; ; ) 129 { 130 if ( askn("Enter employee dept : ",&deptno) < 0 ) 131 break; 132 133 EXEC SQL WHENEVER NOT FOUND GOTO nodept; 134 EXEC SQL SELECT DNAME 135 INTO :dname 136 FROM DEPT 137 WHERE DEPTNO = :deptno; 138 139 dname.arr[dname.len] = '\0'; 140 141 EXEC SQL WHENEVER NOT FOUND STOP; 142 143 /* Here if deptno was found in dbs. Insert new employee into dbs. */ 144 145 EXEC SQL INSERT INTO EMP(EMPNO,ENAME,JOB,SAL,DEPTNO) 146 VALUES (:empno,:ename,:job,:sal,:deptno); 147 148 printf("\n%s added to the %s department as employee number %d\n", 149 ename.arr,dname.arr,empno); 150 break; 151 152 /* Here if deptno NOT found in dbs */ 153 nodept: 154 printf("\nNo such department\n"); 155 continue; 156 } ORACLE RDBMS V6.0.27.9.2, transaction processing option - Production PL/SQL V1.0.30.0.1 - Production/ORACLE Precompiler 1.3.15 Sat Dec 15 06:20:25 1990 Page 5 Options: include=/usr/user1/c/lib ireclen=132 oreclen=132 rebind=no select_error=no sqlcheck=limited 157 } 158 159 /* ----------------------------------------------------------------------------- 160 close the cursors and log off from ORACLE 161 ----------------------------------------------------------------------------- */ 162 163 EXEC SQL COMMIT WORK RELEASE; 164 printf ("\nEnd of the C/ORACLE example program.\n"); 165 return(1); 166 167 errexit: 168 errrpt(); 169 EXEC SQL WHENEVER SQLERROR CONTINUE; 170 EXEC SQL ROLLBACK WORK RELEASE; 171 return(0); 172 } 173 174 /*------------------------------------------------------------------------------ 175 COUNT askn(text,variable) 176 177 print the 'text' on STDOUT and read an integer variable from 178 SDTIN. 179 180 text points to the null terminated string to be printed 181 variable points to an integer variable 182 183 askn returns a 1 if the variable was read successfully or a 184 -1 if -eof- was encountered 185 ----------------------------------------------------------------------------- */ 186 187 int askn(text,variable) 188 char text[]; 189 int *variable; 190 { 191 char s[20]; 192 printf(text); 193 if ( gets(s) == (char *)0 ) 194 return(EOF); 195 196 *variable = atoi(s); 197 return(1); 198 } 199 200 /* ----------------------------------------------------------------------------- 201 COUNT asks(text,variable) 202 203 print the 'text' on STDOUT and read up to 'len' characters into 204 the buffer pointed to by variable from STDIN. 205 206 text points to the null terminated string to be printed 207 variable points to a buffer of at least 'len'+1 characters 208 209 asks returns the number of character read into the string, or a 210 -1 if -eof- was encountered 211 ----------------------------------------------------------------------------- */ 212 213 int asks(text,variable) 214 char text[],variable[]; 215 { ORACLE RDBMS V6.0.27.9.2, transaction processing option - Production PL/SQL V1.0.30.0.1 - Production/ORACLE Precompiler 1.3.15 Sat Dec 15 06:20:25 1990 Page 6 Options: include=/usr/user1/c/lib ireclen=132 oreclen=132 rebind=no select_error=no sqlcheck=limited 216 printf(text); 217 return( gets(variable) == (char *)0 ? EOF : strlen(variable) ); 218 } 219 220 /* ----------------------------------------------------------------------------- 221 VOID errrpt() 222 223 errrpt prints the ORACLE error msg and number. 224 ----------------------------------------------------------------------------- */ 225 226 errrpt() 227 { 228 printf("\n%.70s (%d)\n", sqlca.sqlerrm.sqlerrmc, -sqlca.sqlcode); 229 return(0); 230 } ORACLE RDBMS V6.0.27.9.2, transaction processing option - Production PL/SQL V1.0.30.0.1 - Production/ORACLE Precompiler 1.3.15 Sat Dec 15 06:20:25 1990 Page 7 Options: include=/usr/user1/c/lib ireclen=132 oreclen=132 rebind=no select_error=no sqlcheck=limited Host Variables -------------- deptno Long Integer Def: Line 63 in samplec.pc Ref: Line 134 in samplec.pc Ref: Line 145 in samplec.pc dname Varchar(15) Def: Line 64 in samplec.pc Ref: Line 134 in samplec.pc empno Long Integer Def: Line 60 in samplec.pc Ref: Line 92 in samplec.pc Ref: Line 145 in samplec.pc ename Varchar(15) Def: Line 61 in samplec.pc Ref: Line 145 in samplec.pc job Varchar(15) Def: Line 67 in samplec.pc Ref: Line 145 in samplec.pc pwd Varchar(20) Def: Line 57 in samplec.pc Ref: Line 86 in samplec.pc sal Long Integer Def: Line 69 in samplec.pc Ref: Line 145 in samplec.pc uid Varchar(20) Def: Line 55 in samplec.pc Ref: Line 86 in samplec.pc ORACLE RDBMS V6.0.27.9.2, transaction processing option - Production PL/SQL V1.0.30.0.1 - Production/ORACLE Precompiler 1.3.15 Sat Dec 15 06:20:25 1990 Page 8 Options: include=/usr/user1/c/lib ireclen=132 oreclen=132 rebind=no select_error=no sqlcheck=limited SQL Identifiers --------------- UNIX DEFINE Pre-defined symbol ORACLE RDBMS V6.0.27.9.2, transaction processing option - Production PL/SQL V1.0.30.0.1 - Production/ORACLE Precompiler 1.3.15 Sat Dec 15 06:20:25 1990 Page 9 Options: include=/usr/user1/c/lib ireclen=132 oreclen=132 rebind=no select_error=no sqlcheck=limited Current Options --------------- areasize 16384 asacc no errors yes hold_cursor no host C iname samplec.pc inline yes ireclen 132 lname samplec.lis lreclen 132 ltype long maxopencursors 10 mode oracle oname samplec.c oreclen 132 pagelen 66 program SAMPLEC release_cursor no sqlcheck limited xref yes ORACLE RDBMS V6.0.27.9.2, transaction processing option - Production PL/SQL V1.0.30.0.1 - Production/ORACLE Precompiler 1.3.15 Sat Dec 15 06:20:25 1990 Page 10 Options: include=/usr/user1/c/lib ireclen=132 oreclen=132 rebind=no select_error=no sqlcheck=limited Statistics ---------- Return code: 0 Number of messages at severity I: 0 Number of messages at severity W: 0 Number of messages at severity E: 0 Number of messages at severity S: 0 Number of messages at severity U: 0 Number of input lines: 310 Number of host variables declared: 8 Number of cursor names: 0 Number of statement names: 0 Maximum bytes used: 5688