물론 sqlj라는게 있긴 하지만..저는 sqlj는 얼마전에 설명을 한번 했습니다.
그러나..sqlj보다는 pro*C가 수행능력이 훨씬 뛰어나고 더 많이 사용하고
있습니다.
그래서 우리는 pro*C를 하는 것입니다. 그런데 자바 프로젝트를 할 때
어떻게 pro*C 를 사용할 수 있을까 하는 의문이 생깁니다.
즉, 대량의 데이타 처리를 위해 우리는 그냥 java + JDBC, sqlj, pro*C 중
어느 것을 택할것인가에 대한 선택의 문제에 봉착하게 되는데..
우리는 그중에 pro*C를 선택한다는 것입니다.
java + JDBC, sqlj는 당연히 별 문제 없이 그냥 사용할 수 있는 단순한
문제 이므로 우리는 가장 사용하기 힘들지만 가장 많이 사용하는
pro*C를 사용하자는 것인데...자바와 C를 어케 인터페이스 하느냐?
그것이 바로 JNI 입니다. 즉, 바로 아래 게시물에 있는 java + C 인 것입니다.
그것을 좀더 제가 발전시켜 java (JNI) + pro*C를 구현해 보았습니다.
1. 환경 설정은 아래와 같다.
- jdk 는 오라클 인스톨시 사용한 것을 바로 사용함.
- vc 는 Visual Studio 6.0을 이용함.
- Oracle은 9i 사용
set JAVA_HOME=c:\oracle\ora92\jdk
set JDK_CLASSES=%JAVA_HOME%\lib\tools.jar;%JAVA_HOME%\lib\dt.jar
set CLASSPATH=%JDK_CLASSES%;.;
SET PATH=%PATh%;%JAVA_HOME%\bin;%JAVA_HOME%;
2. 작업 디렉토리는 c:\tmp\test 인데 Visual Studio(vc++)를 띄운 후
File메뉴-> New->Projects->Win32 Dynamic-Link Library 선택
project name을 test27으로 입력함 --> OK -->창이 바뀌면서 What kind of dll would like create? 에
An empty Dll project 선택 --> 확인
: 탐색기로 확인해 보라. C"\TMP\TEST\TEST27 디렉토리에 몇 개의 작업 파일이 생성되 있을 것이다.
3. 자바 파일(BackupBatch.java)을 생성한다.
public class BackupBatch {
static
{
System.out.println("=== library was loaded");
System.load("C:\\tmp\\test\\test27\\Debug\\test27.dll");
}
public native void startBackup();
public static void main(String[] args) throws Exception
{
new BackupBatch().startBackup();
}
}
4. javac BackupBatch.java
--> BackupBatch.class 생성
5. javah BackupBatch
--> BackupBatch.h 생성
6. vc에서
Project메뉴->Add to Project --> Files 하여 BackupBatch.h를 추가한다.
7. Pro* C파일을 생성한다.
/* MyBatchProc.pc */
/* 아래와 같은 두개의 테이블이 있는데 sales에서 stat로 데이터를 batch로 복사하는 프로그램
으로 구현함. sales는 10개 이상의 Data를 입력한 상태에서 테스트
SQL> desc sales;
이름 널? 유형
----------------------------------------- -------- ----------------
NO NOT NULL NUMBER
SALE_CODE NOT NULL NUMBER
AMOUNT NUMBER
SALE_DATE DATE
SALE_MAN VARCHAR2(200)
SQL> desc stat;
이름 널? 유형
----------------------------------------- -------- ----------------
NO NOT NULL NUMBER
SALE_CODE NOT NULL NUMBER
SALE_DATE VARCHAR2(20)
AMOUNT NUMBER
*/
#include <stdio.h>
#include <stdlib.h>
#include <sqlca.h>
#define ARR_LENGTH 10
char *username = "SCOTT";
char *password = "TIGER";
/* Declare a host structure tag. */
EXEC SQL INCLUDE sqlca;
struct {
int NO[ARR_LENGTH];
int SALE_CODE[ARR_LENGTH];
int AMOUNT[ARR_LENGTH];
VARCHAR SALE_DATE[ARR_LENGTH][20];
VARCHAR SALE_MAN[ARR_LENGTH][200];
} sale_rec;
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL END DECLARE SECTION;
void print_rows(int n);
void sql_error(char *msg);
int main()
{
int num_ret; /* number of rows returned */
int i;
/* Connect to ORACLE. */
EXEC SQL WHENEVER SQLERROR DO sql_error("Connect error:");
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\nConnected to ORACLE as user: %s\n", username);
EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle error:");
/* Declare a cursor for the FETCH. */
EXEC SQL DECLARE c1 CURSOR FOR
SELECT no, sale_code,amount,to_char(sale_date,'yyyy-mm-dd'),sale_man
FROM sales
where to_char(sale_date,'yyyymmdd') like '200303%' ;
EXEC SQL OPEN c1;
//위 쿼리에서 to_char(sale_date,'yyyymmdd') like '200303%'는 엄청
//문제있는 부분입니다. 실제 쿼리 튜닝시 반드시 수정해 주어야 합니다만...
//여기서의 문제에서는 제외하도록 합니다. (문제의 핵심은 튜닝을 얘기하는것이 아니라서)
//튜닝시 해결하는 방법은 임시테이블을 만들고 그 임시테이블에다
//insert 하는데 2003-03-01 ~ 2003-03-31까지를 insert한 후
//where sale_date in(select tmp_date from tmp_tbl)
//이런식으로 쿼리하도록 합니다. 음하하 쉽지요?
num_ret = 0;
/* Array fetch loop - ends when NOT FOUND becomes true. */
EXEC SQL WHENEVER NOT FOUND DO break;
for (;;)
{
printf("----------------------------------- 10개 단위로 fetch\n");
//만약 레코드가 100만건 정도 된다면 시스템에 따라 다르지만
// 보통 5000건 단위로 fetch 하는것이 대체로 유리함
//여기서는 10건 단위의 fetch
EXEC SQL FETCH c1 INTO :sale_rec;
/* Print however many rows were returned. */
print_rows(sqlca.sqlerrd[2] - num_ret);
num_ret = sqlca.sqlerrd[2]; /* Reset the number. */
for(i = 0; i < num_ret; i++)
sale_rec.SALE_DATE[i].arr[sale_rec.SALE_DATE[i].len] = 0x00;
EXEC SQL
INSERT INTO stat(no,sale_code,sale_date,amount)
VALUES ( :sale_rec.NO, :sale_rec.SALE_CODE, :sale_rec.SALE_DATE, :sale_rec.AMOUNT);
EXEC SQL COMMIT;
printf("================= insert ok");
}
/* Print remaining rows from last fetch, if any. */
if ((sqlca.sqlerrd[2] - num_ret) > 0)
print_rows(sqlca.sqlerrd[2] - num_ret);
EXEC SQL CLOSE c1;
printf("OK insert.\n\n\n");
/* Disconnect from the database. */
exit(0);
}
void sql_error(char *msg)
{
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\n%s", msg);
printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
void print_rows(int n)
{
int i;
printf("\nNumber sale_code sale_date");
printf("\n------ -------- -------\n");
for (i = 0; i < n; i++)
{
printf("%d %d %s\n", sale_rec.NO[i], sale_rec.SALE_CODE[i], sale_rec.SALE_DATE[i].arr);
}
}
8. vc에서
위에서 생성한 MyBatchProc.pc를 Project메뉴->Add to Project --> Files 하여 추가한다.
화면왼쪽의 FileView Tab에서 pc파일을 선택 후 마우스 오른쪽 클릭--> setting 선택
Custom Build 탭의 Command 부분에
proc parse=full iname="C:\tmp\test\test27\MyBatchProc.pc" include="c:\Program Files\Microsoft Visual Studio\vc98\include"
Outputs 부분에
oname="c:\tmp\test\test27\MyBatchProc.c"
라고 입력한후 ok를 클릭하고
다시 위에서 생성한 MyBatchProc.pc를 화면왼쪽의 FileView Tab에서 선택 후 마우스 오른쪽 클릭--> compile한다.
9. MyBatchProc.c를 더블클릭하여 Open한다.
아래가 proc를 precompile하여 생성한 c 파일로서 아래와 같이 편집한다.
/* 전체적으로 변경 부분은 3 부분이다.
1. header 추가
2. main 함수를 jni 함수로 변경
3. 원래 main함수의 exit(0)를 return으로 변경
*/
//////////////////////////////////////////////////////////////////////////////////////////////////
/* 이 부분은 c파일에 추가된 부분으로 jni를 사용하기 위해
신규로 추가해야 한다 */
#include <jni.h>
#include "BackupBatch.h"
#include <stdio.h>
//////////////////////////////////////////////////////////////////////////////////////////////////
/* Result Sets Interface */
#ifndef SQL_CRSR
# define SQL_CRSR
struct sql_cursor
{
unsigned int curocn;
void *ptr1;
void *ptr2;
unsigned int magic;
};
typedef struct sql_cursor sql_cursor;
typedef struct sql_cursor SQL_CURSOR;
#endif /* SQL_CRSR */
/* Thread Safety */
typedef void * sql_context;
typedef void * SQL_CONTEXT;
/* Object support */
struct sqltvn
{
unsigned char *tvnvsn;
unsigned short tvnvsnl;
unsigned char *tvnnm;
unsigned short tvnnml;
unsigned char *tvnsnm;
unsigned short tvnsnml;
};
typedef struct sqltvn sqltvn;
struct sqladts
{
unsigned int adtvsn;
unsigned short adtmode;
unsigned short adtnum;
sqltvn adttvn[1];
};
typedef struct sqladts sqladts;
static struct sqladts sqladt = {
1,1,0,
};
/* Binding to PL/SQL Records */
struct sqltdss
{
unsigned int tdsvsn;
unsigned short tdsnum;
unsigned char *tdsval[1];
};
typedef struct sqltdss sqltdss;
static struct sqltdss sqltds =
{
1,
0,
};
/* File name & Package Name */
struct sqlcxp
{
unsigned short fillen;
char filnam[34];
};
static const struct sqlcxp sqlfpn =
{
33,
"c:\\tmp\\test\\test27\\MyBatchProc.pc"
};
static unsigned int sqlctx = 2133591581;
static struct sqlexd {
unsigned int sqlvsn;
unsigned int arrsiz;
unsigned int iters;
unsigned int offset;
unsigned short selerr;
unsigned short sqlety;
unsigned int occurs;
const short *cud;
unsigned char *sqlest;
const char *stmt;
sqladts *sqladtp;
sqltdss *sqltdsp;
void **sqphsv;
unsigned int *sqphsl;
int *sqphss;
void **sqpind;
int *sqpins;
unsigned int *sqparm;
unsigned int **sqparc;
unsigned short *sqpadto;
unsigned short *sqptdso;
unsigned int sqlcmax;
unsigned int sqlcmin;
unsigned int sqlcincr;
unsigned int sqlctimeout;
unsigned int sqlcnowait;
int sqfoff;
unsigned int sqcmod;
unsigned int sqfmod;
void *sqhstv[5];
unsigned int sqhstl[5];
int sqhsts[5];
void *sqindv[5];
int sqinds[5];
unsigned int sqharm[5];
unsigned int *sqharc[5];
unsigned short sqadto[5];
unsigned short sqtdso[5];
} sqlstm = {12,5};
/* SQLLIB Prototypes */
extern void sqlcxt (void **, unsigned int *,
struct sqlexd *, const struct sqlcxp *);
extern void sqlcx2t(void **, unsigned int *,
struct sqlexd *, const struct sqlcxp *);
extern void sqlbuft(void **, char *);
extern void sqlgs2t(void **, char *);
extern void sqlorat(void **, unsigned int *, void *);
/* Forms Interface */
static const int IAPSUCC = 0;
static const int IAPFAIL = 1403;
static const int IAPFTL = 535;
extern void sqliem(char *, int *);
static const char *sq0002 =
"select no ,sale_code ,amount ,to_char(sale_date,'yyyy-mm-dd') ,sale_man fr\
om sales where to_char(sale_date,'yyyymmdd') like '200303%' ";
typedef struct { unsigned short len; unsigned char arr[1]; } VARCHAR;
typedef struct { unsigned short len; unsigned char arr[1]; } varchar;
/* cud (compilation unit data) array */
static const short sqlcud0[] =
{12,4130,846,0,0,
5,0,0,1,0,0,27,44,0,0,4,4,0,1,0,1,97,0,0,1,97,0,0,1,10,0,0,1,10,0,0,
36,0,0,2,145,0,9,56,0,0,0,0,0,1,0,
51,0,0,2,0,0,13,78,0,0,5,0,0,1,0,2,3,0,0,2,3,0,0,2,3,0,0,2,9,0,0,2,9,0,0,
86,0,0,3,73,0,3,86,0,0,4,4,0,1,0,1,3,0,0,1,3,0,0,1,9,0,0,1,3,0,0,
117,0,0,4,0,0,29,90,0,0,0,0,0,1,0,
132,0,0,2,0,0,15,100,0,0,0,0,0,1,0,
147,0,0,5,0,0,32,111,0,0,0,0,0,1,0,
};
#include <stdio.h>
#include <stdlib.h>
#include <sqlca.h>
#define ARR_LENGTH 10
char *username = "SCOTT";
char *password = "TIGER";
/* Declare a host structure tag. */
/* EXEC SQL INCLUDE sqlca;
*/
/*
* $Header: sqlca.h,v 1.3 1994/12/12 19:27:27 jbasu Exp $ sqlca.h
*/
/* Copyright (c) 1985,1986, 1998 by Oracle Corporation. */
/*
NAME
SQLCA : SQL Communications Area.
FUNCTION
Contains no code. Oracle fills in the SQLCA with status info
during the execution of a SQL stmt.
NOTES
**************************************************************
*** ***
*** This file is SOSD. Porters must change the data types ***
*** appropriately on their platform. See notes/pcport.doc ***
*** for more information. ***
*** ***
**************************************************************
If the symbol SQLCA_STORAGE_CLASS is defined, then the SQLCA
will be defined to have this storage class. For example:
#define SQLCA_STORAGE_CLASS extern
will define the SQLCA as an extern.
If the symbol SQLCA_INIT is defined, then the SQLCA will be
statically initialized. Although this is not necessary in order
to use the SQLCA, it is a good pgming practice not to have
unitialized variables. However, some C compilers/OS's don't
allow automatic variables to be init'd in this manner. Therefore, if you are INCLUDE'ing the SQLCA in a place where it would be
an automatic AND your C compiler/OS doesn't allow this style
of initialization, then SQLCA_INIT should be left undefined --
all others can define SQLCA_INIT if they wish.
If the symbol SQLCA_NONE is defined, then the SQLCA variable will
not be defined at all. The symbol SQLCA_NONE should not be defined
in source modules that have embedded SQL. However, source modules
that have no embedded SQL, but need to manipulate a sqlca struct
passed in as a parameter, can set the SQLCA_NONE symbol to avoid
creation of an extraneous sqlca variable.
MODIFIED
lvbcheng 07/31/98 - long to int
jbasu 12/12/94 - Bug 217878: note this is an SOSD file
losborne 08/11/92 - No sqlca var if SQLCA_NONE macro set
Clare 12/06/84 - Ch SQLCA to not be an extern.
Clare 10/21/85 - Add initialization.
Bradbury 01/05/86 - Only initialize when SQLCA_INIT set
Clare 06/12/86 - Add SQLCA_STORAGE_CLASS option.
*/
#ifndef SQLCA
#define SQLCA 1
struct sqlca
{
/* ub1 */ char sqlcaid[8];
/* b4 */ int sqlabc;
/* b4 */ int sqlcode;
struct
{
/* ub2 */ unsigned short sqlerrml;
/* ub1 */ char sqlerrmc[70];
} sqlerrm;
/* ub1 */ char sqlerrp[8];
/* b4 */ int sqlerrd[6];
/* ub1 */ char sqlwarn[8];
/* ub1 */ char sqlext[8];
};
#ifndef SQLCA_NONE
#ifdef SQLCA_STORAGE_CLASS
SQLCA_STORAGE_CLASS struct sqlca sqlca
#else
struct sqlca sqlca
#endif
#ifdef SQLCA_INIT
= {
{'S', 'Q', 'L', 'C', 'A', ' ', ' ', ' '},
sizeof(struct sqlca),
0,
{ 0, {0}},
{'N', 'O', 'T', ' ', 'S', 'E', 'T', ' '},
{0, 0, 0, 0, 0, 0},
{0, 0, 0, 0, 0, 0, 0, 0},
{0, 0, 0, 0, 0, 0, 0, 0}
}
#endif
;
#endif
#endif
/* end SQLCA */
/* */
/*
* $Header: sqlca.h,v 1.3 1994/12/12 19:27:27 jbasu Exp $ sqlca.h
*/
/* Copyright (c) 1985,1986, 1998 by Oracle Corporation. */
/*
NAME
SQLCA : SQL Communications Area.
FUNCTION
Contains no code. Oracle fills in the SQLCA with status info
during the execution of a SQL stmt.
NOTES
**************************************************************
*** ***
*** This file is SOSD. Porters must change the data types ***
*** appropriately on their platform. See notes/pcport.doc ***
*** for more information. ***
*** ***
**************************************************************
If the symbol SQLCA_STORAGE_CLASS is defined, then the SQLCA
will be defined to have this storage class. For example:
#define SQLCA_STORAGE_CLASS extern
will define the SQLCA as an extern.
If the symbol SQLCA_INIT is defined, then the SQLCA will be
statically initialized. Although this is not necessary in order
to use the SQLCA, it is a good pgming practice not to have
unitialized variables. However, some C compilers/OS's don't
allow automatic variables to be init'd in this manner. Therefore,
if you are INCLUDE'ing the SQLCA in a place where it would be
an automatic AND your C compiler/OS doesn't allow this style
of initialization, then SQLCA_INIT should be left undefined --
all others can define SQLCA_INIT if they wish.
If the symbol SQLCA_NONE is defined, then the SQLCA variable will
not be defined at all. The symbol SQLCA_NONE should not be defined
in source modules that have embedded SQL. However, source modules
that have no embedded SQL, but need to manipulate a sqlca struct
passed in as a parameter, can set the SQLCA_NONE symbol to avoid
creation of an extraneous sqlca variable.
MODIFIED
lvbcheng 07/31/98 - long to int
jbasu 12/12/94 - Bug 217878: note this is an SOSD file
losborne 08/11/92 - No sqlca var if SQLCA_NONE macro set
Clare 12/06/84 - Ch SQLCA to not be an extern.
Clare 10/21/85 - Add initialization.
Bradbury 01/05/86 - Only initialize when SQLCA_INIT set
Clare 06/12/86 - Add SQLCA_STORAGE_CLASS option.
*/
#ifndef SQLCA
#define SQLCA 1
struct sqlca
{
/* ub1 */ char sqlcaid[8];
/* b4 */ int sqlabc;
/* b4 */ int sqlcode;
struct
{
/* ub2 */ unsigned short sqlerrml;
/* ub1 */ char sqlerrmc[70];
} sqlerrm;
/* ub1 */ char sqlerrp[8];
/* b4 */ int sqlerrd[6];
/* ub1 */ char sqlwarn[8];
/* ub1 */ char sqlext[8];
};
#ifndef SQLCA_NONE
#ifdef SQLCA_STORAGE_CLASS
SQLCA_STORAGE_CLASS struct sqlca sqlca
#else
struct sqlca sqlca
#endif
#ifdef SQLCA_INIT
= {
{'S', 'Q', 'L', 'C', 'A', ' ', ' ', ' '},
sizeof(struct sqlca),
0,
{ 0, {0}},
{'N', 'O', 'T', ' ', 'S', 'E', 'T', ' '},
{0, 0, 0, 0, 0, 0},
{0, 0, 0, 0, 0, 0, 0, 0},
{0, 0, 0, 0, 0, 0, 0, 0}
}
#endif
;
#endif
#endif
/* end SQLCA */
struct
{
int NO[ARR_LENGTH];
int SALE_CODE[ARR_LENGTH];
int AMOUNT[ARR_LENGTH];
/* VARCHAR SALE_DATE[ARR_LENGTH][20]; */
struct { unsigned short len; unsigned char arr[22]; } SALE_DATE[10];
/* VARCHAR SALE_MAN[ARR_LENGTH][200]; */
struct { unsigned short len; unsigned char arr[202]; } SALE_MAN[10];
} sale_rec;
/* EXEC SQL BEGIN DECLARE SECTION; */
/* EXEC SQL END DECLARE SECTION; */ void print_rows(int n);
void sql_error(char *msg);
//////////////////////////////////////////////////////////////////////////////////////////////////////////
/* 이 부분이 핵심이다.
원래 이 부분은 int main(){ 이렇게 되어 있는 c 파일의 메인 함수
부분이였다. 그러나, JNI를 사용하기 위한 메소드 부분으로 교체해 주어야
한다. 즉,int main(){ 대신에 아래의 한 라인으로만 대체하면 끝난다.
JNIEXPORT void JNICALL Java_BackupBatch_startBackup(JNIEnv *env, jobject obj) {
///////////////////////////////////////////////////////////////////////////////////////////////////////////
int num_ret; /* number of rows returned */
int i;
/* Connect to ORACLE. */
/* EXEC SQL WHENEVER SQLERROR DO sql_error("Connect error:"); */
/* EXEC SQL CONNECT :username IDENTIFIED BY :password; */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 4;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int )10;
sqlstm.offset = (unsigned int )5;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)256;
sqlstm.occurs = (unsigned int )0;
sqlstm.sqhstv[0] = ( void *)username;
sqlstm.sqhstl[0] = (unsigned int )0;
sqlstm.sqhsts[0] = ( int )0;
sqlstm.sqindv[0] = ( void *)0;
sqlstm.sqinds[0] = ( int )0;
sqlstm.sqharm[0] = (unsigned int )0;
sqlstm.sqadto[0] = (unsigned short )0;
sqlstm.sqtdso[0] = (unsigned short )0;
sqlstm.sqhstv[1] = ( void *)password;
sqlstm.sqhstl[1] = (unsigned int )0;
sqlstm.sqhsts[1] = ( int )0;
sqlstm.sqindv[1] = ( void *)0;
sqlstm.sqinds[1] = ( int )0;
sqlstm.sqharm[1] = (unsigned int )0;
sqlstm.sqadto[1] = (unsigned short )0;
sqlstm.sqtdso[1] = (unsigned short )0;
sqlstm.sqphsv = sqlstm.sqhstv;
sqlstm.sqphsl = sqlstm.sqhstl;
sqlstm.sqphss = sqlstm.sqhsts;
sqlstm.sqpind = sqlstm.sqindv;
sqlstm.sqpins = sqlstm.sqinds;
sqlstm.sqparm = sqlstm.sqharm;
sqlstm.sqparc = sqlstm.sqharc;
sqlstm.sqpadto = sqlstm.sqadto;
sqlstm.sqptdso = sqlstm.sqtdso;
sqlstm.sqlcmax = (unsigned int )100;
sqlstm.sqlcmin = (unsigned int )2;
sqlstm.sqlcincr = (unsigned int )1;
sqlstm.sqlctimeout = (unsigned int )0;
sqlstm.sqlcnowait = (unsigned int )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
if (sqlca.sqlcode < 0) sql_error("Connect error:");
}
printf("\nConnected to ORACLE as user: %s\n", username);
/* EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle error:"); */ /* Declare a cursor for the FETCH. */
/* EXEC SQL DECLARE c1 CURSOR FOR
SELECT no, sale_code,amount,to_char(sale_date,'yyyy-mm-dd'),sale_man
FROM sales
where to_char(sale_date,'yyyymmdd') like '200303%' ; */
/* EXEC SQL OPEN c1; */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 4;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.stmt = sq0002;
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )36;
sqlstm.selerr = (unsigned short)1;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)256;
sqlstm.occurs = (unsigned int )0;
sqlstm.sqcmod = (unsigned int )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
if (sqlca.sqlcode < 0) sql_error("Oracle error:");
}
//위 쿼리에서 to_char(sale_date,'yyyymmdd') like '200303%'는 엄청
//문제있는 부분입니다. 실제 쿼리 튜닝시 반드시 수정해 주어야 합니다만...
//여기서의 문제에서는 제외하도록 합니다. (문제의 핵심은 튜닝을 얘기하는것이 아니라서)
//튜닝시 해결하는 방법은 임시테이블을 만들고 그 임시테이블에다
//insert 하는데 2003-03-01 ~ 2003-03-31까지를 insert한 후
//where sale_date in(select tmp_date from tmp_tbl)
//이런식으로 쿼리하도록 합니다. 음하하 쉽지요?
num_ret = 0;
/* Array fetch loop - ends when NOT FOUND becomes true. */
/* EXEC SQL WHENEVER NOT FOUND DO break; */
for (;;)
{
printf("----------------------------------- 10개 단위로 fetch\n");
//만약 레코드가 100만건 정도 된다면 시스템에 따라 다르지만
// 보통 5000건 단위로 fetch 하는것이 대체로 유리함
//여기서는 10건 단위의 fetch
/* EXEC SQL FETCH c1 INTO :sale_rec; */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 5;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int )10;
sqlstm.offset = (unsigned int )51;
sqlstm.selerr = (unsigned short)1;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)256;
sqlstm.occurs = (unsigned int )0;
sqlstm.sqfoff = ( int )0;
sqlstm.sqfmod = (unsigned int )2;
sqlstm.sqhstv[0] = ( void *)sale_rec.NO;
sqlstm.sqhstl[0] = (unsigned int )sizeof(int);
sqlstm.sqhsts[0] = ( int )sizeof(int);
sqlstm.sqindv[0] = ( void *)0;
sqlstm.sqinds[0] = ( int )0;
sqlstm.sqharm[0] = (unsigned int )0;
sqlstm.sqharc[0] = (unsigned int *)0;
sqlstm.sqadto[0] = (unsigned short )0;
sqlstm.sqtdso[0] = (unsigned short )0;
sqlstm.sqhstv[1] = ( void *)sale_rec.SALE_CODE;
sqlstm.sqhstl[1] = (unsigned int )sizeof(int);
sqlstm.sqhsts[1] = ( int )sizeof(int);
sqlstm.sqindv[1] = ( void *)0;
sqlstm.sqinds[1] = ( int )0;
sqlstm.sqharm[1] = (unsigned int )0;
sqlstm.sqharc[1] = (unsigned int *)0;
sqlstm.sqadto[1] = (unsigned short )0;
sqlstm.sqtdso[1] = (unsigned short )0;
sqlstm.sqhstv[2] = ( void *)sale_rec.AMOUNT;
sqlstm.sqhstl[2] = (unsigned int )sizeof(int);
sqlstm.sqhsts[2] = ( int )sizeof(int);
sqlstm.sqindv[2] = ( void *)0;
sqlstm.sqinds[2] = ( int )0;
sqlstm.sqharm[2] = (unsigned int )0;
sqlstm.sqharc[2] = (unsigned int *)0;
sqlstm.sqadto[2] = (unsigned short )0;
sqlstm.sqtdso[2] = (unsigned short )0;
sqlstm.sqhstv[3] = ( void *)sale_rec.SALE_DATE;
sqlstm.sqhstl[3] = (unsigned int )22;
sqlstm.sqhsts[3] = ( int )24;
sqlstm.sqindv[3] = ( void *)0;
sqlstm.sqinds[3] = ( int )0;
sqlstm.sqharm[3] = (unsigned int )0;
sqlstm.sqharc[3] = (unsigned int *)0;
sqlstm.sqadto[3] = (unsigned short )0;
sqlstm.sqtdso[3] = (unsigned short )0;
sqlstm.sqhstv[4] = ( void *)sale_rec.SALE_MAN;
sqlstm.sqhstl[4] = (unsigned int )202;
sqlstm.sqhsts[4] = ( int )204;
sqlstm.sqindv[4] = ( void *)0;
sqlstm.sqinds[4] = ( int )0;
sqlstm.sqharm[4] = (unsigned int )0;
sqlstm.sqharc[4] = (unsigned int *)0;
sqlstm.sqadto[4] = (unsigned short )0;
sqlstm.sqtdso[4] = (unsigned short )0;
sqlstm.sqphsv = sqlstm.sqhstv;
sqlstm.sqphsl = sqlstm.sqhstl;
sqlstm.sqphss = sqlstm.sqhsts;
sqlstm.sqpind = sqlstm.sqindv;
sqlstm.sqpins = sqlstm.sqinds;
sqlstm.sqparm = sqlstm.sqharm;
sqlstm.sqparc = sqlstm.sqharc;
sqlstm.sqpadto = sqlstm.sqadto;
sqlstm.sqptdso = sqlstm.sqtdso;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
if (sqlca.sqlcode == 1403) break;
if (sqlca.sqlcode < 0) sql_error("Oracle error:");
}
/* Print however many rows were returned. */
print_rows(sqlca.sqlerrd[2] - num_ret);
num_ret = sqlca.sqlerrd[2]; /* Reset the number. */
for(i = 0; i < num_ret; i++)
sale_rec.SALE_DATE[i].arr[sale_rec.SALE_DATE[i].len] = 0x00;
/* EXEC SQL
INSERT INTO stat(no,sale_code,sale_date,amount)
VALUES ( :sale_rec.NO, :sale_rec.SALE_CODE, :sale_rec.SALE_DATE, :sale_rec.AMOUNT); */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 5;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.stmt = "insert into stat (no,sale_code,sale_date,amount) va\
lues (:b0,:b1,:b2,:b3)";
sqlstm.iters = (unsigned int )10;
sqlstm.offset = (unsigned int )86;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)256;
sqlstm.occurs = (unsigned int )0;
sqlstm.sqhstv[0] = ( void *)(sale_rec.NO);
sqlstm.sqhstl[0] = (unsigned int )sizeof(int);
sqlstm.sqhsts[0] = ( int )sizeof(int);
sqlstm.sqindv[0] = ( void *)0;
sqlstm.sqinds[0] = ( int )0;
sqlstm.sqharm[0] = (unsigned int )0;
sqlstm.sqharc[0] = (unsigned int *)0;
sqlstm.sqadto[0] = (unsigned short )0;
sqlstm.sqtdso[0] = (unsigned short )0;
sqlstm.sqhstv[1] = ( void *)(sale_rec.SALE_CODE);
sqlstm.sqhstl[1] = (unsigned int )sizeof(int);
sqlstm.sqhsts[1] = ( int )sizeof(int);
sqlstm.sqindv[1] = ( void *)0;
sqlstm.sqinds[1] = ( int )0;
sqlstm.sqharm[1] = (unsigned int )0;
sqlstm.sqharc[1] = (unsigned int *)0;
sqlstm.sqadto[1] = (unsigned short )0;
sqlstm.sqtdso[1] = (unsigned short )0;
sqlstm.sqhstv[2] = ( void *)(sale_rec.SALE_DATE);
sqlstm.sqhstl[2] = (unsigned int )22;
sqlstm.sqhsts[2] = ( int )24;
sqlstm.sqindv[2] = ( void *)0;
sqlstm.sqinds[2] = ( int )0;
sqlstm.sqharm[2] = (unsigned int )0;
sqlstm.sqharc[2] = (unsigned int *)0;
sqlstm.sqadto[2] = (unsigned short )0;
sqlstm.sqtdso[2] = (unsigned short )0;
sqlstm.sqhstv[3] = ( void *)(sale_rec.AMOUNT);
sqlstm.sqhstl[3] = (unsigned int )sizeof(int);
sqlstm.sqhsts[3] = ( int )sizeof(int);
sqlstm.sqindv[3] = ( void *)0;
sqlstm.sqinds[3] = ( int )0;
sqlstm.sqharm[3] = (unsigned int )0;
sqlstm.sqharc[3] = (unsigned int *)0;
sqlstm.sqadto[3] = (unsigned short )0;
sqlstm.sqtdso[3] = (unsigned short )0;
sqlstm.sqphsv = sqlstm.sqhstv;
sqlstm.sqphsl = sqlstm.sqhstl;
sqlstm.sqphss = sqlstm.sqhsts;
sqlstm.sqpind = sqlstm.sqindv;
sqlstm.sqpins = sqlstm.sqinds;
sqlstm.sqparm = sqlstm.sqharm;
sqlstm.sqparc = sqlstm.sqharc;
sqlstm.sqpadto = sqlstm.sqadto;
sqlstm.sqptdso = sqlstm.sqtdso;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
if (sqlca.sqlcode == 1403) break;
if (sqlca.sqlcode < 0) sql_error("Oracle error:");
}
/* EXEC SQL COMMIT; */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 5;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )117;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)256;
sqlstm.occurs = (unsigned int )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
if (sqlca.sqlcode < 0) sql_error("Oracle error:");
}
printf("================= insert ok");
}
/* Print remaining rows from last fetch, if any. */
if ((sqlca.sqlerrd[2] - num_ret) > 0)
print_rows(sqlca.sqlerrd[2] - num_ret);
/* EXEC SQL CLOSE c1; */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 5;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )132;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)256;
sqlstm.occurs = (unsigned int )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
if (sqlca.sqlcode < 0) sql_error("Oracle error:");
} printf("OK insert.\n\n\n");
/* Disconnect from the database. */
////////////////////////////////////////////////////////////////////////////////////
/* 원래 proc로부터 생성된 C파일에서는 exit(0)로 되어 있지만
여기서는 return으로 변경한다. */
return;
////////////////////////////////////////////////////////////////////////////////////
}
void sql_error(char *msg)
{
/* EXEC SQL WHENEVER SQLERROR CONTINUE; */
printf("\n%s", msg);
printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
/* EXEC SQL ROLLBACK WORK RELEASE; */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 5;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )147;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)256;
sqlstm.occurs = (unsigned int )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
} exit(1);
}
void print_rows(int n)
{
int i;
printf("\nNumber sale_code sale_date");
printf("\n------ -------- -------\n");
for (i = 0; i < n; i++)
{
printf("%d %d %s\n", sale_rec.NO[i],
sale_rec.SALE_CODE[i],
sale_rec.SALE_DATE[i].arr);
}
}
10. 이때 반드시 File View 탭에서 MyBatchProc.pc를 선택 --> 마우스 오른쪽 클릭 --> setting 클릭하여 output 설정 부분을 삭제하도록 한다. 즉, proc파일이 precompile되면서 c 파일이 Rebuild 되어 위에서 수정한 c 파일 날아가지 않도록 조치한다.
11. Tools메뉴 -> Option -> Directory탭에서 C:\ORACLE\ORA92\JDK\include 추가하고, C:\ORACLE\ORA92\JDK\include\win32 추가한다. 만약 jdk를 별도로 설치한 것을 사용한다면 해당 디렉토리를 추가하도록 한다.
12. File View 탭에서 Project를 선택(여기서는 test27 files라고 표시된 부분임) 한 후 --> 마우스 오른쪽 클릭 -->setting 클릭 --> 모달 창이 하나 나타나면 왼쪽 부분의 settings for 부분에 'all configurations'을 선택하고 오른쪽 탭은 Link를 선택하여 Object/Library modules 부분에 orasql9.lib(oracle 9i인 경우 임)를 추가적으로 입력후 ok 클릭
13. File View 탭에서 MyBatchProc.c를 선택 후 --> 마우스 오른쪽 클릭 --> compile
14. Build메뉴--> build test27.dll을 선택하여 test27.dll을 생성한다.
--> 탐색기로 c:\tmp\test\test27\Debug에 test27.dll이 생성되었는지 확인하고
--> 도스창에서 java HelloWorld를 실행한다.
C:\tmp\test>cd test27
C:\tmp\test\test27>dir
C 드라이브의 볼륨에는 이름이 없습니다.
볼륨 일련 번호: 2749-1605
C:\tmp\test\test27 디렉터리
2003.04.09 오후 07:31 <DIR> .
2003.04.09 오후 07:31 <DIR> ..
2003.04.09 오후 07:31 535 test27.dsw
2003.04.09 오후 09:49 50,176 test27.ncb
2003.04.09 오후 07:31 <DIR> Debug
2003.04.09 오후 07:32 402 BackupBatch.java
2003.04.09 오후 07:32 654 BackupBatch.class
2003.04.09 오후 07:32 406 BackupBatch.h
2003.04.09 오후 07:33 302 BackupBatch.c.BAK
2003.04.09 오후 07:34 302 BackupBatch.c
2003.04.09 오후 07:41 3,720 MyBatchProc.pc.BAK
2003.04.09 오후 07:46 3,663 MyBatchProc.pc
2003.04.09 오후 09:48 828 test27.plg
2003.04.09 오후 07:53 24,626 MyBatchProc.c.BAK
2003.04.09 오후 07:53 25,028 MyBatchProc.c
2003.04.09 오후 09:49 4,608 test27.dsp
2003.04.09 오후 09:49 48,640 test27.opt
14개 파일 163,890 바이트
3개 디렉터리 15,563,145,216 바이트 남음
C:\tmp\test\test27>cd Debug
C:\tmp\test\test27\Debug>dir
C 드라이브의 볼륨에는 이름이 없습니다.
볼륨 일련 번호: 2749-1605
C:\tmp\test\test27\Debug 디렉터리
2003.04.09 오후 07:31 <DIR> .
2003.04.09 오후 07:31 <DIR> ..
2003.04.09 오후 11:52 33,792 vc60.idb
2003.04.09 오후 07:53 61,440 vc60.pdb
2003.04.09 오후 07:53 14,205 MyBatchProc.obj
2003.04.09 오후 09:48 533,504 test27.pdb
2003.04.09 오후 07:53 218,964 test27.pch
2003.04.09 오후 09:48 2,026 test27.lib
2003.04.09 오후 09:48 619 test27.exp
2003.04.09 오후 09:48 263,364 test27.ilk
2003.04.09 오후 09:48 208,948 test27.dll
9개 파일 1,336,862 바이트
2개 디렉터리 15,563,128,832 바이트 남음
C:\tmp\test\test27\Debug>cd ..
C:\tmp\test\test27>java BackupBatch
=== library was loaded
Connected to ORACLE as user: SCOTT
----------------------------------- 10개 단위로 fetch
Number sale_code sale_date
------ -------- -------
1 1 2003-03-31
1 2 2003-03-31
1 3 2003-03-31
2 1 2003-03-31
2 2 2003-03-31
2 3 2003-03-31
2 4 2003-03-31
3 1 2003-03-31
3 2 2003-03-31
4 1 2003-03-31
================= insert ok----------------------------------- 10개 단위로 fetch
Number sale_code sale_date
------ -------- -------
5 1 2003-03-31
5 2 2003-03-31
OK insert.
http://blog.naver.com/st95041/40001613429 
RECENT COMMENT