|
/*
This is a cgi program which connects oracle using proc. But before you test this program
you have to edit /etc/ld.so.conf file. Add following line to your /etc/ld.so.conf and enter
ldconfig command in the command line.
/home/oracle/product/8.1.7/lib
And if you don't want to use putenv in your cgi program you can add following lines at the
end of your http.conf file.
SetEnv ORACLE_HOME /home/oracle/product/8.1.7
SetEnv ORACLE_SID ORCL
SetEnv NLS_LANG Korean
SetEnv LD_LIBRARY_PATH /home/oracle/product/8.1.7/lib
*/
#include <stdio.h>
#include <string.h>
/*--------------*/
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR name[50];
VARCHAR varbuf[1024];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
/*--------------*/
void proc_perror(void){
printf("%s\n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK WORK RELEASE;
exit(-1);
}
/*--------------*/
void proc_conn(char *uid, char *pwd){
EXEC SQL WHENEVER SQLERROR DO proc_perror();
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
}
/*--------------*/
void proc_conn2(char *uid, char *pwd, char *sid){
EXEC SQL WHENEVER SQLERROR DO proc_perror();
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd; USING :sid;
}
/*--------------*/
void proc_close(void){
EXEC SQL COMMIT WORK RELEASE;
}
/*--------------*/
void proc_exec(char *q){
EXEC SQL WHENEVER SQLERROR DO proc_perror();
EXEC SQL EXECUTE IMMEDIATE :q;
EXEC SQL COMMIT WORK;
}
/*--------------*/
void proc_exec2(char *q){/*트랜잭션 모드*/
EXEC SQL WHENEVER SQLERROR DO proc_perror();
EXEC SQL EXECUTE IMMEDIATE :q;
}
/*--------------*/
void proc_commit(void){
EXEC SQL WHENEVER SQLERROR DO proc_perror();
EXEC SQL COMMIT WORK;
}
/*--------------*/
void proc_rollback(void){
EXEC SQL WHENEVER SQLERROR DO proc_perror();
EXEC SQL ROLLBACK WORK;
}
/*--------------*/
void proc_one_str(char *q, char *buf){
/*커서 OPEN*/
EXEC SQL PREPARE s_one_str FROM :q;
EXEC SQL DECLARE c_one_str CURSOR FOR s_one_str;
EXEC SQL OPEN c_one_str;
EXEC SQL FETCH c_one_str INTO :varbuf;
/*커서 CLOSE*/
EXEC SQL CLOSE c_one_str;
strcpy(buf, varbuf.arr);
}
/*--------------*/
int proc_one_int(char *q){
int res = 0;
/*커서 OPEN*/
EXEC SQL PREPARE s_one_int FROM :q;
EXEC SQL DECLARE c_one_int CURSOR FOR s_one_int;
EXEC SQL OPEN c_one_int;
EXEC SQL FETCH c_one_int INTO :res;
/*커서 CLOSE*/
EXEC SQL CLOSE c_one_int;
return res;
}
/*--------------*/
int proc_is_table(char *tab){
int res = 0;
char q[200];
sprintf(q, "select count(*) from tab where tname = '%s'", tab);
/*커서 OPEN*/
EXEC SQL PREPARE t_one_int FROM :q;
EXEC SQL DECLARE t_one_int CURSOR FOR t_one_int;
EXEC SQL OPEN t_one_int;
EXEC SQL FETCH t_one_int INTO :res;
/*커서 CLOSE*/
EXEC SQL CLOSE t_one_int;
return res;
}
/*--------------*/
int main(void){
int no, age;
char q[300];
char buf[100];
printf("Content-type: text/html\n\n");
putenv("ORACLE_HOME=/home/oracle/product/8.1.7");
putenv("ORACLE_SID=ORCL");
putenv("LD_LIBRARY_PATH=/home/oracle/product/8.1.7/lib");
putenv("NLS_LANG=Korean");
/*오라클 연결*/
proc_conn("scott", "tiger");
/*delete table*/
if(proc_is_table("TEMP")){
proc_exec("drop table temp");
}
/*create table*/
proc_exec("create table temp(no number, name varchar2(100), age number)");
/*insert data*/
proc_exec("insert into temp(no, name, age) values(1, '박찬호', 30)");
proc_exec("insert into temp(no, name, age) values(2, '김병현', 23)");
proc_exec("insert into temp(no, name, age) values(3, '최희섭', 22)");
/*select one str*/
puts("select one string..<br>");
proc_one_str("select name from temp where no = 3", buf);
printf("name: %s\n<br>", buf);
/*select one int*/
puts("<br>select one int..<br>");
age = proc_one_int("select age from temp where no = 3");
printf("age: %d\n<br>", age);
/*커서 OPEN*/
strcpy(q, "select no, name, age from temp order by no");
EXEC SQL PREPARE S1 FROM :q;
EXEC SQL DECLARE C1 CURSOR FOR S1;
EXEC SQL OPEN C1;
EXEC SQL WHENEVER NOT FOUND DO break;
puts("<br>fetch rows..<br>");
while(1){
EXEC SQL FETCH C1
INTO :no, :name, :age;
printf("no: %d, name: %s, age: %d\n<br>", no, name.arr, age);
}
/*커서 CLOSE*/
EXEC SQL CLOSE C1;
/*연결 종료*/
proc_close();
return 0;
}
|
1318751164_dynmic_ex.pc