Updating Data

---

Applications can update data by executing the UPDATE, DELETE, and INSERT statements.

An alternative method is to position the cursor on a particular row and then use DELETE CURRENT, or UPDATE CURRENT statements.

The following example illustrates how this can be done by using two statement handles:

 SQLHSTMT   cscroll, cupdate;
 SQLCHAR    code[4];
 SQLCHAR    currency[33];
 SQLINTEGER codeInd, currencyInd;
 . . .
 
 /* Allocate statement handles */
 SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &cscroll );
 SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &cupdate );
 
 /* Set scroll cursor attributes */
 SQLSetStmtAttr( cscroll, SQL_ATTR_CURSOR_SCROLLABLE,
                 (SQLPOINTER)SQL_SCROLLABLE, 0 );
 SQLSetStmtAttr( cscroll, SQL_ATTR_CONCURRENCY,
                 (SQLPOINTER)SQL_CONCUR_VALUES, 0 );
 
 /* Name the cursor */
 SQLSetCursorName( cscroll, "CRN", SQL_NTS );
 
 SQLExecDirect( cscroll,
                "SELECT code, currency \
                    FROM mimer_store.currencies \
                    FOR UPDATE OF currency",
                SQL_NTS );
 
 SQLBindCol( cscroll, 1, SQL_C_CHAR,
             code, sizeof(code), &codeInd );
 SQLBindCol( cscroll, 2, SQL_C_CHAR,
             currency, sizeof(currency), &currencyInd );
 
 /* Set the update cursor to use optimistic concurrency */
 SQLSetStmtAttr( cupdate, SQL_ATTR_CONCURRENCY,
                 (SQLPOINTER)SQL_CONCUR_VALUES, 0 );
 
 /* Prepare the positioned update statement using scroll cursor name */
 SQLPrepare( cupdate,
             "UPDATE mimer_store.currencies \
                 SET currency = ? \
                 WHERE CURRENT OF crn",
             SQL_NTS );
 
 /* Bind the code parameter in the update statement */
 SQLBindParameter( cupdate, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
                   SQL_CHAR, 33, 0,
                   currency, sizeof(currency), &currencyInd );
 
 /* Position within the result-set on the scrolling cursor */
 SQLFetchScroll( cscroll, SQL_FETCH_ABSOLUTE, 3 );
 SQLSetPos( cscroll, 1, SQL_POSITION, SQL_LOCK_NO_CHANGE );
 
 /* Update currency name using update statement handle */
 if (strncmp( currency, "Leke", 4 ) == 0)
    strcpy( currency, "Albanian Leke" );
 else
    strcpy( currency, "Leke" );
 currencyInd = SQL_NTS;
 
 SQLExecute( cupdate );

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se