The SET AUTOFREE statement

The SET AUTOFREE statement allows you to take the following actions in the program:

• Enable the AUTOFREE feature for all cursors:

  EXEC SQL set autofree;
  EXEC SQL set autofree enabled;

  These statements are equivalent because the default action of the SET AUTOFREE statement is to enable all cursors.

• Disable the AUTOFREE feature for all cursors:

  EXEC SQL set autofree disabled;

• Enable the AUTOFREE feature for a specified cursor identifier or cursor variable:

  EXEC SQL set autofree for cursor_id; 
  EXEC SQL set autofree for :cursor_var;

The following code fragment uses the FOR clause of the SET AUTOFREE statement to enable the AUTOFREE feature for the curs1 cursor only. After the database server executes the CLOSE statement for curs1, it automatically frees the cursor and the prepared statement. The curs2 cursor and its prepared statement are not automatically freed.

EXEC SQL BEGIN DECLARE SECTION;
   int a_value;
EXEC SQL END DECLARE SECTION;

EXEC SQL create database tst_autofree;
EXEC SQL connect to 'tst_autofree';
EXEC SQL create table tab1 (a_col int);
EXEC SQL insert into tab1 values (1);

/* Declare the curs1 cursor for the slct1 prepared 
 * statement */
EXEC SQL prepare slct1 from 'select a_col from tab1';
EXEC SQL declare curs1 cursor for slct1;

/* Enable AUTOFREE for cursor curs1 */
EXEC SQL set autofree for curs1;

/* Open the curs1 cursor and fetch the contents */
EXEC SQL open curs1;
while (SQLCODE == 0)
   {
   EXEC SQL fetch curs1 into :a_value;
   printf("Value is: %d\n", a_value);
   }

/* Once the CLOSE completes, the curs1 cursor is freed and
 * cannot be used again. */
EXEC SQL close curs1;

/* Declare the curs2 cursor for the slct2 prepared 
 * statement */
EXEC SQL prepare slct2 from 'select a_col from tab1';
EXEC SQL declare curs2 cursor for slct2;

/* Open the curs2 cursor and fetch the contents */
EXEC SQL open curs2;
while (SQLCODE == 0)
   {
   EXEC SQL fetch curs2 into :a_value;
   printf("Value is: %d\n", a_value);
   }

/* Once this CLOSE completes, the curs2 cursor is still
 * available for use. It has not been automatically freed. */
EXEC SQL close curs2;

/* You must explicitly free the curs2 cursor and slct2
 * prepared statement.  */
EXEC SQL free curs2;
EXEC SQL free slct2;

A prepared statement becomes detached when either of the following events occur:

• If the prepared statement was not associated with any declared cursor
• If the cursor with the prepared statement was freed but the prepared statement was not.

The following code fragment declares the following cursors on the slct1 prepared statement:

• The curs1 cursor, with which the slct1 prepared statement is first associated
• The curs2 cursor, which executes slct1 but with which slct1 is not associated
• The curs3 cursor, with which slct1 is associated

The following code fragment shows how a detached prepared statement can occur:

/******************************************************************
 * Declare curs1 and curs2. The slct1 prepared statement is       *
 * associated curs1 because curs1 is declared first.                      */
EXEC SQL prepare slct1 'select a_col from tab1';
EXEC SQL declare curs1 cursor for slct1;
EXEC SQL declare curs2 cursor for slct1;

/******************************************************************
 * Enable the AUTOFREE feature for curs2                           */
EXEC SQL set autofree for curs2;

/*****************************************************************
 * Open the curs1 cursor and fetch the contents                    */
EXEC SQL open curs1;
   {
   EXEC SQL fetch curs1 into :a_value;
   printf("Value is: %d\n", a_value);
   }

EXEC SQL close curs1;

/* Because AUTOFREE is enabled only for the curs2 cursor, this   *
 * CLOSE statement frees neither the curs1 cursor nor the slct1  *
 * prepared statement. The curs1 cursor is still defined so the  *
 * slct1 prepared statement does not become detached.            *
 **********************************************************************/

/*****************************************************************
 * Open the curs2 cursor and fetch the contents                   */
EXEC SQL open curs2;
while (SQLCODE == 0)
   {
   EXEC SQL fetch curs2 into :a_value;
   printf("Value is: %d\n", a_value);
   }

EXEC SQL close curs2;

/* This CLOSE statement frees the curs2 cursor but does not free *
 * slct1 prepared statement because the prepared statement is not*
 * associated with curs2.                                        *
 **********************************************************************/

/*****************************************************************
 * Reopen the curs1 cursor. This open is possible because the    *
 * AUTOFREE feature has not been enabled on curs1. Therefore, the*
 * database server did not automatically free curs1 when it closed it.*/
EXEC SQL open curs1;
while (SQLCODE == 0)
   {
   EXEC SQL fetch curs1 into :a_value;
   printf("Value is: %d\n", a_value);
   }

EXEC SQL close curs1;
EXEC SQL free curs1;

/* Explicitly freeing the curs1 cursor, with which the slct1     *
 * statement is associated, causes slct1 to become detached. It  *
 * is no longer associated with a cursor.                        * 
******************************************************************/

/***************************************************************
 * This DECLARE statement causes the slct1 prepared statement  *
 * to become reassociated with a cursor. Therefore, the slct1  *
 * statement is no longer detached.                               */
EXEC SQL declare curs3 cursor for slct1;
EXEC SQL open curs3;

/* Enable the AUTOFREE feature for curs                           */
EXEC SQL set autofree for curs3;

/* Open the curs3 cursor and fetch the content                    */
EXEC SQL open curs3;
while (SQLCODE == 0)
   {
   EXEC SQL fetch curs3 into :a_value;
   printf("Value is: %d\n", a_value);
   }

EXEC SQL close curs3;

/* Because AUTOFREE is enabled for the curs3 cursor, this CLOSE*
 * statement frees the curs3 cursor and the slct1 PREPARE stmt.*
 **********************************************************************/

/***************************************************************
 * This DECLARE statement would generate a run time error      *
 * because the slct1 prepared statement has been freed.            */
EXEC SQL declare x4 cursor for slct1;
/***********************************************************************/