Re: Insert into LONG RAW?

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1996/08/11
Message-ID: <320d3fc5.1974409_at_dcsun4>

At the bottom you will find a pro*c program that loads long raws into a table.

On Fri, 09 Aug 96 14:06:33, rs6_at_inel.gov wrote:

>
>I'm trying how to figure out how to use the INSERT
>statement to load data into a LONG RAW column. My
>table TESTRS6 is as follows:
>
>ID VARCHAR2(8) NOT NULL
>DATA LONG RAW
>
>I've found that
>
>INSERT INTO TESTRS6 VALUES ('A1', HEXTORAW('41'));
>
>seems to work fine for one byte, but I really
>need to load a rather large binary file. I know
>there must be a way to do this, but can it be done
>without using some other database to load from?
>(That is, just from an INSERT statement.) Also, when
>I try to select the data from this table with a
>
>SELECT DATA FROM TESTRS6;
>
>I get a message that I'm trying to use incompatible
>data types, or something like that. I'd greatly
>appreciate any info you could pass along on this!
>
>
>-------------------------------------
>Name: Robert K. Smith
>E-mail: rs6_at_inel.gov
>(208) 526-5801
>

#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 100m
 * bytes of data (we will never allocate that much here, just an upper

• bound, could be MUCH higher (eg: 1 billion) */ 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 Aug 11 1996 - 00:00:00 CEST