Naming Program Variables in INSERT

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 specifies 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 (not defined within this example) obtains customer information from user input and stores 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 might be NULL.