Re: Access LONG RAW columns

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 04 Aug 1999 14:57:10 GMT
Message-ID: <37ab5493.7560962@newshost.us.oracle.com>

A copy of this was sent to "Matthias Späth" <matthias.spaeth_at_optimum-gmbh.de> (if that email address didn't require changing) On Wed, 4 Aug 1999 08:12:14 +0200, you wrote:

>Hi,
>
>how can I write and read LONG RAW columns in Pro*C ?
>
>Thanks.
>Matthias
>

here is an example:

#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 100k
 * 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 BEGIN DECLARE SECTION;
EXEC SQL TYPE my_raw IS LONG VARRAW(100000) REFERENCE; my_raw * buffer;
EXEC SQL END DECLARE SECTION;

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 99k 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);

}

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Aug 04 1999 - 09:57:10 CDT