Sub-optimal error message, different behaviour for Cursor.execute with/without bindings

[rogerbinns]

From basti1302 on April 02, 2008 03:10:38

The method Cursor.executes seems to behaves differently when used with the
optional bindings argument compared to using it without the bindings
argument. More specifically, the error message when trying to insert a
NULL value into an PRIMARY KEY NOT NULL column (without autoincrement in
place) is different. The error message in the first case (with bindings)
is less helpful than it could be. I'm not sure if this is intended
behaviour, IMHO it's a bit confusing.

The following program demonstrates the difference:

import apsw

print "apsw.apswversion(): " + apsw.apswversion()
print "apsw.sqlitelibversion(): " + apsw.sqlitelibversion()

connection = apsw.Connection("dbfile")
cursor = connection.cursor()

try:
print "test 1"
try:
cursor.execute("DROP TABLE A_TABLE")
cursor.execute("CREATE TABLE A_TABLE (ID ABC PRIMARY KEY NOT NULL,
SOME_VALUE TEXT)")
cursor.execute("INSERT INTO A_TABLE VALUES (NULL, 'Foobar')")
except Exception, e:
print e

print "test 2"
try:
    cursor.execute("DROP TABLE A_TABLE")
    cursor.execute("CREATE TABLE A_TABLE (ID ABC PRIMARY KEY NOT NULL, 

SOME_VALUE TEXT)")
cursor.execute("INSERT INTO A_TABLE VALUES (NULL, ?)", ("Foobar",))
except Exception, e:
print e

finally:
connection.close()

The output of this program is:

apsw.apswversion(): 3.3.13- r1 apsw.sqlitelibversion(): 3.3.13
test 1
ConstraintError: A_TABLE.ID may not be NULL
test 2
ConstraintError: not an error

In both tests, a table is created with an ID column and a value column.
The column affinity of ID is set to ABC (which is a nonsense value), so it
defaults to NUMERIC, afaik. The point is that it the column is not INTEGER
PRIMARY KEY NOT NULL, so we have no autoincrement, as specified in the
sqlite docs. Because of that, inserting NULL should be illegal.

In test 1, the exception raised is "ConstraintError: A_TABLE.ID may not be
NULL", as expected. However, in test 2, the exception is "ConstraintError:
not an error" which sounds a bit paradox to me.

Note 1: This is on Windows 2000, Python 2.5

Note 2: This may or may not be related to this bug report: http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=456500 but I don't really know how this Debian package bug reports relate to apsw
bug reports.

Regards
Bastian Krol

Original issue: http://code.google.com/p/apsw/issues/detail?id=4

[rogerbinns]

From rogerbinns on April 05, 2008 03:47:17

Reproduced with current development apsw and sqlite 3.5.7.

In general the underlying cause is that SQLite is very finnicky and counter-intuitive
about when error text is available. In this case apsw is getting the constraint
error code but getting the generic unset error text.

Thanks for reporting the problem.

Status: Accepted

[rogerbinns]

From rogerbinns on April 16, 2008 20:41:18

I have found the root cause which is an inconsistency in the way SQLite does things,
but I'll have to work around it. Basically the error code looks like this:

 sqlite3_reset(stmt);
 sqlite3_clear_bindings(stmt);
 sqlite3_errmsg(db);

If the error was caused by a binding then calling sqlite3_clear_bindings clears the
error message as well.

[rogerbinns]

From rogerbinns on April 16, 2008 21:14:59

Fixed in r122 . Worked around "bug" in SQLite reported at http://www.sqlite.org/cvstrac/tktview?tn=3063

Status: Verified

[rogerbinns]

From rogerbinns on May 16, 2008 02:20:49

SQLite fixed the issue in 3.5.9 so undo r122 in r136

[rogerbinns]

From rogerbinns on June 30, 2009 22:13:11

This issue was closed by 92c20b5636.

Status: Fixed