2014년 9월 24일 수요일

proc로 cgi만들기(5) - 김태양[ORACLE/SQL/HINT/오라클/교육/강의/강좌/SQL교육/오라클 PLSQL/강의/강좌/오라클교육/ORACLE초보교육/오라클실무강좌]

오늘은 세부내용을 볼 수 있는 기능을 추가하여 게시판의 기능을 완성하여 봅시다. 

1) 소스 

#include <stdio.h> 

#include "/home/superman/cgic/cgic.h" 

#define USERNAME "superman" 

#define PASSWORD "superman" 

#define DBSTRING "DB" 

/*////////////////////////////////////////////////////////////*/ 

/* Declare variables*/ 

EXEC SQL BEGIN DECLARE SECTION; 

char *username = USERNAME; 

char *password = PASSWORD; 

char *dbstring = DBSTRING; 



VARCHAR re1[20]; 

int re2; 

int re3; 

VARCHAR re4[60]; 

VARCHAR re5[20]; 

VARCHAR re6[2000]; 

VARCHAR re7[10]; 

VARCHAR sqlstmt[300]; 

VARCHAR sqlstmt2[300]; 



VARCHAR se_memo[20]; 

int temp; 

int id,i,c_id;  

int s,result1; 

char temp2[3]; 

int temp3; 

int start_age , end_age; 

int get_id; 

int flag1; 

int allpage; 

int page1,count1=0; 

EXEC SQL END DECLARE SECTION; 

EXEC SQL INCLUDE sqlca; 



/*/////////////////////////////////////////////////////*/ 



void sqlerror(); 

void setMemory (); 



int cgiMain ( void ) { 



/*//////////////////Set Environment///////////////////*/ 



putenv("ORACLE_HOME=/usr2/oracle/733"); 

putenv("ORACLE_SID=NEONET"); 

putenv("NLS_LANG=American_America.ko16ksc5601"); 

putenv("LD_LIBRARY_PATH=/usr2/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");  



/*////////////////////////////////////////////////////*/  



/*///////// Get Parameter //////////////////////////*/ 



setMemory(); 

/*/////////////// 함수로 만들어 놓는것이 좋겠죠...////////*/ 

result1 = cgiFormString ( "search1",se_memo.arr, sizeof(se_memo.arr)); 

se_memo.len = strlen((char *)se_memo.arr); 

result1 = cgiFormString ( "st_age" , temp2 , 3); 

start_age = atoi(temp2); 

result1 = cgiFormString ( "ed_age" , temp2 , 3); 

end_age = atoi(temp2); 

result1 = cgiFormString ( "s_id", temp2 , 3); 

get_id = atoi(temp2); 

result1 = cgiFormString ( "page" , temp2 , 3); 

page1 = atoi(temp2); 

result1 = cgiFormString ( "flag" , temp2 , 3); 

flag1 = atoi(temp2); 



c_id = get_id; 



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 " 

" where s_id = %d and ( s_age >= %d and s_age <= %d ) and s_memo " 

"like '%%%s%%' order by s_date",get_id,start_age, end_age , se_memo.arr); 

sqlstmt.len = strlen((char *)sqlstmt.arr); 



/*////////////// 상세 보기를 위한 query 문장 ////////////////////*/ 

sprintf(sqlstmt2.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 " 

" where ( s_age >= %d and s_age <= %d ) and s_memo " 

" like '%%%s%%' order by s_date",start_age,end_age,se_memo.arr); 

sqlstmt2.len = strlen((char *)sqlstmt2.arr); 



/*///////////////////////////////////////////////////////////*/ 

EXEC SQL PREPARE s_reg_result FROM :sqlstmt2; 

EXEC SQL DECLARE reg_cursor3 CURSOR for s_reg_result; 

EXEC SQL OPEN reg_cursor3; 



/*////////////// 총 레코드수를 세는 명령 /////////////////*/ 

for ( i=1 ; i<=10000 ; i++ ) 

{  

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

if ( sqlca.sqlcode == 1403 ) 

{ 

allpage=i-1;  

break; 

} 

} 

/*///////////// 위의 것 보다는 요것이 더욱 편하겠죠. ///////////*/ 

/* EXEC SQL SELECT count(*) INTO :allpage FROM s_reg_db where ( s_age >= %d and s_age <= %d )  

and s_memo like '%%%s%%' order by s_date",start_age,end_age,se_memo.arr /////////*/ 



EXEC SQL CLOSE reg_cursor3; 



EXEC SQL PREPARE s_reg_result FROM :sqlstmt2; 

EXEC SQL DECLARE reg_cursor CURSOR for s_reg_result; 

EXEC SQL OPEN reg_cursor; 



/* 이전에 배웠던것처럼 테이블을 만들어서 찍어주기만 하면 됩니다.*/ 

/* 첫 페이지일 경우에 출력하는 형태 */ 

