Why are you looking for a duplicate with col4 instead of the unique key,
col2, col3 that caused the collision?
On 12/1/2010 7:29 AM, Hemant Shah wrote:
> Folks,
>
> My C program creates a in-memory database. It creates a table and a unique
> index on two columns. If the insert fails due to unique index, it prints old
> row and new row. Sometimes it cannot find the old row even though the insert
> failed.
>
> Here is the pseudo code:
>
> CreateStmt = “create table mytable (TimeStamp char[50], col2 [char 10], col3
> int, col4 char[33]”;
> sqlite3_exec(CreateStmt)
>
> IndexStmt = “create unique index myidx (col3 asc, col4 asc)”;
> sqlite3_exec(IndexStmt);
>
> InsertStmt = “insert into mytable (TimeStamp, col2, col3, col4) values
> (?,?,?,?)”;
> sqlite3_prepare_v2(InsertStmt)
> sqlite3_bind calls for each column
> ReturnCode = sqlite3_step(InsertStmtHandle);
> if (ReturnCode != SQLITE_DONE)
> {
> sqlite3_reset(InsertStmtHandle);
> if (sqlite3_errcode(DbHandle) == SQLITE_CONSTRAINT)
> {
> /* duplicate row */
> sqlite3_finalize(InsertStmtHandle);
> sqlite3_exec(DbHandle, "COMMIT", NULL, NULL, NULL);
>
>
> SelectStmt = “select TimeStamp, col2, col3, col4 from mytable where
> col4 = ?”;
> sqlite3_prepare_v2(SelectStmt)
> sqlite3_bind calls
> ReturnCode = sqlite3_step(SelectStmtHandle);
> if (ReturnCode != SQLITE_ROW)
> {
> sqlite3_reset(SelectStmtHandle);
> printf("Row not found. ReturnCode: %d, Error Message:%s, Error
> Code: %d\n", ReturnCode, sqlite3_errmsg(DbHandle), sqlite3_errcode(DbHandle));
> }
> else
> {
> /* print row */
> }
> }
> else
> {
> /* other error
> }
> }
>
>
> The program prints ReturnCode as 101 which is SQLITE_DONE and error messages
> is “No error message”, errcode is 0.
>
> If the insert statement failed because of duplicate row, then why did it not
> find the old row?
>
> This only happens for some of the rows.
>
> I am inserting rows at very high rate (about every 50 microseconds) and only
> keep one minute worth of data in the table.
>
> How do I debug/fix this problem?
>
> Thanks.
>
>
>
> Hemant Shah
> E-mail: hj...@yahoo.com
>
>
>
> _______________________________________________
> 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
