|
LONG RAW DATA(IMAGE) 를 INSERT, SELECT 하기
============================================
다음은 static 하게 file size 를 주어 image data 를 insert 하는 방법과
dynamic 하게 file size 를 주어 insert 하는 방법, insert 한 data 를
select 해서 file 에 write 해서 확인하는 방법을 위한 내용이다.
image data의 file size 를 static 하게 주는 방법과 dynamic 하게 가져가는
방법이다.( LAST VERSION )
(1) static 하게 image 를 insert 하기
(2) dynamic 하게 file size 를 주어 insert 하기
(3) insert 한 image data를 file에 write 하기
(4) (3)의 output file 을 paint program 에서 열어 봄으로써 확인할 수 있다.
Table 이 다음과 같다고 가정하자.
SQL> desc rawtest
Name Null? Type
------------------------------- -------- ----
A VARCHAR2(10)
B LONG RAW
(1) static 하게 insert 하는 pro*c program
다음 예제는 file size 를 5000 으로 static 하게 가져 가는 방법이다.
#include
#define FILESIZE 5000
typedef struct TAGmy_raw {
long len;
unsigned char arr[1];
} my_raw;
VARCHAR username[20];
VARCHAR password[20];
EXEC SQL TYPE my_raw IS LONG VARRAW(1000000000) REFERENCE;
EXEC SQL INCLUDE sqlca;
my_raw *buffer;
void sqlerror();
/* handles unrecoverable errors */
FILE *fp;
main()
{
strcpy(username.arr, "KHPARK");
username.len = strlen(username.arr);
strcpy(password.arr, "KHPARK");
password.len = strlen(password.arr);
EXEC SQL WHENEVER SQLERROR DO sqlerror();
EXEC SQL CONNECT :username IDENTIFIED BY :password;
EXEC SQL WHENEVER SQLERROR DO sqlerror();
fp=fopen("startus.bmp","r");
if ( fp == NULL )
{
printf( "unable to open input file\n");
exit(1);
}
printf( "Input file opened\n");
printf("FILE%d\n", FILESIZE);
buffer = (my_raw *)malloc(sizeof(my_raw)+FILESIZE);
fread(buffer->arr, 1, FILESIZE, fp);
buffer->len = FILESIZE;
printf("finish");
EXEC SQL INSERT INTO RAWTEST VALUES ('111', :buffer);
EXEC SQL COMMIT WORK RELEASE;
free(buffer);
}
void sqlerror()
{ EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\nORACLE error detected:\n");
printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
(2) dynamic 하게 insert 하는 pro*c program
#include
typedef struct TAGmy_raw {
long len;
unsigned char arr[1];
} my_raw;
VARCHAR username[20];
VARCHAR password[20];
EXEC SQL TYPE my_raw IS LONG VARRAW(1000000000) REFERENCE;
EXEC SQL INCLUDE sqlca;
my_raw *buffer;
long FILESIZE;
void sqlerror(); /* handles unrecoverable errors */
FILE *fp;
main()
{
int res;
printf("well done");
strcpy(username.arr, "KHPARK");
username.len = strlen(username.arr);
strcpy(password.arr, "KHPARK");
password.len = strlen(password.arr);
EXEC SQL WHENEVER SQLERROR DO sqlerror();
EXEC SQL CONNECT :username IDENTIFIED BY :password;
EXEC SQL WHENEVER SQLERROR DO sqlerror();
fp=fopen("startus.bmp","r");
if ( fp == NULL )
{
printf( "unable to open input file\n");
exit(1);
}
printf( "Input file opened\n");
rewind(fp);
printf("well done");
res = fseek(fp,0, SEEK_END);
FILESIZE = ftell (fp);
printf("FILE%d\n", FILESIZE);
printf("well done");
buffer = (my_raw *)malloc(sizeof(my_raw)+FILESIZE);
memset(buffer,0, sizeof(my_raw)+FILESIZE);
res = fseek(fp,0,SEEK_SET);
fread(buffer->arr, 1, FILESIZE, fp);
buffer->len = FILESIZE;
printf("finish");
EXEC SQL INSERT INTO RAWTEST VALUES ('111', :buffer);
EXEC SQL COMMIT WORK RELEASE;
free(buffer);
}
void sqlerror()
{
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\nORACLE error detected:\n");
printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
(3) insert 된 data 를 select 후 file 에 write 하기
#include
#include
#include
EXEC SQL INCLUDE sqlca;
void sqlerror_hard();
int write_file(char*, char*, int);
short int ind;
short int ind1;
typedef struct
{
int len;
char buf[500000];
} long_varraw;
FILE* output;
char file_name[10];
char id[10];
EXEC SQL TYPE long_varraw IS LONG VARRAW(5000000);
char oracleid[50];
long_varraw o_buffer;
main()
{
int num_written;
EXEC SQL WHENEVER SQLERROR do sqlerror_hard();
strcpy(oracleid,"khpark/khpark");
EXEC SQL CONNECT :oracleid;
printf("\nConnected to ORACLE as user: %s\n\n", oracleid);
EXEC SQL DECLARE cur_image CURSOR FOR
select * from rawtest;
EXEC SQL OPEN cur_image;
EXEC SQL WHENEVER NOT FOUND do break;
for (;;)
{
EXEC SQL FETCH cur_image INTO :id :ind, :o_buffer:ind1;
strcpy(file_name, id);
strcat(file_name, ".out");
num_written = write_file(file_name, o_buffer.buf, o_buffer.len);
printf("%d", num_written);
}
EXEC SQL CLOSE cur_image;
exit(0);
}
void sqlerror_hard()
{
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\nORACLE error detected:");
printf("\n%s \n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
int write_file(filename, buf, bufsize)
char *filename, *buf;
int bufsize;
{
FILE *out_fd;/* File descriptor for the output file. */
size_t num_written; /* Number of bytes written. */
/* Open the file for writing. This command replaces
* any existing version. */
out_fd = fopen(filename, "w");
if (out_fd == (FILE *)0) {
/* Can't create the output file - return an error. */
return(0);
}
/* Write the contents of buf to the file. */
num_written = fwrite(buf, sizeof(char), bufsize, out_fd);
/* Close the file, and return the number of bytes written. */
fclose(out_fd);
return(num_written);
}
(4) 이를 paint brush 등에서 bmp file 을 open 해서 확인한다.
<주의 !>
64-bits machine의 경우에는 len의 type을 long에서 int로 바꾸어주어야 합니다.
typedef struct TAGmy_raw {
int len;
unsigned char arr[1];
} my_raw;
--------------------------------------------------------------------------------
Oracle Korea Customer Support Technical Bulletins
|
