[sqlite] 1st Call For Papers - 24th Annual Tcl/Tk Conference (Tcl'2017)

Hello SQLite Users, fyi ... 24th Annual Tcl/Tk Conference (Tcl'2017) http://www.tcl.tk/community/tcl2017/ October 16 - 20, 2017 Crowne Plaza Houston River Oaks 2712 Southwest Freeway, 77098 Houston, Texas, USA Important Dates: Abstracts and proposals due August 21, 2017 Notification to

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

This code: SELECT ( substr('02/13/2016',7,4) || '-' || substr('02/13/2016',1,2) || '-' || substr('02/13/2016',4,2) ) ; yields 2016-02-13 The above code, is dependent on fixed length strings (the leading zero) in other words '02/13/2016' and not '2/13/2016'. If you do not have

Re: [sqlite] Select Statement returning incorrect information

Hello Simon, Got that one on my own! The Data was created long before me but I do have the option to alter columns if needs be. Hoping Richard can help out on the Date Select I'm struggling with! Regards, -Ron -Original Message- From: sqlite-users

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

Hi Jim, I could alter the program that populates the Date/Time Column to the format you specify. I'm trying real hard not to as that program has been in use for many years and it would be a significant undertaking to convert the program then convert the existing data. Not saying I won't do

Re: [sqlite] Select Statement returning incorrect information

On 12 Apr 2017, at 2:27am, Ron Barnes wrote: > I needed to add the Cast parameter. Assuming you are actually storing integers, it might be better if you declared that column as integer in the first place. Then you wouldn’t need the CAST. However, well done for

Re: [sqlite] Select Statement returning incorrect information

I figured it out. I needed to add the Cast parameter. SELECTcategory, COUNT(*) AS Expr1 FROM(SELECT(CASE WHEN CAST(VI_File_Len AS INTEGER) < 1024000 THEN 'Less Than 1MB' WHEN CAST(VI_File_Len AS INTEGER) < 2048000 THEN 'Less Than 2MB' WHEN CAST(VI_File_Len AS

Re: [sqlite] Select Statement returning incorrect information

On 4/11/17, Ron Barnes wrote: > > I have been pulling my hair out trying to figure out where I went south. If > someone could, would you point out my mistake, please? What does this query show: SELECT DISTINCT typeof(VI_File_Len) FROM Volume_Information; And in

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

Can you convert the dates to ISO 8601 date time format? https://en.wikipedia.org/wiki/ISO_8601 -MM-DD hh:mm:ss ISO date strings (when zero filled) are sortable which necessarily includes comparable (Java speak). By "zero filled" I mean for March you have "03" and not just "3". Then if you

[sqlite] Select Statement returning incorrect information

Hello All, With the select statement below and my test data of 43 files, I expected the following results 22 'Less than 1MB' 4 'Less than 5MB' 7 'Less than 10MB' 4 'Less than 15MB' 6 'Less than 20MB' Instead I get 16 'Less than 1MB' 18 'Less than 5MB' 9 'Larger than 25GB' I have been pulling

Re: [sqlite] sqlite3_step and ORDER BY random() question.

> I/and the original implementer, am/were more familiar with PostgreSQL's > MVCC. So I think the issue was the assumption that the query being > stepped through would only ever see the rows as they were at the start > of the query and would walk through them from first to last. > > Would >

[sqlite] SQLite - Interrogate Date/Time field Statement question

Hello all, To everyone who helped me before - thank you very much! I'm coding in Visual Basic .NET (Visual Studio 2015) Community. I have to count a Date/Time field and the problem is, this field contains data in a format I'm not sure can be counted. I need to count all the dates in the field

[sqlite] Typo in documentation

The first paragraph in Section 2 of states in part: ... And the SQLITE_OMIT_PROGESS_CALLBACK option is only usable by applications... Note the missing R in PROGRESS. -- J. King ___ sqlite-users mailing list

Re: [sqlite] [sqlite-dev] SELECT this set ELSE that set

Maybe: with FooOrBar as ( select case exists(select 1 from X where x = foo) when 1 then foo else bar end as FooOrBar), select * from A inner join B on A.a = B.a inner join C on B.b = C.b inner join X on C.c = X.c where X.x in FooOrBar; ? -Original Message- From: sqlite-users

Re: [sqlite] What does SQLITE_IOERR_VNODE mean?

Apple's darwin-dev mailing list, hosted at lists.apple.com , might be a good place to ask about this. (I'm subscribed to it, so I could forward a question if the OP doesn't want to go to the bother of subscribing.) —Jens ___

Re: [sqlite] json_valid() crash on Windows - was: json() number value parsing

Ralf Junker wrote: > SQLite on Windows crashes when running this test: > > n_structure_10_opening_arrays.json > > The crash results from a stack overflow because json_valid() is implemented > using a recursive parser. All versions of json1.c up to current trunk are >

Re: [sqlite] [sqlite-dev] SELECT this set ELSE that set

Hi Howard, You should re-post this on the SQLite-users list , it's more appropriate for it and you will get a lot more (and arguably better) responses from there. (I've gone ahead and included that list for you, just remove the dev list from replies to

Re: [sqlite] json_valid() crash on Windows - was: json() number value parsing

SQLite on Windows crashes when running this test: n_structure_10_opening_arrays.json The crash results from a stack overflow because json_valid() is implemented using a recursive parser. All versions of json1.c up to current trunk are affected. Here is a small SQL snippet that also

Re: [sqlite] What does SQLITE_IOERR_VNODE mean?

On 11 Apr 2017, at 4:06pm, Richard Hipp wrote: > SQLITE_IOERR_VNODE is an error code used by proprietary modifications > to SQLite implemented by Apple for use on MacOS and iOS. I am told > "The code indicates that a file relevant to the call was invalidated > by a dispatch

Re: [sqlite] sqlite3_step and ORDER BY random() question.

On 4/11/17, Reid Thompson wrote: > > I/and the original implementer, am/were more familiar with PostgreSQL's > MVCC. So I think the issue was the assumption that the query being > stepped through would only ever see the rows as they were at the start > of the query and

Re: [sqlite] Errors opening WAL-based databases with SQLITE_OPEN_READONLY

On 11 Apr 2017, at 12:33am, Simon Slavin wrote: > It may be that the documentation should be updated to reflect this. After another look I’m going to withdraw everything I wrote in the thread. in section 1 says: "It is not possible to open

Re: [sqlite] sqlite3_step and ORDER BY random() question.

On 11 Apr 2017, at 2:16pm, Reid Thompson wrote: > I/and the original implementer, am/were more familiar with PostgreSQL's > MVCC. So I think the issue was the assumption that the query being > stepped through would only ever see the rows as they were at the start > of

Re: [sqlite] What does SQLITE_IOERR_VNODE mean?

On 4/11/17, Yf Shen wrote: > We have an application that connects to a small SQLite database in > read-only mode and do some very simple SELECT queries. > > We recently upgraded Mac OS to 10.12, and the application started to > sporadically fail with a general (useless) error

[sqlite] What does SQLITE_IOERR_VNODE mean?

We have an application that connects to a small SQLite database in read-only mode and do some very simple SELECT queries. We recently upgraded Mac OS to 10.12, and the application started to sporadically fail with a general (useless) error code SQLITE_IOERR. We managed to get the extended error

Re: [sqlite] sqlite3_step and ORDER BY random() question.

On Tue, 2017-04-11 at 13:17 +0100, Simon Slavin wrote: > On 11 Apr 2017, at 12:33pm, Reid Thompson wrote: >  > > Does 'more complicated' in the below scenario mean that there is > > potential for records to be returned more than once if I use random()? >  > The problem is

Re: [sqlite] sqlite3_step and ORDER BY random() question.

>  > Thank you.  I'll make adjustments to my process.  One follow on > question.  This would be a concern regardless of whether random() is > used or not in the ORDER BY clause?  >  > reid sorry - I posted this before receiving/seeing Simon's response. reid

Re: [sqlite] sqlite3_step and ORDER BY random() question.

On Tue, 2017-04-11 at 07:39 -0400, Richard Hipp wrote: > On 4/11/17, Reid Thompson wrote: > > On Mon, 2017-04-10 at 21:39 +0100, Simon Slavin wrote: > > > On 10 Apr 2017, at 9:28pm, Reid Thompson wrote: > > >  > > > > my questions are, if I prepare

Re: [sqlite] sqlite3_step and ORDER BY random() question.

On 11 Apr 2017, at 12:33pm, Reid Thompson wrote: > Does 'more complicated' in the below scenario mean that there is > potential for records to be returned more than once if I use random()? The problem is not to do with random(). The problem is to do with modifying a

Re: [sqlite] sqlite3_step and ORDER BY random() question.

On 4/11/17, Reid Thompson wrote: > On Mon, 2017-04-10 at 21:39 +0100, Simon Slavin wrote: >> On 10 Apr 2017, at 9:28pm, Reid Thompson wrote: >> >> > my questions are, if I prepare and utilize a statement for a result set >> > in the tens of

Re: [sqlite] sqlite3_step and ORDER BY random() question.

On Mon, 2017-04-10 at 21:39 +0100, Simon Slavin wrote: > On 10 Apr 2017, at 9:28pm, Reid Thompson wrote: >  > > my questions are, if I prepare and utilize a statement for a result set > > in the tens of thousands (or more) using a where clause along the lines > > of > >    

Re: [sqlite] SQLite Visual 2017

Thanks for the update On 6 Apr 2017 23:36, "Joe Mistachkin" wrote: > > Tim Bateson wrote: > > > > Does anyone know when the SQLite for Visual Studio 2017 Design Time > > Components will be released? > > > > The current estimate is that support for Visual Studio 2017 will

Re: [sqlite] json() number value parsing

Am 04/09/2017 10:34 AM, Olivier Mascia wrote: Le 9 avr. 2017 à 03:08, Jens Alfke a écrit : On Apr 7, 2017, at 5:26 PM, Rolf Ade wrote: ./sqlite3 SQLite version 3.19.0 2017-04-07 20:20:08 [...] sqlite> select json(' { "this" : 000.23 } ');

[sqlite] Issue with LoadExtension with Spatialite

Have you guys seen this? I think this is an issue with System.Data.SQLite for C#. I'm having the same issue. https://groups.google.com/forum/#!topic/spatialite-users/u2QZpQL_6ek -- Donny V. ___ sqlite-users mailing list

Re: [sqlite] Unexpected results from <, <= and BETWEEN with a CTE in 3.18.0

Thanks both that is really helpful! I hadn't come across the query flattening optimisation before. The documentation is helpful as is prepending EXPLAIN. Thanks again. Kind Regards Keith On 11 April 2017 at 09:55, Clemens Ladisch wrote: > Keith Maxwell wrote: > > Can

Re: [sqlite] Unexpected results from <, <= and BETWEEN with a CTE in 3.18.0

Keith Maxwell wrote: > Can anyone please explain the results of the query below? > > sqlite> WITH > ...> t1(X) AS ( > ...> SELECT 1 > ...> UNION ALL > ...> SELECT X+1 FROM t1 > ...> LIMIT 9 > ...> ) > ...> ,t2(Y) AS (

Re: [sqlite] Unexpected results from <, <= and BETWEEN with a CTE in 3.18.0

My guess ist hat random() is being called once for each expression containing Y. Using constant values from a regular table works as expected. It is exactly the bytecode output by EXPLAIN that could help to determine what happens, but unfortunately you did not post it. asql> insert into t

[sqlite] Unexpected results from <, <= and BETWEEN with a CTE in 3.18.0

sqlite-users, Can anyone please explain the results of the query below? I have tried create a very simple test case. In brief returning 9 rows each starting with an integer between zero and nine. Then check if it less than or equal to five, between zero and five and then less than six. I expect