Re: [sqlite] Pivot Sequential Data into Dynamic Groups

On Oct 1, 2010, at 2:41 PM, Igor Tandetnik wrote: > Rather than hardcoding table range, you can synthesize it directly from > measurement. Something like this: Yes, very nice. That said, not all problems deserve sophisticated solutions. > Note also that 'end' and 'temp' are keywords in SQL,

[sqlite] update a record just using a trigger

I appreciate the prompt response to my prior question about triggers, and have another one. I'm using a python program to query an SQLite database from realtime data (IDS data actually). Every time I get a hit in the database, I want to record in that record an updated hitcount, and the

Re: [sqlite] Trigger conditionally causing delete

Quoth Jim Mellander , on 2010-10-01 16:04:57 -0700: > I have another question on this, if you don't mind (please excuse my > ignorance of SQL). I tried your trigger: > > CREATE TRIGGER auto_delete_summary AFTER DELETE ON detail > FOR EACH ROW WHEN NOT EXISTS (SELECT * FROM

Re: [sqlite] Trigger conditionally causing delete

On 2 Oct 2010, at 12:04am, Jim Mellander wrote: > I tried your trigger: > > CREATE TRIGGER auto_delete_summary AFTER DELETE ON detail > FOR EACH ROW WHEN NOT EXISTS (SELECT * FROM detail WHERE key = OLD.key) > BEGIN > DELETE FROM summary WHERE key = OLD.key; > END; > > and it works as

Re: [sqlite] Trigger conditionally causing delete

I have another question on this, if you don't mind (please excuse my ignorance of SQL). I tried your trigger: CREATE TRIGGER auto_delete_summary AFTER DELETE ON detail FOR EACH ROW WHEN NOT EXISTS (SELECT * FROM detail WHERE key = OLD.key) BEGIN  DELETE FROM summary WHERE key = OLD.key; END;

Re: [sqlite] errors after sqlite3_step

On Fri, Oct 1, 2010 at 6:24 PM, Wolfgang Enzinger wrote: > Am Fri, 01 Oct 2010 14:11:51 -0700 schrieb Dave Dyer: > > > There ought to be a definitive list of what errors can occur > > after sqlite3_step, and which (if any) are recoverable. > > IMHO that's impossible, at least

Re: [sqlite] Trigger conditionally causing delete

Thanks, this is great - I'll read up on expression syntax and usage. On Fri, Oct 1, 2010 at 3:11 PM, Drake Wilson wrote: > Quoth Jim Mellander , on 2010-10-01 14:38:03 -0700: >> Hi: >> >> I want to use to trigger on deletion of a detail record to >>

Re: [sqlite] errors after sqlite3_step

Am Fri, 01 Oct 2010 14:11:51 -0700 schrieb Dave Dyer: > There ought to be a definitive list of what errors can occur > after sqlite3_step, and which (if any) are recoverable. IMHO that's impossible, at least because SQLite allows the definition of user defined functions. Now such a function may

Re: [sqlite] Trigger conditionally causing delete

Quoth Jim Mellander , on 2010-10-01 14:38:03 -0700: > Hi: > > I want to use to trigger on deletion of a detail record to > automatically delete a summary record, if not more detail records > exist, something like: > > CREATE TRIGGER detail_delete AFTER DELETE ON detail >

Re: [sqlite] Trigger conditionally causing delete

As a newbie just learning SQL, this looks like a foreign key situation - unfortunately I'm using the standard snowleopard sqlite which is 3.6.12, which appears to not support foreign key constraints. Assuming I upgrade, I presume I can add the summary record with a BEFORE trigger if it isn't

[sqlite] Trigger conditionally causing delete

Hi: I want to use to trigger on deletion of a detail record to automatically delete a summary record, if not more detail records exist, something like: CREATE TRIGGER detail_delete AFTER DELETE ON detail BEGIN -- here I don't know syntax IF COUNT(detail records with key) = 0

[sqlite] errors after sqlite3_step

There ought to be a definitive list of what errors can occur after sqlite3_step, and which (if any) are recoverable. In the case in point, I sqlite_prepare_v2 returns sqlite_ok, and the initial call to sqlite_step returns sqlite_cantopen This doesn't make any sense to me, and certainly isn't

Re: [sqlite] Tricky grouping query

Andy Chambers wrote: > Given the following > > create table events ( > id, > date, > status > ); > insert into events values ('001','a','N'); > insert into events values ('001','b','N'); > insert into events values ('001','c','Y'); > insert into events values

Re: [sqlite] Tricky grouping query

