The unique key is col3 and col4 (SeqNum and MD5Sum).
If the insert fails for this unique key then col4 should be the same.
It should find the row for the even if I select for col4 only.
Hemant Shah
E-mail: hj...@yahoo.com
--- On Wed, 12/1/10, Jim Morris <jmor...@bearriver.com> wrote:
> From: Jim Morris <jmor...@bearriver.com>
> Subject: Re: [sqlite] Select fails even though data is in the table.
> To: sqlite-users@sqlite.org
> Date: Wednesday, December 1, 2010, 9:38 AM
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
