Storing and retrieving image data in Oracle
Date: Mon, 2 Aug 1993 15:45:00 GMT
Message-ID: <1993Aug2.154500.22477_at_template.com>
Step 1: Create a table entitled "document" with the following fields.
create table document (
doc_size char(50),
doc_data long raw
);
Step 2: Insert image data into and retrieve image data from a long raw field.
The following is a ORACLE pre-compiled program that inserts image data into and retrieves image data from a long raw field. The program will have to be run through the pre-compiler and then linked with the ORACLE libraries. The program is set up to read only one image entry per table. Follow the comments in the RetrieveImage static function for directions to handle mutliple images. BEGIN--------------------------------------------CUT HERE---------#include <stdio.h>
static void InsertImage();
static void RetrieveImage();
EXEC SQL INCLUDE SQLCA.H; EXEC SQL INCLUDE SQLDA.H; EXEC SQL BEGIN DECLARE SECTION;
VARCHAR sqlstat[200]; /* to hold SQL statements */ VARCHAR size[12]; /* you need to keep the size too! */ VARCHAR oracleUid[31]; VARCHAR oraclePwd[31]; int nitems; VARCHAR bufsize[15]; /* to get the stored size */ VARCHAR selstat[200];
EXEC SQL END DECLARE SECTION;
static SQLDA *bind_des; extern SQLDA *sqlald(); extern void sqlprc(); static SQLDA *select_des; extern SQLDA *sqlald(); extern void sqlnul(); static short null_ok; /*--------------------------------------------------------------*/
int
main( argc, argv)
int argc;
char **argv;
{
char infile[80];
char editor[80];
/* Obtain the image file name. */
fprintf( stdout, "Please enter the name of a ");
fprintf( stdout, "file that holds an image: \n");
fgets( infile, 80, stdin);
infile[strlen( infile) - 1] = '\0';
/* Obtain the image editor of preference. */
fprintf( stdout, "Please enter the name of the image editor ");
fprintf( stdout, "with complete command line arguments: \n");
fgets( editor, 80, stdin);
editor[strlen( editor) - 1] = '\0';
/* Insert the image into the long raw field in your Oracle table. */
InsertImage( infile);
/* Retrieve the image from the long raw field in your Oracle table. */
RetrieveImage( editor);
return 0;
}
/*-------------------------------------------------------------*/
static void
InsertImage( fname)
char *fname;
{
int i = 0; char *p = NULL; FILE *fp;
EXEC SQL WHENEVER SQLERROR GOTO sqlerror;
/* Connect to the ORACLE db using the generic user & password handles. */
strcpy( (char *) oracleUid.arr, "SCOTT");
oracleUid.len = (unsigned short) strlen( (char *) oracleUid.arr);
strcpy( (char *) oraclePwd.arr, "TIGER");
oraclePwd.len = (unsigned short) strlen( (char *) oraclePwd.arr);
EXEC SQL CONNECT :oracleUid IDENTIFIED BY :oraclePwd;
bind_des = sqlald( 2, 5, 5); /* allocate space for descriptors */ bind_des->N = 2;
sqlstat.len = sprintf( sqlstat.arr,
"INSERT INTO document (doc_size, doc_data) VALUES (:v1, :v2)");
EXEC SQL PREPARE sqlc1 FROM :sqlstat; EXEC SQL DECLARE sqlcursor1 CURSOR FOR sqlc1; EXEC SQL DESCRIBE BIND VARIABLES FOR sqlc1 INTO bind_des;
bind_des->N = bind_des->F;
/* allocate space on the descriptors for 64K of data */
bind_des->L[1] = 65536;
bind_des->V[1] = (char *) malloc( bind_des->L[1] + 1);
bind_des->I[1] = (short *) malloc( sizeof( short));
/* read the file byte by byte into the descriptor */
p = bind_des->V[1];
i = 0;
fp = fopen( fname,"r");
while( !feof( fp)) {
fread( p, sizeof( char), sizeof( char), fp);
i++;
p++;
}
fclose( fp);
i--;
bind_des->L[1] = i;
bind_des->I[1] = 0;
bind_des->T[1] = 24; /* this is the LONG RAW type */
/* fill descriptor for the size field */ size.len = sprintf( size.arr, "%d", i);
bind_des->L[0] = size.len; bind_des->V[0] = (char *) malloc( bind_des->L[0] + 1); bind_des->I[0] = (short *) malloc( sizeof( short));strcpy( bind_des->V[0], size.arr);
bind_des->I[0] = 0;
bind_des->T[0] = 1;
/* now process the cursor with the INSERT stat */ EXEC SQL OPEN sqlcursor1 USING DESCRIPTOR bind_des;
for( i = 0; i < bind_des->F; i++) {
free( bind_des->V[i]);
free( bind_des->I[i]);
}
sqlclu( bind_des);
EXEC SQL CLOSE sqlcursor1;
EXEC SQL COMMIT;
return;
sqlerror:
printf( "\n\n%.70s \n\n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK;
return;
}
/*------------------------------------------------------------------------*/
static void
RetrieveImage( editor)
char *editor;
{
int i = 0; FILE *fp; char *filename = NULL; long sizel; char command[120];
EXEC SQL WHENEVER SQLERROR GOTO sqlerror;
/*
You may want to expand the document table to have a entry that can be keyed upon. Right now this is set up to have only one image in the table. Problems will occur if there are more than one image in the table. The following is an example of how to build the select statement to key off of a title field in the document table.
EXEC SQL SELECT doc_size
INTO :bufsize
FROM document
WHERE doc_title LIKE :title;
bufsize.arr[bufsize.len] = '\0';
*/
select_des = sqlald( 2, 50, 50);
select_des->N = 2;
/* get the binary data according to a certain condition.
REPLACE SEARCH CONDITION. */
selstat.len = sprintf( selstat.arr,
"SELECT doc_data, doc_size FROM document");
EXEC SQL PREPARE sqlcom2 FROM :selstat;
EXEC SQL DECLARE sqlcursor2 CURSOR FOR sqlcom2;
EXEC SQL OPEN sqlcursor2;
EXEC SQL DESCRIBE SELECT LIST FOR sqlcom2 INTO select_des;
sqlnul(&(select_des->T[0]), &(select_des->T[0]), &null_ok);
select_des->N = select_des->F;
/* Reset length and datatypes */
select_des->L[1] = 50;
select_des->L[0] = 65535;
/* get the necessary space */
select_des->V[0] = (char *) malloc( select_des->L[0]);
select_des->I[0] = (short *) malloc( sizeof( short));
select_des->T[0] = 24;
/* get the necessary space */
select_des->V[1] = (char *) malloc( select_des->L[1]);
select_des->I[1] = (short *) malloc( sizeof( short));
select_des->T[1] = 1;
for( i = 0; i < 65536; i++) {
select_des->V[0][i] = 0;
}
EXEC SQL WHENEVER NOT FOUND GOTO notfound;
for(;;) {
EXEC SQL FETCH sqlcursor2 USING DESCRIPTOR select_des;
/* Used in the event of multiple image entries.
sscanf( bufsize.arr, "%d", &sizel); */
filename = tempnam( "/tmp", "Z"); /* create a temporary file */
/* Shove the data into the file. */
fp = fopen( filename, "w");
fwrite( select_des->V[0], sizeof( char), atoi( select_des->V[1]), fp);
fclose( fp);
/* This here just executes indicated image editor, like xv, with the file
as an argument. Remove the break statement for multiple images */
sprintf( command, "%s %s &", editor, filename);
system( command);
break;
}
notfound:
/* free allocated memory */
for( i = 0; i < select_des->F; i++) {
free( select_des->V[i]);
free( select_des->I[i]);
}
sqlclu( select_des);
EXEC SQL CLOSE sqlcursor2;
return;
sqlerror:
printf( "\n\n%.70s \n\n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK;
return;
}
END--------------------------------------------CUT HERE---------
-----------------
________
Mark A. Zurich \ / Template Software
zurich_at_template.com \ / 13100 Worldgate Drive, Suite 340
uunet!template!zurich \ / Herndon, VA 22070-4382
voice: 703-318-1000 \/ fax:703-318-7378
Received on Mon Aug 02 1993 - 17:45:00 CEST