DAL: inconsistent behavior with constraint violation errors

[Hans Murx]

Hi,

given this legacy informix table:
~~~~~~~~~~~~~~~~~~~~~~~
create table kperson
(
name varchar(80, 1) not NULL,
age integer not NULL,
primary key (name)
);

I did:
~~~~
>>> db.define_table("kperson",
... Field("name"),
... Field("age"),
... primarykey=["name"],
... migrate=False
... )

Inserting one row:
~~~~~~~~~~~~~
>>> db.kperson.insert(name="Oli1", age=23)
{'name': 'Oli1'}

Ok!

inserting another row:
~~~~~~~~~~~~~~~~
>>> db.kperson.insert(name="Oli2", age=23)
{'name': 'Oli2'}

Ok!

trying to update the first row:
~~~~~~~~~~~~~~~~~~~~~
>>> db(db.kperson.name=="Oli1").update(name="Oli2")
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/root/web2py-1439/gluon/sql.py", line 3091, in update
self._db._execute(query)
File "/root/web2py-1439/gluon/sql.py", line 1086, in <lambda>
self._execute = lambda a: self._cursor.execute(a[:-1])
IntegrityError: SQLCODE -268 in EXECUTE:
23000: Integrity constraint violation
IX000: ISAM error: duplicate value for a record with unique key.

Ok, this IntegrityError was expected, because we tried to update name
to a value that already existed in the table.

But trying to insert a row with an already existing name:

>>> db.kperson.insert(name="Oli2", age=23)

does not give an error message at all - although the same constraint
violation occurs!?

Why is there no IntegrityError exception raised in this case? Same
strange behavior with appadmin: you can 'insert' new rows with
appadmin that violate constraints and therefore are not really
inserted into the table without even getting an error message. I don't
think that this behavior is consistent.

Regards

Hans

[mdipierro]

I agree this is strange but this seems a database problem to me.

[DenesL]

Hi Hans,

On Nov 19, 7:49 am, Hans Murx <murxun...@googlemail.com> wrote:
> Hi,
>
> given this legacy informix table:
> ~~~~~~~~~~~~~~~~~~~~~~~
> create table kperson
> (
> name varchar(80, 1) not NULL,
> age integer not NULL,
> primary key (name)
> );
>
> I did:
> ~~~~>>> db.define_table("kperson",
>
> ... Field("name"),
> ... Field("age"),

Field("age","integer"),

What is the return code of that insert?
If you get a 0 (zero) the operation was not successful.

>
> does not give an error message at all - although the same constraint
> violation occurs!?
>
> Why is there no IntegrityError exception raised in this case? Same
> strange behavior with appadmin: you can 'insert' new rows with
> appadmin that violate constraints and therefore are not really
> inserted into the table without even getting an error message. I don't
> think that this behavior is consistent.
>
> Regards
>
> Hans

Denes.

[Hans Murx]

database problem seems unlikely because doing the same sql statement
with informix sql tool, returns expected error messages:

$ echo "INSERT INTO kperson(name, age) VALUES ('Test', '23');" |
dbaccess stammdat

prints to stderr:

268: Unique constraint (root.u151_174) violated.
100: ISAM error: duplicate value for a record with unique key.

Regards,

Hans

~~~~~

[Olaf]

Hi Denes,

the second try to insert identical values returns None:

>>> db=DAL("informix://oli:XXXX@xeon2/stammdat")

>>> db.define_table("kperson",
... Field("name"),

... Field("age", "integer"),

... primarykey=["name"],
... migrate=False
... )

>>> a = db.kperson.insert(name="Test", age=23) # first successful insert
>>> print a
{'name': 'Test'}

>>> a = db.kperson.insert(name="Test", age=23) # second try, exception expected - but none
>>> print a
None

>>> db.kperson._insert(name="Test", age=23) # to see if the sql-statement is correct
"INSERT INTO kperson(name, age) VALUES ('Test', 23);"

doing the same sql statement via UNIX commandline:

$ echo "INSERT INTO kperson(name, age) VALUES ('Test', 23);" |
dbaccess stammdat

returns error messages as expected:

268: Unique constraint (root.u151_174) violated.

100: ISAM error: duplicate value for a record with unique key.

Regards

Hans

~~~~~

[DenesL]

Hi Hans,

actually insert should return the inserted record key
(not a return code as I said above).

So a return of None would mean no insert was done.

To be consistent maybe we should trap the exception on the update.
What do you think?.

Denes.

[mdipierro]

update returns the number of updated records so it is correct that
raises an exception on failure.

shouldn't instead insert raise an exception on failure too?