"FOR UPDATE" clause is throwing error in esql program

Question

Hi,

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

Answer 1

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.