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.
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.
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.
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
[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
Re: [sqlite] sqlite cannot find the row
If I use "length(MD5Sum) != 32" I get nothing. If I use "length(MD5Sum) == 32" I gat all rows. The length of the column should be 32. Hemant Shah E-mail: hj...@yahoo.com --- On Mon, 9/27/10, Hemant Shah <hj...@yahoo.com> wrote: > From: Hemant Shah <hj...@yahoo.com> > Subject: Re: [sqlite] sqlite cannot find the row > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > Date: Monday, September 27, 2010, 10:23 PM > I will try it tomorrow morning. I do > not have access right now. > > > Hemant Shah > E-mail: hj...@yahoo.com > > > --- On Mon, 9/27/10, Igor Tandetnik <itandet...@mvps.org> > wrote: > > > From: Igor Tandetnik <itandet...@mvps.org> > > Subject: Re: [sqlite] sqlite cannot find the row > > To: sqlite-users@sqlite.org > > Date: Monday, September 27, 2010, 10:14 PM > > Hemant Shah <hj...@yahoo.com> > > wrote: > > > The MD5Sum column is char[33], the sum is 32 > > characters long, the C datatype is also char[33] (i.e. > one > > extra character for NULL > > > termination). > > > > This is largely irrelevant to the issue at hand. > > > > > I thought the bind text column function stopped > at the > > first NULL character > > > > It does, if you pass -1 for the fourth parameter (the > > length). It doesn't if you pass an explicit length. > SQLite > > can happily store text with embedded NUL characters. > > > > > so the column should not have NULL character. > > > > Have you tried the query I suggested? Does or does not > it > > return any rows? You don't need to guess. > > -- > > Igor Tandetnik > > > > ___ > > 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] sqlite cannot find the row
I will try it tomorrow morning. I do not have access right now. Hemant Shah E-mail: hj...@yahoo.com --- On Mon, 9/27/10, Igor Tandetnik <itandet...@mvps.org> wrote: > From: Igor Tandetnik <itandet...@mvps.org> > Subject: Re: [sqlite] sqlite cannot find the row > To: sqlite-users@sqlite.org > Date: Monday, September 27, 2010, 10:14 PM > Hemant Shah <hj...@yahoo.com> > wrote: > > The MD5Sum column is char[33], the sum is 32 > characters long, the C datatype is also char[33] (i.e. one > extra character for NULL > > termination). > > This is largely irrelevant to the issue at hand. > > > I thought the bind text column function stopped at the > first NULL character > > It does, if you pass -1 for the fourth parameter (the > length). It doesn't if you pass an explicit length. SQLite > can happily store text with embedded NUL characters. > > > so the column should not have NULL character. > > Have you tried the query I suggested? Does or does not it > return any rows? You don't need to guess. > -- > Igor Tandetnik > > ___ > 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] sqlite cannot find the row
The MD5Sum column is char[33], the sum is 32 characters long, the C datatype is also char[33] (i.e. one extra character for NULL termination). I thought the bind text column function stopped at the first NULL character so the column should not have NULL character. Timestamp column is also bigger than timestamp text, but I can select based upon timestamp column. Hemant Shah E-mail: hj...@yahoo.com --- On Mon, 9/27/10, Igor Tandetnik <itandet...@mvps.org> wrote: > From: Igor Tandetnik <itandet...@mvps.org> > Subject: Re: [sqlite] sqlite cannot find the row > To: sqlite-users@sqlite.org > Date: Monday, September 27, 2010, 6:29 PM > Hemant Shah <hj...@yahoo.com> > wrote: > > I ran .dump command to dump the data to a file, and > ran .read command to read it into a different database. Now, > I could do a > > select based upon MD5Sum column. > > > > Next I edited the output file and changed the table > name. I ran .read and loaded the data into new table in > original database. > > Now, I could do a select based upon MD5Sum column. > > > > Any idea what could be wrong? > > Does this statement return any rows on the "bad" database > file: > > select * from find_retransmissions where length(MD5Sum) != > 32; > > My guess is, you managed to insert strings into MD5Sum > column complete with the trailing NUL byte. > -- > Igor Tandetnik > > > ___ > 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] sqlite cannot find the row
Here is the schema: create table find_retransmissions(InsTimeStamp int NOT NULL, SrcIp char[16] NOT NULL, DstIp char[16] NOT NULL, TimeStamp char[50] NOT NULL, SrcPort int NOT NULL, DstPort int NOT NULL, SeqNum int NOT NULL, MD5Sum char[33] NOT NULL) Hemant Shah E-mail: hj...@yahoo.com --- On Mon, 9/27/10, Timothy A. Sawyer <tsaw...@mybowlingdiary.com> wrote: > From: Timothy A. Sawyer <tsaw...@mybowlingdiary.com> > Subject: Re: [sqlite] sqlite cannot find the row > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > Date: Monday, September 27, 2010, 6:35 PM > Without seeing the original schema > and more information on what you changed, no idea. > > ** Sent from my mobile device with the tiny keys ** > > Timothy A. Sawyer, CISSP > Managing Director > MBD Consulting, LLC > 55 Madison Av., Suite 400 > Morristown, NJ 07960 > Phone: (973) 285-3261 | Fax: (973) 538-0503 > Web: http://www.mybowlingdiary.com > Email: tsaw...@mybowlingdiary.com > > > -Original Message- > From: Hemant Shah <hj...@yahoo.com> > Sender: sqlite-users-boun...@sqlite.org > Date: Mon, 27 Sep 2010 16:14:02 > To: General Discussion of SQLite Database<sqlite-users@sqlite.org> > Reply-To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Subject: Re: [sqlite] sqlite cannot find the row > > Sorry, > > I keep replying to my own posts, but I keep trying > different things to figure out the problem. > > I ran select for all other columns in the table and I was > able to get data, but when I run select for MD5Sum column I > do not get anything. > > > I ran .dump command to dump the data to a file, and ran > .read command to read it into a different database. Now, I > could do a select based upon MD5Sum column. > > Next I edited the output file and changed the table name. I > ran .read and loaded the data into new table in original > database. Now, I could do a select based upon MD5Sum > column. > > Any idea what could be wrong? > > > > Hemant Shah > E-mail: hj...@yahoo.com > > > --- On Mon, 9/27/10, Hemant Shah <hj...@yahoo.com> > wrote: > > > From: Hemant Shah <hj...@yahoo.com> > > Subject: Re: [sqlite] sqlite cannot find the row > > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > > Date: Monday, September 27, 2010, 5:43 PM > > Instead of in-memory database, I > > created database in a file and tried to do select with > same > > results. > > > > > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > > > 17.18.37.521968|10101|10101|4801345|fd5cb2d226b48d5dd4645d4fe7ca12cf > > > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > > > 17.18.37.522843|10101|10101|4801345|48cc79ae3c9029c82883843737f98ca1 > > > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > > > 17.18.37.523982|10101|10101|4801345|2522b8b56698cb805170497ebdab5858 > > > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > > > 17.18.37.524920|10101|10101|4801345|4ae983c9b1946dbc905d1253b71a > > > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > > > 17.18.37.525914|10101|10101|4801345|7d081eda7db33ea363b6c6655688f08d > > > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > > > 17.18.37.526910|10101|10101|4801345|105344cb171ecaf81798df75b8d3562e > > > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > > > 17.18.37.527911|10101|10101|4801345|34ab03b3543b1f0b2ca2f505fc6330f7 > > > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > > > 17.18.37.528910|10101|10101|4801345|8c72bd2c854598f9e817f9d85282aae8 > > > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > > > 17.18.37.529911|10101|10101|4801345|69b5c4d087a270b5b671a1765123ae8b > > > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > > > 17.18.37.530910|10101|10101|4801345|28c45b4ab9414819bb4c75ff37bf5a7e > > > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > > > 17.18.37.531911|10101|10101|4801345|2c726e0c63bda2d30e3f9ea38df5ece0 > > > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > > > 17.18.37.532920|10101|10101|4801345|77ac658bcc98321e298cd52d07dcc8d5 > > > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > > > 17.18.37.534031|10101|10101|4801345|0a41725b5b1a183ebf40489f2037f00d > > > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > > > 17.18.37.535178|10101|10101|4801345|652575b633b6f4aefe5b6ddb5b085c06 > > > > 1285625919|10.208.54.32|239.90.91.101|2010-09-27 > > > 17.18.37.
Re: [sqlite] sqlite cannot find the row
Sorry, I keep replying to my own posts, but I keep trying different things to figure out the problem. I ran select for all other columns in the table and I was able to get data, but when I run select for MD5Sum column I do not get anything. I ran .dump command to dump the data to a file, and ran .read command to read it into a different database. Now, I could do a select based upon MD5Sum column. Next I edited the output file and changed the table name. I ran .read and loaded the data into new table in original database. Now, I could do a select based upon MD5Sum column. Any idea what could be wrong? Hemant Shah E-mail: hj...@yahoo.com --- On Mon, 9/27/10, Hemant Shah <hj...@yahoo.com> wrote: > From: Hemant Shah <hj...@yahoo.com> > Subject: Re: [sqlite] sqlite cannot find the row > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > Date: Monday, September 27, 2010, 5:43 PM > Instead of in-memory database, I > created database in a file and tried to do select with same > results. > > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.37.521968|10101|10101|4801345|fd5cb2d226b48d5dd4645d4fe7ca12cf > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.37.522843|10101|10101|4801345|48cc79ae3c9029c82883843737f98ca1 > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.37.523982|10101|10101|4801345|2522b8b56698cb805170497ebdab5858 > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.37.524920|10101|10101|4801345|4ae983c9b1946dbc905d1253b71a > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.37.525914|10101|10101|4801345|7d081eda7db33ea363b6c6655688f08d > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.37.526910|10101|10101|4801345|105344cb171ecaf81798df75b8d3562e > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.37.527911|10101|10101|4801345|34ab03b3543b1f0b2ca2f505fc6330f7 > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.37.528910|10101|10101|4801345|8c72bd2c854598f9e817f9d85282aae8 > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.37.529911|10101|10101|4801345|69b5c4d087a270b5b671a1765123ae8b > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.37.530910|10101|10101|4801345|28c45b4ab9414819bb4c75ff37bf5a7e > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.37.531911|10101|10101|4801345|2c726e0c63bda2d30e3f9ea38df5ece0 > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.37.532920|10101|10101|4801345|77ac658bcc98321e298cd52d07dcc8d5 > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.37.534031|10101|10101|4801345|0a41725b5b1a183ebf40489f2037f00d > > 1285625918|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.37.535178|10101|10101|4801345|652575b633b6f4aefe5b6ddb5b085c06 > > 1285625919|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.37.536085|10101|10101|4801345|3a632902e3e07bc7c20e130baf941a33 > > 1285625919|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.37.537085|10101|10101|4801345|5a44897aef3d81d70dee8246d4a7748f > > 1285625919|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.37.538077|10101|10101|4801345|fd6a9a130a5c3bb46ee0e20bf6f02b52 > > 1285625919|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.37.539090|10101|10101|4801345|2efba1c039c0f41361945a27a3e651cd > > 1285625919|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.37.540077|10101|10101|4801345|1fde5a8f289b299139c26260c75a6df0 > > 1285625920|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.40.481153|10101|10101|4801345|6d7fd3d66e0eb709c38440546a9e3a15 > > 1285625920|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.40.481746|10101|10101|4801345|f236a2ecf56f038ff132b91ba94287f4 > > sqlite> > > sqlite> > > sqlite> > > sqlite> select * from find_retransmissions where > InsTimeStamp=1285625920; > > 1285625920|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.40.481153|10101|10101|4801345|6d7fd3d66e0eb709c38440546a9e3a15 > > 1285625920|10.208.54.32|239.90.91.101|2010-09-27 > 17.18.40.481746|10101|10101|4801345|f236a2ecf56f038ff132b91ba94287f4 > > sqlite> select * from find_retransmissions where > MD5Sum='f236a2ecf56f038ff132b91ba94287f4'; > > sqlite> > > select * displays whole table. If I do select using another > column I get appropriate rows, but if I do select on MD5Sum > column it returns nothing. > > > > Hemant Shah > E-mail: hj...@yahoo.com > > > --- On Mon, 9/27/10, Hemant Shah <hj...@yahoo.com> > wrote: > > > From: Hemant Shah <hj...@yahoo.com> > > Subject: Re: [sqlite] sqlite cannot find the row > > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Re: [sqlite] sqlite cannot find the row
Instead of in-memory database, I created database in a file and tried to do select with same results. 1285625918|10.208.54.32|239.90.91.101|2010-09-27 17.18.37.521968|10101|10101|4801345|fd5cb2d226b48d5dd4645d4fe7ca12cf 1285625918|10.208.54.32|239.90.91.101|2010-09-27 17.18.37.522843|10101|10101|4801345|48cc79ae3c9029c82883843737f98ca1 1285625918|10.208.54.32|239.90.91.101|2010-09-27 17.18.37.523982|10101|10101|4801345|2522b8b56698cb805170497ebdab5858 1285625918|10.208.54.32|239.90.91.101|2010-09-27 17.18.37.524920|10101|10101|4801345|4ae983c9b1946dbc905d1253b71a 1285625918|10.208.54.32|239.90.91.101|2010-09-27 17.18.37.525914|10101|10101|4801345|7d081eda7db33ea363b6c6655688f08d 1285625918|10.208.54.32|239.90.91.101|2010-09-27 17.18.37.526910|10101|10101|4801345|105344cb171ecaf81798df75b8d3562e 1285625918|10.208.54.32|239.90.91.101|2010-09-27 17.18.37.527911|10101|10101|4801345|34ab03b3543b1f0b2ca2f505fc6330f7 1285625918|10.208.54.32|239.90.91.101|2010-09-27 17.18.37.528910|10101|10101|4801345|8c72bd2c854598f9e817f9d85282aae8 1285625918|10.208.54.32|239.90.91.101|2010-09-27 17.18.37.529911|10101|10101|4801345|69b5c4d087a270b5b671a1765123ae8b 1285625918|10.208.54.32|239.90.91.101|2010-09-27 17.18.37.530910|10101|10101|4801345|28c45b4ab9414819bb4c75ff37bf5a7e 1285625918|10.208.54.32|239.90.91.101|2010-09-27 17.18.37.531911|10101|10101|4801345|2c726e0c63bda2d30e3f9ea38df5ece0 1285625918|10.208.54.32|239.90.91.101|2010-09-27 17.18.37.532920|10101|10101|4801345|77ac658bcc98321e298cd52d07dcc8d5 1285625918|10.208.54.32|239.90.91.101|2010-09-27 17.18.37.534031|10101|10101|4801345|0a41725b5b1a183ebf40489f2037f00d 1285625918|10.208.54.32|239.90.91.101|2010-09-27 17.18.37.535178|10101|10101|4801345|652575b633b6f4aefe5b6ddb5b085c06 1285625919|10.208.54.32|239.90.91.101|2010-09-27 17.18.37.536085|10101|10101|4801345|3a632902e3e07bc7c20e130baf941a33 1285625919|10.208.54.32|239.90.91.101|2010-09-27 17.18.37.537085|10101|10101|4801345|5a44897aef3d81d70dee8246d4a7748f 1285625919|10.208.54.32|239.90.91.101|2010-09-27 17.18.37.538077|10101|10101|4801345|fd6a9a130a5c3bb46ee0e20bf6f02b52 1285625919|10.208.54.32|239.90.91.101|2010-09-27 17.18.37.539090|10101|10101|4801345|2efba1c039c0f41361945a27a3e651cd 1285625919|10.208.54.32|239.90.91.101|2010-09-27 17.18.37.540077|10101|10101|4801345|1fde5a8f289b299139c26260c75a6df0 1285625920|10.208.54.32|239.90.91.101|2010-09-27 17.18.40.481153|10101|10101|4801345|6d7fd3d66e0eb709c38440546a9e3a15 1285625920|10.208.54.32|239.90.91.101|2010-09-27 17.18.40.481746|10101|10101|4801345|f236a2ecf56f038ff132b91ba94287f4 sqlite> sqlite> sqlite> sqlite> select * from find_retransmissions where InsTimeStamp=1285625920; 1285625920|10.208.54.32|239.90.91.101|2010-09-27 17.18.40.481153|10101|10101|4801345|6d7fd3d66e0eb709c38440546a9e3a15 1285625920|10.208.54.32|239.90.91.101|2010-09-27 17.18.40.481746|10101|10101|4801345|f236a2ecf56f038ff132b91ba94287f4 sqlite> select * from find_retransmissions where MD5Sum='f236a2ecf56f038ff132b91ba94287f4'; sqlite> select * displays whole table. If I do select using another column I get appropriate rows, but if I do select on MD5Sum column it returns nothing. Hemant Shah E-mail: hj...@yahoo.com --- On Mon, 9/27/10, Hemant Shah <hj...@yahoo.com> wrote: > From: Hemant Shah <hj...@yahoo.com> > Subject: Re: [sqlite] sqlite cannot find the row > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > Date: Monday, September 27, 2010, 4:22 PM > Sorry about that. No I do not have > single quotes around ?. > > strcpy(SqlString, "INSERT INTO > find_retransmissions(InsTimeStamp, SrcIp, DstIp, TimeStamp, > SrcP ort, DstPort, SeqNum, MD5Sum) VALUES > (?,?,?,?,?,?,?,?)"); > > sprintf(SqlString, "SELECT SrcIp, DstIp, TimeStamp, > SrcPort, DstPort, SeqNum FROM find_retransmissions WHERE > MD5Sum = ?"); > > > I do not have quotes in both statemens, but insert > statement works. > > > Hemant Shah > E-mail: hj...@yahoo.com > > > --- On Mon, 9/27/10, Black, Michael (IS) <michael.bla...@ngc.com> > wrote: > > > From: Black, Michael (IS) <michael.bla...@ngc.com> > > Subject: Re: [sqlite] sqlite cannot find the row > > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > > Date: Monday, September 27, 2010, 2:27 PM > > Your code is chopped off and I > > suspect the problem is on the remainder of this line: > > > > sprintf(SqlString, "SELECT SrcIp, DstIp, TimeStamp, > > SrcPort, DstPort, SeqNum > > > > For example, did you put single quotes around the ? > > parameter? > > > > > > Michael D. Black > > Senior Scientist > > Advanced Analytics Directorate > > Northrop Gr
Re: [sqlite] sqlite cannot find the row
Sorry about that. No I do not have single quotes around ?.
strcpy(SqlString, "INSERT INTO find_retransmissions(InsTimeStamp, SrcIp, DstIp,
TimeStamp, SrcP ort, DstPort, SeqNum, MD5Sum) VALUES (?,?,?,?,?,?,?,?)");
sprintf(SqlString, "SELECT SrcIp, DstIp, TimeStamp, SrcPort, DstPort, SeqNum
FROM find_retransmissions WHERE MD5Sum = ?");
I do not have quotes in both statemens, but insert statement works.
Hemant Shah
E-mail: hj...@yahoo.com
--- On Mon, 9/27/10, Black, Michael (IS) <michael.bla...@ngc.com> wrote:
> From: Black, Michael (IS) <michael.bla...@ngc.com>
> Subject: Re: [sqlite] sqlite cannot find the row
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Date: Monday, September 27, 2010, 2:27 PM
> Your code is chopped off and I
> suspect the problem is on the remainder of this line:
>
> sprintf(SqlString, "SELECT SrcIp, DstIp, TimeStamp,
> SrcPort, DstPort, SeqNum
>
> For example, did you put single quotes around the ?
> parameter?
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
>
>
> From: sqlite-users-boun...@sqlite.org
> on behalf of Hemant Shah
> Sent: Mon 9/27/2010 2:22 PM
> To: General Discussion of SQLite Database
> Subject: EXTERNAL:Re: [sqlite] sqlite cannot find the row
>
>
>
> Here is the snippet of code:
>
> int GetMessageFromDB(char *MD5Sum, struct ReceiveNode
> *FromDb)
> {
> int ReturnCode;
> sqlite3_stmt *SelectStmtHandle;
>
> sprintf(SqlString, "SELECT SrcIp, DstIp,
> TimeStamp, SrcPort, DstPort, SeqNum
> ReturnCode = sqlite3_prepare(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);
> }
>
> sqlite3_bind_text(SelectStmtHandle, 1,
> MD5Sum, strlen(MD5Sum), SQLITE_TRANSIE
> 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);
> }
>
> strcpy(FromDb->SourceIP,
> sqlite3_column_text(SelectStmtHandle, 0));
> strcpy(FromDb->DestIP,
> sqlite3_column_text(SelectStmtHandle, 1));
> strcpy(FromDb->TimeStamp,
> sqlite3_column_text(SelectStmtHandle, 2));
> FromDb->SourcePort =
> sqlite3_column_int(SelectStmtHandle, 3);
> FromDb->DestPort =
> sqlite3_column_int(SelectStmtHandle, 4);
> FromDb->SeqNum =
> sqlite3_column_int(SelectStmtHandle, 5);
> strncpy(FromDb->MD5Sum, MD5Sum,
> MD5SUMLEN);
> sqlite3_finalize(SelectStmtHandle);
> return(1);
> }
>
> int GetMessageFromDB(char *MD5Sum, struct ReceiveNode
> *FromDb)
> {
> int ReturnCode;
> sqlite3_stmt *SelectStmtHandle;
>
> sprintf(SqlString, "SELECT SrcIp, DstIp,
> TimeStamp, SrcPort, DstPort, SeqNum
> ReturnCode = sqlite3_prepare(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);
> }
>
> sqlite3_bind_text(SelectStmtHandle, 1,
> MD5Sum, strlen(MD5Sum), SQLITE_TRANSIE
> 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);
> }
>
> strcpy(FromDb->SourceIP,
> sqlite3_column_text(SelectStmtHandle, 0));
> strcpy(FromDb->DestIP,
> sqlite3_column_text(SelectStmtHandle, 1));
> strcpy(FromDb->TimeStamp,
> sqlite3_column_text(SelectStmtHandle, 2));
> FromDb->SourcePort =
> sqlite3_column_int(SelectStmtHandle, 3);
> FromDb->DestPort =
> sqlite3_column_int(SelectStmtHandle, 4);
> FromDb->SeqNum =
> sqlite3_column_int(SelectStmtHandle, 5);
> strncpy(FromDb->MD5Sum, MD5Sum,
> MD5SUMLEN);
> sqlite3_finalize(SelectStmtHandle);
> return(1);
> }
>
> if (FindDuplicateMessage(ReceiveBuf) == 1)
> {
> GetMessageFromDB(ReceiveBuf.MD5Sum,
> );
>
Re: [sqlite] sqlite cannot find the row
Here is the snippet of code:
int GetMessageFromDB(char *MD5Sum, struct ReceiveNode *FromDb)
{
int ReturnCode;
sqlite3_stmt *SelectStmtHandle;
sprintf(SqlString, "SELECT SrcIp, DstIp, TimeStamp, SrcPort, DstPort, SeqNum
ReturnCode = sqlite3_prepare(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);
}
sqlite3_bind_text(SelectStmtHandle, 1, MD5Sum, strlen(MD5Sum), SQLITE_TRANSIE
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);
}
strcpy(FromDb->SourceIP, sqlite3_column_text(SelectStmtHandle, 0));
strcpy(FromDb->DestIP, sqlite3_column_text(SelectStmtHandle, 1));
strcpy(FromDb->TimeStamp, sqlite3_column_text(SelectStmtHandle, 2));
FromDb->SourcePort = sqlite3_column_int(SelectStmtHandle, 3);
FromDb->DestPort = sqlite3_column_int(SelectStmtHandle, 4);
FromDb->SeqNum = sqlite3_column_int(SelectStmtHandle, 5);
strncpy(FromDb->MD5Sum, MD5Sum, MD5SUMLEN);
sqlite3_finalize(SelectStmtHandle);
return(1);
}
int GetMessageFromDB(char *MD5Sum, struct ReceiveNode *FromDb)
{
int ReturnCode;
sqlite3_stmt *SelectStmtHandle;
sprintf(SqlString, "SELECT SrcIp, DstIp, TimeStamp, SrcPort, DstPort, SeqNum
ReturnCode = sqlite3_prepare(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);
}
sqlite3_bind_text(SelectStmtHandle, 1, MD5Sum, strlen(MD5Sum), SQLITE_TRANSIE
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);
}
strcpy(FromDb->SourceIP, sqlite3_column_text(SelectStmtHandle, 0));
strcpy(FromDb->DestIP, sqlite3_column_text(SelectStmtHandle, 1));
strcpy(FromDb->TimeStamp, sqlite3_column_text(SelectStmtHandle, 2));
FromDb->SourcePort = sqlite3_column_int(SelectStmtHandle, 3);
FromDb->DestPort = sqlite3_column_int(SelectStmtHandle, 4);
FromDb->SeqNum = sqlite3_column_int(SelectStmtHandle, 5);
strncpy(FromDb->MD5Sum, MD5Sum, MD5SUMLEN);
sqlite3_finalize(SelectStmtHandle);
return(1);
}
if (FindDuplicateMessage(ReceiveBuf) == 1)
{
GetMessageFromDB(ReceiveBuf.MD5Sum, );
}
When I execute this functions I get following output.
Cannot insert into database. column MD5Sum is not unique
error code = SQLITE_CONSTRAINT
Row not found. ReturnCode: 101, Error Message:not an error, Error Code: 0
101 is SQLITE_DONE. So I get SQLITE_DONE instead of SQLITE_ROW.
Hemant Shah
E-mail: hj...@yahoo.com
--- On Sun, 9/26/10, Black, Michael (IS) <michael.bla...@ngc.com> wrote:
> From: Black, Michael (IS) <michael.bla...@ngc.com>
> Subject: Re: [sqlite] sqlite cannot find the row
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Date: Sunday, September 26, 2010, 8:14 AM
> You don't provide enough into to tell
> what the problem is.
>
> This works for me...so what did you do different?
> Simplify your problem to a complete example like this and we
> can help better...
>
>
> #include
> #include "sqlite3.h"
> int main()
> {
> int rc;
> char *md5sum="dd5b8911bf377682d8963a859b8c2055";
> sqlite3 *db;
> sqlite3_stmt *stmt;
> remove("test.db");
> sqlite3_open_v2("test.db", , SQLITE_OPEN_READWRITE
> | SQLITE_OPEN_CREATE, 0);
> sqlite3_prepare(db, "CREATE TABLE t (md5sum varchar)",
> -1,, 0);
> rc = sqlite3_step(stmt);
> if (rc != SQLITE_DONE) printf("CREATE %d
> %s\n",rc,sqlite3_errmsg(db));
> sqlite3_prepare(db,"INSERT INTO t VALUES(?)",-1,,
> 0);
> sqlite3_bind_text(stmt,1,md5sum,-1,SQLITE_TRANSIENT);
> rc = sqlite3_step(stmt);
> if (rc != SQLITE_DONE) printf("INSERT: %d
> %s\n",rc,sqlite3_errmsg(db));
> sqlite3_prepare(db,"SELECT * FROM t WHERE
> md5sum=?",-1,, 0);
> sqlite3_bind_text(stmt,1,md5sum,-1,SQLITE_TRANSIENT);
> rc = sqlite3_step(stmt);
> if (rc==SQLITE_ROW) {
> const char *s=sqlite3_column_text(stmt,0);
> printf("%s\n",s);
> }
> else {
> printf("SELECT %d %s\n",rc,sq
[sqlite] sqlite cannot find the row
Folks
I have a C program that creates in-memory database with unique column.
My code basically inserts row in database and if it gets duplicate row it
selects the duplicate row from database and prints information.
The problem is that when I select the row it says row not found.
I simplified the code as follows:
sqlite3_prepare("insert statement")
sqlite3_bind columns
sqlite3_step
sqlite3_finalize
sqlite3_exec COMMIT
When I insert the row I get following error message:
column MD5Sum is not unique
So I immediately do a select as follows:
sqlite3_prepare("select statement")
sqlite3_bind column
sqlite3_step
sqlite3_finalize
But I do not get SQLITE_ROW return code. Here is my code.
sqlite3_prepare(select statement)
sqlite3_bind_text(SelectStmtHandle, 1, MD5Sum, strlen(MD5Sum),
SQLITE_TRANSIENT);
ReturnCode = sqlite3_step(SelectStmtHandle);
if (ReturnCode != SQLITE_ROW)
{
sqlite3_reset(SelectStmtHandle);
printf("Row not found. %s\n", sqlite3_errmsg(DbHandle));
return(2);
}
Here is the message it prints:
Row not found. not an error
I am new to sqlite, what am I doing wrong?
What return code should I get if there is only one row in result set?
I print the columns before I do insert and I can see that I have duplicate row.
Sixth column (MD5SUM) is the unique column.
1219 10.136.28.11244376 239.56.112.112 8146
dd5b8911bf377682d8963a859b8c2055 2010-09-24 14.11.07.545436
1219 10.136.28.11244376 239.56.112.112 8146
dd5b8911bf377682d8963a859b8c2055 2010-09-24 14.11.07.545855
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] SQL Logic error for in-memory database
After executing sqlite3_step(), I check to see if the return code is SQLITE_OK
if (rc != SQLITE_OK)
{
printf("%s\n", sqlite3_errmsg(dbhandle);
}
I will add sqlite3_reset() before calling sqlite3_errmsg().
Hemant Shah
E-mail: hj...@yahoo.com
--- On Wed, 9/15/10, Roger Binns <rog...@rogerbinns.com> wrote:
> From: Roger Binns <rog...@rogerbinns.com>
> Subject: Re: [sqlite] SQL Logic error for in-memory database
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Date: Wednesday, September 15, 2010, 3:05 PM
> -BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 09/15/2010 11:53 AM, Hemant Shah wrote:
> > The error occurs while calling sqlite3_step().
>
> Call sqlite3_reset and then get the error message text.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkyRJu8ACgkQmOOfHg372QQV/gCeP+54VCTxuNoQMh2oClW07Tr/
> XA8Ani+2fvvjxo2hvBO6/N3SfkVXgUeR
> =/Hs4
> -END PGP SIGNATURE-
> ___
> 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] SQL Logic error for in-memory database
The error occurs while calling sqlite3_step(). Hemant Shah E-mail: hj...@yahoo.com --- On Wed, 9/15/10, Roger Binns <rog...@rogerbinns.com> wrote: > From: Roger Binns <rog...@rogerbinns.com> > Subject: Re: [sqlite] SQL Logic error for in-memory database > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > Date: Wednesday, September 15, 2010, 1:29 AM > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 09/14/2010 07:41 PM, Hemant Shah wrote: > > Sometimes I get following error: > > > > SQL logic error or missing database. > > That text corresponds to the error code SQLITE_ERROR which > is the code used > for a wide variety of error conditions that don't have a > more specific code. > > In any event you haven't even told us which API is > returning the code. > > http://www.chiark.greenend.org.uk/~sgtatham/bugs.html > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.10 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ > > iEYEARECAAYFAkyQZ9EACgkQmOOfHg372QRwlQCg3abzvZCSB83x4gxJ9422oiiQ > BrwAn0g4/NSB3XqALkDYx641B7jTNfIn > =hMN8 > -END PGP SIGNATURE- > ___ > 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] SQL Logic error for in-memory database
--- On Tue, 9/14/10, Simon Slavin <slav...@bigfraud.org> wrote: > From: Simon Slavin <slav...@bigfraud.org> > Subject: Re: [sqlite] SQL Logic error for in-memory database > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > Date: Tuesday, September 14, 2010, 9:51 PM > > On 15 Sep 2010, at 3:41am, Hemant Shah wrote: > > > I have written a C program that creates in-memory > database. It reads packets from the network and insert some > info in the database. > > > > Sometimes I get following error: > > > > SQL logic error or missing database. > > Does the error occur only when starting your program, or > sometimes during a long run which had gone okay up to that > point ? It occurs after it is running for a while. If I ignore the error and continue, the inserts are successful for few more hours then I again get error. > > Is the database accessed from more than one thread or > process ? > No it is only accessed by one process (no threads). > Are you intentionally using NULL values anywhere ? > No. all columns are defined with NOT NULL. > Simon. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > 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] SQL Logic error for in-memory database
Folks, I have written a C program that creates in-memory database. It reads packets from the network and insert some info in the database. Sometimes I get following error: SQL logic error or missing database. I prepare statement, bind columns and run step. After every insert I also delete rows more than one minute old. The program may inserts between 10 to 100 rows a millisecond. There are no issues with memory or CPU. The system has two quad core CPUs and 24GB of memory. My program takes maximum of 200 MB of memory. Each row is about 250KB. What could be the 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
Re: [sqlite] In memory database and locking.
--- On Thu, 9/9/10, Kees Nuyt <k.n...@zonnet.nl> wrote: > From: Kees Nuyt <k.n...@zonnet.nl> > Subject: Re: [sqlite] In memory database and locking. > To: sqlite-users@sqlite.org > Date: Thursday, September 9, 2010, 11:48 AM > On Wed, 8 Sep 2010 21:03:05 -0700 > (PDT), Hemant Shah > <hj...@yahoo.com> > wrote: > > > I forgot to mention, that I have set pragma > journal_mode to off > > and for every insert I do prepare -> step -> > finalize -> COMMIT. > > That's a weird sequence. > Because you use the same statement all the time, you only > have to prepare() once, and then for every INSERT: > bind(); step(); reset(); clear_bindings(); > > Also, COMMIT doesn't make sense without a BEGIN. > For speed, wrap several (or even many) INSERTs in one > BEGIN > / COMMIT pair. > Without BEGIN; COMMIT, every single INSERT is a > transaction > by itself. > Where can I find good example of this. I have not found a good example of insert and select. I am used to DB2 and Oracle, this is first time I am working with sqlite. > Finalize at program exit (or not at all). > -- > ( Kees Nuyt > ) > c[_] > _______ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 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] In memory database and locking.
--- On Thu, 9/9/10, Black, Michael (IS) <michael.bla...@ngc.com> wrote: > From: Black, Michael (IS) <michael.bla...@ngc.com> > Subject: Re: [sqlite] In memory database and locking. > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > Date: Thursday, September 9, 2010, 9:16 AM > I've never seen an application that > would run faster in ANY database vs custom code. > Databases are for generic query problems...not the end-all > to "store my data" when speed is a concern. > I will see how sqlite works out otherwise I will write my own hash code. > I've pointed out a few times on this list where people are > concerned for speed and showed things like 30X faster using > your own. I've done network data acquisition like this > before and I'll guarantee that you will never keep up with a > data burst on a gigabit network. I don't even think > winpcap can do that let alone a database. > > I don't think you need to run your trigger every > minute. Just run it every insert. I think the > delete will be notably faster than the insert and you won't > notice the difference vs running every 60 seconds. > > What you will want to do is only do a commit every so > often...I think you stated you're doing a commit every > packet which would be dog slow. > I agree with you about commiting every insert. I am going to experiment with different times and see where I get best performance. Right now I have one process doing insert and every 5000 rows do a delete. > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] > On Behalf Of Hemant Shah > Sent: Thursday, September 09, 2010 8:57 AM > To: General Discussion of SQLite Database > Subject: EXTERNAL:Re: [sqlite] EXTERNAL: In memory database > and locking. > > How do I setup trigger to run every minute? > > I thought about writing hash code, but thought sqlite or > other in memory database would work. The in memory database > seems to keep up with the in coming traffic. > > Hemant Shah > > E-mail: hj...@yahoo.com > > --- On Thu, 9/9/10, Black, Michael (IS) <michael.bla...@ngc.com> > wrote: > > From: Black, Michael (IS) <michael.bla...@ngc.com> > Subject: Re: [sqlite] EXTERNAL: In memory database and > locking. > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > Date: Thursday, September 9, 2010, 7:48 AM > > Have you considered doing your cleanup during a trigger? > I assume you're already using transactions for your > inserts. I wouldn't > think it would be much slower doing it every insert as > you'd be deleting > a much smaller set every time. > > This is really a LOT faster if you just hash your info and > then > periodically walk the hash table to delete old stuff. A > database is > never going to keep up with a gigabit interface. > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] > On Behalf Of Hemant Shah > Sent: Wednesday, September 08, 2010 10:55 PM > To: sqlite-users@sqlite.org > Subject: EXTERNAL:[sqlite] In memory database and locking. > > Folks, > I am trying to write an application that reads packets from > the network > and inserts it into sqlite database, I have a unique key > which is > combination of couple of columns. I want to find > re-transmitted packets > so I rely on the fact that if I violate unique key > constraint then I > have found the duplicate packet. Also, I just want to > compare it with > packets received within last minute. One of the column is > timestamp. > I am using C API and statically link sqlite 3.7.2 with my > application. > Here is what I am doing. When I start my application it > creates the > database and table and then forks two processes. One > process reads > packets from network and inserts information about it in > the database, > if insert fails then it has found re-transmission and it > executes the > select statement to get the information about previous > packet and print > information about both packets. > The other process wakes up every 60 seconds and deletes all > row whose > timestamp columns is less then (current timestamp - 60). > The timestamp > is number of seconds since epoch. > The first process is constantly inserting rows into the > database, so the > other process cannot delete any rows. When I use :memory: > for database I > do not get any error but it does not delete any rows as the > memory > footprint of my program keeps on increasing.If I use a file > for database > I get error that database is locked. >
Re: [sqlite] In memory database and locking.
--- On Thu, 9/9/10, Simon Slavin <slav...@bigfraud.org> wrote: From: Simon Slavin <slav...@bigfraud.org> Subject: Re: [sqlite] In memory database and locking. To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Date: Thursday, September 9, 2010, 8:24 AM On 9 Sep 2010, at 4:55am, Hemant Shah wrote: > The other process wakes up every 60 seconds and deletes all row whose > timestamp columns is less then (current timestamp - 60). The timestamp is > number of seconds since epoch. Do you do this using a single DELETE FROM command ? Do you have an index on the timestamp column so it can find the appropriate rows quickly and easily ? Yes, I run "DELETE FROM table WHERE instimestamp < deletetimestamp". I have index on this column. > The first process is constantly inserting rows into the database, so the > other process cannot delete any rows. I would probably merge the processes. Have the one that does the inserting issue the DELETE FROM command. So it could do a DELETE after every INSERT. Or better still, after every 100 INSERTs or something. I think this might work. I will give it a try. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 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] EXTERNAL: In memory database and locking.
How do I setup trigger to run every minute? I thought about writing hash code, but thought sqlite or other in memory database would work. The in memory database seems to keep up with the in coming traffic. Hemant Shah E-mail: hj...@yahoo.com --- On Thu, 9/9/10, Black, Michael (IS) <michael.bla...@ngc.com> wrote: From: Black, Michael (IS) <michael.bla...@ngc.com> Subject: Re: [sqlite] EXTERNAL: In memory database and locking. To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Date: Thursday, September 9, 2010, 7:48 AM Have you considered doing your cleanup during a trigger? I assume you're already using transactions for your inserts. I wouldn't think it would be much slower doing it every insert as you'd be deleting a much smaller set every time. This is really a LOT faster if you just hash your info and then periodically walk the hash table to delete old stuff. A database is never going to keep up with a gigabit interface. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Hemant Shah Sent: Wednesday, September 08, 2010 10:55 PM To: sqlite-users@sqlite.org Subject: EXTERNAL:[sqlite] In memory database and locking. Folks, I am trying to write an application that reads packets from the network and inserts it into sqlite database, I have a unique key which is combination of couple of columns. I want to find re-transmitted packets so I rely on the fact that if I violate unique key constraint then I have found the duplicate packet. Also, I just want to compare it with packets received within last minute. One of the column is timestamp. I am using C API and statically link sqlite 3.7.2 with my application. Here is what I am doing. When I start my application it creates the database and table and then forks two processes. One process reads packets from network and inserts information about it in the database, if insert fails then it has found re-transmission and it executes the select statement to get the information about previous packet and print information about both packets. The other process wakes up every 60 seconds and deletes all row whose timestamp columns is less then (current timestamp - 60). The timestamp is number of seconds since epoch. The first process is constantly inserting rows into the database, so the other process cannot delete any rows. When I use :memory: for database I do not get any error but it does not delete any rows as the memory footprint of my program keeps on increasing.If I use a file for database I get error that database is locked. Both of these processes are sibling and have same database handle. When I read the documentation I found that in-memory database always uses EXCLUSIVE lock. How do I solve 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] In memory database and locking.
vacuum is a no-op for in memory database. Hemant Shah E-mail: hj...@yahoo.com --- On Wed, 9/8/10, Woody & Yuni Ho <hwoody2w...@yahoo.com> wrote: From: Woody & Yuni Ho <hwoody2w...@yahoo.com> Subject: Re: [sqlite] In memory database and locking. To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Date: Wednesday, September 8, 2010, 11:08 PM Have you tried issueing a vacum after n deletes? Woody wizard at large(I'm in shape. Round is a shape) Connected by MOTOBLUR™ on T-Mobile -----Original message- From: Hemant Shah <hj...@yahoo.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Wed, Sep 8, 2010 21:03:21 PDT Subject: Re: [sqlite] In memory database and locking. I forgot to mention, that I have set pragma journal_mode to off and for every insert I doprepare -> step -> finalize -> COMMIT. Hemant Shah E-mail: hj...@yahoo.com --- On Wed, 9/8/10, Hemant Shah <hj...@yahoo.com> wrote: From: Hemant Shah <hj...@yahoo.com> Subject: [sqlite] In memory database and locking. To: sqlite-users@sqlite.org Date: Wednesday, September 8, 2010, 10:55 PM Folks, I am trying to write an application that reads packets from the network and inserts it into sqlite database, I have a unique key which is combination of couple of columns. I want to find re-transmitted packets so I rely on the fact that if I violate unique key constraint then I have found the duplicate packet. Also, I just want to compare it with packets received within last minute. One of the column is timestamp. I am using C API and statically link sqlite 3.7.2 with my application. Here is what I am doing. When I start my application it creates the database and table and then forks two processes. One process reads packets from network and inserts information about it in the database, if insert fails then it has found re-transmission and it executes the select statement to get the information about previous packet and print information about both packets. The other process wakes up every 60 seconds and deletes all row whose timestamp columns is less then (current timestamp - 60). The timestamp is number of seconds since epoch. The first process is constantly inserting rows into the database, so the other process cannot delete any rows. When I use :memory: for database I do not get any error but it does not delete any rows as the memory footprint of my program keeps on increasing.If I use a file for database I get error that database is locked. Both of these processes are sibling and have same database handle. When I read the documentation I found that in-memory database always uses EXCLUSIVE lock. How do I solve 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In memory database and locking.
I forgot to mention, that I have set pragma journal_mode to off and for every insert I doprepare -> step -> finalize -> COMMIT. Hemant Shah E-mail: hj...@yahoo.com --- On Wed, 9/8/10, Hemant Shah <hj...@yahoo.com> wrote: From: Hemant Shah <hj...@yahoo.com> Subject: [sqlite] In memory database and locking. To: sqlite-users@sqlite.org Date: Wednesday, September 8, 2010, 10:55 PM Folks, I am trying to write an application that reads packets from the network and inserts it into sqlite database, I have a unique key which is combination of couple of columns. I want to find re-transmitted packets so I rely on the fact that if I violate unique key constraint then I have found the duplicate packet. Also, I just want to compare it with packets received within last minute. One of the column is timestamp. I am using C API and statically link sqlite 3.7.2 with my application. Here is what I am doing. When I start my application it creates the database and table and then forks two processes. One process reads packets from network and inserts information about it in the database, if insert fails then it has found re-transmission and it executes the select statement to get the information about previous packet and print information about both packets. The other process wakes up every 60 seconds and deletes all row whose timestamp columns is less then (current timestamp - 60). The timestamp is number of seconds since epoch. The first process is constantly inserting rows into the database, so the other process cannot delete any rows. When I use :memory: for database I do not get any error but it does not delete any rows as the memory footprint of my program keeps on increasing.If I use a file for database I get error that database is locked. Both of these processes are sibling and have same database handle. When I read the documentation I found that in-memory database always uses EXCLUSIVE lock. How do I solve 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] In memory database and locking.
Folks, I am trying to write an application that reads packets from the network and inserts it into sqlite database, I have a unique key which is combination of couple of columns. I want to find re-transmitted packets so I rely on the fact that if I violate unique key constraint then I have found the duplicate packet. Also, I just want to compare it with packets received within last minute. One of the column is timestamp. I am using C API and statically link sqlite 3.7.2 with my application. Here is what I am doing. When I start my application it creates the database and table and then forks two processes. One process reads packets from network and inserts information about it in the database, if insert fails then it has found re-transmission and it executes the select statement to get the information about previous packet and print information about both packets. The other process wakes up every 60 seconds and deletes all row whose timestamp columns is less then (current timestamp - 60). The timestamp is number of seconds since epoch. The first process is constantly inserting rows into the database, so the other process cannot delete any rows. When I use :memory: for database I do not get any error but it does not delete any rows as the memory footprint of my program keeps on increasing.If I use a file for database I get error that database is locked. Both of these processes are sibling and have same database handle. When I read the documentation I found that in-memory database always uses EXCLUSIVE lock. How do I solve 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
