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

[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 wonder - is it possible to see a full query string inside sqlite3_step()?



One other way to test what’s going on is to temporarily hardwire your C code to 
execute the bound string (as you think it is after binding) rather than doing 
its own binding, and see if you get the same result.

When this happens it’s usually because of some weird problem with escaping or 
quoting characters.  Sixteen posts later someone points out that section 
14(b)(F) says that after a backslash you have to triple-double-quote.  I don’t 
know if this is what your particular problem is but it happens a lot.

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

[sqlite] How do I see a full query?

[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 sqlite3_step()?

I'm using MSVC++2010 ATM with Windows 8.1. Program is compiled as 32-bit
app.

From shell:
C:\Users\Igor\OneDrive\Documents>sqlite3 draft.db
SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
sqlite> SELECT * FROM "sys.abcattbl" WHERE "abt_tnam" = "leagues" AND "abt_ownr"
 = "";
leaguesLeagues table
sqlite>

From the code routine (it is C++):

sqlite3_stmt *stmt = NULL;
std::wstring errorMessage;
int result;
std::wstring query = L"SELECT * FROM \"sys.abcattbl\" WHERE
\"abt_tnam\" = ? AND \"abt_ownr\" = \"\";";
const unsigned char *dataFontName, *headingFontName, *labelFontName;
int res = sqlite3_prepare_v2( m_db,
sqlite_pimpl->m_myconv.to_bytes( query.c_str() ).c_str(), (int)
query.length(), , 0 );
if( res == SQLITE_OK )
{
res = sqlite3_bind_text( stmt, 1,
sqlite_pimpl->m_myconv.to_bytes( table->GetTableName().c_str()
).c_str(), -1, SQLITE_STATIC );
if( res == SQLITE_OK )
{
while( true )
{
res = sqlite3_step( stmt );


where sqlite_pimpl is:

struct SQLiteDatabase::SQLiteImpl
{
std::wstring m_catalog;
std::wstring_convert m_myconv;
};

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

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

[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 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
>   select 'aaa' n
> )
> order by upper(n)
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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
  select 'aaa' n
)
order by upper(n)

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

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

[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 the ORDER BY expression is a constant integer K then the expression
   is considered an alias for the K-th column of the result set (columns are
   numbered from left to right starting with 1).
   2.

   If the ORDER BY expression is an identifier that corresponds to the
   alias of one of the output columns, then the expression is considered an
   alias for that column.
   3.

   Otherwise, if the ORDER BY expression is any other expression, it is
   evaluated and the returned value used to order the output rows. If the
   SELECT statement is a simple SELECT, then an ORDER BY may contain any
   arbitrary expressions. However, if the SELECT is a compound SELECT, then
   ORDER BY expressions that are not aliases to output columns must be exactly
   the same as an expression used as an output column.


On Wed, Dec 21, 2016 at 1:27 PM,  wrote:

> > After content filtering, the message was empty
>
> Ok. I try to send plain text only with no attachments...
>
>
> SQLite 3.8.6 2014-08-15 11:46:33
> SQLite 3.11.0
> Android ver. 5.0.2
>
> Dear Sirs,
>
> I have problem with ORDER BY UPPER(...) in conjunction with UNION.
> The following query returns an error  "1st ORDER BY term does not match
> any column in the result set".
>
> SELECT
>  0 as TableType,
>  GroupId as RecordId,
>  Name as Name
>  FROM ProductGroup
> UNION
> SELECT
>  1 as TableType,
>  ProductId as RecordId,
>  Name as Name
>  FROM Product
>  ORDER BY UPPER(Name)
>
> When I replace the UPPER (Name) by Name - everything works correctly.
> Below I attached the database on which the error occurs.
>
> On the bug list, I found only simillar bug:
> http://www.sqlite.org/src/tktview?name=d06a25c844
>
>
> Lukasz Stela
> INSOFT sp. z o.o.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] count registers in a table

[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, since it reaches the right answer, there is 
definitely something wrong with this.  Can you please check to see that you get 
similar timings in the SQLite shell tool.  If you do, please post the result of

SELECT sqlite_version()

Please also post the result of

PRAGMA integrity_check

To answer the questions in your post, using COUNT(*) is meant to always be the 
fastest way to count rows.  If it’s not, something is wrong.  Which appears to 
be the case with your own setup.

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

Re: [sqlite] count registers in a table

[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
late 2013.
What version of sqlite are you using?

You can make SELECT COUNT(*) FROM MyTable be almost instantaneous, at the
cost of a little effort and a slight slowdown with inserts and deletes, by
maintaining the current row count in the database, and creating TRIGGERs to
keep it current.

http://stackoverflow.com/questions/2869135/sqlite-trigger-to-update-summary-counts


3) Regarding:  "SELECT COUNT(RowId) FROM MyTable WHERE a LIKE '%abc%' ORDER
BY PrimaryIndex
<< is FAST with more registers, but is SLOWLY with 726000 registers"

You don't mention whether you have an index defined on column "a", but even
if you did, using LIKE with a wildcard (%) at the beginning means that
SQLite cannot use an index and must scan the entire table.

Perhaps:
 a) If you only have to search for a few known strings like %abc%, then
you could check for matches at the time rows are inserted, and update a new
column that signifies, e.g., "contains a match for %abc%" and create
indexes on those new columns.
 b) If the above is not feasible, you might want to look into SQLite's
FTS feature (full text search).


4) Regarding: "Which is the fastest way to select 23 registers that meet a
condition ?"

Simon Slavin answered that in his reply to you two days ago:
   SELECT COUNT(*) FROM MyTable WHERE a = b
and, of course, you would expect a great speed increase by having either
column "a" or column "b" indexed, but remember the caveat for LIKE
expressions.

5) In general:
-- Consider whether defining more sqlite cache would help.
-- Be sure you have useful indexes defined (and remove non-useful ones.
Use EXPLAIN QUERY PLAN)
-- Be sure to use TRANSACTIONs around multiple statements to be executed as
a unit -- this won't speed up individual SELECTS that you asked about, but
can make such a tremendous speedup for other things -- such as multiple
inserts -- that I'm mentioning it.


Regards,
   Donald
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Tcl Interface - minor docs suggestion

[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 {insert into a (xyz) values ('z')}
dbcmd eval {insert into a (XYZ) values ('y')}
dbcmd eval {insert into a (XyZ) values ('x')}

dbcmd eval "select xyz from a"{  puts "$xyz"  };#== gives
error: can't read "xyz": no such variable
dbcmd eval "select xyz from a" v {  puts "$v(xyz)" } ;#== gives error:
can't read "v(xyz)": no such element in array

dbcmd eval "select xyz as xyz from a"  { puts $xyz };#== ouput is as
expected
z
y
z

Apparently the TCL variable names set by "eval' are in the same case as the
column names orginally defined in the CREATE statement* unless an alias is
used.*

In the docs ( http://www.sqlite.org/tclsqlite.html ) this section on "eval"
has a paragraph which begins:

If the array variable name is omitted or is the empty string, then the
> value of each column is stored in a variable with the same name as the
> column itself.
>

Perhaps a warning could be added like "The case of the variable is the same
as in the table definition unless a column alias is used."

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

Re: [sqlite] count registers in a table

[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 WHERE a LIKE '%abc%' ORDER BY PrimaryIndex
<< is FAST with more registers, but is SLOWLY with 726000 registers

Which is the fastest way to count the records of a table with a condition
and 700.000 registers ?


Which is the fastest way to select 23 registers that meet a condition ?

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