Re: [sqlite] Getting all changes within a begin; end; transaction

Simon Slavin, on Wednesday, March 4, 2020 10:47 AM, wrote... > > On 4 Mar 2020, at 3:28pm, Jose Isaias Cabrera wrote: > > > The reason why I know is that if I have 238 INSERTS, but I have a > constraint , there should be 238 INSERTs the first time I run a set of SQL, > but if I run the same SQL a

Re: [sqlite] Getting all changes within a begin; end; transaction

On 4 Mar 2020, at 3:28pm, Jose Isaias Cabrera wrote: > The reason why I know is that if I have 238 INSERTS, but I have a constraint > , there should be 238 INSERTs the first time I run a set of SQL, but if I run > the same SQL again, there should not be any records INSERTED, and thus, the > am

Re: [sqlite] Getting all changes within a begin; end; transaction

On 4 Mar 2020, at 3:28pm, Jose Isaias Cabrera wrote: > However, I have found that it does not actually provide the **ACTUAL** > changes, but a count of the possible changes. Hmm. I understand you. Does this do something more like what you need ?

Re: [sqlite] Getting all changes within a begin; end; transaction

Simon Slavin, on Wednesday, March 4, 2020 09:42 AM, wrote... > > On 4 Mar 2020, at 2:37pm, Jose Isaias Cabrera wrote: > > > Is there a way to know all the changes that may have happened within > the full BEGIN and END? Thanks. > > Use this function > >

Re: [sqlite] Getting all changes within a begin; end; transaction

On 4 Mar 2020, at 2:37pm, Jose Isaias Cabrera wrote: > Is there a way to know all the changes that may have happened within the full > BEGIN and END? Thanks. Use this function before and after your block, and subtract one from another. __

Re: [sqlite] Getting "chunked" output from a large SELECT operation.

Hi, I'm having a situation where the results of a large SELECT operation are apparently too big to fit in memory. Obviously I could jerry-rig something to work around this, but I have a vague recollection that SQLite provides a nice way to get the results of a query in "chunks" so that the m

Re: [sqlite] Getting "chunked" output from a large SELECT operation.

On Wed, 16 Oct 2019 17:38:28 +, you wrote: > I'm having a situation where the results of a large > SELECT operation are apparently too big to fit in memory. > > Obviously I could jerry-rig something to work around > this, but I have a vague recollection that SQLite > provides a nice way to get

Re: [sqlite] Getting "chunked" output from a large SELECT operation.

What language/library are you using? In Python for example there's .fetchone() to get just the next result row, .fetchmany(n) to get the next n rows, or .fetchall() to go get them all. In general though at its core SQLite will get and return one row at a time. Though if there's grouping or sort

Re: [sqlite] Getting "chunked" output from a large SELECT operation.

On 16 Oct 2019, at 6:38pm, Randall Smith wrote: > I'm having a situation where the results of a large SELECT operation are > apparently too big to fit in memory. SQLite only stores results if it has to. It would have to if there is no good index for your SELECT terms. Are you actually using

Re: [sqlite] Getting a notification when a write lock is released.

test user, on Friday, August 16, 2019 02:29 PM, wrote... > > Thanks for the example José. You're welcome. Just thought I would provide some idea... :-) josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite

Re: [sqlite] Getting a notification when a write lock is released.

Thanks for the example José. I was thinking of a more general method that would work when I do not control all of the clients. Your example would only work when all clients are aware of and use the locking logic. On Fri, Aug 16, 2019 at 3:39 PM Jose Isaias Cabrera wrote: > > test user, on

Re: [sqlite] Getting a notification when a write lock is released.

test user, on Thursday, August 15, 2019 07:35 PM, wrote... > The reason for the notification is to minimize time spent waiting. I will tell you what I did with 10 PMs working with a shared windows drive with an SQLite DB. But, take it with a grain of salt, unless you have high-blood pressure,

Re: [sqlite] Getting a notification when a write lock is released.

Thanks Simon, > You can use any other combination that suits you. Perhaps set a short > timeout, after which SQLite calls your busy handler, which can do whatever > it wants then return SQLITE_BUSY to your program. When the short timeout > gets exhausted, SQLite calls your own busy handler, and

Re: [sqlite] Getting a notification when a write lock is released.

On 15 Aug 2019, at 10:43pm, test user wrote: > Currently the API lets you set a timeout. Does this just retry again after a > set amount of time? SQLite's built-in busy handler (which it uses unless you tell it to use yours instead) repeatedly backs off and retries until the timeout you set is

Re: [sqlite] Getting a notification when a write lock is released.

> > SQLite could support this in theory. But if the process holding the > lock is hung, that would hang the process waiting on the look too. > > Getting SQLITE_BUSY is annoying, but it is not nearly as annoying as > getting a > hung process. > > I am not aware of a way to do a blocking file lo

Re: [sqlite] Getting a notification when a write lock is released.

On Thursday, 15 August, 2019 13:11, test user wrote: >If two processes are writing to the same db file, one will get a BUSY >response if the other has locked it. >Currently the API lets you set a timeout. Does this just retry again >after a set amount of time? timeout specifies the time after

Re: [sqlite] Getting a notification when a write lock is released.

On 8/15/19, test user wrote: > Hello, > > If two processes are writing to the same db file, one will get a BUSY > response if the other has locked it. > > Currently the API lets you set a timeout. Does this just retry again after > a set amount of time? Yes. It retries multiple times, increasing

Re: [sqlite] Getting the week of the month from strftime or date functions

> On May 7, 2019, at 05:35, Jens Alfke wrote: > > [https://en.wikipedia.org/wiki/French_Republican_calendar] Along the same lines: International Fixed Calendar https://en.m.wikipedia.org/wiki/International_Fixed_Calendar > You Advocate An Approach To Calendar Reform; Your Idea Will Not Work;

Re: [sqlite] Getting the week of the month from strftime or date functions

I apologize to the group for the non-sqlite emails caused by my post. It was just having a little fun. :-) josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting the week of the month from strftime or date functions

On 2019/05/07 3:07 PM, Jose Isaias Cabrera wrote: Warren Young, on Monday, May 6, 2019 09:15 PM, wrote... On May 6, 2019, at 11:58 AM, Jose Isaias Cabrera wrote: someday, as John Lennon sang, "...the world will live as one." ;-) Okay, but one *what*? Serious question. Yeah, if I have to expla

Re: [sqlite] Getting the week of the month from strftime or date functions

Warren Young, on Monday, May 6, 2019 09:15 PM, wrote... On May 6, 2019, at 11:58 AM, Jose Isaias Cabrera wrote: >> someday, as John Lennon sang, "...the world will live as one." ;-) > > Okay, but one *what*? Serious question. Yeah, if I have to explain it to you, then you miss the idea. :-) But

Re: [sqlite] Getting the week of the month from strftime or date functions

> On May 6, 2019, at 6:15 PM, Warren Young wrote: > > Ideas for fixing this aren’t new. The French had a supremely utopian "Republican Calendar" that lasted from 1793 to 1805 ("and for 18 days by the Paris Commune in 1871" … such pathos in that

Re: [sqlite] Getting the week of the month from strftime or date functions

On May 6, 2019, at 11:58 AM, Jose Isaias Cabrera wrote: > > we have discover DNA; shouldn't we have the knowledge to come up with a > dating system that should work for the world. :-) The Earth year doesn’t divide evenly by Earth days. No matter what you do, the solution *will* be messy. Eve

Re: [sqlite] Getting the week of the month from strftime or date functions

On 5/6/19 1:58 PM, Jose Isaias Cabrera wrote: > Petite Abeille, on Sunday, May 5, 2019 09:10 AM, wrote... >>> On May 4, 2019, at 21:24, Thomas Kurz wrote: >> True enough, even though one could convert a 'week of year' into a 'week of >> month': > [clip] > >> 2019-11-30|2019|11|47|5 >> 2019-12-01|

Re: [sqlite] Getting the week of the month from strftime or date functions

Yes. :-), per month. From: sqlite-users on behalf of Petite Abeille Sent: Monday, May 6, 2019 03:57 PM To: SQLite mailing list Subject: Re: [sqlite] Getting the week of the month from strftime or date functions > On May 6, 2019, at 19:58, Jose Isaias Cabrera wrote: > > som

Re: [sqlite] Getting the week of the month from strftime or date functions

> On May 6, 2019, at 19:58, Jose Isaias Cabrera wrote: > > something is wrong where one week only has 1 day ... per month :P ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/s

Re: [sqlite] Getting the week of the month from strftime or date functions

Petite Abeille, on Sunday, May 5, 2019 09:10 AM, wrote... >> On May 4, 2019, at 21:24, Thomas Kurz wrote: >True enough, even though one could convert a 'week of year' into a 'week of >month': [clip] > 2019-11-30|2019|11|47|5 > 2019-12-01|2019|12|47|1 <-- > 2019-12-02|2019|12|48|2 > 2019-12-0

Re: [sqlite] Getting the week of the month from strftime or date functions

> On May 4, 2019, at 21:24, Thomas Kurz wrote: > >> What about just sticking with the ISO week definition? >> >> https://en.wikipedia.org/wiki/ISO_week_date > > From the document you cited: > > "The ISO standard does not define any association of weeks to months." True enough, even though o

Re: [sqlite] Getting the week of the month from strftime or date functions

> What about just sticking with the ISO week definition? > > https://en.wikipedia.org/wiki/ISO_week_date From the document you cited: "The ISO standard does not define any association of weeks to months." ___ sqlite-users mailing list sqlite-users@mail

Re: [sqlite] Getting the week of the month from strftime or date functions

> On May 4, 2019, at 15:59, Luuk wrote: > > This is the 'standard' used here where i live, so i can accept that ;) "The nice thing about standards is that you have so many to choose from." -- Andrew Stuart "Andy" Tanenbaum :P ___ sqlite-users maili

Re: [sqlite] Getting the week of the month from strftime or date functions

On 4-5-2019 15:21, Petite Abeille wrote: On May 4, 2019, at 12:47, Luuk wrote: As others have noted, it's a question of definition, and which definition do you follow? What about just sticking with the ISO week definition? https://en.wikipedia.org/wiki/ISO_week_date This is the 'standar

Re: [sqlite] Getting the week of the month from strftime or date functions

> On May 4, 2019, at 12:47, Luuk wrote: > > As others have noted, it's a question of definition, and which definition do > you follow? What about just sticking with the ISO week definition? https://en.wikipedia.org/wiki/ISO_week_date ___ sqlite-

Re: [sqlite] Getting the week of the month from strftime or date functions

On 2-5-2019 22:17, Jose Isaias Cabrera wrote: I found this very interesting, 15:52:46.71>sqlite3 SQLite version 3.28.0 2019-04-16 19:49:53 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> SELECT strftime

Re: [sqlite] Getting the week of the month from strftime or date functions

On 04 May 2019, at 09:35, Olivier Mascia wrote: >> Le 2 mai 2019 à 22:01, Thomas Kurz a écrit : >> >> I think "week of the month" is not a standard value. As with week of the >> year, is week #1 the week in which the month starts, the first complete week >> within the month, or the first week

Re: [sqlite] Getting the week of the month from strftime or date functions

> Le 2 mai 2019 à 22:01, Thomas Kurz a écrit : > > I think "week of the month" is not a standard value. As with week of the > year, is week #1 the week in which the month starts, the first complete week > within the month, or the first week with at least 4 days? These are very regional matters

Re: [sqlite] Getting the week of the month from strftime or date functions

04:16 PM, wrote... To: SQLite mailing list Subject: Re: [sqlite] Getting the week of the month from strftime or date functions Hello Jose, Regarding: "...but I need to get the week of that month based on the date." One interpretation of your question might me: Given a date "d&quo

Re: [sqlite] Getting the week of the month from strftime or date functions

00 sqlite> SELECT strftime('%W','2019-01-06'); 00 sqlite> SELECT strftime('%W','2019-01-07'); 01 I expected 2019-01-01 to be part of week 1, since it was Tuesday. So, back to the drawing board. ;-) Thanks. Thomas Kurz, on Thursday, May 2, 2019

Re: [sqlite] Getting the week of the month from strftime or date functions

Hello Jose, Regarding: "...but I need to get the week of that month based on the date." One interpretation of your question might me: Given a date "d", which, say, falls on a Wednesday, then return 1, 2, 3, 4, or 5 denoting whether d is on the 1st, 2nd, 3rd, 4th, or 5th Wednesday of that mo

Re: [sqlite] Getting the week of the month from strftime or date functions

