InformIT: Visual C++ 6 Unleashed > Inserting, Updating, and Deleting Rows

Table of Contents

Inserting, Updating, and Deleting Rows

To insert, update, or delete rows from a data source, you can always simply execute SQL statements to manipulate the rows. This must be supported by all ODBC drivers, and is the best way to perform updates or deletes if you simply want to change a set of rows. However, if you are writing an app that provides more flexible user interaction with the data, this may get to be complicated.

To help simplify applications that involve dynamic user interaction with the data, ODBC allows you to update and delete rows, based on a result set that you have already retrieved. However, these positioned updates or deletions aren't supported by all drivers.

In addition, you can perform bulk operations with the SQLBulkOperations() function, which can be used to insert new rows or perform updates, deletions, and fetches based on bookmarks. You will look at this function shortly.

Positioned Updates and Deletions

First of all, you can retrieve a particular result set by executing a SQL query that uses the FOR UPDATE OF clause:

SELECT Col1, Col2 FROM myTable FOR UPDATE OF Col1, Col2;

This will retrieve the desired rows and tell the data source that you may be altering the data in these rows.

You also name the cursor that is used for this result set by calling SQLSetCursorName(). All cursors have a name, but if you don't name it explicitly, it will be assigned a fairly unwieldy name generated by the driver. You can retrieve the current cursor name by calling SQLGetCursorName().

Next, you call SQLFetch() or SQLFetchScroll() to find the row on which you want to operate. You then make the row you want to modify the current row by calling SQLSetPos() with the SQL_POSITION option.

Next, you use a second statement handle to execute an UPDATE or DELETE statement that uses the WHERE CURRENT OF clause, as in the following examples:

"UPDATE MyTable SET MyCol = 123 WHERE CURRENT OF myCursorName"
"DELETE FROM MyTable WHERE CURRENT OF myCursorName"

where myCursorName is the name that you assigned to the cursor used to retrieve the initial result set. This will perform the update on the current row, as you specified with SQLSetPos().

For updating columns with long data fields, you can use the SQLPutData() function, which can be used to add data one piece at a time, much like the SQLGetData() function.

`SQLBulkOperations()`

Beginning with version 3, the ODBC API also allows you to perform insertions, updates, deletions, and fetches en masse with the SQLBulkOperations() function:

SQLRETURN SQLBulkOperations(SQLHSTMT StatementHandle, SQLUSMALLINT Operation);

This function takes the statement handle that you want to operate on and one of the following values for Operation:

SQL_ADD
SQL_UPDATE_BY_BOOKMARK
SQL_DELETE_BY_BOOKMARK
SQL_FETCH_BY_BOOKMARK

In the next sections, you will see how to use SQLBulkOperations() for each of these operations.

Inserting with `SQLBulkOperations()`

To insert new rows, you first execute a query that returns a result set for the table you want to add rows to.

Next, you use SQLBindCol() to bind arrays of data to the columns of the result set. You must also set the value of SQL_ATTR_ROW_ARRAY_SIZE to the length of these arrays by calling SQLSetStmtAttr().

You can then call SQLBulkOperations() with an Operation of SQL_ADD to insert new rows, containing the data in the bound columns. If you have set the SQL_ATTR_ARRAY_STATUS_PTR statement attribute, this array will receive the status of the insert operation for each row. The following function was implemented by the menu resource and the ClassWizard. The code inside the function shows how bulk operations work:

