Re: How Do You Insert Long Raw Data?
Date: 1996/01/26
Message-ID: <4e9c3a$1gs_at_inet-nntp-gw-1.us.oracle.com>
At the bottom of this email is an example program that
- creates a table with a long raw column - inserts a long raw into - commits - fetches it back out to verify it is the same as it was when it went in - drops the table
Hope this helps...
robert.goodwin_at_msfc.nasa.gov (Robert Goodwin) wrote:
>In article <3107D13E.3A78_at_bearriver.com>,
> Allan Hoeltje <ahoeltje_at_bearriver.com> wrote:
>>I posted this before but got no answer. Since I think it is a valid
>>question and one which should be covered in an Oracle manual but isn't,
>>I am re-posting it in the hope that MAYBE someone at Oracle will answer
>>it.
>>
>>I am trying to store a large binary object in a column defined with the
>>LONG RAW data type...
>There is an Oracle Corporate Support Bulletin that covers this. It discusses
>using the VARRAW (type 15) external datatype to insert long raw data. Get
>with Oracle tech support and ask for the following bulletin:
> Bulletin: 99182.987
> Author: RVASIRED
> Category: PCC
> Headline: RAW AND LONG RAW DATATYPES
>---------------------------------------------------------
>Robert Goodwin |
>New Technology, Inc. |
>Huntsville, AL | robert.goodwin_at_msfc.nasa.gov
>---------------------------------------------------------
#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)
static char * USERID = NULL;
#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
{
printf( "usage: %s userid=xxx/xxx\n", argv[0] );
exit(1);
}
}
if ( USERID == NULL )
{
printf( "usage: %s userid=xxx/xxx\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,000) */ EXEC SQL TYPE my_raw IS LONG VARRAW(100000) REFERENCE;
my_raw * buffer;
long size = 100000;
int i;
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
printf( "EXEC SQL CREATE TABLE TEST_BLOB ( X LONG RAW );\n" );
EXEC SQL CREATE TABLE TEST_BLOB ( X LONG RAW );
/*
* we will allocate a little over 4k of space
*/
buffer = (my_raw *)malloc( size+sizeof(my_raw) );
/*
* fill it up with every possible byte value from 0..254
*/
for( i = 0; i < size; i++ )
buffer->arr[i] = i % 255;
/*
* Just like a varchar, set the length field
*/
buffer->len = size;
printf( "INSERT INTO TEST_BLOB\n" );
EXEC SQL INSERT INTO TEST_BLOB ( X ) values ( :buffer );
/*
* commit the changes
*/
printf("EXEC SQL COMMIT WORK;\n" );
EXEC SQL COMMIT WORK;
/*
* Now, ZERO out the buffer so when we fetch, we know that the buffer
* was NOT equal to what it was on the insert. Just to *prove* that
* the fetch got back the data we inserted
*/
memset( buffer->arr, 0, size );
/*
* Select out the data
*/
printf( " SELECT X INTO :buffer FROM TEST_BLOB;\n" );
EXEC SQL SELECT X
INTO :buffer
FROM TEST_BLOB;
/*
* show what size we got
*/
printf( "The Length = %ld\n", buffer->len );
/*
* Check each character to make sure it is what we inserted
*/
for( i = 0; i < size; i++ )
if ( buffer->arr[i] != i % 255 )
{
printf( "Error! %d != %d\n", buffer->arr[i], i%255 );
break;
}
/*
* delete that table
*/
printf( "EXEC SQL DROP TABLE TEST_BLOB;\n" );
EXEC SQL DROP TABLE TEST_BLOB;
}
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
tkyte_at_us.oracle.com
Oracle Government
Received on Fri Jan 26 1996 - 00:00:00 CET