On Fri, Oct 01, 2010 at 08:37:07PM +0100, Andy Chambers wrote: > Given the following > > create table events ( > id, > date, > status > ); > insert into events values ('001','a','N'); > insert into events values ('001','b','N'); > insert into events values ('001','c','Y'); > insert into

Re: [sqlite] Tricky grouping query

Quoth Andy Chambers , on 2010-10-01 20:37:07 +0100: > Is it possible, using plain SQL, to derive the following > > 001,c,d > 001,e,g In other words, you're comparing rows with "adjacent" ones? Yes, it's probably possible, but it would be very awkward and possibly slow.

Re: [sqlite] Tricky grouping query

Why would you want to do this in plane sql, as opposed to using the API to go through the list and derive it? On Fri, Oct 1, 2010 at 3:37 PM, Andy Chambers wrote: > Given the following > > create table events ( > id, > date, > status > ); > insert into events

[sqlite] Tricky grouping query

Given the following create table events ( id, date, status ); insert into events values ('001','a','N'); insert into events values ('001','b','N'); insert into events values ('001','c','Y'); insert into events values ('001','d','N'); insert into events values ('001','e','Y'); insert into

Re: [sqlite] Accented characters and ODBC

Greg Bryant writes: just saw that you usually should install 3 different ODBC-drivers and 1 of them is called SQLite ODBC (UTF-8) Driver. Maybe you should give it a try before asking the programmer greetings ___ sqlite-users

Re: [sqlite] Accented characters and ODBC

Greg Bryant writes: [...] > Does anyone have any pointers on where I can look to figure > this out? [...] maybe the friendly driver programmer is willing to help you if you send a bug report http://www.ch-werner.de/sqliteodbc/html/index.html greetings Oliver

Re: [sqlite] Accented characters and ODBC

Quoth Greg Bryant , on 2010-10-01 12:51:10 -0500: > I'm using current SQLite (3.7.2) via a visual c++ app. We're connecting a > sqlite3 database via ODBC (driver from Driver from where again? > When I go to do a select via the ODBC connection, however, I get back > garbage

Re: [sqlite] Temporary views across attached databases

I've being using this feature massively since version 3.6.7. Now I'm working (production) with 3.6.22. It works just fine. I've being testing 3.7.x and it seems OK too. Best regards. Alessandro Merolli. On 01/10/2010, at 14:26, John Drescher wrote: > I see that normal views across attached

Re: [sqlite] Trigger mask entities...

Brian Curley wrote: > In reading through the code as a non-C kind of guy, it looks like there's an > array of columnar values lumped together as old.* and new.* would need them, > but nothing that contains the individual field or column names. I'm not sure I understand. A

[sqlite] Accented characters and ODBC

Not sure if ODBC questions belong here, feel free to point me to a better forum. I'm using current SQLite (3.7.2) via a visual c++ app. We're connecting a sqlite3 database via ODBC (driver from , also current version - 0.87). If I do either an insert or update using an accented character (e.g.

[sqlite] Temporary views across attached databases

I see that normal views across attached databases do not work but it looks like I can create a temporary view across attached databases. Is this a supported configuration? I want to make sure before I put this in my application. -- John M. Drescher ___

Re: [sqlite] Trigger mask entities...

On 1 Oct 2010, at 4:44pm, Brian Curley wrote: > My business case would be in the realm of logging, for example. If I'm > using an audit-centric trigger, I'd probably want to store field and value, Don't forget that one 'UPDATE' command can result in many different fields being changed. So

[sqlite] Trigger mask entities...

Looking for some guidance or enlightenment on the available fields within a given trigger. In reading through the code as a non-C kind of guy, it looks like there's an array of columnar values lumped together as old.* and new.* would need them, but nothing that contains the individual field or

[sqlite] FTS Question

I'm experimenting with the FTS capability and have a question How do I enter a value in the database that is hyphenated? Seems to be backwards form what I would expectif the content contains a hyphen then only non-hyphenated query returns the correct value whereas the hyphenated one

Re: [sqlite] Pivot Sequential Data into Dynamic Groups

Petite Abeille wrote: > On Sep 30, 2010, at 11:44 PM, Jordan Dahlke wrote: > >> Is there a good way to do this with Select and Group By statement? > > For a given definition of "good" :P > > create temporary table range as > select 0 as start, 24 as end union all >

Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?

I've used SQLite for exactly this. I have no gripes with it at all. I ended up using just one table in one DB as indexing makes things simple and fast. WAL i've been trying these last few days and seems trouble-free. ___ sqlite-users mailing list

[sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

Hi, I'm validating SQLite test suite version 3.7.2 on QNX operating system. I have built the testfixture using SQLite amalgation file and other related files/libraries. I ran the full test suite and most of the test cases are passing, except some run in journal_mode = WAL. Test cases whose