Re: [sqlite] Does using e.g. LTRIM function remove collation?

2014-11-10 Thread Hick Gunter
I was just refuting the claim that "the WHERE clause converts 'a' to 'A' in the database." It doesn't. So either the "change" was caused by a copy-paste error when creating the mail or by actually runing a (different) query that produces it. Anyway, in the WHERE clause, the arguments to '<'

Re: [sqlite] Does using e.g. LTRIM function remove collation?

2014-11-10 Thread James K. Lowden
On Mon, 10 Nov 2014 08:43:24 + Hick Gunter wrote: > I get the following results for the second select: > > A > B > a (lowercase!!!) > > Are you sure you ran the exact query stated? I didn't run it. Your mail showed 3 uppercase letters: > > sqlite> SELECT * FROM test

Re: [sqlite] Locking errors on network

2014-11-10 Thread Eduardo Morras
On Mon, 10 Nov 2014 20:33:04 +0200 RSmith wrote: > > On 2014/11/10 20:22, Mike McWhinney wrote: > > So SQLite shouldn't be used at all on a network? Aren't there any > > other provisions to handled the locking errors if/when they occur? > > It is not about SQLite, it is

Re: [sqlite] Locking errors on network

2014-11-10 Thread Scott Robison
On Mon, Nov 10, 2014 at 12:56 PM, wrote: > On 2014-11-10 18:33, RSmith wrote: > > >> There is one Client-Server implementation of SQLite (SQLightening I >> think) but it is neither free nor easy to convert to. >> > > Doing some Googling, this looks like the thing: > >

Re: [sqlite] Locking errors on network

2014-11-10 Thread Tim Streater
On 10 Nov 2014 at 19:38, Mike McWhinney wrote: > I am using Sqlite.NET client in C#. How would I go about defining a sqlite > busy timeout handler? Is the API not documented somewhere (I don't know what Sqlite.NET client or C# are, so can't help)? Surely you must have

Re: [sqlite] Locking errors on network

2014-11-10 Thread Scott Robison
On Mon, Nov 10, 2014 at 12:56 PM, wrote: > On 2014-11-10 18:33, RSmith wrote: > > >> There is one Client-Server implementation of SQLite (SQLightening I >> think) but it is neither free nor easy to convert to. >> > > Doing some Googling, this looks like the thing: > >

Re: [sqlite] Locking errors on network

2014-11-10 Thread justin
On 2014-11-10 18:33, RSmith wrote: There is one Client-Server implementation of SQLite (SQLightening I think) but it is neither free nor easy to convert to. Doing some Googling, this looks like the thing: http://sqlitening.com They don't seem to sell it any more (last version was released

Re: [sqlite] Locking errors on network

2014-11-10 Thread Mike McWhinney
I am using Sqlite.NET client in C#. How would I go about defining a sqlite busy timeout handler? Thanks Mike On Monday, November 10, 2014 1:35 PM, Tim Streater wrote: On 10 Nov 2014 at 18:22, Mike McWhinney wrote: > So SQLite shouldn't

Re: [sqlite] Locking errors on network

2014-11-10 Thread Tim Streater
On 10 Nov 2014 at 18:22, Mike McWhinney wrote: > So SQLite shouldn't be used at all on a network? Aren't there any other > provisions to handled the locking errors if/when > they occur? You tried setting a timeout as pointed to here?

Re: [sqlite] Locking errors on network

2014-11-10 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/10/2014 10:22 AM, Mike McWhinney wrote: > So SQLite shouldn't be used at all on a network? Aren't there any > other provisions to handled the locking errors if/when they occur? Network filesystems do not implement locking and other operations

Re: [sqlite] Locking errors on network

2014-11-10 Thread Simon Slavin
On 10 Nov 2014, at 6:22pm, Mike McWhinney wrote: > So SQLite shouldn't be used at all on a network? SQLite is not designed for hosting a database on a server for access by lots of different computers at the same time. To do that efficiently you need a client/server

Re: [sqlite] Locking errors on network

