The Informix is not throwing error when trying to update a row which is not present?

Question

We are analyzing an Informix behavior in production. When trying to update a row which is not present the application is getting "-346: Could not update a row in the table" error in production.

However, when we try the same in the dev region, the application works fine.

Whether the Informix should throw error always when updating a row which is not present or is it configurable at Informix level?

The application is written in C and the corresponding update queries are present in the ".ec" program.

Also, is there any other way Informix throws a -346 error?

Edit: The SQL is as follows:

update <table> set serial_number = <number1> where serial_number = <number2> 

The SQL Error is:

 Execute SQL ERROR

    SQL CODE = -346
    SQLERM =
    SQLERRP =
    SQLERRD[0] = 1
    SQLERRD[1] = -100
    SQLERRD[2] = 0
    SQLERRD[3] = 1
    SQLERRD[4] = 56
    SQLERRD[5] = 0
    SQLWARN =

Edit Again:

The source code is as follows:

const char *sql = "update table_not_named set serial_number = ? where serial_number = ?"; 

if ( prepared_sql == 0 )
{
    exec sql prepare update_sql from :sql;
    if ( sqlca.sqlcode != SQL_OK )
    {
        logSqlError( log_err, __func__, "Prepare" );
        return FAIL;
    }
    prepared_sql = 1;
}

exec sql execute update_sql using :db_new_serial_number, :db_old_serial_number;

if ( sqlca.sqlcode != SQL_OK )
{
    logSqlError( log_err, __func__, "Execute" );
    return FAIL;
}

Answer 1

^{The comments are getting too big to be comments — by quite a margin.}

Error -346 is not a problem with present/absent. The instructions from finderr -346 say:

While the database server was processing an UPDATE, it received an unexpected error. Check the accompanying ISAM error code for more detailed information on the cause. Possible causes include hardware errors and locking conflicts.

You'll need to track the ISAM error (sqca.sqlerrd[1] in ESQL/C) to get some more information; it should be an error number with absolute value between 100 and 199. This probably means there is a problem of some sort on your production system.

Note that updating a non-existent row with a searched UPDATE should simply report zero rows updated successfully. In a MODE ANSI database, you would get a status of 100 (SQLNOTFOUND) instead of 0 (no error). If you're doing a WHERE CURRENT OF update and the row's gone missing, then there's something weird going on indeed — it shouldn't happen (because it means you didn't get a lock on the row when you selected it, but someone managed to delete the row even though you should have had it locked). If you didn't use the FOR UPDATE clause, then the WHERE CURRENT OF should have a more specific error.

^{At this point, the question was updated to show the error information.}

Curious message: I ran

create table sample
(
    serial_number integer not null primary key,
    value varchar(30) not null
);
insert into sample values(123, 'one hundred and twenty-three');
update sample set serial_number = 124 where serial_number = 99;

which gives a status of 0 (and reports 0 rows updated). Your message has -100 as the ISAM error; that's the message for 'ISAM error: duplicate value for a record with unique key'.

But when I try:

insert into sample values(99, 'ninety-nine');
update sample set serial_number = 123 where serial_number = 99;

I get error:

SQL -268: Unique constraint (jleffler.u160_350) violated.
ISAM -100: ISAM error:  duplicate value for a record with unique key.

That's about what I'd expect. I note that you can't directly update a column of type SERIAL (or SERIAL8 or BIGSERIAL): you get SQL -232: A SERIAL column (s) may not be updated (where s was the name of the serial column I tried to update to generate the message — it's not a bad plural in the message).

Since the primary message for error -346 indicates that there could be a problem in the database, it would be worth running ON-Check (oncheck) on the system to see whether that table is all OK. If you're not in the habit of running ON-Check, you'll need to look up the options. I'd probably do some general checks (e.g. oncheck -cr to check the reserved pages) before going on to validate the table and the indexes where the problem is occurring (oncheck -ci and oncheck -cd). It may be worth using -cR, -cI, -cD options to get more information.