"FOR UPDATE" clause is throwing error in esql program

Question

We are developing a migrate program. There are nearly 80 million records are there in DB. The code is as follows:

static int mymigration(struct progargs *args) 
{
  exec sql begin declare section;
    const char *selectQuery;
    const char *updateQuery;
    long cur_start;
    long cur_end;
    long serial;
    long number;
    char frequency[3];
  exec sql end declare section;

    selectQuery = "select * from mytable where number >= ? and number <= ? for update of frequency ,status";
    updateQuery = "update mytable set frequency = ?, "
    " status = ? "
    " where current of my_cursor";

    cur_start= args->start;
    cur_end = args->end;

    exec sql prepare my_select_query from :selectQuery;
    /* Verify the sql code for error here */

    exec sql declare my_select_cursor cursor with hold for my_select_query;

    exec sql open my_select_cursor using :cur_start, :cur_end;
    /* Verify the sql code for error here */

    exec sql prepare my_update_query from :updateQuery;
    /* Verify the sql code for error here */        

    while (1)
    {
            number = 0;
            serial = 0;
            memset(frequency,0,sizeof(frequency));

            exec sql fetch my_select_cursor into number,:serial,:frequency;
            if (sqlca.sqlcode != SQL_OK)
                    break;            

            exec sql execute my_update_query using :frequency, :frequency;

    }      
    exec sql close my_select_trade_cursor;

}

While implementing this, we are getting the error message "-255". We found one solution as to add being work and commit work. Since we have large amount of data, this might clutter the transaction log.

Is there any other solution available for this problem? The IBM website for informix shows the usage is correct.

Appreciate the help in advance.

Thanks, Mathew Liju

The memset() is redundant. If all you want to do is set the frequency and status values to zero, why not do a simple statement with no fetching: `UPDATE MyTable SET (frequency, status) = (0, 0);`? You can add the 'WHERE number >= ? AND number <= ?' clause too if you need it (with 80 million rows, it is likely to be advisable). — Jonathan Leffler, Feb 22 '10 at 19:13
We showed that as an example, but actually there is some logic to be done on the value. There is only 1 column currently frequency. We have to split the data in frequency in to frequency and status columns. For eg: if the frequency is 1 we have to set 01 to frequency and 00 to status. So, we have to do it in each transcation. — Liju Mathew, Feb 23 '10 at 07:47

score 0 · Accepted Answer · answered Feb 22 '10 at 19:09

Error -255 is "Not in transaction".

I see no BEGIN WORK (or COMMIT WORK or ROLLBACK WORK) statements.

You need to add BEGIN WORK before you open the cursor with the FOR UPDATE clause. You then need to decide whether to commit periodically to avoid overlong transactions. The fact that you use a FOR HOLD cursor shows that you had thought about using sub-transactions; if you were not going to do so, you would not use that clause.

Note that Informix has 3 primary database logging modes:

Unlogged (no transaction support)
Logged (by default, each statement is a singleton transaction; an explicit BEGIN WORK starts a multi-statement transaction terminated by COMMIT WORK or ROLLBACK WORK).
Logged MODE ANSI (slightly simplistically, you are automatically in a transaction; you need an explicit COMMIT or ROLLBACK to terminate a transaction, and may then, optionally, use an explicit BEGIN, but the BEGIN is not actually necessary).

From the symptoms you describe, you have a logged but not MODE ANSI database. Therefore, you must explicitly code the BEGIN WORK statements.

"FOR UPDATE" clause is throwing error in esql program

1 Answers1