Re: [sqlite] Use of sqlite3_step()

[Mike Shal]

On 7/6/10, Andrew Wood  wrote:
> Whats the procedure for using sqlite3_step?
>
>  I thought it would be along the lines of the following, but the while
>  loop executes infinately. I would have expected the while loop to run
>  once for each row found?
>
>  Code is as follows:
>
>  int queryreturn = sqlite3_step(preparedstatement);
>
>  if (queryreturn == SQLITE_DONE)
>  {
>  //no rows found
>  return;
>
>  }
>  else if (queryreturn == SQLITE_ROW)
>  {
>  //row found, will process in a sec
>  }
>  else
>  {
>  //error, throw exception
>
>  }
>  //if we get here we have 1 or more rows to process
>
>  while (queryreturn==SQLITE_ROW)
>  {//process row
>  printf("row found\n");
>
>  //advance to next row
>  int queryreturn = sqlite3_step(preparedstatement);
^^^

I haven't tried it, but I suspect this is your problem. It looks like
you're declaring a new 'queryreturn' int, but the while loop is
checking against the one from the top of the program. You can probably
just remove the 'int' here.

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

Re: [sqlite] sqlite3_close ==> " library routine called out of sequence"

[Mike Shal]

On 2/24/10, Jean-Denis Muys  wrote:
>
>  On 2/24/10 9:36 , "Simon Davies"  wrote:
>
>  > Rogue semicolon on line
>  >
>  >>  if( sqlite3_close( db ) != SQLITE_OK );
>  >
>  > Regards,
>  > Simon
>
>
> I have been bitten by such silly mistakes often enough!
>
>  This is why I have switched to a compiler with sanity checks. On this line
>  of code, it warns:
>
>  "if statement has empty body"
>
>  This compiler is llvm. Check it out at http://llvm.org
>  You can test for this mistake on the demo page at http://llvm.org/demo
>

What compiler were you using before? Gcc warns about this too, though
you have to explicitly enable the warning using -W (or -Wempty-body
for just that particular warning).

I suppose you could argue whether or not it's right to have that
warning on by default, but I've found warnings are generally a matter
of personal preference. If you don't make spurious semicolon mistakes,
you probably don't need that warning on. I make tons of such mistakes,
so I use a variant of the BDECFLAGS. That helps catch most of the
stupid things I do :)

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

Re: [sqlite] performance of select in transactions

[Mike Shal]

On 10/2/09, Pavel Ivanov  wrote:
> > It seems a bit disingenuous to claim
>  > there will be no performance gain by putting selects in a transaction,
>  > when sqlite clearly does less work with the transaction (in the form
>  > of not getting the read lock multiple times).
>
>
> It's pretty ingenuous in fact to silently assume that nobody wants to
>  do 5 selects at once. Lower that to 5 (already a huge amount of
>  work for "one request to database" that blocks out any writers) or to
>  1 (the real number when somebody speaks about "difference of putting
>  select inside transaction") and you'll get a difference of 0.08 ms or
>  0.016 ms which is pretty small number to ignore when it's compared to
>  the time of actual reading from disk for the actual select.

Sorry, I was not aware of these implicit assumptions. Nor did I mean
to imply that you were being disingenuous -- you have answered in 10
minutes what took me several hours to narrow down to my original
example. I guess my usage is a bit peculiar, where the results of a
single select must be analyzed to determine what to select next, and
this can potentially cascade into many other selects, which must also
be analyzed, and so on. During this time no writes are allowed (due to
other requirements that I have), so for me there is no other
consideration as to whether or not it is in a transaction.

Thanks for your help,
-Mike
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] performance of select in transactions

[Mike Shal]

On 10/2/09, Pavel Ivanov  wrote:
> > Does anybody know why just adding the begin/commit here improves
>  > performance? If I have to do a large number of selects like this in my
>  > application, should I always wrap it in a transaction?
>
>
> This looks like some overhead of your file system. When you don't put
>  begin/commit around selects then every select is a different read-only
>  transaction. So before each select SQLite takes read lock on database
>  and then after executing select SQLite releases the lock. If you put
>  begin/commit around all selects then SQLite will take read lock only
>  once at the beginning and release lock once at the end. So you have
>  just found how long will it take to acquire/release read lock on
>  database 5 times. So if your application indeed needs to do so
>  many selects and it needs to do it in minimum amount of time then
>  beginning transaction could be indeed a good solution. But be aware
>  that by doing this you're blocking any other instance of your
>  application which wants to write to database. It will not be able to
>  do it until you make commit in your read-only transaction. If it's
>  okay for you then why not.

Ahh, ok - that makes sense. Does this locking overhead not occur on
other platforms (I've only tried linux -- gentoo and ubuntu), or if
sqlite is built differently? It seems a bit disingenuous to claim
there will be no performance gain by putting selects in a transaction,
when sqlite clearly does less work with the transaction (in the form
of not getting the read lock multiple times).

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

Re: [sqlite] Warning message in amalgamation

[Mike Shal]

On 2/2/09, Roger Binns  wrote:
>  Maurí­cio wrote:
>  > I know this is not a problem, but I would like to
>  > remove this warning since it's not important for
>  > the rest of the code. What could I do?
>
>
> The usual method is to cast the result to (void) but gcc still whines.
>  I suggest you just live with it.  With many people reporting warning
>  issues the assumption seems to be that somehow the compiler is reporting
>  an important insight that the developers missed and could cause SQLite
>  to trivially malfunction.  The opposite is actually true - SQLite
>  functions just fine and the compilers are making mistaken claims.
>  Consider those warnings pointing out an inadequacy in the compiler and
>  get the compiler people to fix their program!
>
>   http://sqlite.org/testing.html
>

I would hope the "usual method" would be to check the return value of
the write call to make sure that it actually wrote. Looking at the
file in question, a later pwrite() call correctly checks the return
value to see if it is negative (indicating an error), and also checks
to see if fewer bytes were written than was requested. So why should
this write() be different? The comment doesn't seem to indicate why we
can assume this particular write() will always succeed, or why we
don't care if it doesn't. The testing page you linked to suggests that
sqlite is tested under full-disk conditions, which is somewhat
reassuring, though without digging into the testing internals too
significantly I am not convinced that we could have a case where a
disk fills up just before the write() call, causing it to fail to
write the one byte and forcing the inode returned by fstat() to be the
bizarre 9 thing again. Can you explain why that shouldn't be a
concern? If so, it would be nice to comment that in the source.

Also, if you truly believe the unused result warning to be an error,
you would want to take it up with your libc maintainers, not the
compiler maintainers. The __warn_unused_result gcc attribute is a
useful facility - I think you're just questioning its use in the
write() call in libc.

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