[자바오라클스터디] ProC - 간단한 강좌

http://blog.naver.com/st95041/40001613322

예전에 comp76에서 작업한 예입니다. test.pc 에 있는 부분중 일부는 comp66의 현 상황에 맞게 고쳐져 있습니다. 참고하시길..

[dbhw00@comp76]/home/dbhw00/public_html/cgi-bin 52 > ls

proc.mk test.pc

[dbhw00@comp76]/home/dbhw00/public_html/cgi-bin 53 > proc test.pc

/* pc화일을 pre-compile한다. */

Pro*C/C++: Release 2.1.3.0.0 - Production on Mon Nov 24 21:15:18 1997

Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.

System default option values taken from: /home/oracle/oracle7/proc/pmscfg.h

/* pre-compile 하면 c 화일이 생성된다 */

[dbhw00@comp76]/home/dbhw00/public_html/cgi-bin 54 > ls -al

total 5232

drwxr-xr-x 2 dbhw00 50000 512 Nov 24 21:15 ./

drwxr-xr-x 3 dbhw00 50000 512 Nov 24 21:06 ../

-rw--xr-x 1 dbhw00 50000 8115 Nov 24 18:41 proc.mk

-rw------- 1 dbhw00 50000 17835 Nov 24 21:15 test.c

-rw--xr-x 1 dbhw00 50000 3271 Nov 24 20:22 test.pc

/* 다음과 같이 make 하면 실행화일이 생성된다 */

[dbhw00@comp76]/home/dbhw00/public_html/cgi-bin 55 > make -f proc.mk EXE=test.cg

i OBJS=test.o

cc -I. -O -xcg92 -I/home/oracle/oracle7/sqllib/public -c test.c

ld -dy /home/oracle/oracle7/lib/crti.o /home/oracle/oracle7/lib/crt1.o /home/ora

cle/oracle7/lib/__fstd.o -R /opt/SUNWcluster/lib -Y P,/lib:/usr/lib:/usr/dt/lib:

/usr/openwin/lib:/usr/ucblib:/opt/SUNWcluster/lib:/usr/ccs/lib:/usr/lib -Qy -lc

/home/oracle/oracle7/lib/crtn.o -L/home/oracle/oracle7/lib -o test.cgi test.o -l

sql /home/oracle/oracle7/lib/osntab.o -lsqlnet -lora -lsqlnet -lpls -lora -lnlsr

tl3 -lc3v6 -lcore3 -lnlsrtl3 -lcore3 -lsocket -lnsl -lm -ldl -laio -lsocket -lns

l -lm -ldl -laio

[dbhw00@comp76]/home/dbhw00/public_html/cgi-bin 57 > ls -al

total 10416

drwxr-xr-x 2 dbhw00 50000 512 Nov 24 21:16 ./

drwxr-xr-x 3 dbhw00 50000 512 Nov 24 21:06 ../

-rw-r--r-- 1 dbhw00 50000 8115 Nov 24 18:41 proc.mk

-rw------- 1 dbhw00 50000 17835 Nov 24 21:15 test.c

-rwx------ 1 dbhw00 50000 2633868 Nov 24 21:16 test.cgi*

-rw------- 1 dbhw00 50000 7944 Nov 24 21:16 test.o

-rw-r--r-- 1 dbhw00 50000 3271 Nov 24 20:22 test.pc

------------------------------------------------------------------

test.pc 의 내용

#include <stdio.h>

#include <stdlib.h>

#include <ctype.h>

#include <sys/types.h>

#include <time.h>

/* 각자의 id와 passwd로 수정 */

#define USERID "dbhw000"

#define PASSWD "dbhw000"

#define ORACLE_HOME_ENV "ORACLE_HOME=/usr/local/oracle"

#define ORACLE_SID_ENV "ORACLE_SID=ORA8"

void Print_html();

int DB_Task();

void error_out(char *msg1);

EXEC SQL INCLUDE sqlca;

EXEC SQL INCLUDE oraca;

EXEC ORACLE OPTION (ORACA=YES);

EXEC SQL BEGIN DECLARE SECTION;

int id;

VARCHAR name[20];

VARCHAR address[200];

VARCHAR phone[14];

VARCHAR email[100];

VARCHAR dbuid[20];

VARCHAR dbpwd[20];

VARCHAR servicename[20];

EXEC SQL END DECLARE SECTION;

main(){

/* 환경 변수 설정 부분 */

putenv(ORACLE_HOME_ENV);

putenv(ORACLE_SID_ENV);

/* html 문서임을 표시, \n이 2개 이상 */

printf("Content-type: text/html\n\n");

DB_Task();

Print_html();

exit(0);

}

/* DB 작업과 sqlca를 이용한 에러 처리 */

int DB_Task() {

int idnum;

/* ID, Passwd 설정 */

/* VARCHAR 타입은 문자열을 위한 arr과 문자열 길이를 위한 len으로 구성 */

strcpy(dbuid.arr, USERID); dbuid.len = strlen(USERID);

strcpy(dbpwd.arr, PASSWD); dbpwd.len = strlen(PASSWD);

/* DB 연결 */

EXEC SQL CONNECT :dbuid IDENTIFIED BY :dbpwd;

/* test 테이블로부터 id, ..., email을 검색하여 :id, ..., :email 에 대입 */

idnum = 1;

EXEC SQL SELECT id, name, address, email

INTO :id, :name, :address, :email

FROM test

WHERE id=:idnum;

if(sqlca.sqlcode!=0) {

EXEC SQL WHENEVER SQLERROR CONTINUE;

EXEC SQL ROLLBACK WORK RELEASE;

error_out("SQL문에 오류가 있습니다.");

return 1;

}

EXEC SQL WHENEVER SQLERROR CONTINUE;

EXEC SQL COMMIT WORK RELEASE;

return 0;

}

/* DB 작업과 레이블을 이용한 에러 처리 */

int DB_Task(){

int idnum;

strcpy(dbuid.arr, USERID); dbuid.len = strlen(USERID);

strcpy(dbpwd.arr, PASSWD); dbpwd.len = strlen(PASSWD);

/* DB 작업중 에러 발생하면 ErrExit로 감 */

EXEC SQL WHENEVER SQLERROR GOTO ErrExit;

EXEC SQL CONNECT :dbuid IDENTIFIED BY :dbpwd;

idnum = 1;

EXEC SQL SELECT id, name, address, email

INTO :id, :name, :address, :email

FROM test

WHERE id=:idnum;

EXEC SQL WHENEVER SQLERROR CONTINUE;

EXEC SQL COMMIT WORK RELEASE;

return 0;

ErrExit:

EXEC SQL WHENEVER SQLERROR CONTINUE;

EXEC SQL ROLLBACK WORK RELEASE;

error_out("시스템에 문제가 발생했습니다");

return 1;

}

void Print_html() {

printf("<html>\n");

printf("<head>\n");

printf("<title> Query 1 </title>\n");

printf("</head>\n");

printf("<body bgcolor=#ffffff >\n");

printf("<H1> <Center> <U> QUERY 1 </U> </Center> </H1>\n");

printf("<center>\n");

printf("<Table Border=1>\n");

printf("<Tr>\n");

printf("<Td> <Center> 구분 </Center> </td>\n");

printf("<Td > <center>내 용</center></td>\n");

printf("</tr>\n");

printf("<Tr>\n");

printf("<Td> <Center> ID </Center> </td>\n");

printf("<Td > %d</td>\n", id);

printf("</tr>\n");

printf("<Tr>\n");

printf("<Td> <Center> 이름 </Center> </td>\n");

printf("<Td > %s</td>\n", name.arr);

printf("</tr>\n");

printf("<Tr>\n");

printf("<Td> <Center> 주소 </Center> </td>\n");

printf("<Td > %s</td>\n", address.arr);

printf("</tr>\n");

printf("<Tr>\n");

printf("<Td> <Center> E-mail </Center> </td>\n");

printf("<Td > %s</td>\n", email.arr);

printf("</tr>\n");

printf("</Table>\n");

printf("</center>\n");

printf("<Hr Width=100%%>\n");

printf("<Center>\n");

printf("</body>\n");

}

comp66>/usr/loca/oracle/precomp/demo/proc 내에 있는 .pc 파일들을 참고할 것.

< 참고 문헌 >

Rick F. van der Lans, "Introduction to SQL", Addison-Wesley

Rick F. van der Lans, "The SQL Guide to ORACLE", Addison-Wesley

Jim Melton, "Understanding the new SQL", Morgan Kaufmann.

원래소스 http://dbmain.snu.ac.kr/courses/DB99/proc.html