Re: Oracle 7 Pro*C code for long > 65533

From: Franz Krojer <franz.krojer_at_mch.sni.de>
Date: 25 Nov 1993 12:40:03 GMT
Message-ID: <1993Nov25.131112_at_mch.sni.de>

In article <2d0qms$d2o_at_lll-winken.llnl.gov>, coffey1_at_llnl.gov (Stu Coffey) writes:
> Hi,
>
> I need to insert and select a field of datatype long that is > 65K. As indicated in the Pro*C supp. the largest varchar is 65K. Could someone point me in the
> right direction for getting code or information that allows this access of a long under oracle 7.
>

You can use dynamic sql and set T to 94 or 96 and L to the length you need and you must allocate memory for V. In a select you must set L to a maximum length and you have to allocate memory for V. Oracle sets after the select the actual length.

Or you can define a own data-type. Here is a simple pc-test-program for putting a lob into a db. Its written for a raw-lob, but you can easy change it to text-lob (but you can't mix).

/* PUTLOB, Krojer 7/1993 */

#include <stdio.h>
#include <stdlib.h>

#define DB_LOB_SIZE 2048000L /* my maximum length */

typedef struct
{

	long			len;
	unsigned char	arr[1];

} LONGRAW; EXEC SQL BEGIN DECLARE SECTION;

	EXEC SQL TYPE LONGRAW IS LONG VARRAW(2147483643);
	VARCHAR		username[20];
	VARCHAR		password[20];
	LONGRAW		*lob;

EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE sqlca;

long SQLCODE;
void sqlerror();

main()
{

	char filename[128]; 
	FILE *fp;
	long lobmaxsize;
	long lobsize;

	strcpy(username.arr,"krojer");
	username.len=strlen(username.arr);
	strcpy(password.arr,"franz");
	password.len=strlen(password.arr);

	EXEC SQL WHENEVER SQLERROR DO sqlerror();


	EXEC SQL CONNECT :username IDENTIFIED BY :password;
	printf("\nConnected to ORACLE as user: %s\n",username.arr);


	/* Clear the whole table */
	/* This table must exist before the table is dropped */
	/* At first time create the table manually (via sqlplus) */
	EXEC SQL DROP TABLE lobtest;
	EXEC SQL CREATE TABLE lobtest (lob LONG RAW, lob2 LONG RAW);
	
	/* Gets a filename and open the file */
	printf("Enter filename for input: ");
	gets(filename);
	if((fp=fopen(filename,"r"))==NULL)
	{
		fprintf(stderr,"Couldn't open file %s\n",filename);
		exit(1);
	}

	lobmaxsize=DB_LOB_SIZE;	/* or from env (DB_MAX_LOB) */

	lob=(LONGRAW*)malloc(sizeof(long)+lobmaxsize);

	if(!lob)
	{
		printf("Not enough memory\n");
		exit(1);
	}

	lobsize=fread(lob->arr,1,lobmaxsize,fp);

	lob->len=lobsize;

	EXEC SQL INSERT INTO lobtest ( lob ) 
		VALUES(:lob);

	printf("%ld bytes written to the database\n",lobsize);

	fclose(fp);
	free((LONGRAW*)lob);


	/* Commit */

	EXEC SQL COMMIT WORK;

}

/* Error-Handling */

void sqlerror()
{

EXEC SQL WHENEVER SQLERROR CONTINUE;

	printf("\nORACLE error detected:\n");
	printf("\n% .70s\n", sqlca.sqlerrm.sqlerrmc);

	EXEC SQL ROLLBACK RELEASE;
	exit(1);

}

And here follows a pc-programm for getting a lob:

/* GETLOB, Krojer 7/1993 */