2014-11-10 Thread RSmith
On 2014/11/10 20:22, Mike McWhinney wrote: So SQLite shouldn't be used at all on a network? Aren't there any other provisions to handled the locking errors if/when they occur? It is not about SQLite, it is about the Networking systems lying about whether a file is locked or not. No RDBMS

Re: [sqlite] Locking errors on network

2014-11-10 Thread Mike McWhinney
So SQLite shouldn't be used at all on a network? Aren't there any other provisions to handled the locking errors if/when they occur? On Monday, November 10, 2014 12:10 PM, Roger Binns wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/10/2014 09:41 AM,

Re: [sqlite] Locking errors on network

2014-11-10 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/10/2014 09:41 AM, Mike McWhinney wrote: > Please let know if there are any other solutions to this database > locking problem as used on a network. Yes. Do not do it. See the FAQ: https://www.sqlite.org/faq.html#q5 Roger -BEGIN PGP

[sqlite] Locking errors on network

2014-11-10 Thread Mike McWhinney
Hello, I continue to have "database is locked errors" when running an application which accesses a SQLite dabase on a network. I have tried many of the connection string options: pooling, changing default time out. I just recently tried a block of code which does a BeginTransaction and

Re: [sqlite] SQLite where clause tree

2014-11-10 Thread Ward Willats
> On Nov 10, 2014, at 9:31 AM, Richard Hipp wrote: > > On Mon, Nov 10, 2014 at 12:28 PM, Ward Willats > wrote: > >> >>> On Nov 10, 2014, at 3:11 AM, Richard Hipp wrote: >>> >>> If you recompile the SQLite command-line shell

Re: [sqlite] SQLite where clause tree

2014-11-10 Thread Richard Hipp
On Mon, Nov 10, 2014 at 12:28 PM, Ward Willats wrote: > > > On Nov 10, 2014, at 3:11 AM, Richard Hipp wrote: > > > > If you recompile the SQLite command-line shell (sqlite3.exe) using the > > -DSQLITE_ENABLE_SELECTTRACE option, then you can enter: > > >

Re: [sqlite] SQLite where clause tree

2014-11-10 Thread Ward Willats
> On Nov 10, 2014, at 3:11 AM, Richard Hipp wrote: > > If you recompile the SQLite command-line shell (sqlite3.exe) using the > -DSQLITE_ENABLE_SELECTTRACE option, then you can enter: > If I do that, gcc -D SQLITE_ENABLE_SELECTTRACE -D HAVE_READLINE -l readline -o

Re: [sqlite] Virtual memory management of Sqlite core on Windows Mobile 6.5 with .NET CF 3.5

2014-11-10 Thread Simon Slavin
On 10 Nov 2014, at 4:45pm, Pavlo wrote: > That main problem is that we are working with managed code and .net wrapper > packaged in System.Data.Sqlite :) I apologise that I missed this. Fortunately we have several readers to the list who understand that

Re: [sqlite] Virtual memory management of Sqlite core on Windows Mobile 6.5 with .NET CF 3.5

2014-11-10 Thread Pavlo
Thanks Simon for quick reply. That main problem is that we are working with managed code and .net wrapper packaged in System.Data.Sqlite :) Our Sqlite usage boils down to the following: 1. Fetch ready to use Sqlite database from server 2. Open one Sqlite connection 2. Working only with this one

Re: [sqlite] Virtual memory management of Sqlite core on Windows Mobile 6.5 with .NET CF 3.5

2014-11-10 Thread Simon Slavin
On 10 Nov 2014, at 3:45pm, Pavlo wrote: > Total Virtual memory allocations size made by Sqlite core seems to grow in > time in application process even though Sqlite connection get closed on a > regular basis. > For example if we do search touching several tables

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

2014-11-10 Thread Simon Davies
On 10 November 2014 16:03, Don V Nielsen wrote: > Isn't this the result of the results cache? The two queries are identical. > The query plan changes... . . . sqlite> explain query plan select count(*) from v wherez = 0 and ...> (

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

2014-11-10 Thread Don V Nielsen
Isn't this the result of the results cache? The two queries are identical. On Mon, Nov 10, 2014 at 9:26 AM, Clemens Ladisch wrote: > RP McMurphy wrote: > > Is there a way we can make the w index work with both queries and not > > have to run external loops to flatten all

