Re: [sqlite] How do I see a full query?

2016-12-26 Thread Simon Slavin
On 27 Dec 2016, at 4:24am, Igor Korot wrote: > I have a weird situation where executing a query in a shell gives me a row, > but executing the same query through the C-interface: sqlite3_prepare_v2(), > sqlite3_bind_text() and sqlite3_step() produces SQLITE_DONE. > > So I

[sqlite] How do I see a full query?

2016-12-26 Thread Igor Korot
Hi, ALL, I have a weird situation where executing a query in a shell gives me a row, but executing the same query through the C-interface: sqlite3_prepare_v2(), sqlite3_bind_text() and sqlite3_step() produces SQLITE_DONE. So I wonder - is it possible to see a full query string inside

Re: [sqlite] Bug: Problem with ORDER BY UPPER(...) in conjunction with UNION

2016-12-26 Thread Darko Volaric
Or use a collation instead, although "collate" is an operator it's not treated as a function: select 'abc' n union select 'ABC' n order by n collate nocase On Tue, Dec 27, 2016 at 1:34 AM, Jean-Christophe Deschamps wrote: > At 00:45 27/12/2016, you wrote: > > The work

Re: [sqlite] Bug: Problem with ORDER BY UPPER(...) in conjunction with UNION

2016-12-26 Thread Jean-Christophe Deschamps
At 00:45 27/12/2016, you wrote: The work arounds is using a WITH clause or putting the upper function expression in the output of each select. Another way to rewrite is to wrap the compound select inside a simple outer select: select n from ( select 'Abc' n union

Re: [sqlite] Bug: Problem with ORDER BY UPPER(...) in conjunction with UNION

2016-12-26 Thread Darko Volaric
It's not a bug, it's a documented restriction, see the last point below. The work arounds is using a WITH clause or putting the upper function expression in the output of each select. From http://www.sqlite.org/lang_select.html : Each ORDER BY expression is processed as follows: 1. If

Re: [sqlite] count registers in a table

2016-12-26 Thread Simon Slavin
On 26 Dec 2016, at 3:14pm, MONSTRUO Hugo González wrote: > I have a table with 726.000 registers. > > SELECT COUNT(*) FROM MyTable << is very slowly > > SELECT COUNT (RowId) FROM MyTable ORDER BY PrimaryIndex << is very FAST While this is not a bug in SQLite,

Re: [sqlite] count registers in a table

2016-12-26 Thread Donald Griggs
Hello, Hugo, Regarding: "I have a table with 726.000 registers." 1) I assume that you mean what others call "rows" correct? (and not columns, I hope) 2) Regarding: "SELECT COUNT(*) FROM MyTable << is very slowly" As I understand it, that should be as fast as SELECT COUNT (RowId) as of

[sqlite] Tcl Interface - minor docs suggestion

2016-12-26 Thread John G
Having used the Tcl interface to SQLite for 10+ years I was caught out when accessing someone else's DB. I don't see a satisfactory way to fix it, but a warning would help. Example to illustrate: sqlite3 dbcmd grbg.db package require sqlite3 dbcmd eval {create table a (Xyz text)} dbcmd eval

Re: [sqlite] count registers in a table

2016-12-26 Thread MONSTRUO Hugo González
> Which is the fastest way to count the records of a table. ? And records > that meet a condition? I have a table with 726.000 registers. SELECT COUNT(*) FROM MyTable << is very slowly SELECT COUNT (RowId) FROM MyTable ORDER BY PrimaryIndex << is very FAST SELECT COUNT(RowId) FROM MyTable