================================================================================
/***
테스트 SAMPLE 테이블
CREATE TABLE SAMPLE_TABLE
(
USER_ID CHAR(10) NOT NULL,
USER_NAME CHAR(20) NOT NULL,
CONSTRAINT SAMPLE_TABLE_PK PRIMARY KEY(USER_ID) USING INDEX TABLESPACE SAMPLE_INDEX_TS
)TABLESPACE SAMPLE_TABLE_TS LOGGING;
***/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <errno.h>
#include <sys/types.h>
#include <sqlca.h>
#include <sqlda.h>
#include <sqlcpr.h>
EXEC SQL BEGIN DECLARE SECTION;
char dbusername[32] = { '\0' };
char dbpassword[32] = { '\0' };
EXEC SQL END DECLARE SECTION;
int sample_table_select(char *sUserId);
int sample_table_insert(char *sUserId, char *sUserName);
int sample_table_update(char *sUserId, char *sUserName);
int sample_table_fetch();
int sample_table_delete(char *sUserId, char *sUserName);
int main(int argc, char *argv[])
{
char sUserId [10+1] = { '\0' };
char sUserName[20+1] = { '\0' };
int nRet = 0;
// DB연결
memset(dbusername, 0x00, sizeof(dbusername));
sprintf(dbusername, "오라클DB계정");
memset(dbpassword, 0x00, sizeof(dbpassword));
sprintf(dbpassword, "오라클DB패스워드");
EXEC SQL CONNECT :dbusername IDENTIFIED BY :dbpassword;
if( sqlca.sqlcode != 0 ) {
fprintf(stdout,"ERROR_SQL_CONNECT_FAIL sqlca.sqlcode[%d]\n", sqlca.sqlcode);
exit(1);
}
// 테이블의 내용 FETCH - 여러개의 DATA 조회
fprintf(stdout, "start sample_table_fetch\n");
nRet = sample_table_fetch();
if( nRet < 0 ) {
goto END;
}
// 테이블의 내용 SELECT - 1개의 데이터 조회
fprintf(stdout, "start sample_table_select\n");
memset(sUserId, 0x00, sizeof(sUserId));
sprintf(sUserId, "test_id");
nRet = sample_table_select(sUserId);
if( nRet < 0 ) {
goto END;
}
// 테이블에 내용 입력 처리
fprintf(stdout, "start sample_table_insert\n");
memset(sUserId, 0x00, sizeof(sUserId));
sprintf(sUserId, "test_id1");
memset(sUserName, 0x00, sizeof(sUserName));
sprintf(sUserName, "test_name");
nRet = sample_table_insert(sUserId,sUserName);
if( nRet < 0 ) {
goto END;
}
// 테이블에 내용 입력 처리
fprintf(stdout, "start sample_table_insert\n");
memset(sUserId, 0x00, sizeof(sUserId));
sprintf(sUserId, "test_id2");
memset(sUserName, 0x00, sizeof(sUserName));
sprintf(sUserName, "test_name");
nRet = sample_table_insert(sUserId,sUserName);
if( nRet < 0 ) {
goto END;
}
// 테이블의 내용 FETCH - 여러개의 DATA 조회
fprintf(stdout, "start sample_table_fetch\n");
nRet = sample_table_fetch();
// 테이블의 내용 업데이트
fprintf(stdout, "start sample_table_update\n");
memset(sUserId, 0x00, sizeof(sUserId));
sprintf(sUserId, "test_id1");
memset(sUserName, 0x00, sizeof(sUserName));
sprintf(sUserName, "test_name2");
nRet = sample_table_update(sUserId,sUserName);
if( nRet < 0 ) {
goto END;
}
// 테이블의 내용 FETCH - 여러개의 DATA 조회
fprintf(stdout, "start sample_table_fetch\n");
nRet = sample_table_fetch();
if( nRet < 0 ) {
goto END;
}
// 테이블의 내용 삭제 처리
fprintf(stdout, "start sample_table_delete\n");
memset(sUserId, 0x00, sizeof(sUserId));
sprintf(sUserId, "test_id1");
memset(sUserName, 0x00, sizeof(sUserName));
sprintf(sUserName, "test_name2");
nRet = sample_table_delete(sUserId,sUserName);
if( nRet < 0 ) {
goto END;
}
// 테이블의 내용 FETCH - 여러개의 DATA 조회
fprintf(stdout, "start sample_table_fetch\n");
nRet = sample_table_fetch();
if( nRet < 0 ) {
goto END;
}
END:
// DB 접속종료 RELEASE
EXEC SQL COMMIT WORK RELEASE;
return 0;
}
/*** 오라클 UNIQUE데이타 SELECT ***/
int sample_table_select(char *sUserId)
{
EXEC SQL BEGIN DECLARE SECTION;
char rzUserId[11] = { '\0' };
char rzUserName[21] = { '\0' };
EXEC SQL END DECLARE SECTION;
int nRet = 0;
memset(rzUserId, 0x00, sizeof(rzUserId));
memcpy(rzUserId, sUserId, sizeof(rzUserId)-1 );
EXEC SQL
SELECT USER_ID, USER_NAME
INTO :rzUserId, :rzUserName
FROM SAMPLE_TABLE
WHERE USER_ID = 'test';
if(sqlca.sqlcode == 0) {
fprintf(stdout,"sqlca.sqlcode[%d] rzUserId[%s] rzUserName[%s]\n", sqlca.sqlcode, rzUserId, rzUserName);
nRet = 0;
}
else if( sqlca.sqlcode == 1403 ) {
fprintf(stdout,"sqlca.sqlcode[%d] DATA_NOT_EXIST!!! \n", sqlca.sqlcode);
nRet = -1;
}
else {
fprintf(stdout,"sqlca.sqlcode[%d] SQL_SELECT_QUERY_FAIL!!!\n", sqlca.sqlcode);
nRet = -1;
}
return nRet;
}
/*** 오라클 데이타 INSERT ***/
int sample_table_insert(char *sUserId, char *sUserName)
{
EXEC SQL BEGIN DECLARE SECTION;
char rzUserId[11] = { '\0' };
char rzUserName[21] = { '\0' };
EXEC SQL END DECLARE SECTION;
int nRet = 0;
memset(rzUserId, 0x00, sizeof(rzUserId));
memcpy(rzUserId, sUserId, sizeof(rzUserId)-1 );
memset(rzUserName, 0x00, sizeof(rzUserName));
memcpy(rzUserName, sUserName, sizeof(rzUserName)-1 );
EXEC SQL
INSERT INTO SAMPLE_TABLE(USER_ID, USER_NAME)
VALUES ( :rzUserId, :rzUserName );
if(sqlca.sqlcode == 0) {
fprintf(stdout,"sqlca.sqlcode[%d] rzUserId[%s] rzUserName[%s]\n", sqlca.sqlcode, rzUserId, rzUserName);
EXEC SQL commit;
nRet = 0;
}
else {
fprintf(stdout,"sqlca.sqlcode[%d] SQL_INSERT_QUERY_FAIL!!!\n", sqlca.sqlcode);
EXEC SQL rollback;
nRet = -1;
}
return nRet;
}
/*** 오라클 데이타 UPDATE ***/
int sample_table_update(char *sUserId, char *sUserName)
{
EXEC SQL BEGIN DECLARE SECTION;
char rzUserId[11] = { '\0' };
char rzUserName[21] = { '\0' };
EXEC SQL END DECLARE SECTION;
int nRet = 0;
memset(rzUserId, 0x00, sizeof(rzUserId));
memcpy(rzUserId, sUserId, sizeof(rzUserId)-1 );
memset(rzUserName, 0x00, sizeof(rzUserName));
memcpy(rzUserName, sUserName, sizeof(rzUserName)-1 );
EXEC SQL
UPDATE SAMPLE_TABLE
SET USER_NAME = :rzUserName
WHERE USER_ID = :rzUserId;
if(sqlca.sqlcode == 0) {
fprintf(stdout,"sqlca.sqlcode[%d] rzUserId[%s] rzUserName[%s]\n", sqlca.sqlcode, rzUserId, rzUserName);
EXEC SQL commit;
nRet = 0;
}
else {
fprintf(stdout,"sqlca.sqlcode[%d] SQL_UPDATE_QUERY_FAIL!!!\n", sqlca.sqlcode);
EXEC SQL rollback;
nRet = -1;
}
return nRet;
}
/*** 오라클 데이타 FETCH ***/
int sample_table_fetch()
{
EXEC SQL BEGIN DECLARE SECTION;
char rzUserId[11] = { '\0' };
char rzUserName[21] = { '\0' };
EXEC SQL END DECLARE SECTION;
int nRet = 0;
EXEC SQL DECLARE CUR_SAMPLE_SEL CURSOR FOR
SELECT USER_ID, USER_NAME
FROM SAMPLE_TABLE;
if(sqlca.sqlcode != 0) {
fprintf(stdout,"ERROR cursor_declare_fail sqlca.sqlcode[%d] \n", sqlca.sqlcode);
return -1;
}
EXEC SQL OPEN CUR_SAMPLE_SEL;
if(sqlca.sqlcode != 0) {
fprintf(stdout,"ERROR cursor_open_fail sqlca.sqlcode[%d] \n", sqlca.sqlcode);
return -1;
}
while(1) {
memset(rzUserId, 0x00, sizeof(rzUserId));
memset(rzUserName, 0x00, sizeof(rzUserName));
EXEC SQL FETCH CUR_SAMPLE_SEL INTO :rzUserId, :rzUserName;
if( sqlca.sqlcode == 0 ) {
fprintf(stdout, "FETCH rzUserId[%s] rzUserName[%s]\n", rzUserId, rzUserName);
}
else if( sqlca.sqlcode == 1403 ) {
break;
}
else {
fprintf(stdout, "ERROR_FETCH_FAIL sqlca.sqlcode[%d]\n", sqlca.sqlcode);
nRet = -1;
break;
}
}
EXEC SQL close CUR_SAMPLE_SEL;
return nRet;
}
/*** 오라클 데이타 DELETE ***/
int sample_table_delete(char *sUserId, char *sUserName)
{
EXEC SQL BEGIN DECLARE SECTION;
char rzUserId[11] = { '\0' };
char rzUserName[21] = { '\0' };
EXEC SQL END DECLARE SECTION;
int nRet = 0;
memset(rzUserId, 0x00, sizeof(rzUserId));
memcpy(rzUserId, sUserId, sizeof(rzUserId)-1 );
memset(rzUserName, 0x00, sizeof(rzUserName));
memcpy(rzUserName, sUserName, sizeof(rzUserName)-1 );
EXEC SQL
DELETE
FROM SAMPLE_TABLE
WHERE USER_ID = :rzUserId
AND USER_NAME = :rzUserName;
if(sqlca.sqlcode == 0) {
fprintf(stdout,"sqlca.sqlcode[%d] rzUserId[%s]\n", sqlca.sqlcode, rzUserId);
EXEC SQL commit;
nRet = 0;
}
else {
fprintf(stdout,"sqlca.sqlcode[%d] SQL_DELETE_QUERY_FAIL!!!\n", sqlca.sqlcode);
EXEC SQL rollback;
nRet = -1;
}
return nRet;
}
================================================================================
Makefile 샘플
================================================================================
================================================================================
================================================================================
#!/bin/sh
PROC = $(ORACLE_HOME)/bin/proc
CC = gcc
RM = rm -f
DEPEND = makedepend
LINT = lint
CFLAG = -g -DDEBUG -g -c -W -Wall -m64
PROC_INC = include=$(ORACLE_HOME)/precomp/public/ include=$(ORACLE_HOME)/rdbms/demo/ include=$(ORACLE_HOME)/rdbms/public/ include=$(ORACLE_HOME)/network/public/
INC_PATH = -I. -I./include -I$(ORACLE_HOME)/precomp/public/ -I$(ORACLE_HOME)/rdbms/demo/ -I$(ORACLE_HOME)/rdbms/public/ -I$(ORACLE_HOME)/network/public/
LIB_PATH = -L. -L$(ORACLE_HOME)/lib
LIBS = -lsocket -lnsl -lm -lclntsh -ldl -lm `cat $(ORACLE_HOME)/lib/sysliblist`
###############################################################################
PSRCS1 = proc_sample1.pc
SRCS1 = proc_sample1.c
OBJS1 = proc_sample1.o
AOUT1 = proc_sample1
DEST = .
###############################################################################
.SUFFIXES: .pc .c
.pc.c:
$(PROC) $* $(PROC_INC)
.c.o:
$(CC) -c -o $*.o $*.c $(INC_PATH)
###############################################################################
all: $(AOUT1) #install clean
all: $(AOUT1)
$(AOUT1): $(OBJS1)
$(CC) $(CFLAG) -o $(AOUT1) $(OBJS1) $(LIB_PATH) $(LIBS)
@echo "컴파일 : $(AOUT1) Complted!!!"
$(OBJS1): $(SRCS1)
$(CC) $(CFLAG) -c -o $*.o $*.c $(INC_PATH)
@echo "컴파일 : $(OBJS1) Complted!!!"
$(SRCS1): $(PSRCS1)
$(PROC) $* $(PROC_INC)
@echo "컴파일 : $(SRCS1) Complted!!!"
install:
\mv $(AOUT1) $(DEST)
echo "Install Complted!!!"
clean:
\rm -f $(SRCS1) $(OBJS1) $(AOUT1) core
#DO NOT DELETE THIS LINE -- make depend depends on it.