-----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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