void CODBCTestView::OnViewBulkinsert() {


        SQLRETURN sr;
        SQLHSTMT hstmt;
    SQLCHAR SQL[] = "SELECT EmpName, Salary, Dept FROM Employee";
        struct rowTag {
                SQLCHAR name[51];
                double salary;
                SQLCHAR dept[11];
                SQLINTEGER nameLength;
                SQLINTEGER salaryLength;
                SQLINTEGER deptLength;
       } row [7];
       // Allocate Statement Handle
       sr = SQLAllocHandle(SQL_HANDLE_STMT, hDbConn, &hstmt);
       if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
               displayODBCError(sr, "Error in allocating statement in OnViewBulkinsert");
       // Execute SQL statement to open cursor
       sr = SQLExecDirect(hstmt, SQL, SQL_NTS);
       if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
               displayODBCError(sr, "Error executing statement in  OnViewBulkinsert");
       //SET STATEMENT ATTRIBUTES
       // Set the cursor type.
       sr = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE,
               (void*) SQL_CURSOR_DYNAMIC, SQL_IS_UINTEGER);
       if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
               displayODBCError(sr, "Error in setting cursor type in  OnViewBulkinsert");
       // Lock out other users.
       sr = SQLSetStmtAttr(hstmt, SQL_ATTR_CONCURRENCY,
               (void*) SQL_CONCUR_LOCK, SQL_IS_UINTEGER);
       if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
               displayODBCError(sr, "Error in setting locking in  OnViewBulkinsert");
       // Set the number of rows to process
       sr = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE,
               (void *) 7, SQL_IS_INTEGER);
       if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
               displayODBCError(sr, "Error in array size in OnViewBulkinsert");
       // Set the size of the structure for each row.
       sr = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE,
               (void *) sizeof(row[0]), SQL_IS_INTEGER);
       if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
               displayODBCError(sr, "Error in setting bind type in  OnViewBulkinsert");
    // Bind each column
    sr = SQLBindCol(hstmt, 1, SQL_C_CHAR, row[0].name, sizeof(row[0].name), &row[0].nameLength);
        if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
               displayODBCError(sr, "Error in Binding 1 in OnViewBulkinsert");
    sr = SQLBindCol(hstmt, 2, SQL_C_DOUBLE, &row[0].salary, sizeof(row[0].salary), &row[0].salaryLength);
        if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
               displayODBCError(sr, "Error in Binding 2 in OnViewBulkinsert");
    sr = SQLBindCol(hstmt, 3, SQL_C_CHAR, row[0].dept, sizeof(row[0].dept), &row[0].deptLength);
        if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
               displayODBCError(sr, "Error in Binding 3 in OnViewBulkinsert");
// Hire the Gilligan's Island crew
// at the same salary for the IS department
        strcpy((char *) row[0].name, "Gilligan");
        strcpy((char *) row[1].name, "Skipper");
        strcpy((char *) row[2].name, "Mr. Howe");
        strcpy((char *) row[3].name, "Mrs. Howe");
        strcpy((char *) row[4].name, "Ginger");
        strcpy((char *) row[5].name, "Professor");
        strcpy((char *) row[6].name, "Mary Ann");
        for(int loop=0; loop < 7; loop++) {
                row[loop].salary = 50000;
                strcpy((char *) row[loop].dept, "IS");
                row[loop].nameLength = SQL_NTS;
                row[loop].salaryLength =
                        sizeof(row[0].salary);
                row[loop].deptLength = SQL_NTS;
        }
        // Add new rows to the database
        sr = SQLBulkOperations(hstmt, SQL_ADD);
        if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
                displayODBCError(sr, "Error in bulk insert");


}

Updating with `SQLBulkOperations()`

Except for inserting new rows, the other operations supported by SQLBulkOperations() all use bookmarks to specify the rows that will be manipulated. Thus, you must set the SQL_ATTR_USE_BOOKMARKS statement attribute to SQL_UB_VARIABLE before executing a query that returns the initial result set with which you will be working.

Next, to update rows, you bind the columns you want to update with SQLBindCol(). You should also bind column 0 to an array that will hold bookmarks.

You then fill the bookmark array with the bookmarks for the rows that you want to update and update the other corresponding bound arrays for the row data. You should also set the SQL_ATTR_ROW_ARRAY_SIZE statement attribute to the number of rows (and corresponding bookmark array entries) that you want to update.

You can then call SQLBulkOperations() with an Operation of SQL_UPDATE_BY_BOOKMARK to update the rows with the data in the bound arrays. If you have set the SQL_ATTR_ROW_STATUS_PTR statement attribute, this array will contain the status of each of the updates.

Deleting with `SQLBulkOperations()`

Deleting rows with SQLBulkOperations() is very similar to updating rows. You execute a statement that returns an appropriate result set (with bookmarks enabled), bind column 0 to an array that you fill with the bookmarks for rows to be deleted, set the SQL_ATTR_ROW_ARRAY_SIZE statement attribute to the number of bookmark entries, and call SQLBulkOperations() with an Operation of SQL_DELETE_BY_BOOKMARK.

Fetching with `SQLBulkOperations()`

Fetching by bookmark is also very similar to the update and delete operations shown previously. You should simply bind columns for the data you want to retrieve, including column 0, which you fill with bookmarks for the rows to fetch.

You then set the number of rows to fetch by setting the SQL_ATTR_ROW_ARRAY_SIZE statement attribute and call SQLBulkOperations() with SQL_FETCH_BY_BOOKMARK.

Asynchronous Operations | Next Section

Previous Section

Inserting, Updating, and Deleting Rows

Positioned Updates and Deletions

SQLBulkOperations()

Inserting with SQLBulkOperations()

Updating with SQLBulkOperations()

Deleting with SQLBulkOperations()

Fetching with SQLBulkOperations()

`SQLBulkOperations()`

Inserting with `SQLBulkOperations()`

Updating with `SQLBulkOperations()`

Deleting with `SQLBulkOperations()`

Fetching with `SQLBulkOperations()`