Informix Guide to SQL: Syntax SQL Statements

Use the PUT statement to store a row in an insert buffer for later insertion into the database.

Use this statement with ESQL/C.

Each PUT statement stores a row in an insert buffer that was created when the cursor was opened. If the buffer has no room for the new row when the statement executes, the buffered rows are written to the database in a block and the buffer is emptied. As a result, some PUT statement executions cause rows to be written to the database, and some do not.

You can use the FLUSH statement to write buffered rows to the database without adding a new row. The CLOSE statement writes any remaining rows before it closes an insert cursor.

If the current database uses explicit transactions, you must execute a PUT statement within a transaction.

The following example uses a PUT statement in ESQL/C:

EXEC SQL prepare  ins_mcode  from  

	'insert  into  manufact  values(?,?)';

EXEC SQL declare  mcode  cursor  for  ins_mcode;

EXEC SQL open  mcode;

EXEC SQL put  mcode  from  :the_code,  :the_name; 

The PUT statement is not an X/Open SQL statement. Therefore, you get a warning message if you compile a PUT statement in X/Open mode.

The values that reside in the inserted row can come from one of the following sources:

• Constant values that are written into the INSERT statement
• Program variables that are named in the INSERT statement
• Program variables that are named in the FROM clause of the PUT statement
• Values that are prepared in memory addressed by an sqlda structure or a system-descriptor area and then named in the USING clause of the PUT statement

The VALUES clause of the INSERT statement lists the values of the inserted columns. One or more of these values might be constants (that is, numbers or character strings).

When all the inserted values are constants, the PUT statement has a special effect. Instead of creating a row and putting it in the buffer, the PUT statement merely increments a counter. When you use a FLUSH or CLOSE statement to empty the buffer, one row and a repetition count are sent to the database server, which inserts that number of rows.

In the following ESQL/C example, 99 empty customer records are inserted into the customer table. Because all values are constants, no disk output occurs until the cursor closes. (The constant zero for customer_num causes generation of a SERIAL value.)

int count;

EXEC SQL declare fill_c cursor for

	insert into customer(customer_num) values(0);

EXEC SQL open fill_c;

for (count = 1; count <= 99; ++count)

	EXEC SQL put fill_c;

EXEC SQL close fill_c;

When you associate the INSERT statement with a cursor (in the DECLARE statement), you create an insert cursor. In the INSERT statement, you can name program variables in the VALUES clause. When each PUT statement is executed, the contents of the program variables at that time are used to populate the row that is inserted into the buffer.

If you are creating an insert cursor (using DECLARE with INSERT), you must use only program variables in the VALUES clause. Variable names are not recognized in the context of a prepared statement; you associate a prepared statement with a cursor through its statement identifier.

The following ESQL/C example illustrates the use of an insert cursor. The code includes the following statements:

• The DECLARE statement associates a cursor called ins_curs with an INSERT statement that inserts data into the customer table. The VALUES clause names a data structure that is called cust_rec; the ESQL/C preprocessor converts cust_rec to a list of values, one for each component of the structure.
• The OPEN statement creates a buffer.
• A user-defined function that is not defined in the example obtains customer information from an interactive user and leaves it in cust_rec.
• The PUT statement composes a row from the current contents of the cust_rec structure and sends it to the row buffer.
• The CLOSE statement inserts into the customer table any rows that remain in the row buffer and closes the insert cursor.
int keep_going = 1;
EXEC SQL BEGIN DECLARE SECTION
struct cust_row { /* fields of a row of customer table */ } cust_rec;
EXEC SQL END DECLARE SECTION

EXEC SQL declare ins_curs cursor for
insert into customer values (:cust_row);
EXEC SQL open ins_curs;
while ( (sqlca.sqlcode == 0) && (keep_going) )
{
keep_going = get_user_input(cust_rec); /* ask user for new customer */
if (keep_going ) /* user did supply customer info */
{
cust_rec.customer_num = 0; /* request new serial value */
EXEC SQL put ins_curs;
}
if (sqlca.sqlcode == 0) /* no error from PUT */
keep_going = (prompt_for_y_or_n("another new customer") =='Y')
}
EXEC SQL close ins_curs;

