[자바오라클스터디] http://www.oraclejava.co.kr 의 PRO*C + CGI강의

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

오늘은 게시판처럼 List를 Table로 보여주는 기능이 들어있는 cgi를 작성하여 보도록 하겠습니다.

이 소스는 첫번째 강좌의 cgic.c와 cgic.h를 필요로 합니다.
또한 이 소스 역시 제가 임의로 수정해서 올리는 것입니다.
물론 테스트 했습니다.
#include <stdio.h>

#include <cgic.h>

#define USERNAME "scott"
#define PASSWORD "tiger"

#define DBSTRING "ora9i"

#define NEXT 5 // 한화면에 5개의 개시물을 보여주기 위한 선언


EXEC SQL BEGIN DECLARE SECTION;

char *username = USERNAME;
char *password = PASSWORD;
char *dbstring = DBSTRING;

int re2;
int re3;

int page1,page2,page3;

int pagecount1;
int pagecount2;
int pagecount4;

int temp;
int allpage;

int i,j,flag1;
int id;
int xx;

VARCHAR re1[20];
VARCHAR re4[60];
VARCHAR re5[20];
VARCHAR re6[2000];
VARCHAR re7[10];
VARCHAR sqlstmt[200];

EXEC SQL END DECLARE SECTION;


EXEC SQL INCLUDE sqlca;

void sqlerror();
void setMemory ( );
void getCgiParameter();



