Re: Using PRO*C to update a long column

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/01
Message-ID: <34abd0b7.1873694_at_inet16>#1/1

On 31 Dec 1997 01:00:26 GMT, noodles_at_aol.com (Noodles) wrote:

>We're running Oracle 7.1 on OpenVMS 6.2. I'm a newbie to Pro*C but I know SQL
>and ANSI C very well. Can someone please point me to some docs or outline the
>procedur
>TIA,
>Cliff

The following pro*c routine outlines how to do this. The trick with Pro*c is that you will have to tell pro*c at compile time what the largest long you want to deal with will be. You won't allocate that much memory at any time tho (the example below allocates only enough memory to deal with the file to be read for example but can only deal with files upto 2m in size -- you need to change a constant to make it bigger).

I tested this with 7.1.6 on solaris

static void process()
{

/*
 * This is the data structure we will use with our LONGs.  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_long
{

	long			len;
	unsigned char	arr[1];
}
	my_long;

/*
 * Use type equivalencing to tell Oracle that the C type "my_long" is
 * equivalent to the Oracle type LONG VARCHAR and can hold upto 2m
 * 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_long IS LONG VARCHAR(2097152) REFERENCE;

my_long	* buffer;
int		i;
int		size;
FILE	* input;
FILE	* output;

	EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

	printf( "EXEC SQL CREATE TABLE TEST_LONG  ( ID NUMBER, TEXT LONG );\n" );
	EXEC SQL CREATE TABLE TEST_LONG ( ID NUMBER, TEXT LONG );

	/*
	 * Insert a row we will come back and update later
	 */
	printf( "INSERT INTO TEST_LONG\n" );
	EXEC SQL INSERT INTO TEST_LONG ( ID ) values ( 1 );

	/*
	 * commit the changes
	 */
	printf("EXEC SQL COMMIT WORK;\n" );
	EXEC SQL COMMIT WORK;

	input = fopen( "test.dat", "rt" );
	fseek( input, 0, SEEK_END );
	size = ftell(input);
	fseek( input, 0, SEEK_SET );

	buffer = (my_long*) malloc( size + sizeof(my_long) );
	buffer->len = fread( buffer->arr, 1, size, input );
	fclose( input );

	printf( "EXEC SQL UPDATE TEST_LONG\n" );
	EXEC SQL UPDATE TEST_LONG SET TEXT = :buffer WHERE ID = 1;

	memset( buffer->arr, 0, size );

	/*
	 * Select out the data
	 */
	printf( " SELECT TEXT INTO :buffer FROM TEST_LONG;\n" );
	EXEC SQL SELECT TEXT
			 INTO :buffer
			 FROM TEST_LONG
			 WHERE ID = 1;

	/* 
	 * show what size we got 
	 */
	printf( "The Length = %ld\n", buffer->len );

	output = fopen( "test2.dat", "wt" );
	fwrite( buffer->arr, 1, buffer->len, output );
	fclose( output );
	printf( "output should be in test2.dat\n" );

	/*
	 * drop that table
	 */
	printf( "EXEC SQL DROP TABLE TEST_LONG;\n" );
	EXEC SQL DROP TABLE TEST_LONG;

}  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jan 01 1998 - 00:00:00 CET