if ( page1 == 1 && flag1 != 1) 

{ setMemory(); 

EXEC SQL PREPARE s_reg_result2 FROM :sqlstmt; 

EXEC SQL DECLARE reg_cursor2 CURSOR for s_reg_result2; 

EXEC SQL OPEN reg_cursor2; 

/* 테이블 제목 출력 */  

fprintf(cgiOut,"<table border=1 width=70%%>"); 

fprintf(cgiOut,"<tr>"); 

fprintf(cgiOut,"<td width=5%%><font size=2>"); 

fprintf(cgiOut,"<center>Number</center>"); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td width=10%%><font size=2>"); 

fprintf(cgiOut,"<center>Write Date</center>"); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td><font size=2>"); 

fprintf(cgiOut,"<center>Name</center>"); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td><font size=2>"); 

fprintf(cgiOut,"<center>Age</center>"); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td><font size=2>"); 

fprintf(cgiOut,"<center>Sex</center>"); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td width=30%%><font size=2>"); 

fprintf(cgiOut,"<center>Address</center>"); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td><font size=2>"); 

fprintf(cgiOut,"<center>Telephone</center>"); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"</tr>"); 



setMemory(); 

/* 1개씩 fetch */ 

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



if ( sqlca.sqlcode == 1403 ) 

exit(1); 

/* 실제 데이터 출력 */ 

fprintf(cgiOut,"<tr>"); 

fprintf(cgiOut,"<td>"); 

fprintf(cgiOut,"<font size=2>"); 

fprintf(cgiOut,"<center>%d</center>",id); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td>"); 

fprintf(cgiOut,"<font size=2>"); 

fprintf(cgiOut,"<center>%s</center>",re7.arr); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td>"); 

fprintf(cgiOut,"<font size=2>"); 

fprintf(cgiOut,"<center>%s</center>",re1.arr); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td>"); 

fprintf(cgiOut,"<center>%d</center>",re2); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td>"); 

fprintf(cgiOut,"<font size=2>");  



if ( re3 == 1 ) { 

fprintf(cgiOut,"<center>Male</center>"); 

} 

else if ( re3 == 2 ) { 

fprintf(cgiOut,"<center>Female</center>"); 

} 



fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td><font size=2>"); 

fprintf(cgiOut,"<center>%s</center>",re4.arr); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td><font size=2>"); 

fprintf(cgiOut,"<center>%s</center>",re5.arr); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"</tr>");  

fprintf(cgiOut,"</table>"); 

fprintf(cgiOut,"<table border=1 width=70%%>"); 

fprintf(cgiOut,"<tr><td><center>MEMO</center></td></tr>"); 

fprintf(cgiOut,"<tr><td><center><pre>%s</pre></center></td></tr>",re6.arr); 

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



EXEC SQL close reg_cursor2; 



for (;;)  

{ 

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

count1++; 



if ( id == c_id ) 

break; 

} 



EXEC SQL close reg_cursor; 

page1=count1; 



} 

else 

{ 



if ( page1 != 1 ) 

{ 

for ( i = 1 ; i<page1 ; i++ ) 

{ setMemory(); 

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

} 

} 



/* 1 페이지 이상일 경우를 위한 출력양식 */ 

setMemory(); 

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



fprintf(cgiOut,"<table border=1 width=70%%>"); 

fprintf(cgiOut,"<tr><td width=5%%><font size=2>"); 

fprintf(cgiOut,"<center>Number</center>"); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td width=10%%><font size=2>"); 

fprintf(cgiOut,"<center>Write Date</center>"); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td><font size=2>"); 

fprintf(cgiOut,"<center>Name</center>"); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td><font size=2>"); 

fprintf(cgiOut,"<center>Age</center>"); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td><font size=2>"); 

fprintf(cgiOut,"<center>Sex</center>"); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td width=30%%><font size=2>"); 

fprintf(cgiOut,"<center>Address</center>"); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td><font size=2>"); 

fprintf(cgiOut,"<center>Telephone</center>"); 

fprintf(cgiOut,"</font></td></tr>"); 

fprintf(cgiOut,"<tr><td>"); 

fprintf(cgiOut,"<font size=2>"); 

fprintf(cgiOut,"<center>%d</center>",id); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td><font size=2>"); 

fprintf(cgiOut,"<center>%s</center>",re7.arr); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td><font size=2>"); 

fprintf(cgiOut,"<center>%s</center>",re1.arr); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td><font size=2>"); 

fprintf(cgiOut,"<center>%d</center>",re2); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td><font size=2>"); 



if ( re3 == 1 ) { 

fprintf(cgiOut,"<center>Male</center>"); 

} 

else if ( re3 == 2 ) { 

fprintf(cgiOut,"<center>Female</center>"); 

} 



fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td><font size=2>"); 

fprintf(cgiOut,"<center>%s</center>",re4.arr); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"<td><font size=2>"); 