[sqlite] Virtual memory management of Sqlite core on Windows Mobile 6.5 with .NET CF 3.5

2014-11-10 Thread Pavlo
Hi all, Here is what we have 1. We are using Sqlite provider from sqlite-netFx35-binary-PocketPC-ARM-2008-1.0.94.0.zip on Windows Mobile 6.5 in several our projects written using compact

Re: [sqlite] Change UPDATE with JOIN sintax

2014-11-10 Thread Clemens Ladisch
dylan666 wrote: > update Table1 > set Visibility=1 > where ConsumerID in (select * from > Table1 join Table2 > on Table1.ConsumerID = Table2.id > where Table1.visibility = 0 and Table2.visibility = 1) > > Unfortunately I get this error: > only a single result allowed for a SELECT that is part of

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

2014-11-10 Thread Clemens Ladisch
RP McMurphy wrote: > Is there a way we can make the w index work with both queries and not > have to run external loops to flatten all the WHERE clauses? sqlite> .timer on sqlite> select count(*) from v wherez = 0 and ...>

[sqlite] Discrepancy with indexing and WHERE clause with AND/OR

2014-11-10 Thread RP McMurphy
If there is a large table and we need to select a subset of values using a WHERE clause with an AND/OR construct sqlite has trouble finding the answer in a reasonable time. Breaking the queries down into separate SELECT statements speeds up the process exponentially. For example the following

[sqlite] Change UPDATE with JOIN sintax

2014-11-10 Thread dylan666
Hi, I'm new with SQLite and I't trying to use an update query with a join. The original query is this: I tyied to chenge it in this way: Unfortunately I get this error: only a single result allowed for a SELECT that is part of an expression What is wrong? Thanks in advance -- View this

Re: [sqlite] SQLite where clause tree

2014-11-10 Thread Igor Tandetnik
On 11/10/2014 4:40 AM, Prakash Premkumar wrote: = /\ salary+ / \ 3 3 / * /

Re: [sqlite] SQLite where clause tree

2014-11-10 Thread Richard Hipp
On Mon, Nov 10, 2014 at 4:40 AM, Prakash Premkumar wrote: > The where clause in sqlite is encoded as a tree > > Let's say I have select statement like : > > SELECT * from employee where salary = 3+5*4+3; > > The tree which takes care of operator precedence is : > >

Re: [sqlite] SQLite where clause tree

2014-11-10 Thread Simon Slavin
On 10 Nov 2014, at 9:40am, Prakash Premkumar wrote: > If I am constructing this tree for where clause by myself, should i take > the operator precedence in to account while constructing it or will sqlite > take care of precedence,given any tree (i.e constructing it with

Re: [sqlite] SQLite where clause tree

2014-11-10 Thread Stephen Chrzanowski
>From a mathematical standpoint in your example, going back to grade 4 math (35 years ago for me. *sigh*. I'm so sad), where clause works based off of standard order of operations based on BEDMAS and eventually working things down to booleans. In your example, the math would be processed as

Re: [sqlite] SQLite where clause tree

2014-11-10 Thread Hick Gunter
Your tree is wrong. I would expect that operator precedence is handled in the parser. The code generator will happily implement any tree, regardless of how insane it may be. -Urspr√ľngliche Nachricht- Von: Prakash Premkumar [mailto:prakash.p...@gmail.com] Gesendet: Montag, 10. November

[sqlite] SQLite where clause tree

2014-11-10 Thread Prakash Premkumar
The where clause in sqlite is encoded as a tree Let's say I have select statement like : SELECT * from employee where salary = 3+5*4+3; The tree which takes care of operator precedence is : = /\ salary+

Re: [sqlite] Does using e.g. LTRIM function remove collation?

2014-11-10 Thread Hick Gunter
I get the following results for the second select: A B a (lowercase!!!) Are you sure you ran the exact query stated? -Urspr√ľngliche Nachricht- Von: James K. Lowden [mailto:jklow...@schemamania.org] Gesendet: Samstag, 08. November 2014 01:52 An: sqlite-users@sqlite.org Betreff: Re: