Re: [sqlite] Select fails even though data is in the table.
It is hard to debug using debugger because the process is reading from a message queue and if I stop at a break point, the message queue will fill up and the process writing to the queue will start having problems. This is a in-memory database so I cannot query from the command line. I tried to create database on the disk but the I/O to disk cannot keep up with the input. Hemant Shah E-mail: hj...@yahoo.com --- On Wed, 12/1/10, Black, Michael (IS) <michael.bla...@ngc.com> wrote: > From: Black, Michael (IS) <michael.bla...@ngc.com> > Subject: Re: [sqlite] Select fails even though data is in the table. > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > Date: Wednesday, December 1, 2010, 10:37 AM > To debug set your breakpoint on your > error statement. > Then examine col4 and build the SQL statement yourself > while still at the breakpoint. > Then run the command line shell and execute the SQL and see > what you get. > Theoretically you should see the same problem. > > In order to prove it you may need to build your SQL string > in your program and execute it by itself instead of using > prepared statements -- then you would defintely be comparing > apples-to-apples. You can't really see what the > prepared statement looks like. Then you can print out > the SQL statement that fails and perhaps see what's going > on. > > > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Northrop Grumman Information Systems > > > > > From: sqlite-users-boun...@sqlite.org > on behalf of Hemant Shah > Sent: Wed 12/1/2010 10:24 AM > To: General Discussion of SQLite Database > Subject: EXTERNAL:Re: [sqlite] Select fails even though > data is in the table. > > > > This is a single thread/process. No other thread or process > is accessing the data. > > This is a single process that reads data from message queue > and dumps into database to look for duplicate rows. > > The problem occurs for some rows only (about 3 to 5 an > hour). > > > 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, 10:15 AM > > If you have another thread running > > that deletes or modifies the table, > > then move the commit to after the select for duplicate > to > > ensure > > transactional integrity. > > > > On 12/1/2010 8:10 AM, Black, Michael (IS) wrote: > > > The problem is probably in the bind calls that > you are > > not showing. > > > If you care to share them we may be able to > help. > > > > > > Michael D. Black > > > Senior Scientist > > > Advanced Analytics Directorate > > > Northrop Grumman Information Systems > > > > > > > > > > > > > > > From: sqlite-users-boun...@sqlite.org > > on behalf of Hemant Shah > > > Sent: Wed 12/1/2010 10:08 AM > > > To: General Discussion of SQLite Database > > > Subject: EXTERNAL:Re: [sqlite] Select fails even > > though data is in the table. > > > > > > > > > > > > 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 > > > > > > > > > > > > > > > > > > > > > ___ > > > 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 > > > > -Inline Attachment Follows- > > ___ > 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
Re: [sqlite] Select fails even though data is in the table.
Duplicate row is because of uniqueness of col3 and col4. I will try to change the code to select for col3 and col4, but I am not sure it would make any difference. 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, 10:29 AM > I still think you should use the same > columns for searching for the > duplicate that cause the collision. Using > col4 seem problematic. Can > you change the code to use col2 and col3? > > On 12/1/2010 8:24 AM, Hemant Shah wrote: > > This is a single thread/process. No other thread or > process is accessing the data. > > > > This is a single process that reads data from message > queue and dumps into database to look for duplicate rows. > > > > The problem occurs for some rows only (about 3 to 5 an > hour). > > > > > > 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, 10:15 AM > >> If you have another thread running > >> that deletes or modifies the table, > >> then move the commit to after the select for > duplicate to > >> ensure > >> transactional integrity. > >> > >> On 12/1/2010 8:10 AM, Black, Michael (IS) wrote: > >>> The problem is probably in the bind calls that > you are > >> not showing. > >>> If you care to share them we may be able to > help. > >>> > >>> Michael D. Black > >>> Senior Scientist > >>> Advanced Analytics Directorate > >>> Northrop Grumman Information Systems > >>> > >>> > >>> > >>> > >>> From: sqlite-users-boun...@sqlite.org > >> on behalf of Hemant Shah > >>> Sent: Wed 12/1/2010 10:08 AM > >>> To: General Discussion of SQLite Database > >>> Subject: EXTERNAL:Re: [sqlite] Select fails > even > >> though data is in the table. > >>> > >>> > >>> 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 > >>> > >>> > >>> > >>> > >>> > >>> > >>> > ___ > >>> 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 > > > ___ > 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
Re: [sqlite] Select fails even though data is in the table.
Here is the snippet of the code:
int InsertIntoDatabase(char *TimeStamp, int Source, u_int32_t SeqNum, char
*MD5Sum)
{
int ReturnCode;
sqlite3_stmt *InsertStmtHandle;
strcpy(SqlString, "INSERT INTO mytable(TimeStamp, col2, col3, col4) VALUES
(?,?,?,?)");
ReturnCode = sqlite3_prepare_v2(DbHandle, SqlString, -1, ,
NULL);
if (ReturnCode != SQLITE_OK || InsertStmtHandle == NULL)
{
sqlite3_reset(InsertStmtHandle);
printf("Cannot prepare insert statement. %s\n", sqlite3_errmsg(DbHandle));
return(2);
}
ReturnCode = sqlite3_bind_text(InsertStmtHandle, 1, TimeStamp, -1,
SQLITE_TRANSIENT);
if (ReturnCode != SQLITE_OK)
{
sqlite3_reset(InsertStmtHandle);
printf("Error binding insert statement 1. ReturnCode: %d, Error
Message:%s, Error Code: %d\n",
ReturnCode, sqlite3_errmsg(DbHandle), sqlite3_errcode(DbHandle));
return(2);
}
ReturnCode = sqlite3_bind_int(InsertStmtHandle, 2, Source);
if (ReturnCode != SQLITE_OK)
{
sqlite3_reset(InsertStmtHandle);
printf("Error binding insert statement 2. ReturnCode: %d, Error
Message:%s, Error Code: %d\n",
ReturnCode, sqlite3_errmsg(DbHandle), sqlite3_errcode(DbHandle));
return(2);
}
ReturnCode = sqlite3_bind_int(InsertStmtHandle, 3, SeqNum);
if (ReturnCode != SQLITE_OK)
{
sqlite3_reset(InsertStmtHandle);
printf("Error binding insert statement 3. ReturnCode: %d, Error
Message:%s, Error Code: %d\n",
ReturnCode, sqlite3_errmsg(DbHandle), sqlite3_errcode(DbHandle));
return(2);
}
ReturnCode = sqlite3_bind_text(InsertStmtHandle, 4, MD5Sum, -1,
SQLITE_TRANSIENT);
if (ReturnCode != SQLITE_OK)
{
sqlite3_reset(InsertStmtHandle);
printf("Error binding insert statement 4. ReturnCode: %d, Error
Message:%s, Error Code: %d\n",
ReturnCode, sqlite3_errmsg(DbHandle), sqlite3_errcode(DbHandle));
return(2);
}
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);
return(1);
}
else
{
/*
* Other error.
*/
printf("Cannot insert into database. %s\n", sqlite3_errmsg(DbHandle));
sqlite3_finalize(InsertStmtHandle);
sqlite3_exec(DbHandle, "COMMIT", NULL, NULL, NULL);
return(2);
}
}
sqlite3_finalize(InsertStmtHandle);
sqlite3_exec(DbHandle, "COMMIT", NULL, NULL, NULL);
return(0);
}
int GetMessageFromDB(char *MD5Sum, struct ReceiveNode *FromDb)
{
int ReturnCode;
sqlite3_stmt *SelectStmtHandle;
strcpy(SqlString, "SELECT TimeStamp, col2, col3, col4 FROM mytable WHERE
col4 = ?");
ReturnCode = sqlite3_prepare_v2(DbHandle, SqlString, -1, ,
NULL);
if (ReturnCode != SQLITE_OK || SelectStmtHandle == NULL)
{
sqlite3_reset(SelectStmtHandle);
printf("Cannot prepare select statement. %s\n", sqlite3_errmsg(DbHandle));
return(2);
}
ReturnCode = sqlite3_bind_text(SelectStmtHandle, 1, MD5Sum, -1,
SQLITE_TRANSIENT);
if (ReturnCode != SQLITE_OK)
{
sqlite3_reset(SelectStmtHandle);
printf("Error binding select statement. ReturnCode: %d, Error Message:%s,
Error Code: %d\n",
ReturnCode, sqlite3_errmsg(DbHandle), sqlite3_errcode(DbHandle));
return(2);
}
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));
return(2);
}
/* Copy data to FromDb structure) */
sqlite3_finalize(SelectStmtHandle);
return(1);
}
/* Main */
if (InsertIntoDatabase(TimeStamp, Source, SeqNum, MD5Sum) == 1)
{
if (GetMessageFromDB(MD5Sum, ) == 1)
{
/* print message */
}
else
{
/* print error message */
}
}
Hemant Shah
E-mail: hj...@yahoo.com
--- On Wed, 12/1/10, Black, Michael (IS) <michael.bla...@ngc.com> wrote:
> From: Black, Michael (IS) <michael.bla...@ngc.com>
> Subject: Re: [sqlite] Select fails even though data is in the table.
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Date: Wednesday, December 1, 2010, 10:23 AM
> If you bind the wrong thing the wrong
> way the return code doesn't matter.
>
> Any particular reason you can't just show us the whole code
> section?
>
> Michael D. Black
> Senior Scientist
> Advan
Re: [sqlite] Select fails even though data is in the table.
To debug set your breakpoint on your error statement. Then examine col4 and build the SQL statement yourself while still at the breakpoint. Then run the command line shell and execute the SQL and see what you get. Theoretically you should see the same problem. In order to prove it you may need to build your SQL string in your program and execute it by itself instead of using prepared statements -- then you would defintely be comparing apples-to-apples. You can't really see what the prepared statement looks like. Then you can print out the SQL statement that fails and perhaps see what's going on. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Hemant Shah Sent: Wed 12/1/2010 10:24 AM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] Select fails even though data is in the table. This is a single thread/process. No other thread or process is accessing the data. This is a single process that reads data from message queue and dumps into database to look for duplicate rows. The problem occurs for some rows only (about 3 to 5 an hour). 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, 10:15 AM > If you have another thread running > that deletes or modifies the table, > then move the commit to after the select for duplicate to > ensure > transactional integrity. > > On 12/1/2010 8:10 AM, Black, Michael (IS) wrote: > > The problem is probably in the bind calls that you are > not showing. > > If you care to share them we may be able to help. > > > > Michael D. Black > > Senior Scientist > > Advanced Analytics Directorate > > Northrop Grumman Information Systems > > > > > > > > > > From: sqlite-users-boun...@sqlite.org > on behalf of Hemant Shah > > Sent: Wed 12/1/2010 10:08 AM > > To: General Discussion of SQLite Database > > Subject: EXTERNAL:Re: [sqlite] Select fails even > though data is in the table. > > > > > > > > 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 > > > > > > > > > > > > > > ___ > > 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select fails even though data is in the table.
I still think you should use the same columns for searching for the duplicate that cause the collision. Using col4 seem problematic. Can you change the code to use col2 and col3? On 12/1/2010 8:24 AM, Hemant Shah wrote: > This is a single thread/process. No other thread or process is accessing the > data. > > This is a single process that reads data from message queue and dumps into > database to look for duplicate rows. > > The problem occurs for some rows only (about 3 to 5 an hour). > > > 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, 10:15 AM >> If you have another thread running >> that deletes or modifies the table, >> then move the commit to after the select for duplicate to >> ensure >> transactional integrity. >> >> On 12/1/2010 8:10 AM, Black, Michael (IS) wrote: >>> The problem is probably in the bind calls that you are >> not showing. >>> If you care to share them we may be able to help. >>> >>> Michael D. Black >>> Senior Scientist >>> Advanced Analytics Directorate >>> Northrop Grumman Information Systems >>> >>> >>> ____________ >>> >>> From: sqlite-users-boun...@sqlite.org >> on behalf of Hemant Shah >>> Sent: Wed 12/1/2010 10:08 AM >>> To: General Discussion of SQLite Database >>> Subject: EXTERNAL:Re: [sqlite] Select fails even >> though data is in the table. >>> >>> >>> 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 >>> >>> >>> >>> >>> >>> >>> ___ >>> 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select fails even though data is in the table.
This is a single thread/process. No other thread or process is accessing the data. This is a single process that reads data from message queue and dumps into database to look for duplicate rows. The problem occurs for some rows only (about 3 to 5 an hour). 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, 10:15 AM > If you have another thread running > that deletes or modifies the table, > then move the commit to after the select for duplicate to > ensure > transactional integrity. > > On 12/1/2010 8:10 AM, Black, Michael (IS) wrote: > > The problem is probably in the bind calls that you are > not showing. > > If you care to share them we may be able to help. > > > > Michael D. Black > > Senior Scientist > > Advanced Analytics Directorate > > Northrop Grumman Information Systems > > > > > > > > > > From: sqlite-users-boun...@sqlite.org > on behalf of Hemant Shah > > Sent: Wed 12/1/2010 10:08 AM > > To: General Discussion of SQLite Database > > Subject: EXTERNAL:Re: [sqlite] Select fails even > though data is in the table. > > > > > > > > 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 > > > > > > > > > > > > > > ___ > > 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
Re: [sqlite] Select fails even though data is in the table.
If you bind the wrong thing the wrong way the return code doesn't matter. Any particular reason you can't just show us the whole code section? Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Hemant Shah Sent: Wed 12/1/2010 10:21 AM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] Select fails even though data is in the table. I check for the return code after each bind call and if it is not SQLITE_OK then I return with error. So I do not think it is a bind problem. 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
Re: [sqlite] Select fails even though data is in the table.
I check for the return code after each bind call and if it is not SQLITE_OK then I return with error. So I do not think it is a bind problem. Hemant Shah E-mail: hj...@yahoo.com --- On Wed, 12/1/10, Black, Michael (IS) <michael.bla...@ngc.com> wrote: > From: Black, Michael (IS) <michael.bla...@ngc.com> > Subject: Re: [sqlite] Select fails even though data is in the table. > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > Date: Wednesday, December 1, 2010, 10:10 AM > The problem is probably in the bind > calls that you are not showing. > If you care to share them we may be able to help. > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Northrop Grumman Information Systems > > > > > From: sqlite-users-boun...@sqlite.org > on behalf of Hemant Shah > Sent: Wed 12/1/2010 10:08 AM > To: General Discussion of SQLite Database > Subject: EXTERNAL:Re: [sqlite] Select fails even though > data is in the table. > > > > 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 > > > > > > -Inline Attachment Follows- > > ___ > 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
Re: [sqlite] Select fails even though data is in the table.
If you have another thread running that deletes or modifies the table, then move the commit to after the select for duplicate to ensure transactional integrity. On 12/1/2010 8:10 AM, Black, Michael (IS) wrote: > The problem is probably in the bind calls that you are not showing. > If you care to share them we may be able to help. > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Northrop Grumman Information Systems > > > > > From: sqlite-users-boun...@sqlite.org on behalf of Hemant Shah > Sent: Wed 12/1/2010 10:08 AM > To: General Discussion of SQLite Database > Subject: EXTERNAL:Re: [sqlite] Select fails even though data is in the table. > > > > 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 > > > > > > > ___ > 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
Re: [sqlite] Select fails even though data is in the table.
The problem is probably in the bind calls that you are not showing. If you care to share them we may be able to help. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Hemant Shah Sent: Wed 12/1/2010 10:08 AM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] Select fails even though data is in the table. 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select fails even though data is in the table.
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
Re: [sqlite] Select fails even though data is in the table.
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] Select fails even though data is in the table.
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
