Re: [sqlite] Bug report: column name includes table alias when CTE is used

[Simon Slavin]

On 21 Apr 2014, at 6:16am, Andre  wrote:

> Apparently when a CTE is used, the column name includes the table alias.
> However, when no CTE is used, the alias is not present in the returned
> column name.

SQLite has no standards at all about column names unless you specifically set a 
column name using "AS" in your SELECT.  If you don't use "AS" then SQLite can 
return all sorts of weird things as the column names, even without using CTE.

If you are expecting a specific column name, you're welcome to tell us what you 
expect and why, but so far nobody has come up with any good reason for a 
specific form.

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

[sqlite] Bug report: column name includes table alias when CTE is used

[Andre]

Hi,

Apparently when a CTE is used, the column name includes the table alias.
However, when no CTE is used, the alias is not present in the returned
column name.

SQLite version 3.8.4.3 2014-04-03 16:53:12
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table X (columnA int);
sqlite> insert into X values (1);
sqlite> .header on
sqlite> select alias.columnA from X alias;
*columnA*
1
sqlite> with CTE as (select columnA from X) select alias.columnA from CTE
alias;
*alias.columnA*
1
sqlite>

I experienced this when rewriting a query to use CTE in an application that
based some logic on the column name. I'd expect not to see the alias either
way. Is this a bug or is it expected for CTEs?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Remote access to SQLite in Linux from Windows.

[Simon Slavin]

On 21 Apr 2014, at 12:43am, Stephen Chrzanowski  wrote:

> Short of writing a server type application that listens for incoming
> communication, I'm not aware of anything of the sort.  SQLite is an
> embedded library which compiles with your code, or, with references to
> external libraries installed on your local machine.  SQLite doesn't behave
> well with ANY sort of networking capabilities.
> 
> The best bet would be to have a server type of application that listens for
> inbound communications via TCP, and you build the server to deal with
> transactions.
> 
> You can build an API based off PHP (Or pretty much any other language) on
> the server in which your windows app makes an HTTP request to your server,
> your server handles the SQL calls and then returns the results.

I agree with everything Stephen wrote, but I'll extend it with answers to the 
next two questions you're going to ask.

A) Yes, you can write a PHP page (or a few different ones) which give you 
SQLite access.  You access them as normal web pages and they return results as 
normal web pages.  To save you lots of experimentation, if you do this I 
recommend you pass your parameters in JSON format using POST rather than GET, 
and return your results in JSON format.  And use PHP's 'sqlite3' library rather 
than PDO.  Also, if your system is going to be used by anyone apart from you, 
build in some security measures so the pages process requests only from the 
programs you want them to obey.  Writing something like this is not hard and 
it's very satisfying when it works.

B) No, there is no standard solution to this that everyone uses and you can 
download from a site somewhere.  There are various reasons for this, but one 
seems to be that although everyone describes their needs the same, once someone 
comes up with a solution everyone immediately says "I can't possibly use this 
unless ..." and adds something weird that only they'd want.  So you're going to 
have to write your own.  Sorry.

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

Re: [sqlite] Remote access to SQLite in Linux from Windows.

[Stephen Chrzanowski]

Short of writing a server type application that listens for incoming
communication, I'm not aware of anything of the sort.  SQLite is an
embedded library which compiles with your code, or, with references to
external libraries installed on your local machine.  SQLite doesn't behave
well with ANY sort of networking capabilities.

The best bet would be to have a server type of application that listens for
inbound communications via TCP, and you build the server to deal with
transactions.

You can build an API based off PHP (Or pretty much any other language) on
the server in which your windows app makes an HTTP request to your server,
your server handles the SQL calls and then returns the results.


On Sat, Apr 19, 2014 at 8:26 AM, Zheng, Ting fang BIS <
ting-fang.zh...@fs.utc.com> wrote:

> Folks,
>
> We plan to remote access to SQLite in Linux from Windows using .NET C#
>
> 1.   Any existing tools are available?
>
> 2.   Is possible to use SSH for remote?
>
> Thanks,
>
> TFZ
>
>
> ___
> 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] detach failed with error code 1

[Simon Slavin]

On 20 Apr 2014, at 12:58pm, dd  wrote:

> Given different database name for in-memory database for every
> iteration(looped for 1000 times). Still, it's throwing Database Locked at
> least once on Windows, not on Mac/Linux. Is there any way to track this
> issue? I am using 3.7.11. Any ideas?

