|
#include <stdlib.h>
#include <stdio.h>
#include <ctype.h>
#include <string.h>
#include <sqlda.h>
#include <sqlcpr.h>
int errrpt(void);
int norows();
int asks(char *,char *);
int updateYN();
void createtable();
void droptable();
void insertinto();
void deletefrom();
void updatetable();
void selectfrom();
void selectall();
void errexit();
void jobsuccess();
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR uid[80];
VARCHAR pwd[20];
VARCHAR sid[20];
VARCHAR tid[20];
int in_id;
VARCHAR in_name[10];
VARCHAR in_addr[30];
VARCHAR in_phone[13];
VARCHAR in_email[35];
int in_age;
VARCHAR strsql[500];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca.h;
EXEC SQL INCLUDE oraca.h;
void main()
{
int l;
int menu=1;
int flag=0;
reconnect:
while(1)
{
l = asks("Enter User ID\t\t:\t", (char *)uid.arr);
if ( l <= 0 )
continue;
uid.len = (short) l;
l = asks("Enter Passwd\t\t:\t", (char *)pwd.arr);
if ( l <= 0 )
continue;
pwd.len = (short) l;
l = asks("Enter Net Service Name\t:\t", (char *)sid.arr);
if ( l <= 0 )
continue;
sid.len = (short) l;
l = asks("Input Your Tbl Name\t:\t", (char *)tid.arr);
if ( l <= 0 )
continue;
tid.len = (short) l;
break;
}
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd USING :sid;
if(sqlca.sqlcode!=0){
goto reconnect;
}else{printf("Connect to ORACLE\n\n");}
while(flag == 0)
{
printf("1. Create Table(ID,NAME,ADDRESS,PHONE,EMAIL,AGE).\n");
printf("2. Drop Table.\n");
printf("3. Insert Into Table.\n");
printf("4. Delete From Table(ID)\n");
printf("5. Update Table(ID).\n");
printf("6. Select From Table(ID).\n");
printf("7. Select All.\n");
printf("0. Exit.\n");
printf("Input number : ");
scanf("%d",&menu);
switch(menu)
{
case 1:
createtable();
break;
case 2:
droptable();
break;
case 3:
insertinto();
break;
case 4:
deletefrom();
break;
case 5:
updatetable();
break;
case 6:
selectfrom();
break;
case 7:
selectall();
break;
case 0:
flag=1;
break;
default:
continue;
}
}
EXEC SQL COMMIT WORK RELEASE;
return;
}
void errexit()
{
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
int updateYN()
{
char ch;
printf("Do you want to modify the value?(Y(Yes),ETC...(NO))=>");
fflush(stdout);
ch=toupper(getchar());
getchar();
return( ch == 'Y' ? 1 : 0 );
}
int asks(text,variable)
char text[],variable[];
{
printf(text);
fflush(stdout);
return( gets(variable) == (char *)0 ? EOF : strlen(variable) );
}
void createtable()
{
strsql.len = sprintf(strsql.arr,"CREATE TABLE %s (ID number(4),NAME VARCHAR(20),ADDRESS VARCHAR(30),PHONE CHAR(14),EMAIL VARCHAR(30),AGE NUMBER(3),primary key(ID))",tid.arr);
EXEC SQL EXECUTE IMMEDIATE :strsql;
if(sqlca.sqlcode!=0){
errrpt();
}else{
printf("%s\n\n",strsql.arr);
}
}
void droptable()
{
strsql.len = sprintf(strsql.arr,"DROP TABLE %s",tid.arr);
EXEC SQL EXECUTE IMMEDIATE :strsql;
if(sqlca.sqlcode!=0){
errrpt();
}else{
printf("DROP TABLE SUCCESS\n\n");
}
}
void insertinto()
{
int length=0;
while(1)
{
printf("INPUT ID\t:\t");
scanf("%d",&in_id);
getchar();
if ( in_id <= 0 )
continue;
length = asks("INPUT NAME\t:\t", (char *)in_name.arr);
if ( length <= 0 )
continue;
in_name.len = (short) length;
length = asks("INPUT ADDRESS\t:\t", (char *)in_addr.arr);
if ( length <= 0 )
continue;
in_addr.len = (short) length;
length = asks("INPUT PHONE\t:\t", (char *)in_phone.arr);
if ( length <= 0 )
continue;
in_phone.len = (short) length;
length = asks("INPUT EMAIL\t:\t", (char *)in_email.arr);
if ( length <= 0 )
continue;
in_email.len = (short) length;
printf("INPUT AGE\t:\t");
scanf("%d",&in_age);
getchar();
if ( in_age <= 0 )
continue;
break;
}
strsql.len = sprintf(strsql.arr,"INSERT INTO %s (ID,NAME,ADDRESS,PHONE,EMAIL,AGE) VALUES (%d,'%s','%s','%s','%s',%d)",tid.arr,in_id,in_name.arr,in_addr.arr,in_phone.arr,in_email.arr,in_age);
EXEC SQL EXECUTE IMMEDIATE :strsql;
if(sqlca.sqlcode!=0){
errrpt();
}else{
printf("INSERT INTO TABLE SUCCESS\n\n");
}
}
void deletefrom()
{
printf("INPUT ID\t:\t");
scanf("%d",&in_id);
getchar();
strsql.len = sprintf(strsql.arr,"DELETE FROM %s WHERE ID=%d",tid.arr,in_id);
EXEC SQL EXECUTE IMMEDIATE :strsql;
if(sqlca.sqlcode!=0){
errrpt();
}else{
printf("DELETE FROM TABLE SUCCESS\n\n");
}
}
void updatetable()
{
int change=0;
int length=0;
printf("INPUT ID\t:\t");
scanf("%d",&in_id);
getchar();
EXEC SQL WHENEVER SQLERROR DO errrpt();
strsql.len = sprintf(strsql.arr,"SELECT ID,NAME,ADDRESS,PHONE,EMAIL,AGE FROM %s WHERE ID= %d",tid.arr,in_id);
EXEC SQL PREPARE UP FROM :strsql;
EXEC SQL DECLARE cursor2 CURSOR FOR UP;
EXEC SQL OPEN cursor2;
EXEC SQL WHENEVER NOT FOUND GOTO nodept;
EXEC SQL FETCH cursor2
INTO :in_id,:in_name,:in_addr,:in_phone,:in_email,:in_age;
in_name.arr[in_name.len] = '\0';
in_addr.arr[in_addr.len] = '\0';
in_phone.arr[in_phone.len] = '\0';
in_email.arr[in_email.len] = '\0';
printf("\nCurrent Name\t:= %s\t\t\t",in_name.arr);
if(updateYN()==1)
{
while(1)
{
length = asks("INPUT NAME\t:\t", (char *)in_name.arr);
if ( length <= 0 )
continue;
in_name.len = (short) length;
break;
}
change=1;
}
printf("\nCurrent Address\t:= %s\t\t\t",in_addr.arr);
if(updateYN()==1)
{
while(1)
{
length = asks("INPUT New Value\t:= ", (char *)in_addr.arr);
if ( length <= 0 )
continue;
in_addr.len = (short) length;
break;
}
change=1;
}
printf("\nCurrent Phone\t:= %s\t\t\t",in_phone.arr);
if(updateYN()==1)
{
while(1)
{
length = asks("INPUT New Value\t:= ", (char *)in_phone.arr);
if ( length <= 0 )
continue;
in_phone.len = (short) length;
break;
}
change=1;
}
printf("\nCurrent Email\t:= %s\t\t\t",in_email.arr);
if(updateYN()==1)
{
while(1)
{
length = asks("INPUT New Value\t:= ", (char *)in_email.arr);
if ( length <= 0 )
continue;
in_email.len = (short) length;
break;
}
change=1;
}
printf("\nCurrent Age\t:= %d\t\t\t",in_age);
if(updateYN()==1)
{
while(1)
{
printf("INPUT New Value\t:= ");
scanf("%d",&in_age);
getchar();
if ( in_age <= 0 )
continue;
break;
}
change=1;
}
if(change == 1)
{
strsql.len = sprintf(strsql.arr,"UPDATE %s SET NAME='%s',ADDRESS='%s',PHONE='%s',EMAIL='%s',AGE=%d where ID=%d",tid.arr,in_name.arr,in_addr.arr,in_phone.arr,in_email.arr,in_age,in_id);
EXEC SQL EXECUTE IMMEDIATE :strsql;
if(sqlca.sqlcode!=0){
errrpt();
}else{
printf("UPDATE TABLE SUCCESS\n\n");
}
}
EXEC SQL CLOSE cursor2;
ret:
return;
nodept:
norows();
goto ret;
}
void selectfrom()
{
int rows=0;
printf("INPUT ID\t:\t");
scanf("%d",&in_id);
getchar();
printf("\n\nID\tNAME\t\tADDRESS\t\tPHONE\t\tEMAIL\t\tAGE\n");
printf("===============================================================================\n");
EXEC SQL WHENEVER SQLERROR DO errrpt();
strsql.len = sprintf(strsql.arr,"SELECT ID,NAME,ADDRESS,PHONE,EMAIL,AGE FROM %s WHERE ID= %d",tid.arr,in_id);
EXEC SQL PREPARE SEL1 FROM :strsql;
EXEC SQL DECLARE cursor3 CURSOR FOR SEL1;
EXEC SQL OPEN cursor3;
while(1)
{
EXEC SQL WHENEVER NOT FOUND GOTO nodept;
EXEC SQL FETCH cursor3
INTO :in_id,:in_name,:in_addr,:in_phone,:in_email,:in_age;
in_name.arr[in_name.len] = '\0';
in_addr.arr[in_addr.len] = '\0';
in_phone.arr[in_phone.len] = '\0';
in_email.arr[in_email.len] = '\0';
rows++;
printf("%-7d %-15s %-15s %-15s %-15s %-5d\n",in_id,in_name.arr,in_addr.arr,in_phone.arr,in_email.arr,in_age);
continue;
nodept:
printf("Query returned %d rows.\n\n",rows);
break;
}
EXEC SQL CLOSE cursor3;
}
void selectall()
{
int rows=0;
printf("\n\nID\tNAME\t\tADDRESS\t\tPHONE\t\tEMAIL\t\tAGE\n");
printf("===============================================================================\n");
EXEC SQL WHENEVER SQLERROR DO errrpt();
strsql.len = sprintf(strsql.arr,"SELECT ID,NAME,ADDRESS,PHONE,EMAIL,AGE FROM %s",tid.arr);
EXEC SQL PREPARE SEL2 FROM :strsql;
EXEC SQL DECLARE cursor1 CURSOR FOR SEL2;
EXEC SQL OPEN cursor1;
while(1)
{
EXEC SQL WHENEVER NOT FOUND DO break;
EXEC SQL FETCH cursor1
INTO :in_id,:in_name,:in_addr,:in_phone,:in_email,:in_age;
in_name.arr[in_name.len] = '\0';
in_addr.arr[in_addr.len] = '\0';
in_phone.arr[in_phone.len] = '\0';
in_email.arr[in_email.len] = '\0';
rows++;
printf("%-7d %-15s %-15s %-15s %-15s %-5d\n",in_id,in_name.arr,in_addr.arr,in_phone.arr,in_email.arr,in_age);
continue;
EXEC SQL WHENEVER NOT FOUND DO break;
}
printf("Query returned %d rows.\n\n",rows);
EXEC SQL CLOSE cursor1;
}
errrpt()
{
printf("%.70s (%d)\n", sqlca.sqlerrm.sqlerrmc, -sqlca.sqlcode);
errexit();
return(0);
}
norows()
{
printf("선택한 값을 가지고 있는 열이 없습니다.\n\n");
return(0);
}
|