I think "week of the month" is not a standard value. As with week of the year, is week #1 the week in which the month starts, the first complete week within the month, or the first week with at least 4 days? - Original Message - From: Jose Isaias Cabrera To: sqlite-users@mailinglists.

Re: [sqlite] Getting data from two JOIN tables

On Wed, Feb 27, 2019 at 9:20 PM Keith Medcalf wrote: > [...] As such, except in OUTER joins, you do not even have to have the ON > expression related to the table(s) which have been seen so far or even > those in the join expression ... because ON is merely a syntactic substitute for WHERE and m

Re: [sqlite] Getting data from two JOIN tables

: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Jose Isaias Cabrera >Sent: Wednesday, 27 February, 2019 07:42 >To: David Raymond; SQLite mailing list >Subject: Re: [sqlite] Getting data from two JOIN tables > > >Thanks, David. I actually like the

Re: [sqlite] Getting data from two JOIN tables

SQL, the language of the free... choices: JOIN or commas (,)... ;-) From: sqlite-users on behalf of Dominique Devienne Sent: Wednesday, February 27, 2019 09:47 AM To: SQLite mailing list Subject: Re: [sqlite] Getting data from two JOIN tables On Wed, Feb 27, 2019 at 3:42 PM Jose Isaias

Re: [sqlite] Getting data from two JOIN tables

On Wed, Feb 27, 2019 at 3:42 PM Jose Isaias Cabrera wrote: > Thanks, David. I actually like the comma (,) than the words (JOIN, > etc). Less wordy and, to me, more logically flow-y. > Just the reverse as myself. I much prefer explicit join-on, to separate filtering from join-conditions in the

Re: [sqlite] Getting data from two JOIN tables

Thanks, David. I actually like the comma (,) than the words (JOIN, etc). Less wordy and, to me, more logically flow-y. josé From: sqlite-users on behalf of David Raymond Sent: Wednesday, February 27, 2019 09:31 AM To: SQLite mailing list Subject: Re: [sqlite] Getting data from two JOIN

Re: [sqlite] Getting data from two JOIN tables

e.org] On Behalf Of Dominique Devienne Sent: Wednesday, February 27, 2019 9:06 AM To: SQLite mailing list Subject: Re: [sqlite] Getting data from two JOIN tables On Wed, Feb 27, 2019 at 2:18 PM Jose Isaias Cabrera wrote: > Thanks. This is exactly what I needed. So, there is really no JOIN

Re: [sqlite] Getting data from two JOIN tables

Thanks, Dominique. From: sqlite-users on behalf of Dominique Devienne Sent: Wednesday, February 27, 2019 09:06 AM To: SQLite mailing list Subject: Re: [sqlite] Getting data from two JOIN tables On Wed, Feb 27, 2019 at 2:18 PM Jose Isaias Cabrera wrote: > Thanks. This is exactly wha

Re: [sqlite] Getting data from two JOIN tables

On Wed, Feb 27, 2019 at 2:18 PM Jose Isaias Cabrera wrote: > Thanks. This is exactly what I needed. So, there is really no JOIN here, > or is the "from t outer_t, z outer_z" a JOIN like statement? Where can I > read more about this? And yes, your assessment of t(a, idate) and z(f, > idate) be

Re: [sqlite] Getting data from two JOIN tables