fprintf(cgiOut,"<center>%s</center>",re5.arr); 

fprintf(cgiOut,"</font></td>"); 

fprintf(cgiOut,"</tr>"); 

fprintf(cgiOut,"</table>"); 

fprintf(cgiOut,"<table border=1 width=70%%>"); 

fprintf(cgiOut,"<tr><td><center>MEMO</center></td></tr>"); 

fprintf(cgiOut,"<tr><td><center><pre>%s</pre></center></td></tr>",re6.arr); 

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



EXEC SQL close reg_cursor; 

} 



if ( allpage != 1 && allpage != page1 ) 

{ 

page1++; 

fprintf(cgiOut,"<a href=/cgi-bin/test/henryiii/ex6/ex6.cgi?flag=1&page=%d&search1=%s&st_age=%d&ed_age=%d>Next</a>",page1,se_memo.arr,start_age,end_age); 

page1--;  

} 

else if ( allpage == 1 ) 

{ 

fprintf(cgiOut,"<a href=/test/henryiii/ex1/index.html>Write</a>"); 

fprintf(cgiOut,"<br><p>"); 

fprintf(cgiOut,"<a href=/cgi-bin/test/henryiii/ex5/ex5.cgi?page=1&s=1&search1=%s&st_age=%d&ed_age=%d>Home</a>",se_memo.arr,start_age,end_age); 

exit(1);  

} 



if ( allpage == page1 || page1 > 1) 

{ 

page1--; 

fprintf(cgiOut,"<a href=/cgi-bin/test/henryiii/ex6/ex6.cgi?flag=1&page=%d&search1=%s&st_age=%d&ed_age=%d>Prev</a>",page1,se_memo.arr,start_age,end_age); 

} 



fprintf(cgiOut,"<a href=/test/henryiii/ex1/index.html>Write</a>"); 

fprintf(cgiOut,"<br><p>");  

fprintf(cgiOut,"<a href=/cgi-bin/test/henryiii/ex7/ex7.cgi?page=1&s=1&search1=%s&st_age=%d&ed_age=%d>Home</a>",se_memo.arr,start_age,end_age); 



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)); 

memset(se_memo.arr,0,sizeof(se_memo.arr)); 

re2=NULL; 

re3=NULL; 

id=NULL; 



} 



Next와 Back을 구현하기 위해서 조금 어렵고 번거럽게 코딩을 해놓았죠... 



제가 처음 배울때 사용한 코드라서 그럽니다.. 여러분께서는 좀더 효율적이고 짧은 코드로 작성하실 수 있을 것 입니다.



지금까지 배운것을 종합하면 하나의 게시판이 만들어 질 것입니다. 



 기업100%환급/오라클/자바/스프링/안드로이드/닷넷C#/웹퍼블리싱… 오라클자바…12-271983
 [채용예정교육]오라클자바개발잘하는신입뽑기2개월과정,교육전취…오라클자바…12-111453
53 [평일주간]100%환급6건,안드로이드,자바,C#,스프링3.2,SQL,힌트/… 오라클자바…03-151207
52 [주말주간]C#, ASP.NET마스터 오라클자바…01-311372
51 [평일,기업100%환급]SQL기초에서 Schema Object까지 오라클자바…01-311199
50 [평일야간]HTML5, CSS3,Ajax, jQuery마스터과정 오라클자바…01-311063
49 [평일주간,평일야간,주말]Spring,MyBatis,Hibernate개발자과정 오라클자바…01-191371
48 [평일주간,평일야간,주말]안드로이드개발자과정 오라클자바…01-111204
47 [평일야간,주말주간]JAVA,Network&JSP&Spring,MyBatis,Hibernate 오라클자바…01-031706
46 기업100%환급/오라클/자바/스프링/안드로이드/닷넷C#/웹퍼블리싱… 오라클자바…12-271983
45 [평일야간,주말]자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis… 오라클자바…12-191474
44 웹퍼블리싱 마스터(HTML5,CSS3,jQUERY,AJAX,JavaScript) 오라클자바…12-141436
43 [채용예정교육]오라클자바개발잘하는신입뽑기2개월과정,교육전취… 오라클자바…12-111453
42 [평일,기업100%환급]자바기초에서 JDBC, Servlet/JSP까지 오라클자바…12-091168
41 [평일야간]닷넷(C#,Network,ADO.NET,ASP.NET)마스터 오라클자바…12-011375
40 [기업100%환급]C#4.0,WinForm,ADO.NET프로그래밍(평일주간(단기)… 오라클자바…12-011532
39 [평일야간,주말]SQL기초에서실무까지(SQL기초,PLSQL,힌트,튜닝) 오라클자바…12-011031
작성자: 시간:

댓글 없음:

댓글 쓰기

피드 구독하기: 댓글 (Atom)