Vincent Vega wrote:
> Well , since I do need to avoid a case where duplicated tags are
> inserted ,I have added a treatment to the case of SQLITE_CONSTRAINT.
OK, but what you have shown doesn't do anything useful. If your Tag
values must really be unique, then it is an error to insert a duplicate
Tag value. There is really nothing to do except possibly report the
error. If you believe the Tag value that are being inserted are supposed
to be unique then you might want to rollback your transaction on a
constraint error (which is what you get when you break the uniqueness
constraint on the Tag column).
> I am just curious whether I need to call sqlite3_finalize(Statement)
> after sqlite3_step(Statement) returns SQLITE_CONSTRAINT so that the
> memory that was allocated at sqlite3_mprintf be released ?
>
Yes and no. You do need to finalize the statement to release resources
allocated when the statement was prepared. In addition, you also need to
call sqlite3_free() to release the memory allocated by
sqlite3_mprintf(). These are separate allocations and require separate
release calls.
> Here are the table defenitions :
> CREATE TABLE 'Inventory' (
> 'TagIndex' integer PRIMARY KEY,
> 'Tag' varchar(12) NOT NULL,
> ) ;
> CREATE UNIQUE INDEX InventoryIndex ON Inventory (Tag);
>
The word unique between create and index is what causes the constraint
error you are seeing. This only happens if you insert a duplicate Tag value.
> My code looks like this:
>
> query = sqlite3_mprintf("Insert into Inventory (Tag) values (?)");
This allocates memory and saves a pointer to it in query.
> rc=sqlite3_prepare_v2(DB,query ,-1,&Statement,NULL);
This allocates a statement and saves a pointer to it in Statement.
> if (rc!=SQLITE_OK)
> error_print();
>
> sqlite3_exec(db,"BEGIN;",NULL,NULL,&err);
> for (i=1;i<500;i ++)
> {
> rc=sqlite3_bind_text(Statement,1,list[i],-1,NULL);
This binds the i'th value in the array list to the parameter that will
be inserted into the Tag column.
> if (rc!=SQLITE_OK)
> error_print();
> rc=sqlite3_step(Statement);
This executes the statement. Either inserting the value and assigning a
new TagIndex and then returning SQLITE_DONE, or doing nothing and
returning SQLITE_CONSTRAINT.
> if (rc!=SQLITE_DONE)
> {
> if (rc==SQLITE_CONSTRAINT )
> {
You end up here if your list has duplicate entries.
> query = sqlite3_mprintf("Insert into Inventory (Tag) values
> (?)");
This overwrites the pointer stored in query. The previous value is lost
and memory is leaked. You must use a different pointer or call
sqlite3_free() to release the old value first.
> rc=sqlite3_prepare_v2(DB,query ,-1,&Statement,NULL);
This overwrites the statement pointer stored in Statement. The previous
value is lost and memory is leaked. You must use a different pointer or
call sqlite3_finalize() to release the old value first.
> if (rc!=SQLITE_OK)
> error_print();
> }
> error_print();
> }
> sqlite3_reset(Statement) ;
> }
> sqlite3_finalize(Statement) ;
This releases the statement allocated by prepare above.
>
> sqlite3_exec(db,"END;",NULL,NULL,&err) ;
The string query is never released. Call sqlite3_free() to do so.
I'm not sure what you are trying to accomplish when you detect the
SQLITE_CONSTRAINT error returned from sqlite3_step(). Re-preparing the
same SQL statement again will not correct anything. Your list has real
duplicate values, you have told SQLite not to accept duplicate values,
and it has told you that you have tried to enter a duplicate value. You
really need to ignore the duplicate and continue (possibly reporting the
duplicate), or abort, fix your list to remove the duplicates, and then
try again later.
This code should probably be changed to something like this depending
upon exactly what you want to do if your list contains duplicates:
query = sqlite3_mprintf("Insert into Inventory (Tag) values (?)");
rc=sqlite3_prepare_v2(DB,query ,-1,&Statement,NULL);
if (rc!=SQLITE_OK)
error_print();
int abort = 0;
sqlite3_exec(db,"BEGIN;",NULL,NULL,&err);
for (i=1;i<500;i ++)
{
rc=sqlite3_bind_text(Statement,1,list[i],-1,NULL);
if (rc!=SQLITE_OK)
error_print();
rc=sqlite3_step(Statement);
if (rc!=SQLITE_DONE)
{
// SQLITE_CONSTRAINT error
abort = 1;
// uncomment this line to report errors
error_print();
// comment this line to ignore errors
break;
}
sqlite3_reset(Statement) ;
}
sqlite3_finalize(Statement) ;
sqlite3_free(query);
if (abort)
sqlite3_exec(db, "ROLLBACK", NULL, NULL, &err);
else
sqlite3_exec(db,"COMMIT",NULL,NULL,&err) ;
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
