Re: [sqlite] EXT : An interesting (strange) issue with selects

[Simon Slavin]

On 21 Jun 2012, at 2:55pm, Dennis Volodomanov  wrote:

> On 21/06/2012 11:47 PM, Simon Slavin wrote:
>> Are you examining the result code returned by the SELECT commands ?  I bet 
>> in the cases where the select gets zero rows, it isn't returning SQLITE_OK 
>> but it's instead returning some sort of error code.
> 
> Yes, I do examine those and the return is SQLITE_DONE in this case. I 
> typically check for SQLITE_ROW to get the results and bail out on anything 
> else. The prepare does return a SQLITE_OK.

Hmm.  Okay, well I'm out of ideas.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT : An interesting (strange) issue with selects

[Dennis Volodomanov]

On 21/06/2012 11:47 PM, Simon Slavin wrote:

Are you examining the result code returned by the SELECT commands ?  I bet in 
the cases where the select gets zero rows, it isn't returning SQLITE_OK but 
it's instead returning some sort of error code.

Simon.


Hi Simon,

Yes, I do examine those and the return is SQLITE_DONE in this case. I 
typically check for SQLITE_ROW to get the results and bail out on 
anything else. The prepare does return a SQLITE_OK.


   Dennis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT : An interesting (strange) issue with selects

[Simon Slavin]

On 21 Jun 2012, at 2:44pm, Dennis Volodomanov  wrote:

> I'm using SQLite in C++ code (Windows, MSVC2008, amalgamation). The data is 
> inserted using sqlite3_bind_int64()/sqlite3_bind_int(). What I mean by "bomb 
> out" is that it executes this pair of statements tens of thousands of times 
> (count, then get one row) and works (e.g. "count" returns more than one and 
> "select" returns one row) and then, once in a while, count returns more than 
> one, but select doesn't get anything.

Are you examining the result code returned by the SELECT commands ?  I bet in 
the cases where the select gets zero rows, it isn't returning SQLITE_OK but 
it's instead returning some sort of error code.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT : An interesting (strange) issue with selects

[Dennis Volodomanov]

Michael,

Thanks for the reply. I know, it's usually the user :)

On 21/06/2012 11:31 PM, Black, Michael (IS) wrote:


You don't show where you inserted your data.

Are you postiive ColC is an integer and you didn't insert it as a string?

You don't show a dump of your table which would be handy.

What does "bomb" mean?  Your program gets a seg fault or such?

What are you programming in, on what OS?

I can tell you now if there isn't a simple answer you need to make a 
complete example so somebody else can reproduce the problem and see 
what you're doing wrong (in all likelihood it's probably you).





I'm using SQLite in C++ code (Windows, MSVC2008, amalgamation). The data 
is inserted using sqlite3_bind_int64()/sqlite3_bind_int(). What I mean 
by "bomb out" is that it executes this pair of statements tens of 
thousands of times (count, then get one row) and works (e.g. "count" 
returns more than one and "select" returns one row) and then, once in a 
while, count returns more than one, but select doesn't get anything.


   Dennis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT : An interesting (strange) issue with selects

[Black, Michael (IS)]

You don't show where you inserted your data.

Are you postiive ColC is an integer and you didn't insert it as a string?



You don't show a dump of your table which would be handy.



What does "bomb" mean?  Your program gets a seg fault or such?



What are you programming in, on what OS?



I can tell you now if there isn't a simple answer you need to make a complete 
example so somebody else can reproduce the problem and see what you're doing 
wrong (in all likelihood it's probably you).





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Dennis Volodomanov [i...@psunrise.com]
Sent: Thursday, June 21, 2012 8:15 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] An interesting (strange) issue with selects

Hello all,

I've been using SQLite for quite a few years, but have just recently
started exploring WAL mode (may or may not be related to WAL) and I'm
experiencing an interesting issue that perhaps is known to others, so
I've decided to ask for your wisdom. This is the amalgamation 3.7.13.

Let's consider the following setup - pragmas used (there's also a bunch
of indexes, but I believe those shouldn't affect the problem):

"PRAGMA journal_mode = WAL;";
"PRAGMA synchronous = NORMAL;";
"PRAGMA page_size = 4096;";
"PRAGMA read_uncommitted = 1;";

Also, some defines when building:

#define SQLITE_OMIT_DEPRECATED
#define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT 5000
#define SQLITE_THREADSAFE 2
#define SQLITE_ENABLE_STAT3

Table:

"CREATE TABLE TableA (ColA INTEGER, ColB INTEGER, ColC INTEGER)";

Now the problem - the first SQL returns SQLITE_ROW and 1 as
sqlite3_column_int(), while the second doesn't find any items:

SELECT COUNT(ColA) FROM TableA;

SELECT ColA FROM CriteriaItemsToProcess WHERE ColC=0 LIMIT 1;

The data in the table is like this (that's the only one row):

1| 12| 0

Just as a side note, the same SQL/functions above work 99.9% of the
time, but bomb out once in a while. Most likely this doesn't help, but
thought I'd mention it.

I appreciate you reading so far and hopefully you can help me out.

Best regards,

Dennis

___
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