Use an indicator variable if the data to be inserted by the INSERT statement might be null.

When the INSERT statement is prepared (see PREPARE), you cannot use program variables in its VALUES clause. However, you can represent values using a question-mark (?) placeholder. List the names of program variables in the FROM clause of the PUT statement to supply the missing values. The following ESQL/C example lists host variables in a PUT statement:

char answer [1] = 'y';
EXEC SQL BEGIN DECLARE SECTION;
char ins_comp[80];
char u_company[20];
EXEC SQL END DECLARE SECTION;

main()
{
EXEC SQL connect to 'stores_demo';
EXEC SQL prepare ins_comp from
'insert into customer (customer_num, company) values (0, ?)';
EXEC SQL declare ins_curs cursor for ins_comp;
EXEC SQL open ins_curs;

while (1)
{
printf("\nEnter a customer: ");
gets(u_company);
EXEC SQL put ins_curs from :u_company;
printf("Enter another customer (y/n) ? ");
if (answer = getch() != 'y')
break;
}
EXEC SQL close ins_curs;
EXEC SQL disconnect all;
}

If you do not know the number of parameters to be supplied at runtime or their data types, you can associate input values from a system-descriptor area or an sqlda structure. Both of these descriptor structures describe the data type and memory location of one or more values to replace question-mark (?) placeholders.

Each time the PUT statement executes, the values that the descriptor structure describes are used to replace question-mark (?) placeholders in the INSERT statement. This process is similar to using a FROM clause with a list of variables, except that your program has full control over the memory location of the data values.

Use the SQL DESCRIPTOR option to introduce the name of a system descriptor area.

The COUNT field in the system-descriptor area corresponds to the number of dynamic parameters in the prepared statement. The value of COUNT must be less than or equal to the number of item descriptors that were specified when the system-descriptor area was allocated with the ALLOCATE DESCRIPTOR statement. You can obtain the value of a field with the GET DESCRIPTOR statement and set the value with the SET DESCRIPTOR statement.

A system-descriptor area conforms to the X/Open standards.

The following ESQL/C example shows how to associate values from a system-descriptor area:

EXEC SQL allocate descriptor 'desc1';

...

EXEC SQL put selcurs using sql descriptor 'desc1';

Use the DESCRIPTOR option to introduce the name of a pointer to an sqlda structure.

The following ESQL/C example shows how to associate values from an sqlda structure:

EXEC SQL put selcurs using descriptor pointer2;

A collection cursor allows you to access the individual elements of a collection variable. To declare a collection cursor, use the DECLARE statement and include the Collection Derived Table segment in the INSERT statement that you associate with the cursor. Once you open the collection cursor with the OPEN statement, the cursor allows you to put elements in the collection variable.

To put elements, one at a time, into the insert cursor, use the PUT statement and the FROM clause. The PUT statement identifies the collection cursor that is associated with the collection variable. The FROM clause identifies the element value to be inserted into the cursor. The data type of any host variable in the FROM clause must match the element type of the collection.

Important: The collection variable stores the elements of the collection. However, it has no intrinsic connection with a database column. Once the collection variable contains the correct elements, you must then save the variable into the actual collection column with the INSERT or UPDATE statement.

Suppose you have a table called children with the following structure:

CREATE TABLE children

(
	age			SMALLINT,
	name			VARCHAR(30),
	fav_colors			SET(VARCHAR(20)),
)

The following ESQL/C code fragment shows how to use an insert cursor to put elements into a collection variable called child_colors:

EXEC SQL BEGIN DECLARE SECTION;

	client collection child_colors;

	char *favorites[]

	(

		"blue",

		"purple",

		"green",

		"white",

		"gold",

		0

	);

	

	int a = 0;

	char child_name[21];

EXEC SQL END DECLARE SECTION;

EXEC SQL allocate collection :child_colors;

/* Get structure of fav_colors column for untyped 

  * child_colors collection variable */

EXEC SQL select fav_colors into :child_colors

	from children

	where name = :child_name;

/* Declare insert cursor for child_colors collection

  * variable and open this cursor */

