컴퓨터이야기

물론 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.