Re: Storing IMAGES in an ORACLE DATABASE
Date: 1996/06/09
Message-ID: <31bb1487.2091617_at_dcsun4>
On Fri, 07 Jun 1996 15:02:16 +1100, Andrew Robb <arobb_at_kilo.uws.edu.au> wrote:
>I need to be able to store ID Photos in and Oracle Database. I am able
>to do this if I load the JPG image using a graphics package, copy it to
>the clipboard and then using power object paste it into the database
>field that I have set up as LONG RAW. This works fine.
>
>The problem I have is that there are some 10,000 of these images and I
>don't particularly want to sit down and cut and paste all of them.
>
>Could someone please give some suggestions as to how I could automate
>this. The images are stored on a PC in many directories. The images are
>all about 15K in size and as I said I have about 10,000 of them. I want
>to load these images along with other data into an Oracle database
>table.
>
>Any and all suggestions are appreciated. Thanks.
Without knowing more about your environment (db version, PC operating system, network, etc etc etc) I will offer up this small pro*c program. The pro*c app is what I use to 'bulk' load images into the database for later display using the web. It assumes a database table that looks like:
create table image ( name varchar2(255) primary key, img_size number, mime_type varchar2(80), image long raw );
You would change the pro*c code to suit your needs.... I've used this on NT and Unix but should run just fine as an 'easywin' type of application under windows 3.11 as well.
The usage: imgload userid=xxx/xxx mime=yyy/zzz name=aaa ifile=xxx size=nnn\n",
where....
userid- your username password
mime- the mime type you would want ass. with the image (eg: mime=image/jpg)
name- what the NAME column in the table will hold
ifile- name of input file
size- OPTIONAL, defaults to 32k, will size the buffer to read the file into
#include <stdio.h> #include <string.h> #include <ctype.h> #include <assert.h>
#define vstrcpy( a, b ) \
(strcpy( a.arr, b ), a.len = strlen( a.arr ), a.arr)
EXEC SQL BEGIN DECLARE SECTION;
static char * USERID = NULL; static char * MIME = NULL; static char * NAME = NULL; static char * IFILE = NULL; static char * SIZE = "32000";
EXEC SQL END DECLARE SECTION; #define SQLCA_INIT
EXEC SQL INCLUDE sqlca;
EXEC SQL BEGIN DECLARE SECTION;
int SQLCODE;
EXEC SQL END DECLARE SECTION;
static void process_parms( argc, argv )
int argc;
char * argv[];
{
int i;
for( i = 1; i < argc; i++ )
{
if ( !strncmp( argv[i], "userid=", 7 ) ) USERID = argv[i]+7;
else
if ( !strncmp( argv[i], "mime=", 5 ) ) MIME = argv[i]+5;
else
if ( !strncmp( argv[i], "name=", 5 ) ) NAME = argv[i]+5;
else
if ( !strncmp( argv[i], "ifile=", 6 ) ) IFILE = argv[i]+6;
else
if ( !strncmp( argv[i], "size=", 5 ) ) SIZE = argv[i]+5;
else
{
printf( "usage: %s userid=xxx/xxx\n", argv[0] );
exit(1);
}
}
if ( USERID == NULL || MIME == NULL || NAME == NULL || IFILE == NULL) {
printf(
"usage: %s userid=xxx/xxx mime=yyy/zzz name=aaa ifile=xxx size=nnn\n",
argv[0] );
exit(1);
}
}
static void sqlerror_hard()
{
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\nORACLE error detected:");
printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
static void process()
{
/* * This is the data structure we will use with our LONG RAWs. We will * always use pointers to this structure as we don't know how big we need * until runtime. Will use malloc to allocate storage on the fly. */
typedef struct TAGmy_raw
{
long len;
unsigned char arr[1];
}
my_raw;
/* * Use type equivalencing to tell Oracle that the C type "my_raw" is * equivalent to the Oracle type LONG VARRAW and can hold upto 5k * bytes of data (we will never allocate that much here, just an upper * bound, could be MUCH higher (eg: 10,000,000) */
EXEC SQL TYPE my_raw IS LONG VARRAW(100000000) REFERENCE; FILE * input;
my_raw * buffer;
long size ;
int i; int seqno; int real_size;
size = atoi( SIZE );
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
buffer = (my_raw *)malloc( size+sizeof(my_raw) );
input = fopen( IFILE, "rb" );
if ( input == NULL )
{
printf( "unable to open %s\n", IFILE );
exit(1);
}
EXEC SQL DELETE FROM IMAGE WHERE NAME = :NAME; buffer->len = real_size = fread( buffer->arr, 1, size, input ); printf( "Inserting blob of %d bytes\n", buffer->len );
EXEC SQL
INSERT INTO IMAGE ( name, mime_type, img_size, Image )
values ( :NAME, :MIME, :real_size, :buffer );
fclose( input );
EXEC SQL COMMIT WORK;
}
main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50];
EXEC SQL END DECLARE SECTION;
process_parms( argc, argv );
/* Connect to ORACLE. */
vstrcpy( oracleid, USERID );
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL CONNECT :oracleid;
printf("\nConnected to ORACLE as user: %s\n\n", oracleid.arr);
process();
/* Disconnect from ORACLE. */
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Sun Jun 09 1996 - 00:00:00 CEST