Technically this should not solve the problem, but I'm curious to know.

Have you set a timeout value ?  If not, please set one to at least five seconds 
using either of these:




I'm curious to know whether (a) this solves the problem or (b) it makes your 
loop of 1000 take longer.

However, we do seem to still think that the error is in your code somewhere 
rather than in the SQLite library.  Are you checking the result returned by all 
calls for errors, rather than checking just the result returned by your DETACH 
?  Check the result returned by every single _prepare(), _step() and 
_finalize() and other sqlite_ call in your code.

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

Re: [sqlite] detach failed with error code 1

[dd]

Given different database name for in-memory database for every
iteration(looped for 1000 times). Still, it's throwing Database Locked at
least once on Windows, not on Mac/Linux. Is there any way to track this
issue? I am using 3.7.11. Any ideas?


On Thu, Apr 17, 2014 at 5:11 PM, Richard Hipp  wrote:

> On Thu, Apr 17, 2014 at 9:08 AM, dd  wrote:
>
> > Dear Richard,
> >
> >   This is great api.
> >
> >   output:
> >
> >   (1) statement aborts at 5: [DETACH my_in_memory_db;] database
> > my_in_memory_db is locked
> >
>
> The DETACH failed because you have unfinalized statements using the
> attached database and you cannot detach a database file out from under a
> statement that is using that database file.  Because the DETACH failed,
> subsequent ATTACH statements cannot succeed because there would be a name
> conflict.
>
>
>
> >   (1) statement aborts at 5: [ATTACH DATABASE '/full/path/info.db' AS
> > my_in_memory_db;] database my_in_memory_db is
> > already in use
> >   (1) statement aborts at 5: [ATTACH DATABASE '/full/path/info.db' AS
> > my_in_memory_db;] database my_in_memory_db is already in use
> >   (1) statement aborts at 5: [ATTACH DATABASE '/full/path/info.db' AS
> > my_in_memory_db;] database my_in_memory_db is already in use
> >   (1) statement aborts at 5: [ATTACH DATABASE '/full/path/info.db' AS
> > my_in_memory_db;] database my_in_memory_db is already in use
> >
> >   there is a detach for every attach.  What could be the solution for
> this?
> >
> > Thanks,
> > dd
> >
> >
> >
> >
> > On Thu, Apr 17, 2014 at 2:58 PM, Richard Hipp  wrote:
> >
> > > Please turn on error logging (http://www.sqlite.org/errlog.html) and
> > > report
> > > back what error messages you are seeing.
> > >
> > >
> > > On Thu, Apr 17, 2014 at 6:47 AM, dd  wrote:
> > >
> > > > Hi All,
> > > >
> > > >   1. prepare, step, then finalize: Attach DATABASE '/full/path' as
> > > > 'my_in_memory_db';
> > > >   2. prepare, step, then finalize: Delete from
> my_in_memory_db.table_1
> > > > where primary_key = 'value';
> > > >   3. prepare, step, then finalize: DETACH my_in_memory_db;
> > > >
> > > >   Executed above three queries in loop for 100 times for empty
> database
> > > > (/full/path). It's throwing sqlite error 1 at some random iteration.
> Is
> > > it
> > > > correct way to implement attach and detach dbs?
> > > >
> > > > Thanks,
> > > > dd.
> > > > ___
> > > > sqlite-users mailing list
> > > > sqlite-users@sqlite.org
> > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > >
> > >
> > >
> > >
> > > --
> > > D. Richard Hipp
> > > d...@sqlite.org
> > > ___
> > > 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
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Bug report: ORDER BY ignored in presence of GROUP BY and index

[Richard Hipp]

On Sat, Apr 19, 2014 at 11:14 PM, foxlit  wrote:

> Hi,
>
> I recently noticed something similar to the following behaviour:
>
> sqlite> .version
> SQLite 3.8.4.3 2014-04-03 16:53:12 a611fa96c4a848614efe899130359c9f6fb889c3
> sqlite> CREATE TABLE t1 (x, y);
> sqlite> INSERT INTO t1 VALUES (1, 1), (2, 0);
> sqlite> SELECT x, y FROM t1 GROUP BY x, y ORDER BY x,y;
> 1|1
> 2|0
> sqlite> CREATE INDEX i1 ON t1 (y, x); -- (sic)
> sqlite> SELECT x, y FROM t1 GROUP BY x, y ORDER BY x, y;
> 2|0
> 1|1
>
> The second result appears to be ignoring the ORDER BY clause. Is this a
> bug, or am I missing something obvious?
>

Bug.  I created a ticket here
http://www.sqlite.org/src/tktview/b75a9ca6b0499

The work-around is to add a "+" before one of the terms on the ORDER BY
clause.  Ex:

 SELECT x,y FROM t1 GROUP BY x,y ORDER BY x,+y;

The problem is caused by an optimization (
http://www.sqlite.org/src/artifact/269c3e31a4?ln=4722-4732) that has been
in the code since 2010-04-26 that omits the ORDER BY clause if there is an
identical GROUP BY clause, since GROUP BY is (or at least was) implemented
by sorting as if it were an ORDER BY.  This optimization worked fine until
the next generation query planner (
http://www.sqlite.org/queryplanner-ng.html) was cut over in 2013-06-26.
The NGQP introduced some new ways to handle GROUP BY which made that
optimization no longer valid in some circumstances - one of which you have
just found.  So, this is a case of two separate optimizations interfering
with one another.

Thanks for the bug report.

Oops.  Look like Dan and I entered duplicate tickets.  I'll cancel one of
them



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

[sqlite] Bug report: ORDER BY ignored in presence of GROUP BY and index

[foxlit]

Hi,

I recently noticed something similar to the following behaviour:

sqlite> .version
SQLite 3.8.4.3 2014-04-03 16:53:12 a611fa96c4a848614efe899130359c9f6fb889c3
sqlite> CREATE TABLE t1 (x, y);
sqlite> INSERT INTO t1 VALUES (1, 1), (2, 0);
sqlite> SELECT x, y FROM t1 GROUP BY x, y ORDER BY x,y;
1|1
2|0
sqlite> CREATE INDEX i1 ON t1 (y, x); -- (sic)
sqlite> SELECT x, y FROM t1 GROUP BY x, y ORDER BY x, y;
2|0
1|1

The second result appears to be ignoring the ORDER BY clause. Is this a bug, or 
am I missing something obvious?


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

[sqlite] CTE in views for older versions

[Max Vlasov]

Hi,
noticed that attempt to open a database containing a view

Create vew ... with recursive ...

... with older (non-cte) versions of sqlite failed. The call to
open_v2 was ok, but any access for example to PRAGMA encoding led to
"malformed database schema" error. Although it came as no big
surprise, probably one might expect delayed handling behavior as with
virtual tables so it don't face incompatibility until referenced in a
query.

So probably either
  https://www.sqlite.org/lang_createview.html
or
  https://sqlite.org/lang_with.html

might contain a little sentence about such incompatibility.

Thanks,

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

Re: [sqlite] cannot set connection while a datareader is active

[Stefano Ravagni]

Il 20/04/2014 6.00, Joe Mistachkin ha scritto:

Stefano Ravagni wrote:

Is a big project wich re-call many function and interacts many times
with sqlite provider... i could try to isolate a piece of code but
isolating the code i don't know if the error will be reproduced...but i
will try is all VB codeare you interested to receive a ZIP file
containing a little piece of code with also database ? Or exist another
way to find the error in your mind ?
Thanks for answer!!!


It would be great if you could come up with an isolated body of code that
demonstrates the issue.  Since this mailing list strips attachments, you
could post a link to a ZIP file that can be downloaded.

--
Joe Mistachkin

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

Ok Joe, i'll try to prepare in next days i don't know if i will able 
to isolate but i'll write to you soon (i hope). Thanks for now and good 
holydays...

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

Re: [sqlite] how can i omit the number/0 in the result in sqlite?

[Scott Robison]

On Fri, Apr 18, 2014 at 8:35 PM, 水静流深 <1248283...@qq.com> wrote:

> sqlite3  test.db create table data(num1 float,num2 float); insert into
> data values(1.2,3.4); insert into data values(4,0); insert into data
> values(2,5); insert into data values(3,0); insert into data values(5.2,3);
> sqlite> select num1/num2  from data  order by num1/num2 asc;
> 0.352941176470588
>  0.4
>  1.73
>
> There are two blank lines ,how can i omit the  number/0  in the result,i
> want only three lines as my result
>

SELECT NUM1/NUM2 FROM data WHERE NUM2 <> 0 ORDER BY NUM1/NUM2 ASC;

Off the top of my head, I think that should do it.

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