Now fixed here:
http://www.sqlite.org/src/ci/f660be615a
For those playing at home, this is an example of how to write
a great bug report. Clear, concise explanation and a
self-contained minimal example.
Dan.
On Apr 30, 2010, at 8:51 AM, Roger Binns wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> I prepare a statement with one binding that results in a constraint
> failure
> (correctly). Then I reset it, clear the bindings and bind a second
> value.
> I still get a constraint failure even though it shouldn't (it was
> reset).
> If I prepare a new statement then it works ok.
>
> Setup SQL:
>
> PRAGMA FOREIGN_KEYS = ON;
> CREATE TABLE main(id INTEGER PRIMARY KEY);
> CREATE TABLE sub(id INT NOT NULL REFERENCES main(id));
> INSERT INTO main VALUES(1);
> INSERT INTO main VALUES(2);
> INSERT INTO sub VALUES(1)
>
> stmt=prepare("DELETE FROM main WHERE id=?")
> sqlite3_bind_int(stmt, 1, 1);
>
> A step gives rc of 19 (SQLITE_CONSTRAINT) (correct)
> A reset then gives rc of 19
>
> sqlite3_bind_int(stmt, 1, 2)
> step and reset now also give SQLITE_CONSTRAINT - this is not correct
>
> If I prepare a new statement (same code and binding of 2) then it
> works
> correctly.
>
> Actual C code below:
>
> #include <stdio.h>
> #include <assert.h>
> #include <sqlite3.h>
>
> int main(int argc, char **argv)
> {
> int rc;
> sqlite3 *db;
> char *error=NULL;
> sqlite3_stmt *stmt=NULL, *stmt2=NULL;;
>
> rc=sqlite3_open(":memory:", &db);
> assert(rc==SQLITE_OK);
>
> rc=sqlite3_exec(db,
> "PRAGMA FOREIGN_KEYS = ON; "
> "CREATE TABLE main(id INTEGER PRIMARY KEY); "
> "CREATE TABLE sub(id INT NOT NULL REFERENCES main(id)); "
> "INSERT INTO main VALUES(1); "
> "INSERT INTO main VALUES(2); "
> "INSERT INTO sub VALUES(1) ",
> NULL, NULL, &error);
> assert(rc==SQLITE_OK);
>
> rc=sqlite3_prepare_v2(db, "DELETE FROM main WHERE id=?", -1, &stmt,
> NULL);
> assert(rc==SQLITE_OK);
> assert(stmt);
>
> /* the statement will cause a constraint error with bindings of 1 */
> rc=sqlite3_bind_int(stmt, 1, 1);
> assert(rc==SQLITE_OK);
> rc=sqlite3_step(stmt);
> assert(rc!=SQLITE_DONE);
> /* reset it */
> rc=sqlite3_reset(stmt);
> assert(rc==SQLITE_CONSTRAINT);
> /* clear bindings */
> rc=sqlite3_clear_bindings(stmt);
> assert(rc==SQLITE_OK);
>
> /* now execute with binding of 2 which should be okay */
> rc=sqlite3_bind_int(stmt, 1, 2);
> assert(rc==SQLITE_OK);
> rc=sqlite3_step(stmt);
> if(rc!=SQLITE_DONE)
> {
> printf("ERROR:\nrc=%d resetrc=%d errmsg=%s\n", rc,
> sqlite3_reset(stmt), sqlite3_errmsg(db));
> }
>
> /* Re-execute with a freshly prepared statement */
> rc=sqlite3_prepare_v2(db, "DELETE FROM main WHERE id=?",
> -1, &stmt2, NULL);
> assert(rc==SQLITE_OK);
> assert(stmt2);
> rc=sqlite3_bind_int(stmt2, 1, 2);
> assert(rc==SQLITE_OK);
> rc=sqlite3_step(stmt2);
> assert(rc==SQLITE_DONE);
>
> return 0;
> }
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkvaN6gACgkQmOOfHg372QQdEACgizqs2Ua4fWeR5TYZTtJbUsnA
> VlUAn0tOgT/VUaSIaoG1HijwFGDXk9+M
> =Auzd
> -----END PGP SIGNATURE-----
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