EXEC SQL declare colors_curs cursor for 

	insert into table(:child_colors) 

	values (?);

EXEC SQL open colors_curs;

/* Use PUT to gather the favorite-color values
  * into a cursor */

while (fav_colors[a])

{

	EXEC SQL put colors_curs from :favorites[:a];

	a++

	...

}

/* Flush cursor contents to collection variable */

EXEC SQL flush colors_curs;

EXEC SQL update children set fav_colors = :child_colors;

EXEC SQL close colors_curs;

EXEC SQL deallocate collection :child_colors;

After the FLUSH statement executes, the collection variable, child_colors, contains the elements {"blue", "purple", "green", "white", "gold"}. The UPDATE statement at the end of this code fragment saves the new collection into the fav_colors column of the database. Without this UPDATE statement, the collection column never has the new collection added.

When the OPEN statement opens an insert cursor, an insert buffer is created. The PUT statement puts a row into this insert buffer. The block of buffered rows is inserted into the database table as a block only when necessary; this process is called flushing the buffer. The buffer is flushed after any of the following events:

• The buffer is too full to hold the new row at the start of a PUT statement.
• A FLUSH statement executes.
• A CLOSE statement closes the cursor.
• An OPEN statement executes, naming the cursor.

When the OPEN statement is applied to an open cursor, it closes the cursor before reopening it; this implied CLOSE statement flushes the buffer.

• A COMMIT WORK statement executes.
• The buffer contains BYTE or TEXT data (flushed after a single PUT statement).

If the program terminates without closing an insert cursor, the buffer remains unflushed. Rows that were inserted into the buffer since the last flush are lost. Do not rely on the end of the program to close the cursor and flush the buffer.

The sqlca structure contains information on the success of each PUT statement as well as information that lets you count the rows that were inserted. The result of each PUT statement is contained in the following fields of the sqlca: sqlca.sqlcode, SQLCODE and sqlca.sqlerrd[2].

Data buffering with an insert cursor means that errors are not discovered until the buffer is flushed. For example, an input value that is incompatible with the data type of the column for which it is intended is discovered only when the buffer is flushed. When an error is discovered, rows in the buffer that are located after the error are not inserted; they are lost from memory.

The SQLCODE field is set to 0 if no error occurs; otherwise, it is set to an error code. The third element of the sqlerrd array is set to the number of rows that are successfully inserted into the database:

• If a row is put into the insert buffer, and buffered rows are not written to the database, SQLCODE and sqlerrd are set to 0 (SQLCODE because no error occurred, and sqlerrd because no rows were inserted).
• If a block of buffered rows is written to the database during the execution of a PUT statement, SQLCODE is set to 0 and sqlerrd is set to the number of rows that was successfully inserted into the database.
• If an error occurs while the buffered rows are written to the database, SQLCODE indicates the error, and sqlerrd contains the number of successfully inserted rows. (The uninserted rows are discarded from the buffer.)

Tip: When you encounter an SQLCODE error, a corresponding SQLSTATE error value also exists. For information about how to get the message text, check the GET DIAGNOSTICS statement.

To count the number of rows that were actually inserted in the database as well as the number not yet inserted, perform the following steps:

1. Prepare two integer variables (for example, total and pending).
2. When the cursor is opened, set both variables to 0.
3. Each time a PUT statement executes, increment both total and pending.
4. Whenever a PUT or FLUSH statement executes, or the cursor closes, subtract the third field of the SQLERRD array from pending.

At any time, (total - pending) represents the number of rows that were actually inserted. If all commands are successful, pending contains zero after the cursor is closed. If an error occurs during a PUT, FLUSH, or CLOSE statement, the value that remains in pending is the number of uninserted (discarded) rows.

Related statements: ALLOCATE DESCRIPTOR, CLOSE, DEALLOCATE DESCRIPTOR, FLUSH, DECLARE, GET DESCRIPTOR, OPEN, PREPARE, and SET DESCRIPTOR

For a task-oriented discussion of the PUT statement, see the Informix Guide to SQL: Tutorial.

For further information about error checking, the system-descriptor area, and the sqlda structure, see the Informix ESQL/C Programmer's Manual.