Re: [sqlite] Can it (should it) be done in SQL?

r by 1, 2; -- 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 On >Behalf Of David Bicking >Sent: Saturday, 18 January, 2020 11:20 >To: SQLite Mailing List

[sqlite] Can it (should it) be done in SQL?

I suspect the answer is that it is best to do this in the application program. However, the platform I want to use is dumb as a brick. It basically can call sqlite3_get_table, mildly reformat the data and send it to the display. Anyway, there are two tables CREATE TABLE Goals (period integer

Re: [sqlite] Lazy JSON data to sqlite database

>> Is there a tool out there that will more or less automate the task for >> me? Hopefully free, as no one is paying me to do this. (The other >> volunteers have maybe a dozen records in total and are doing their >> reports by hand. ) >The automation is at a lower level than you seem to

[sqlite] Lazy JSON data to sqlite database

Okay, I know this is just me being lazy, but I have a data file with JSON data, and I want to get the data into an sqlite database so I can run queries against it. It is not a large file, only about 600 records in the main table. I've never worked with JSON before, and really don't want to

[sqlite] Task management schema

Okay, this is a "help me with my homework" type request, so feel free to tell me to go away. But I have been tasked with creating a simple task management system. For it, I need to store tasks: who is assigned, what is assigned, and when it is due. Where the task could be recurring, ie due the

Re: [sqlite] Protecting databases

If you are trying to protect against casual snooping, you could probably zip the sqlite data with a password then have your application unzip the data to a temp location on open, then re-zip at the end, deleting the unzipped file. Your application then would be able to use the normal sqlite

Re: [sqlite] Complicated join

to blow up in size when translated to your real version with the 8 fields, which is what makes me cringe. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of David Bicking Sent: Monday, September 19, 2016 1:34 PM To: SQLite mailing lis

Re: [sqlite] Complicated join

te.org Sent: Monday, September 19, 2016 2:43 PM Subject: Re: [sqlite] Complicated join On 19-09-16 19:33, David Bicking wrote: > select E.CombinedKeyField, E.EvtNbr, M.EvtNbr > from E left join M > on E.CombinedKeyField = M.CombinedKeyField > and (E.EvtNbr = M.EvtNbr > or  M.Ev

Re: [sqlite] Complicated join

ect: Re: [sqlite] Complicated join On 19-09-16 19:33, David Bicking wrote: > INSERT INTO M (CombinedKeyField, EvtNbr) > > VALUES ('A', 1), > ('A', 5); > > INSERT INTO E (CombineKeyField, EvtNbr) > VALUES ('A', 1) > , ('A', 2) > , ('A', 3) > , ('A', 4) > , ('A', 5) &

Re: [sqlite] Complicated join

ber 19, 2016 10:57 AM Subject: Re: [sqlite] Complicated join On Thu, 15 Sep 2016 15:53:10 + (UTC) David Bicking <dbic...@yahoo.com> wrote: > (1) The CombinedKeyFields must always match in each table(2) Match > using the EvtNbr, but if no match, use the lowest M.Evt

Re: [sqlite] Complicated join

d reader end up seeing an unable to read small font on their end. From: R Smith <rsm...@rsweb.co.za> To: sqlite-users@mailinglists.sqlite.org Sent: Saturday, September 17, 2016 7:25 AM Subject: Re: [sqlite] Complicated join On 2016/09/15 5:53 PM, David Bicki

[sqlite] Complicated join

I have two tables: CREATE TABLE M ( CombineKeyFields, EvtNbr, TransDate, OtherFields, PRIMARY KEY(CombinedKeyFields, EvtNbr, TransDate));CREATE TABLE E  ( CombineKeyFields, EvtNbr, TransDate, OtherFields, PRIMARY KEY(CombinedKeyFields, EvtNbr, TransDate)); "CombinedKeyFields" is shorthand for a

Re: [sqlite] Does sqlite3 have variables like Oracle?

Sqlite doesn't have variable.  While last row id is available other ways, a trick to emulate a variable is to create a temp table with one field. You put the value in to the that field. You can then cross join with the rest of your table as need be, or do a sub-select to value a SET command.

[sqlite] MIN/MAX query

Whatever mangled the text must have put a 2 in front of the 7, cause the copy in my sent mail box has a 1 in front of the 7.?? I never got a copy of my email from the mailing list, so I never saw the mangled version, just quotes of it. I assumed you made the typo and didn't want to nit-pick

[sqlite] MIN/MAX query

Thanks. You understood my intention, and confirmed my fear that I couldn't do it efficiently in SQL. David From: Igor Tandetnik To: sqlite-users at mailinglists.sqlite.org Sent: Thursday, February 18, 2016 9:59 AM Subject: Re: [sqlite] MIN/MAX query On 2/18/2016 4:55 AM, R Smith

[sqlite] MIN/MAX query

Um, I understand sets; which is why I knew the naive group by wouldn't work. I guess I should have stated my question is HOW do I define the group so that in the order of I,L, clusters of common V values are a "group". I need to return the value of I and V, with the minimum and maximum L in that

[sqlite] MIN/MAX query

On 02/17/2016 03:22 PM, nomad at null.net wrote: > On Wed Feb 17, 2016 at 06:17:40PM +0000, David Bicking wrote: >> I have a table >> I L V1 1 A1 2 A1 3 A1 4 B1 5 B1 6 A1 7 A2 1 C2 2 C > The formatting of this (and your desired results) does not make the >

[sqlite] MIN/MAX query

I have a table I? L? V1 1?? A1 2?? A1 3?? A1 4?? B1 5 ? B1 6?? A1 7?? A2 1 ? C2 2?? C I want to return the minimal and maximum L for each "group" of V in a given I. The result I want: I? MinL? MaxL? V1? 1??? 3? A1? 4??? 5?? B1? 6??? 7?? A2? 1???

Re: [sqlite] Performance gain in SQLite

I recall there is or once was a way to compile sqlite so that you could embed in your program the pre-generated sql. This was for embedded programs, not to speed things up, but to remove the parser and save memory in very small embedded systems. For it to work, the sql and the database schema

Re: [sqlite] Application with 'grid' form for entering table data wanted

I don't know php, but this sounds like what I think you want: phpGrid | PHP Datagrid Made Easy. phpGrid | PHP Datagrid Made Easy. phpGrid is a simple, powerful and fully customizable PHP component for generating PHP AJAX datagrid for create, read, update, delete (CRUD) records. View on

Re: [sqlite] Joining different databases

ferent databases On 5/29/2014 10:42 AM, David Bicking wrote: >> How complicated is the join? Could you show a hypothetical SQL statement > you would have used had both tables been in the same database? > > Not complicated:  Select b.id, b.name, b.otherfields from a inner join b on >

Re: [sqlite] Joining different databases

From: Igor Tandetnik <i...@tandetnik.org> To: sqlite-users@sqlite.org Sent: Thursday, May 29, 2014 10:34 AM Subject: Re: [sqlite] Joining different databases On 5/29/2014 10:26 AM, David Bicking wrote: >> I have a somewhat large table

[sqlite] Joining different databases

I have a somewhat large table in an sqlite database and another large table on an MS SQL Server database (on a slow network).  I want to query both tables in a join. The join is likely to produce from zero to a dozen rows. First thought was to copy the data from the SQL Server table to the

Re: [sqlite] Windows user app to display input form + reporting?

On 03/12/2014 08:05 AM, Gilles Ganault wrote: On Wed, 12 Mar 2014 07:59:39 -0400, David Bicking <dbic...@yahoo.com> wrote: Not exactly "lite" in size, but kexi does have most of the features of Access and uses sqlite to store its data: http://kexi-project.org/ Thanks fo

Re: [sqlite] Windows user app to display input form + reporting?

On 03/11/2014 07:31 PM, Gilles Ganault wrote: Hello A friend needs to move from Excel to a database. The school won't pay for the full version of MS Office that includes Access, so recommended that she use LibreOffice Base instead. I just checked it out, and it seems to only be a

Re: [sqlite] partially excluding records

uding records To: sqlite-users@sqlite.org Date: Friday, February 21, 2014, 3:25 PM On 2/21/2014 3:11 PM, David Bicking wrote: > But I am curious, wouldn't this yield a "Statuses" for key 2 of 'C+', when it should be 'O'? You could use the same technique there. The technique let

Re: [sqlite] partially excluding records

On Fri, 2/21/14, Igor Tandetnik <i...@tandetnik.org> wrote: Subject: Re: [sqlite] partially excluding records To: sqlite-users@sqlite.org Date: Friday, February 21, 2014, 2:58 PM On 2/21/2014 1:23 PM, David Bicking wrote: >> SELEC

Re: [sqlite] partially excluding records

On Fri, 2/21/14, Clemens Ladisch <clem...@ladisch.de> wrote: Subject: Re: [sqlite] partially excluding records To: sqlite-users@sqlite.org Date: Friday, February 21, 2014, 1:38 PM David Bicking wrote: >> The complication is that if

Re: [sqlite] partially excluding records

On Fri, 2/21/14, RSmith <rsm...@rsweb.co.za> wrote: Subject: Re: [sqlite] partially excluding records To: sqlite-users@sqlite.org Date: Friday, February 21, 2014, 1:34 PM On 2014/02/21 20:23, David Bicking wrote: > I have a t

[sqlite] partially excluding records

I have a table like SELECT * FROM T1; Key Status 1 O 1 O 2 O 2 C 3 C 3 C 4 O 4 P Now, I need to consolidate that data. SELECT Key, COUNT(STATUS) Cnt , MIN(STATUS) || CASE WHEN COUNT(STATUS)>1 THEN '+' ELSE '' END Statuses FROM T1 WHERE ... GROUP BY KEY;

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

The first time I saw sqlite demonstrated at a linux user group, the presenter didn't realize he was using a memory database. I had to explain why all his work was lost, then proceeded to continue the demo since I knew more about the product. (This was years ago, I think we were still at sqlite

Re: [sqlite] (no subject)

On Tue, 1/28/14, Igor Tandetnik <i...@tandetnik.org> wrote: Subject: Re: [sqlite] (no subject) To: sqlite-users@sqlite.org Date: Tuesday, January 28, 2014, 2:41 PM On 1/28/2014 2:26 PM, David Bicking wrote: > I have two tables:

[sqlite] (no subject)

I have two tables: ARB KEY (PRIMARY KEY) ASSIGN (NOT NECESSARILY UNIQUE) DMC KEY (NOT UNIQUE) ASSIGN (NOT UNIQUE) VALUE I need to report all the records from ARB, and sum up the values if the keys match OR if the keys don't match, then sum up the values where the ASSIGN matches, but

Re: [sqlite] Does not detect invalid column name when subexpression optimized away

But isn't NULL and 0 a NULL? So wouldn't it need to evaluate X to determine if it was null, and thus discover it wasn't a valid column name and return an error? David On Thu, 12/19/13, Richard Hipp wrote: Subject: Re: [sqlite]

Re: [sqlite] Is this a proper syntax?

On 10/24/2013 07:34 PM, Igor Korot wrote: Igor, On Thu, Oct 24, 2013 at 12:57 PM, Igor Tandetnik wrote: On 10/24/2013 3:23 PM, Igor Korot wrote: Will this query work: UPDATE a SET a.field1 = (SELECT b.field1 FROM b AS myfield), a.field2 = myfield... or I will have to

Re: [sqlite] Insert statement

You might be able to store your "variable" in a table: CREATE TABLE table_lastid  (id INTEGER); INSERT INTO table_lastid (id) VALUES(0); Then in your sequence: INSERT INTO table_a (val) VALUES ('xx'); UPDATE table_lastid SET id = last_insert_rowid(); INSERT INTO table_b (id, key, val)  

Re: [sqlite] SQLite Input with validation and lookup

I've never used Visual FoxPro, but I suspect that it allows you to create forms to insert data in to the database. Sqlite doesn't do that. Sqlite only provides the library to store the data via SQL statements that you execute via sqlite3_prepare()/sqlite3_step() function calls. You are

Re: [sqlite] Fuzzy joins

. But I am really close now. David From: Petite Abeille <petite.abei...@gmail.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Monday, March 11, 2013 5:45 PM Subject: Re: [sqlite] Fuzzy joins On Mar 11, 2013, at 10:

Re: [sqlite] Fuzzy joins

ussion of SQLite Database <sqlite-users@sqlite.org> Sent: Monday, March 11, 2013 3:24 PM Subject: Re: [sqlite] Fuzzy joins On Mar 11, 2013, at 4:54 PM, David Bicking <dbic...@yahoo.com> wrote: > Am I missing an obviously better way to do it? > A way that can easily be

[sqlite] Fuzzy joins

This is a weird request. I have a table of data with no natural primary key. I need to update this table from a prior table, but some of the data fields can change over time, so I must be flexible on how I match. So the matching I need to do is something like this, if Key1 is unique in both

Re: [sqlite] SQLite3 Bug Report: The shell accepts some dot commands ending in semicolons while rejecting others.

From: Ryan Johnson To: sqlite-users@sqlite.org Sent: Monday, January 28, 2013 12:54 PM Subject: Re: [sqlite] SQLite3 Bug Report: The shell accepts some dot commands ending in semicolons while rejecting others. On 28/01/2013

Re: [sqlite] Subject: Re: Simple SQLite-based spreadsheet?

I will say one of the spreadsheet like functions I have wanted, and haven't really seen, is the ability to copy a value in to the column in multiple rows. MS Access doesn't allow that, but it is trival in a spreadsheet, just highlight the cells, and Ctrl-D to copy the value down.. I don't

Re: [sqlite] just a test

I don't know what gmail is doing, but this is the first of your messages that I have seen for a long time. I thought you had left, except I'd occasionally see you quoted in someone's else email. Yahoo mail was completely dropping your email; not in spam, just not there. David - Original

Re: [sqlite] Simple SQLite-based spreadsheet?

>From: Gilles Ganault >On Fri, 7 Dec 2012 13:37:45 +, Simon Slavin wrote: >Use the free SQLite shell tool downloadable from the SQLite site. > >Write your own interface in PHP using the sqlite3 interface which does exactly >what you want.

Re: [sqlite] Inserting from another table...

Have you tried to replace the "Insert into values(" with just "Select (" to make sure the values are what you expect them to be. That might also point out if any of them are null. David From: Sam Carleton To: General

Re: [sqlite] Why can't sqlite disregard unused outer joins?

If Uniform has a given EmployeeName twice, you will get the Employee.Name twice in this query. Thus it would be a different result than if you did not join with Uniform. David From: Charles Samuels To: General Discussion of SQLite

Re: [sqlite] rearranging 2 fields into 1 based on an order indicator in 2 other fields

Create Table table2 (Field3, FieldC); insert into table2 (Field3, FieldC) Select Field1x, FieldAx from Table1; insert into table2 (Field3, FieldC) Select Field2y, FieldBy from Table1; this will put your data in to the new table. I suspect the rowids won't match what you want, but you can always

Re: [sqlite] don't understand what "query" returns...

I don't know python, but because you have count(*) and no group by, it will only return one row, with the total rows that matched your where clause. The Item1, Item2, Item3 are arbitrary values that were in the two rows of your data. You can either remove the count(*) and get both rows, or as

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

Did you remember to specify the file name when you started sqlite3. If you don't give the file name, everything is done to the memory database and is discarded when you exit. David From: peter korinis To: sqlite-users@sqlite.org

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

>From the docs: .separator STRING Change separator used by output mode and .import replace string with a comma, without quotes, then do your import. It should work. David From: peter korinis To: sqlite-users@sqlite.org Sent:

Re: [sqlite] How to access values of a columns with specific row id.

On 01/31/2012 04:05 AM, bhaskarReddy wrote: Hi Friends, Can any one tell me how to access values of a table column with its particular row id. I tried with "select * from ontTable where rowid=2;" I am getting the output as

Re: [sqlite] Calculating MSO

To: sqlite-users@sqlite.org Sent: Friday, December 16, 2011 4:05 PM Subject: Re: [sqlite] Calculating MSO David Bicking <dbic...@yahoo.com> wrote: > The calculation is that for each customer: > MSO = (Per + (AR-sum(Sales)/Sales)) > > Result > Cust MSO > 01 2.3 = (3+(100-120)

[sqlite] Calculating MSO

I need to calculate Months Sales Outstanding. CREATE TABLE  AR     Cust    Text     AR    Double CREATE TABLE Sales     Cust    Text     Per    Integer -- runs 1, 2, 3, etc     Sales    Double The calculation is that for each customer: MSO = (Per + (AR-sum(Sales)/Sales))        Where Per

Re: [sqlite] Slow INDEX

On 11/02/2011 12:31 PM, Fabian wrote: 2011/11/2 Mr. Puneet Kishor Others will have better answers, but methinks that when you reboot the computer, the operating system's caches are flushed out, which slows the operation. Try working with the db for a bit (SELECT, repeat

Re: [sqlite] Slow JOIN on ROWID

On 10/14/2011 06:39 AM, Fabian wrote: Exactly. I still don't have optimal performance in the query (although it's much better now), and it seems to be related to ORDER BY. When I execute: SELECT * FROM table WHERE data = 10 LIMIT 250 OFFSET 50 It's very fast, but it's get much slower

Re: [sqlite] Hidding records from the application

lient1 can see all the records except > rowid 1. > It looks like temporary view and temporary table is good solutions, > which one is better and which one could be more efficient? > > > > 2011/7/16, David Bicking<dbic...@yahoo.com>: >> I don't know I have much to of

Re: [sqlite] Hidding records from the application

I don't know I have much to offer here as I still really don't understand what you are trying to accomplish. But I looked and it appears that sqlite supports TEMPORARY VIEW, which, I believe, is only visible to the process that created it. And it will automatically go away when that process

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

Part of the problem here is that you are misusing the term BETWEEN. In SQL txt BETWEEN 1990 AND 1991 means those rows where the value of txt is >= 1990 AND <= 1991. It has nothing to do with the order of the rows in the table, it is purely a comparison of the value of txt in a particular row

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

I'm not entirely sure what your data looks like, but I am thinking that when you say "txt BETWEEN 'x1' AND 'x2'" you mean those rows between the row where txt = 'x1' and the row where txt = 'x2' If that is the case, maybe this will give you what you want: SELECT POS FROM T_x WHERE POS BETWEEN

Re: [sqlite] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

ou can probably gather...this is very new to me. > > On 21 June 2011 12:48, David Bicking<dbic...@yahoo.com> wrote: > >> On 06/21/2011 07:22 AM, e-mail mgbg25171 wrote: >>> The commented out lines work. >>> I'm wondering... >>> a) is it possible to

Re: [sqlite] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

On 06/21/2011 07:22 AM, e-mail mgbg25171 wrote: > The commented out lines work. > I'm wondering... > a) is it possible to do what's not commented out > b) what's the syntax re the "sql =..." and "sql +=..." lines > Any help much appreciated! > > > sql = "BEGIN"; //you need to add newline here >

[sqlite] query to find mixed values

I have two tables: CREATE TABLE INV ( REQ int, INV char, AMT float ); CREATE TABLE REP ( REQ int, ACCT char AMT float ); I need to flag any combinations where for a given REQ value, one table has a list of numbers of mixed signs, and the other table has one and only one value. So

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

--- On Thu, 2/10/11, Puneet Kishor wrote: > Date: Thursday, February 10, 2011, 10:35 AM > > On Thursday, February 10, 2011 at 9:22 AM, Nißl Reinhard > wrote: > > Hi, > > > > I'm sorry Pavel, I think you've got me wrong. > > > > > It's not "buggy". Name of the column in

Re: [sqlite] Bi-directional unique

I don't think this will work. xor(6,1) = 7 and xor(4,3) = 7, so you would fail to insert proper pairs. Or am I missing something? (At least I assume that the integers are not limited to just 1 2 or 3 as in the examples. David On 02/09/2011 05:58 PM, Samuel Adam wrote: > On Wed, 09 Feb 2011

Re: [sqlite] does sqlite differ between char, varchar and text?

Oh, and as I recall, sqlite2 completely ignored the type declaration. It stored what you typed in the schema, but did nothing with it. I am pretty sure that sqlite3 treats text, char and varchar completely the same. It ignores the number after char(x) or varchar(x). David --- On Thu,

Re: [sqlite] does sqlite differ between char, varchar and text?

If I recall correctly, sqlite2 stores everything as text. It doesn't have a concept of affinity. Everythign is text and it will convert anything as needed. David --- On Thu, 12/16/10, Artur Reilin wrote: > From: Artur Reilin > Subject: [sqlite] does

Re: [sqlite] gui for data entry

Take a look at Kexi. (kexi-project.org) It is part of the Koffice suite, but can be used standalone. I had tested it on Windows and had no trouble installing it. It uses sqlite as its back end storage and allows you to create forms, etc. Its aim is to be "like" MS Access. David --- On Tue,

Re: [sqlite] Getting unique years from a timestamp column

neral Discussion of SQLite Database" <sqlite-users@sqlite.org> > Date: Wednesday, September 29, 2010, 12:42 PM > This one doesn't seem to return the > desired result, instead it returned a > blank line? > > On Wed, Sep 29, 2010 at 12:40 PM, David Bicking <dbic...@

Re: [sqlite] Getting unique years from a timestamp column

How about trying select distinct datetime(date,'%Y') as year from data; David --- On Wed, 9/29/10, J. Bobby Lopez wrote: > From: J. Bobby Lopez > Subject: [sqlite] Getting unique years from a timestamp column > To: sqlite-users@sqlite.org > Date:

[sqlite] Error message in RAISE(ABORT,...)

Can the Error message returned by a trigger be an expression? My testing seems to indicate that you can only put a string literal in the Raise function. What I am trying to do is return the data that the trigger was looking at when it raised the error. My application can insert one to four

Re: [sqlite] How to get the original rows after 'group by'?

you can use: select col1, col2 from test where col1 in (select col1 from test group by col1 having count(*)<=2); David --- On Thu, 8/26/10, Peng Yu wrote: > From: Peng Yu > Subject: [sqlite] How to get the original rows after 'group by'? > To:

Re: [sqlite] playing with triggers

--- On Thu, 8/19/10, Simon Slavin <slav...@bigfraud.org> wrote: > > On 19 Aug 2010, at 8:10pm, David Bicking wrote: > > > The way it is set up, if any of the updates/inserts > done by the triggers fail, everything rolls back, including > the original data that cause

[sqlite] playing with triggers

I am more or less playing with triggers trying to learn what they can do. I have a setup where I write data to Table1. An after insert trigger looks up the newly written data codes in Table1, and writes 1 to 4 records to Table2. An after insert trigger on Table2 looks at the new data and

Re: [sqlite] trigger or application code

y [code] value > under the sun is > acceptable; there's no ITEMS table to prevent invalid codes > via a foreign > key declaration. > > Regards > Tim Romano > > > > > On Tue, Aug 10, 2010 at 1:20 PM, Igor Tandetnik <itandet...@mvps.org> > wrote: > > >

[sqlite] trigger or application code

I am building an application with these two tables: CREATE TABLE changes(ChangeDate, Code, AdjAmount, Descr, Creatable); CREATE TABLE items(Code, Amount) Now, what I would like to happen, I insert in to changes, and it updates the Amount in items. I can get that with CREATE TRIGGER

Re: [sqlite] list table structure

You would use pragma table_info(); http://www.sqlite.org/pragma.html#pragma_table_info --- On Mon, 8/2/10, Chris Hare wrote: > From: Chris Hare > Subject: [sqlite] list table structure > To: sqlite-users@sqlite.org > Date: Monday, August 2, 2010, 12:11 PM > I

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

On Wed, 2010-06-30 at 09:04 -0400, Tim Romano wrote: > > The EXAMPLE: If you create a database in the authoritative version of SQLite > using INT PRIMARY KEY (rather than INTEGER PRIMARY KEY), when you share the > database with your Adobe-using affiliate, all hell will break loose. I will >

Re: [sqlite] Parametrized Queries issue (Possible bug)

I think in your first example, the :a comes first, so it is assigned the first index value. You then use ?1, which also uses the first parameter index. In the second, you use ?1 first, then :b, which sees the first index has been used and thus assigns it to the second index. As I believe Jay

Re: [sqlite] Get a specific sequence of rows...

On Fri, 2010-03-26 at 10:00 +0100, Fredrik Karlsson wrote: > Hi, > > I have a list of id:s stored in a field. I would now like to get some > information from a table by these id:s, but exactly in this order. So, > if I have a table > > 1 One > 2 Two > 3 Three > > and the sequence "3,1,2" stored

Re: [sqlite] Question about binding

--- On Fri, 3/19/10, Vance E. Neff wrote: > UPDATE table1 set (?, ?, ?) WHERE col1=? and col2=?; > > I've never used binding before but have known it is a good > idea in order > to avoid injection of bad stuff. > > Vance > You count the question marks from left to

Re: [sqlite] Shocked by long updates of the text database, 1000 speed improvement after small modification to the code.

As a test, have you tried wrapping your updates in a transaction? That would isolate if the slow down is the actual writing of the data to disk. Where is the file sitting: A local drive, or something across a network connection? David On Wed, 2010-02-03 at 08:51 -0800, a1rex wrote: > >-

Re: [sqlite] uninstalling sqlite

On Sun, 2010-01-03 at 03:09 +, Simon Slavin wrote: > On 3 Jan 2010, at 2:44am, Ervin Sebag wrote: > > > I installed sqlite myself from a compressed tar package, using the > > funpkg -i command. > > if -i does install, perhaps -u does uninstall. I can't find the > documentation for funpkg

Re: [sqlite] BACK API Questions

Raghu, Can you either use the backup API or a simple attach to copy the data from the memory database to a new file based db. A second process could then much more slowly poll to see if a new "temporary" file was available, and attach it, and insert its data albeit slowly in to the consolidated

Re: [sqlite] Ideal way to check whether a table has a specific column

You could try to prepare a statement like "SELECT YourColumnName FROM YourTable;" If prepare returns an error, then likely the error message will say the column doesn't exist. If no error, it does exist. Such a simple query shouldn't take long to prepare. Probably less time then to grab the

Re: [sqlite] SUM(tripSeconds) and format output as days.hours:minutes:seconds.hundredths?

I probably should have explicitly stated that my suggestion only worked up to 24 hours. Unfortunately I couldn't think of a solution for greater values. Yesterday Igor posted a solution that works with days. You never responded to him so perhaps you didn't see it. I'll copy it here: ** SELECT

Re: [sqlite] SUM(tripSeconds) and format output as days.hours:minutes:seconds.hundredths?

On Sun, 2009-11-01 at 12:15 -0800, DaleEMoore wrote: > I'd like to SUM(tripSeconds) and format output as > days.hours:minutes:seconds.hundredths, but have not been able to figure out > how to do that with sqlite. This didn't seem to come close: > > SELECT > STRFTIME('%d', SUM(tripSeconds)) + '.'

Re: [sqlite] (no subject)

alues ('C0', 'name1', > 'name2'); > sqlite> select * from assets; > C0|name1|name2 > sqlite> insert into assets select 'C0', 'name1', 'name3' > where not > exists (select 1 from assets where Code='C0' and > acct1='name1'); > sqlite> select * from assets; > C0|name1|

[sqlite] (no subject)

I have a table ASSETS with fields (Code Text, Acct1 Text, Acct2 Text). (There are other fields, and the primary key is a combination of 5 columns) For one code ('C0') I want to implement a rule that if I attempt to insert a combination of 'C0'/Acct1/Acct2 it will be ignored if the first two keys

Re: [sqlite] controlling changes

On Sat, 2009-10-10 at 19:31 +0100, Simon Slavin wrote: > On 10 Oct 2009, at 5:08pm, David Bicking wrote: > > > I have a table: > > CREATE TABLE Assets > > ( ControlDate Date > > , Amt > > ) > > There is no such column type as 'Date' in SQLite. You g

Re: [sqlite] controlling changes

Can you show me what the constraint would look like? Thanks, David On Sat, 2009-10-10 at 09:11 -0700, Harold Wood & Meyuni Gani wrote: > You can do it as a constraint. > > -Original Message- > From: David Bicking <dbic...@yahoo.com> > Sent: Saturday,

[sqlite] controlling changes

I have a table: CREATE TABLE Assets ( ControlDate Date , Amt ) Now, the business rules are you can INCREASE the Amt if the Current Date is the ControlDate in the record. You can DECREASE the amount if the Current Date is greater than or equal to the ControlDate. Can this be enforced

[sqlite] exceptions to check constraints

I've never had to deliberately use check constraints in my projects, but I would like to use them on a new projects. I've googled the subject, but found no good explanations. I have a table CREATE TABLE Assets ( Nametext PRIMARY KEY , Amt integer ); Now, for 99% of the

Re: [sqlite] Problems with max(datestamp) in subquery

On Mon, 2009-08-17 at 15:05 -0700, Leo Freitag wrote: > David Bicking-2 wrote: > > > > As written, you were selecting any record with the correct date > > regardless of Ensemble or Steuck. > > > > David > The following seem to work: > >

Re: [sqlite] SELECT * from ?

On Thu, 2009-08-13 at 01:12 +0100, Simon Slavin wrote: > On 12 Aug 2009, at 11:35pm, Igor Tandetnik wrote: > > > Bill Welbourn > > wrote: > >> I am new to the forum and have a question regarding querying data > >> from a table, where the table name is random. For example,

Re: [sqlite] Problems with max(datestamp) in subquery

On Wed, 2009-08-12 at 07:02 -0700, Leo Freitag wrote: > > I modified the code a get the following results. Unfortunetly only a little > more as expected: > > DROP TABLE 'tblZO_Haupt'; > CREATE TABLE 'tblZO_Haupt' ('id' INTEGER PRIMARY KEY, 'zo_tblEnsemble' > INTEGER, 'zo_tblSaenger' INTEGER,

Re: [sqlite] Date Comparisons SQL

I think your problem is that you need to put the value in sDateTemp in quotes. "WHERE Date < '" & sDateTemp & "'" or "WHERE Format$(Date, "-mm-dd") < '"& sDateTemp & "'" Without the quote, I think sqlite is subtracting the day from the month from the year, and comparing that number with

Re: [sqlite] sqlite version 2.8 vs 3.x

On Thu, 2009-07-30 at 09:18 -0430, An wrote: > my question stayed unanswered, so that is why i'm repeating it on the > mailinglist: > > if sqlite2.8 will be supported of bugs the following years, as the web page > says, what is another reason for working with version 3.x instead of 2.8 ? > > i'm

Re: [sqlite] Reporting on summary data

On Sat, 2009-07-25 at 10:33 +1000, John Machin wrote: > On 25/07/2009 6:17 AM, David Bicking wrote: > > That works. Thanks! > > It struck me that Pavel's revised query didn't mention the d2 column at > all, only d1: > Thanks for the additional info. In real life, I added

Re: [sqlite] Reporting on summary data

That works. Thanks! > From: Pavel Ivanov > Oops, sorry! I misunderstood what you > need. Try this: > > select id, > case when act_sum = 1 then 'NEW' > when act_sum = 0 then 'CHANGE' > else 'DROP' > end as Action, > net > from > ( > select id, > sum(case when d1='X' then 1

Re: [sqlite] Reporting on summary data

> From: Pavel Ivanov > > Just do the same approach with CASE you've already used: > > Select ID, > CASE WHEN d1='X' AND d2 IS NULL THEN 'NEW' >     WHEN d1 IS NULL AND d2='X' THEN 'DROP' >     ELSE 'CHANGE' END AS Action, > Sum(CASE WHEN d1='X' THEN AMT ELSE 0 END) -

  1   2   >