Informix-ESQL/C Programmer's Manual Chapter 7: Working with INFORMIX-Universal Server Smart Large

Home

Contents

Index

Master Index

New Book

EXEC SQL include int8;

EXEC SQL include locator;

EXEC SQL define BUFSZ 10;

extern char statement[30];

main()

{

EXEC SQL BEGIN DECLARE SECTION;

	long catalog_number;

	int error;

	int numbytes;

	char buf[BUFSZ];

	char srvr_name[256];

	char col_name[300];

	int buflen = BUFSZ;

	int8 estbytes, offset;

	ifx_lo_create_spec_t *create_spec;

	fixed binary 'blob' ifx_lo_t picture;

	int lofd;

EXEC SQL END DECLARE SECTION;

	EXEC SQL whenever sqlerror call whenexp_chk;

	EXEC SQL whenever sqlwarning call whenexp_chk;

	printf("CREATE_CLOB Sample ESQL program running.\n\n");

	strcpy(statement, "CONNECT stmt");
	EXEC SQL connect to 'stores7';

	EXEC SQL get diagnostics exception 1 

		:srvr_name = server_name;

/* Set up a test table called catalog2 in the demonstration

  * database.

  */
	strcpy(statement, "CREATE DISTINCT TYPE stmt");

	EXEC SQL create distinct type fpicture as clob;

	strcpy(statement, "CREATE CAST to CLOB stmt");

	EXEC SQL create cast (fpicture as clob);

	strcpy(statement, "CREATE CAST from CLOB stmt");

	EXEC SQL create cast (clob as fpicture);

/* Assign the following column-level storage characteristics

  * for the cat_desc column:

  *           sbspace name = sb1 (this sbspace must already exist)

  *            extent size = 100 kilobytes

  *            keep last access time is on

  */
	strcpy(statement, "CREATE TABLE stmt");

	EXEC SQL create table catalog2

	(

		catalog_num INTEGER,

		cat_descr fpicture

	) put cat_descr in (sb1) 

		(extent size = 100, 

		keep access time);

/* Allocate and initialize the LO-specification structure 

  */

	error = ifx_lo_def_create_spec(&create_spec);

	if (error < 0)

		{

		strcpy(statement, "ifx_lo_def_create_spec()");

		handle_lo_error(error);

		}

/* Get the column-level storage characteristics for the 

  * CLOB column, cat_descr.

  */

	sprintf(col_name, "stores7@%s:catalog2.cat_descr",

			srvr_name);

	error =ifx_lo_col_info(col_name, create_spec);
	if (error < 0)

		{

		strcpy(statement, "ifx_lo_col_info()");

		handle_lo_error(error);

		}

/* Override column-level storage characteristics for

  * cat_desc with the following user-defined storage

  * characteristics:

  *            no logging

  *            extent size = 10 kilobytes

  */

	ifx_lo_specset_flags(create_spec,LO_LOG);

	ifx_int8cvint(BUFSZ, &estbytes);

	ifx_lo_specset_estbytes(create_spec, &estbytes);

/* Create an LO-pointer structure for the smart large object

  */

	if (lofd = ifx_lo_create(create_spec, LO_RDWR, 

		&picture, &error) == -1)

	{

	strcpy(statement, "ifx_lo_create()");

	handle_lo_error(error);

	}

/* Copy data into the character buffer 'buf' */

	sprintf(buf, "%s %s %s"

		"The rain in Spain stays mainly in the plain.",

		"In Hartford, Hereford, and Hampshire, hurricanes",

		"hardly happen.");

/* Write contents of character buffer to the open smart

  * large object that lofd points to.

  */

	ifx_int8cvint(0, &offset);

	numbytes = ifx_lo_writewithseek(lofd, buf, buflen, 

		&offset, LO_SEEK_SET, &error);

	if ( numbytes < buflen )

		{

		strcpy(statement, "ifx_lo_writewithseek()");

		handle_lo_error(error);

		}

/* Insert the smart large object into the table */

	strcpy(statement, "INSERT INTO catalog2");

	EXEC SQL insert into catalog2 values (5, :picture);

/* Close the LO file descriptor */
	ifx_lo_close(lofd);

/* Select back the newly inserted value. The SELECT

  * returns an LO-pointer structure, which you then use to 

  * open a smart large object to get an LO file descriptor. 

  */

	strcpy(statement, "SELECT FROM catalog2");

	EXEC SQL select cat_descr into :picture from catalog2;

/* Use the returned LO-pointer structure to open a smart 

  * large object and get an LO file descriptor.

  */

	lofd = ifx_lo_open(&picture, LO_RDONLY, &error);

	if (error < 0)

		{

		strcpy(statement, "ifx_lo_open()");

		handle_lo_error(error);

		}

/* Use the LO file descriptor to read the data in the

  * smart large object.

  */

	ifx_int8cvint(0, &offset);

	strcpy(buf, "");

	numbytes = ifx_lo_readwithseek(lofd, buf, buflen,

		&offset, LO_SEEK_CUR, &error);

	if (error || numbytes == 0)

		{

		strcpy(statement, "ifx_lo_readwithseek()");

		handle_lo_error(error);

		}
	printf("Contents of CLOB column:\n %s", buf);
	/* Close open smart large object */
	ifx_lo_close(lofd);
	/* Free LO-specification structure */
	ifx_lo_spec_free(create_spec);
}

void handle_lo_error(error_num)

int error_num;

{

	printf("%s generated error %d\n", statement, error_num);

	exit(1);

}

/* Include source code for whenexp_chk() exception-checking

  * routine

  */
EXEC SQL include exp_chk.ec;