int cgiMain ( void ) {
int nextflag = 0;
int prevflag = 0;
char nn[200];
char pr[200];

putenv("ORACLE_HOME=c:\\oracle\\ora92");
putenv("ORACLE_SID=ora9i");
putenv("NLS_LANG=AMERICAN_AMERICA.KO16KSC5601");
//putenv("LD_LIBRARY_PATH=/usr/oracle/733/lib");


EXEC SQL WHENEVER SQLERROR DO sqlerror();
EXEC ORACLE OPTION(RELEASE_CURSOR=YES);
EXEC ORACLE OPTION(HOLD_CURSOR=NO);

EXEC SQL CONNECT :username IDENTIFIED BY :password USING :dbstring;

cgiHeaderContentType("text/html"); // web으로 보여줘야 하게 때문에 html라고 header에 정의

EXEC SQL SELECT count(*) INTO : allpage FROM s_reg_db;//총 개수를 allpage변수에 저장시킴

sprintf(sqlstmt.arr," select S_NAME , S_AGE , S_SEX , S_ADDRESS , S_TELEPHONE , to_char(S_DATE, 'yy/mm/dd') , S_MEMO, S_ID "
" from s_reg_db "
" order by S_DATE "); // list에 넣을 데이터를 query해옴


sqlstmt.len = strlen( (char *)sqlstmt.arr); // 이것 꼭 써줘야 올바르게 값이 나옵니다!!



/* 커서 설정 */

EXEC SQL PREPARE s_reg_result FROM :sqlstmt;

EXEC SQL DECLARE reg_cursor CURSOR for s_reg_result;

EXEC SQL OPEN reg_cursor;


/* 나이별로 검색하는 부분 - 다음시간에 할것임*/
fprintf(cgiOut,"<form action=/cgi-bin/test20.exe method=post>");
fprintf(cgiOut,"<h5>Age Search</h5>");

fprintf(cgiOut,"<input type=text name=st_age maxlength=5 size=10 >~<input type=text name=ed_age maxlength=5 size=10 >");
fprintf(cgiOut,"<br><p>");
fprintf(cgiOut,"<input type=hidden name=s value=1>");
fprintf(cgiOut,"<input type=hidden name=page value=1>");
fprintf(cgiOut,"<input type=text name=search1>");
fprintf(cgiOut,"<input type=submit value=search>");
fprintf(cgiOut,"</form>");


/* 웹에 보여주는 list table의 label을 작성하는 부분*/
fprintf(cgiOut,"<table border=1 width=100%>");
fprintf(cgiOut,"<tr>");
fprintf(cgiOut,"<td>");
fprintf(cgiOut,"<font size=2>");
fprintf(cgiOut,"Number");
fprintf(cgiOut,"</font></td>");
fprintf(cgiOut,"<td>");
fprintf(cgiOut,"<font size=2>");
fprintf(cgiOut,"Write Date");
fprintf(cgiOut,"</font></td>");
fprintf(cgiOut,"<td>");
fprintf(cgiOut,"<font size=2>");
fprintf(cgiOut,"Name");
fprintf(cgiOut,"</font></td>");
fprintf(cgiOut,"<td>");
fprintf(cgiOut,"<font size=2>");
fprintf(cgiOut,"Age");
fprintf(cgiOut,"</font></td>");
fprintf(cgiOut,"<td>");
fprintf(cgiOut,"<font size=2>");
fprintf(cgiOut,"Sex");
fprintf(cgiOut,"</font></td>");
fprintf(cgiOut,"<td>");
fprintf(cgiOut,"<font size=2>");
fprintf(cgiOut,"Address");
fprintf(cgiOut,"</font></td>");
fprintf(cgiOut,"<td>");
fprintf(cgiOut,"<font size=2>");
fprintf(cgiOut,"Telephone");
fprintf(cgiOut,"</font></td>");
fprintf(cgiOut,"<td>");
fprintf(cgiOut,"<font size=2>");
fprintf(cgiOut,"Memo");
fprintf(cgiOut,"</font></td>");
fprintf(cgiOut,"</tr>");


getCgiParameter(); // 페이지수를 받아오는 함수
if(page1 == NULL || page1 == 0) page1 = 1;

pagecount1 = page1; //현재 페이지.



pagecount2 = pagecount1 * NEXT; //pagecount2 = pagecount1 * 5; //

for ( i=1 ; i <= pagecount2 ; i++)
{
setMemory(); // 변수 초기화

if ( page1 > 1 && flag1==0)
{
for ( j=1 ; j<=((page1-1)*5) ; j++ )

{
EXEC SQL FETCH reg_cursor INTO :re1 , :re2 , :re3 , :re4 , :re5 , :re7 , :re6 , id; //위에서 만든 커서를 가지고 1개씩 row를 fetch해옴
}

i=j;

flag1=1;
}

EXEC SQL FETCH reg_cursor INTO :re1, :re2 , :re3, :re4, :re5, :re7, :re6, id;

if ( sqlca.sqlcode == 1403 ) /* 더 이상의 데이터가 없다면 중단하라는 문장 */
break;

/* 실제 query해온 data를 table에 찍어줌 */

fprintf(cgiOut,"<tr>");
fprintf(cgiOut,"<td>");
fprintf(cgiOut,"<font size=2>");
fprintf(cgiOut,"%d",id);
fprintf(cgiOut,"</font></td>");
fprintf(cgiOut,"<td>");
fprintf(cgiOut,"<font size=2>");
fprintf(cgiOut,"%s",re7.arr);
fprintf(cgiOut,"</font></td>");
fprintf(cgiOut,"<td>");
fprintf(cgiOut,"<font size=2>");
fprintf(cgiOut,"%s",re1.arr);
fprintf(cgiOut,"</font></td>");
fprintf(cgiOut,"<td>");
fprintf(cgiOut,"%d",re2);
fprintf(cgiOut,"</font></td>");
fprintf(cgiOut,"<td>");
fprintf(cgiOut,"<font size=2>");

if ( re3 == 1 ) {
fprintf(cgiOut,"Male");
}
else if ( re3 == 2 ) {
fprintf(cgiOut,"Female");
}
fprintf(cgiOut,"</font></td>");
fprintf(cgiOut,"<td>");
fprintf(cgiOut,"<font size=2>");
fprintf(cgiOut,"%s",re4.arr);
fprintf(cgiOut,"</font></td>");
fprintf(cgiOut,"<td>");
fprintf(cgiOut,"<font size=2>");
fprintf(cgiOut,"%s",re5.arr);
fprintf(cgiOut,"</font></td>");
fprintf(cgiOut,"<td>");
fprintf(cgiOut,"<font size=2>");
fprintf(cgiOut,"%s",re6.arr);
fprintf(cgiOut,"</font></td>");

}

fprintf(cgiOut,"</tr>");
fprintf(cgiOut,"</table>");
fprintf(cgiOut,"<br><p>");


EXEC SQL close reg_cursor;

if ( allpage == 0 ) exit(1);

page3 = page1;
if ( allpage > NEXT && allpage > (page1*NEXT))
{
flag1=0;
nextflag =1;
sprintf(nn," \\\<a href=/cgi-bin/test21.exe?page=%d>Next</a>",++page1); // Next를 눌렀을 때 자신의 cgi를 다시 호출하여 사용 */
page2=page1-1;
page3 = page2;
}

if ( allpage > 5 && (page3*5) >= 10)
{
flag1=0;
prevflag = 1;
sprintf(pr," \\\<a href=/cgi-bin/test21.exe?page=%d>Prev</a>",--page3);
}

if(prevflag == 1)
fprintf(cgiOut,"%s",pr);

if(nextflag == 1)
fprintf(cgiOut,"%s",nn); // Next를 눌렀을 때 자신의 cgi를 다시 호출하여 사용 */


fprintf(cgiOut," \\\ <a href=/test20.html>Write</a>");

return 0;
}


void sqlerror() {
EXEC SQL WHENEVER SQLERROR CONTINUE;

fprintf(cgiOut, "ORACLE Error detected:<BR>\n");
fprintf(cgiOut, "% .70s <BR>\n", sqlca.sqlerrm.sqlerrmc);

EXEC SQL ROLLBACK WORK RELEASE;

exit(1);
}


void setMemory ( ) { /* 변수초기화*/

memset(re1.arr,0,sizeof(re1.arr));
memset(re4.arr,0,sizeof(re4.arr));
memset(re5.arr,0,sizeof(re5.arr));
memset(re6.arr,0,sizeof(re6.arr));
memset(re7.arr,0,sizeof(re7.arr));

re2=NULL;
re3=NULL;

id=NULL;
}


void getCgiParameter() { /* page값을 받아옴 */
int result;
char x[3];

result = cgiFormString ("page",x,3);
page1 = atoi (x);

}