Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


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
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX