The DECLARE CURSOR statement has many variations. Most are used to create cursors for different kinds of scans over data, but one variation creates a special kind of cursor, called an insert cursor. You use an insert cursor with the PUT and FLUSH statements to efficiently insert rows into a table in bulk.
To create an insert cursor, declare a cursor to be for an INSERT statement instead of a SELECT statement. You cannot use such a cursor to fetch rows of data; you can use it only to insert them. The following 4GL code fragment shows the declaration of an insert cursor:
DEFINE the_company LIKE customer.company,
the_fname LIKE customer.fname,
the_lname LIKE customer.lname
DECLARE new_custs CURSOR FOR
INSERT INTO customer (company, fname, lname)
VALUES (the_company, the_fname, the_lname)
When you open an insert cursor, a buffer is created in memory to hold a block of rows. The buffer receives rows of data as the program produces them; then they are passed to the database server in a block when the buffer is full. The buffer reduces the amount of communication between the program and the database server, and it lets the database server insert the rows with less difficulty. As a result, the insertions go faster.
The buffer is always made large enough to hold at least two rows of inserted values. It is large enough to hold more than two rows when the rows are shorter than the minimum buffer size.
The code in the previous example prepares an insert cursor for use. The continuation, as the following example shows, demonstrates how the cursor can be used. For simplicity, this example assumes that a function named next_cust returns either information about a new customer or null data to signal the end of input.
EXEC SQL BEGIN WORK;
EXEC SQL OPEN new_custs;
while(SQLCODE == 0)
{
next_cust();
if(the_company == NULL)
break;
EXEC SQL PUT new_custs;
}
if(SQLCODE == 0) /* if no problem with PUT */
{
EXEC SQL FLUSH new_custs; /* write any rows left */
if(SQLCODE == 0) /* if no problem with FLUSH */
EXEC SQL COMMIT WORK; /* commit changes */
}
else
EXEC SQL ROLLBACK WORK; /* else undo changes */
The code in this example calls next_cust repeatedly. When it returns non-null data, the PUT statement sends the returned data to the row buffer. When the buffer fills, the rows it contains are automatically sent to the database server. The loop normally ends when next_cust has no more data to return. Then the FLUSH statement writes any rows that remain in the buffer, after which the transaction terminates.
Re-examine the INSERT statement on page ***. The statement by itself, not part of a cursor definition, inserts a single row into the customer table. In fact, the whole apparatus of the insert cursor can be dropped from the example code, and the INSERT statement can be written into the code where the PUT statement now stands. The difference is that an insert cursor causes a program to run somewhat faster.
When a program executes a PUT statement, the program should test whether the row is placed in the buffer successfully. If the new row fits in the buffer, the only action of PUT is to copy the row to the buffer. No errors can occur in this case. However, if the row does not fit, the entire buffer load is passed to the database server for insertion, and an error can occur.
The values returned into the SQL Communications Area (SQLCA) give the program the information it needs to sort out each case. SQLCODE and SQLSTATE are set to zero after every PUT statement if no error occurs and to a negative error code if an error occurs.
The database server sets the third element of SQLERRD to the number of rows actually inserted into the table, as follows:
Read the code once again to see how SQLCODE is used (see the previous example). First, if the OPEN statement yields an error, the loop is not executed because the WHILE condition fails, the FLUSH operation is not performed, and the transaction rolls back. Second, if the PUT statement returns an error, the loop ends because of the WHILE condition, the FLUSH operation is not performed, and the transaction rolls back. This condition can occur only if the loop generates enough rows to fill the buffer at least once; otherwise, the PUT statement cannot generate an error.
The program might end the loop with rows still in the buffer, possibly without inserting any rows. At this point, the SQL status is zero, and the FLUSH operation occurs. If the FLUSH operation produces an error code, the transaction rolls back. Only when all inserts are successfully performed is the transaction committed.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]