of Keith Medcalf Sent: Tuesday, February 26, 2019 10:46 PM To: SQLite mailing list Subject: Re: [sqlite] Getting data from two JOIN tables select a, b, c, g, h, i from t outer_t, z outer_z where a == f and a == 'p001' and outer_t.idate == (select max(idate) from t where a

Re: [sqlite] Getting data from two JOIN tables

select a, b, c, g, h, i from t outer_t, z outer_z where a == f and a == 'p001' and outer_t.idate == (select max(idate) from t where a == outer_t.a) and outer_z.idate == (select max(idate) from z where f == outer_z.f) ; This requires that t(a, idate) is unique and that z(f, idate) is u

Re: [sqlite] Getting SQLITE_LOCKED

Ya must be using shared cache as well? --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Prajeesh Prakash >

Re: [sqlite] Getting SQLITE_LOCKED

Sorry i was a wrong attempt. > > On November 29, 2018 at 5:07 PM Prajeesh Prakash > wrote: > > Hi Team, > > I am writing to a table 1 and reading from table 2 both operation are > from different DB connection i am getting SQLITE_LOCKED > > and when i try to read and write th

Re: [sqlite] Getting call backs on WRITEs?

On Jun 5, 2018, at 1:04 PM, Stephen Chrzanowski wrote: > > None of these applications describe a hook for my Delphi code This feels like an instance of the XY problem: https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem You’re focused on your predetermined solution and are

Re: [sqlite] Getting call backs on WRITEs?

Thanks Dan. Initial glance, looks to be something that I can tie into. On Tue, Jun 5, 2018 at 12:14 PM, Dan Kennedy wrote: > > It might be worth looking at the "sessions" module: > > https://www.sqlite.org/sessionintro.html > > Sessions uses this to get SQLite to make the required callbacks:

Re: [sqlite] Getting call backs on WRITEs?

The problem I have is that I was wondering what kind of hooks are available to get my application to do what I need it to do 'in a moment', so my app can hand off information to another service at a time that is convenient. None of these applications describe a hook for my Delphi code, but packages

Re: [sqlite] Getting call backs on WRITEs?

On 06/05/2018 10:46 PM, Stephen Chrzanowski wrote: Using the Amalgamation DLL, I'd like to know if there is a function that is exposed when any WRITE function to the database happens on a per connection basis, a call back to my program (Delphi - Tokyo and Berlin) will happen and I can act on the

Re: [sqlite] Getting call backs on WRITEs?

On Jun 5, 2018, at 9:46 AM, Stephen Chrzanowski wrote: > > I'm *DREAMING* for a way to allow for an application level notification > system that when a write to the database is performed from anywhere It’s usually better to say what problem you’re trying to solve rather than specify the form th

Re: [sqlite] Getting statement column data types on Android

Yes, I think the only solution might be to clean up the tables So if for example there is text in a real column, update it to 0.0 etc. RBS On Tue, May 8, 2018 at 5:46 PM, Simon Slavin wrote: > On 8 May 2018, at 5:37pm, Bart Smissaert wrote: > > >> SQLite does not have column types. It has c

Re: [sqlite] Getting statement column data types on Android

On 8 May 2018, at 5:37pm, Bart Smissaert wrote: >> SQLite does not have column types. It has column affinities instead. > > OK, so I would like to see that declared column affinity as that will > determine how to process the data. Ah. You don't care about the data, you want the column declara

Re: [sqlite] Getting statement column data types on Android

> SQLite does not have column types. It has column affinities instead. OK, so I would like to see that declared column affinity as that will determine how to process the data. I have no problem doing this on the Windows PC. More difficult though to do this on Android. RBS On Tue, May 8, 2018 at

Re: [sqlite] Getting statement column data types on Android

On 8 May 2018, at 4:19pm, Bart Smissaert wrote: > Just tested that (TypeOf) on the Android phone and it doesn't do what I > wanted. > I tested on a column declared Real but with text values in it as well. > It will give both real and text and what I wanted was to produce only real > as that > is

Re: [sqlite] Getting statement column data types on Android

> int sqlite3_column_type(sqlite3_stmt*, int iCol); Android and certainly B4A doesn't have that as far as I can see. RBS On Tue, May 8, 2018 at 3:21 AM, J Decker wrote: > https://www.sqlite.org/c3ref/column_blob.html > > int sqlite3_column_type(sqlite3_stmt*, int iCol); > > ? > > On Mon, May 7

Re: [sqlite] Getting statement column data types on Android

Just tested that (TypeOf) on the Android phone and it doesn't do what I wanted. I tested on a column declared Real but with text values in it as well. It will give both real and text and what I wanted was to produce only real as that is what the column is declared as. RBS On Tue, May 8, 2018 at 1

Re: [sqlite] Getting statement column data types on Android

https://www.sqlite.org/c3ref/column_blob.html int sqlite3_column_type(sqlite3_stmt*, int iCol); ? On Mon, May 7, 2018 at 4:28 PM, Bart Smissaert wrote: > Yes, thanks, that might be the best way, but it can get a bit complicated > with complex SQL. > > RBS > > > > On Tue, May 8, 2018 at 12:05 A

Re: [sqlite] Getting statement column data types on Android

Yes, thanks, that might be the best way, but it can get a bit complicated with complex SQL. RBS On Tue, May 8, 2018 at 12:05 AM, Simon Slavin wrote: > On 7 May 2018, at 10:49pm, Bart Smissaert > wrote: > > > Using B4A for a SQLite database app on an Android phone. > > B4A doesn't have functi

Re: [sqlite] Getting statement column data types on Android

On 7 May 2018, at 10:49pm, Bart Smissaert wrote: > Using B4A for a SQLite database app on an Android phone. > B4A doesn't have functions like sqlite3_column_decltype and > sqlite3_column_type > and this is causing some difficulty getting the column datatypes of a row > producing statement. See

Re: [sqlite] getting error "string or blob too big" for 500 MB data insertion

On 12/8/17, Durgesh wrote: > I am trying to insert 500 MB of row data using Qt SQL into sqlite db. How are you measuring the row size? > > Insertion is successful up to 450 MB. > > defined macro SQLITE_MAX_LENGTH to larger value than 500 MB, as mentioned in > http://www.sqlite.org/limits.html >

Re: [sqlite] Getting an advance list of RowIDs for a query result set

*I’m now wondering if you omit the WHERE & ORDER BY and run the following EXPLAIN QUERY PLAN SELECT BaseTbl.RowID FROM BaseTbl left join Tbl1 on comparison_1 left join Tbl2 on comparison_2 . . left join Tbln on comparison_n then if it returns more than 1 row then this implies there’s a 1

Re: [sqlite] Getting an advance list of RowIDs for a query result set

E.Pasma wrote >> What about changing the remaining inner join to left join > >> Select BaseTbl.RowID >> from BaseTbl >> left join Tbl_2 on Tbl2.Y = BaseTbl.Y >> where BaseTbl.Col=? > >> and see if the SQLiter optimizer now leaves Tbl_2 out from the query >> plan. It will only do that if it is not

Re: [sqlite] Getting an advance list of RowIDs for a query result set

>What about changing the remaining inner join to left join >Select BaseTbl.RowID >from BaseTbl >left join Tbl_2 on Tbl2.Y = BaseTbl.Y >where BaseTbl.Col=? >and see if the SQLiter optimizer now leaves Tbl_2 out from the query >plan. It will only do that if it is not a 1-to-n join. If Tbl_2 isn’t

Re: [sqlite] Getting an advance list of RowIDs for a query result set

Op 27 nov 2017, om 20:51 heeft x het volgende geschreven: So if I build a view that includes look-ups in other tables, the optimizer may skip these at places where not selected. However only if the look-ups are written as outer joins. Then it may be good practice allways doing that. For inst

Re: [sqlite] Getting an advance list of RowIDs for a query result set

>So if I build a view that includes look-ups in other tables, the >optimizer may skip these at places where not selected. However only if >the look-ups are written as outer joins. Then it may be good practice >allways doing that. For instance: >create view vtrack as >select trackname, artistname

Re: [sqlite] Getting an advance list of RowIDs for a query result set

So if I build a view that includes look-ups in other tables, the optimizer may skip these at places where not selected. However only if the look-ups are written as outer joins. Then it may be good practice allways doing that. For instance: create view vtrack as select trackname, artistname

Re: [sqlite] Getting an advance list of RowIDs for a query result set

>Thanks to you, this topic has inspired a useful change or changes. Wow. Glad I could help. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users __

Re: [sqlite] Getting an advance list of RowIDs for a query result set

x wrote: From: E.Pasma<mailto:pasm...@concepts.nl> Sent: 26 November 2017 17:30 To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Getting an advance list of RowIDs for a query result set If step 3 is xxx-ed and only left-joins

Re: [sqlite] Getting an advance list of RowIDs for a query result set

From: E.Pasma<mailto:pasm...@concepts.nl> Sent: 26 November 2017 17:30 To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Getting an advance list of RowIDs for a query result set >If step 3 is xxx-ed and only left-joins remain to be consi

Re: [sqlite] Getting an advance list of RowIDs for a query result set

x wrote: I proceed as follows 1. Omit a table join from the SQL and try preparing it. 2. If it prepares OK then the table isn’t involved in the WHERE or ORDER BY. 3. If it’s joined to the BaseTbl by an integer primary key or FULLY joined by a unique index then the table is redundant.

Re: [sqlite] Getting an advance list of RowIDs for a query result set

> If it’s joined to the BaseTbl by an integer primary key or FULLY joined by a > unique index then the table is redundant. I’m talking there. If it’s an inner join SQLite needs to check the record exists in the joined table. Sorry about that, back to left joins. __

Re: [sqlite] Getting an advance list of RowIDs for a query result set

ite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Getting an advance list of RowIDs for a query result set >This is fixed in the current head of trunk. Although the implementation may >change, it will appear in the next release. https://www.sqlite.org

Re: [sqlite] Getting an advance list of RowIDs for a query result set

olume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of x >Sent: Saturday, 25 November, 2017 10:08 >To: SQLite mailing list >Subject: Re: [sqlite] Getting an advance list of RowIDs for a query >result set > >Simon, I

Re: [sqlite] Getting an advance list of RowIDs for a query result set

: Saturday, November 25, 2017 1:26:00 PM To: SQLite mailing list Subject: Re: [sqlite] Getting an advance list of RowIDs for a query result set On 25 Nov 2017, at 1:15pm, curmudgeon wrote: > Given a select where a 'base table' is attached to lookup tables > how can I determine whi

Re: [sqlite] Getting an advance list of RowIDs for a query result set

On 25 Nov 2017, at 1:15pm, curmudgeon wrote: > Given a select where a 'base table' is attached to lookup tables > how can I determine which of the lookup tables can be removed from the table > such that > > select BaseTbl.RowID from ... where ... order by ... > > will find the set of records

Re: [sqlite] Getting an advance list of RowIDs for a query result set

Sorry, in last post select * from (select Value from carray(ID+?1, ?2, 'int64')) inner join AwfyBigTbl on AwfyBigTbl.RowID = _Value; by setting ?1 = TopRecNo and ?2 = n. should read select * from (select Value from carray(*?1*, ?2, 'int64')) inner join AwfyBigTbl on AwfyBigTbl.RowID = _Value;

Re: [sqlite] Getting an advance list of RowIDs for a query result set

A trivial example of what I'm trying to do. Given select * from AwfyBigTbl where ACol=?; I'd run the query select RowID from AwfyBigTbl where ACol=?; step through the records and store the values in a std::vector called ID. I could then retrieve n records starting at TopRecNo (0 based) with the

Re: [sqlite] Getting number of rows with NULL

I agarre. 😁 Mensaje original De: R Smith Fecha: 5/9/17 4:11 PM (GMT-05:00) A: sqlite-users@mailinglists.sqlite.org Asunto: Re: [sqlite] Getting number of rows with NULL On 2017/09/05 10:00 PM, Stephen Chrzanowski wrote: > On behalf of Cecil, the fault in that logic is t

Re: [sqlite] Getting number of rows with NULL

2017-09-05 22:46 GMT+02:00 R Smith : > > > On 2017/09/05 10:13 PM, John McKown wrote: > >> On Tue, Sep 5, 2017 at 3:00 PM, Stephen Chrzanowski >> wrote: >> >> On behalf of Cecil, the fault in that logic is that count(*) returns the >>> number of rows in that table, not whether there is a hole "so

Re: [sqlite] Getting number of rows with NULL

On 2017/09/05 10:13 PM, John McKown wrote: On Tue, Sep 5, 2017 at 3:00 PM, Stephen Chrzanowski wrote: On behalf of Cecil, the fault in that logic is that count(*) returns the number of rows in that table, not whether there is a hole "somewhere: Your query will either return 1, or, 0. ​I

Re: [sqlite] Getting number of rows with NULL

2017-09-05 22:09 GMT+02:00 Igor Tandetnik : > It's possible I misunderstand what it is the OP is trying to do. But in > any case, the query I show is equivalent to the query the OP has shown > (which, apparently, does what they want), except formulated in a less > roundabout way. ​Yes, your quer

Re: [sqlite] Getting number of rows with NULL

On 9/5/2017 4:05 PM, Igor Tandetnik wrote: On 9/5/2017 4:00 PM, Stephen Chrzanowski wrote: select count(*) from teaInStock where "Last Used" IS NULL; On behalf of Cecil, the fault in that logic is that count(*) returns the number of rows in that table, not whether there is a hole "somewhere: 

Re: [sqlite] Getting number of rows with NULL

On Tue, Sep 5, 2017 at 3:00 PM, Stephen Chrzanowski wrote: > On behalf of Cecil, the fault in that logic is that count(*) returns the > number of rows in that table, not whether there is a hole "somewhere: Your > query will either return 1, or, 0. > > ​I either don't understand you, or I am doin

Re: [sqlite] Getting number of rows with NULL

On 2017/09/05 10:00 PM, Stephen Chrzanowski wrote: On behalf of Cecil, the fault in that logic is that count(*) returns the number of rows in that table, not whether there is a hole "somewhere: Your query will either return 1, or, 0. Perhaps this is the opportune moment to learn. Test the theo

Re: [sqlite] Getting number of rows with NULL

As I understand the requirements, he wants to find out how many entries (Not which entries) don't exist between the first ID (Assumed 1) and max ID value. So if he's got 3 rows, but max ID is 5, the result should be 2. But I also suspect you're better in tune with the requirements, since I suspec

Re: [sqlite] Getting number of rows with NULL

2017-09-05 21:55 GMT+02:00 Igor Tandetnik : > On 9/5/2017 3:45 PM, Cecil Westerhof wrote: > >> It is not very important, but I am just curious. I need to know how many >> records are not yet used. I do that with: >> SELECT COUNT(*) - COUNT("Last Used") AS "Not Used" >> FROM teaInStock >> >> Is t

Re: [sqlite] Getting number of rows with NULL

On 9/5/2017 4:00 PM, Stephen Chrzanowski wrote: select count(*) from teaInStock where "Last Used" IS NULL; On behalf of Cecil, the fault in that logic is that count(*) returns the number of rows in that table, not whether there is a hole "somewhere: Your query will either return 1, or, 0. Wh

Re: [sqlite] Getting number of rows with NULL

On behalf of Cecil, the fault in that logic is that count(*) returns the number of rows in that table, not whether there is a hole "somewhere: Your query will either return 1, or, 0. On Tue, Sep 5, 2017 at 3:55 PM, Igor Tandetnik wrote: > On 9/5/2017 3:45 PM, Cecil Westerhof wrote: > >> It is

Re: [sqlite] Getting number of rows with NULL

On 9/5/2017 3:45 PM, Cecil Westerhof wrote: It is not very important, but I am just curious. I need to know how many records are not yet used. I do that with: SELECT COUNT(*) - COUNT("Last Used") AS "Not Used" FROM teaInStock Is that the correct way, or is there a better way? Why not be expl

Re: [sqlite] Getting number of rows with NULL

Untested (Obviously as I don't have your schema) select max(WheverYourIDFieldIs)-count(WhateverYourIDFieldIs) as "Not Used" from teaInStock group by WhateverYouIDFieldIs On Tue, Sep 5, 2017 at 3:45 PM, Cecil Westerhof wrote: > It is not very important, but I am just curious. I need to know how

Re: [sqlite] Getting SQLITE_IOERR_WRITE when running sqlite

On 1/2/15, Dan Kennedy wrote: > On 01/02/2015 04:44 PM, Waiba, Aswin wrote: >> we were getting SQLITE_IOERR_WRITE (778). > > It means a call to write(), pwrite(), fallocate() or similar has failed. > Because it ran out of disk space, or the media was removed or perhaps is > faulty. Or a bug in SQL

Re: [sqlite] Getting SQLITE_IOERR_WRITE when running sqlite

On 01/02/2015 04:44 PM, Waiba, Aswin wrote: Hi, I am currently using sqlite version 3.7.14 in our application. We are using it via a single thread, however we are getting SQLITE_IOERR (10) when running the application. After enabling the extended result code, we found out that we were getting

Re: [sqlite] Getting an unexpected result

"Richard Hipp" wrote... On Thu, Oct 16, 2014 at 7:16 PM, Keith Medcalf wrote: Although the parser does not care how you format your query, human readability is improved by using nice formatting. Indeed, the error would have likely been spotted much faster had the query been reformatted

Re: [sqlite] Getting an unexpected result

On Thu, Oct 16, 2014 at 7:16 PM, Keith Medcalf wrote: > > Although the parser does not care how you format your query, human > readability is improved by using nice formatting. > Indeed, the error would have likely been spotted much faster had the query been reformatted for readability. That sh

  1   2   3   4   5   >