Re: [sqlite] Simple SQL question?

Bart Smissaert wrote: > Have (simplified) a table like this: > > CREATE TABLE TABLE1( > [PATIENT_ID] INTEGER PRIMARY KEY, > [ADDRESS] TEXT, > [DATE_OF_BIRTH] TEXT) > > DATE_OF_BIRTH is in the

Re: [sqlite] Simple SQL question?

On Nov 16, 2010, at 11:55 PM, Bart Smissaert wrote: > This seems to work fine, Then you are golden :) > but I am not sure if this SQL is correct and > if the results will always be correct and have a feeling > that there must be a better construction. > Any suggestions? Nothing very

[sqlite] Simple SQL question?

Have (simplified) a table like this: CREATE TABLE TABLE1( [PATIENT_ID] INTEGER PRIMARY KEY, [ADDRESS] TEXT, [DATE_OF_BIRTH] TEXT) DATE_OF_BIRTH is in the ISO8601 format -mm-dd Now I need a SQL to find the oldest

Re: [sqlite] sqlite_sequence table

See my previous e-mail: I was querying temp.sqlite_sequence when I thought I was querying main.sqlite_sequence. Will On 11/16/10 2:22 PM, "Richard Hipp" wrote: On Tue, Nov 16, 2010 at 4:31 PM, Duquette, William H (316H) < william.h.duque...@jpl.nasa.gov> wrote: > Yes. I've

Re: [sqlite] Resursive trigger not doing full recursion...

Hi Igor, Thank you for the qick response. Of course, this PRAGMA solved the entire issue, once I got a never version of SQLite installed. Thank you! /Fredrik On Tue, Nov 16, 2010 at 10:58 PM, Igor Tandetnik wrote: > Fredrik Karlsson wrote: >> I'm

Re: [sqlite] sqlite_sequence table

On Tue, Nov 16, 2010 at 4:31 PM, Duquette, William H (316H) < william.h.duque...@jpl.nasa.gov> wrote: > Yes. I've done a couple of inserts into the table, interspersed with > queries to the > sqlite_sequence table; they show the sqlite_sequence table as being empty. > Test case: CREATE TABLE

Re: [sqlite] Trouble with TRIGGERS

On 16 Nov 2010, at 10:18pm, Bernard Ertl wrote: > Kees Nuyt wrote: >> > >>> Nicolas Williams-2 wrote: Do you have recursive triggers enabled? >>> >>> I'm not sure. How do I check? >> >> http://www.sqlite.org/pragma.html#pragma_recursive_triggers > > I'm not using any pragma commands,

Re: [sqlite] Trouble with TRIGGERS

Kees Nuyt wrote: > >> Nicolas Williams-2 wrote: >> > Do you have recursive triggers enabled? >> >> I'm not sure. How do I check? > > http://www.sqlite.org/pragma.html#pragma_recursive_triggers > I'm not using any pragma commands, so no, I'm not using recursive triggers. -- View this

Re: [sqlite] SQLITE 2.3 and SQLITE3 3.7.x SHELL interoperability.

On 16 Nov 2010, at 9:41pm, Piszcz, Al wrote: > If a database file is created with 2.3.2 is it 'safe' to use with SQLITE3 > 3.7.x shell? > When VACUUM is performed on 2.3.2 database file is with SQLITE 3.7.x are > there any side effects? THe database formats of SQLite version 2 and version 3

Re: [sqlite] Trouble with TRIGGERS

On Tue, 16 Nov 2010 14:07:34 -0800 (PST), Bernard Ertl wrote: > > > Nicolas Williams-2 wrote: > > Do you have recursive triggers enabled? > > I'm not sure. How do I check? http://www.sqlite.org/pragma.html#pragma_recursive_triggers -- ( Kees Nuyt ) c[_]

Re: [sqlite] Trouble with TRIGGERS

Nicolas Williams-2 wrote: > Do you have recursive triggers enabled? I'm not sure. How do I check? -- View this message in context: http://old.nabble.com/Trouble-with-TRIGGERS-tp30228089p30233496.html Sent from the SQLite mailing list archive at Nabble.com.

Re: [sqlite] Trouble with TRIGGERS

Do you have recursive triggers enabled? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Resursive trigger not doing full recursion...

Fredrik Karlsson wrote: > I'm probably doing something really stupid here, but I feel I need to > ask you anyway to see id there is something that I've missed. > Is it not possible for a trigger to trigger itself? Recursive triggers have to be explicitly turned on:

[sqlite] Resursive trigger not doing full recursion...

Dear list, I'm probably doing something really stupid here, but I feel I need to ask you anyway to see id there is something that I've missed. Is it not possible for a trigger to trigger itself? I get triggers that trigger triggers, but so far not triggers that trigger themselves... (yes, many

[sqlite] SQLITE 2.3 and SQLITE3 3.7.x SHELL interoperability.

If a database file is created with 2.3.2 is it 'safe' to use with SQLITE3 3.7.x shell? When VACUUM is performed on 2.3.2 database file is with SQLITE 3.7.x are there any side effects? Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] sqlite_sequence table

Aha! I've got AUTOINCREMENT keys in both regular and temporary tables. This results in two sqlite_sequence tables, one in sqlite_master and one in sqlite_temp_master. And evidently, if I have a permanent and a temporary table with the same name, a query that doesn't specify gets the

Re: [sqlite] sqlite_sequence table

Yes. I've done a couple of inserts into the table, interspersed with queries to the sqlite_sequence table; they show the sqlite_sequence table as being empty. Will On 11/16/10 1:29 PM, "Gerry Snyder" wrote: On 11/16/2010 2:14 PM, Duquette, William H (316H) wrote: >

Re: [sqlite] sqlite_sequence table

On 11/16/2010 2:14 PM, Duquette, William H (316H) wrote: > Howdy! > > According to the docs on sqlite.org, a table with "INTEGER PRIMARY KEY > AUTOINCREMENT" gets an entry in the sqlite_sequence table. I've got some > code that contains such a table; but if I query the sqlite_sequence table I

[sqlite] sqlite_sequence table

Howdy! According to the docs on sqlite.org, a table with "INTEGER PRIMARY KEY AUTOINCREMENT" gets an entry in the sqlite_sequence table. I've got some code that contains such a table; but if I query the sqlite_sequence table I don't see it being updated; it's always empty. Anyone have any

Re: [sqlite] Trouble with TRIGGERS

Dan Kennedy-4 wrote: > > Sounds like it. > > Calling sqlite3_prepare_v2() generates the VM code for all > triggers that could possibly be invoked by your statement. > All it considers when determining which triggers might be > needed is the type of statement (UPDATE, DELETE, INSERT) and > for

Re: [sqlite] WAL and multiple writers?

Hi there, Well I'm sure I was, all I did was add a "busy handler" and it seems to work 100% now ;-) Lynton -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Sylvain Pointeau Sent: 16 November 2010 06:09 PM To: General

[sqlite] curent_timestamp value

Hi, Could you please tip me why the CURRENT_TIMESTAMP value would always get set to "1999-21-31 12:00:00"? Any SQLITE_OMIT flags may contribute to this? Thanks in advance, Mark Ps. building the sqlite: v.3.6.23.1 for linux (kernel 2.6.31.8) on arm. My omits: -DSQLITE_OMIT_ALTERTABLE \

Re: [sqlite] threads and last_insert_rowid()

On Tue, Nov 16, 2010 at 11:38 AM, Ruslan Mullakhmetov < r.mullakhme...@tools4brokers.com> wrote: > > Understand this: Any programming language that is built around threads > is > > inherently broken and should be avoided. (I will not name names - you > know > > the languages I'm talking about.)

Re: [sqlite] Attach database problem

It solves the problem. I put all in one connection, made attach and everything is fine. Thanks Igor, you save me a hours! 2010/11/16 Igor Tandetnik : > Mihailo wrote: >> I have one database1 with dataTable1 and other database2 with dataTable2. >>

Re: [sqlite] threads and last_insert_rowid()

> Understand this:  Any programming language that is built around threads is > inherently broken and should be avoided.  (I will not name names - you know > the languages I'm talking about.)  And any operating system that depends > upon threads for performance is equality busted and should also be

Re: [sqlite] Attach database problem

Mihailo wrote: > I have one database1 with dataTable1 and other database2 with dataTable2. > conn1 and conn2. > > conn1->exec("UPDATE dataTable1 set spt_activtrigger = 3 where sim_id > in ( select sim_id from dataTable2 where )); > conn2->exec("UPDATE dataTable2 set

Re: [sqlite] threads and last_insert_rowid()

On Tue, Nov 16, 2010 at 10:29 AM, Ruslan Mullakhmetov < r.mullakhme...@tools4brokers.com> wrote: > > as you see in _serialized_ mode "SQLite can be safely used by multiple > threads with no restriction". > > do i misunderstand something? > With serialized threading, SQLite is perfectly "safe" to

Re: [sqlite] WAL and multiple writers?

Are you sure that you are not keeping a transaction in the message sender? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Attach database problem

I have one database1 with dataTable1 and other database2 with dataTable2. conn1 and conn2. conn2->query("attach '".$_SESSION['QPO_dataBase1Path']."' as dst1"); //$_SESSION['QPO_dataBase1Path'] is path to database1, adding database1 to database2 conn1.beginTransaction(); conn2.beginTransaction();

Re: [sqlite] threads and last_insert_rowid()

thanks everybody, especially Pavel Ivanov who did not give himself a trouble to look at the source though i did. but as far as i understand this is only applicable to current version of sqlite and future releases may change things. Jay A. Kreibich wrote: > If each thread is using its own,

Re: [sqlite] threads and last_insert_rowid()

> whom to trust? Trust Kees. Borgan's thought about keeping the lock and transaction management inside sqlite3_exec is incorrect. > as for Kees Nuyt reply, did you toke int account that "select > last_insert_rowid() " > and insert query combined in single query and executed via single call > of

Re: [sqlite] Strange Corruption

.dump (3.6.23.1) and afterwards "sqlite3 /tmp/new.db < /tmp/dump.sql" with 3.7.2 worked and fixed the .backup problem (as expected). As I've already downgraded sqlite3 in our new firmware and patched the live-systems that were running with the new firmware I'll only have one machine to check

Re: [sqlite] Query help - two similar queries

This also works...a little mod to Igor's... You need to ensure that the players are always listed in the same 1,2 order though. Otherwise the group by won't work. .mode column .width 8 create table Games(id,player1,player2,score); insert into Games values (1,1,2,1); insert into Games

Re: [sqlite] Query help - two similar queries

Many thanks again Igor. On 16/11/2010 13:15, Igor Tandetnik wrote: > Ian Hardingham wrote: >> Thanks Igor. Can i get custom results like >> >> GamesWonByPlayer1 >> >> By using getColumn in the normal way? > I'm not familiar with the term "custom result". GamesWonByPlayer1 is

Re: [sqlite] Strange Corruption

Sorry, I meant .dump Given what you're describing I think it's worth finding out if you've found some bug in 3.7.2. The docs say 3.7.2 fixed a long-standing corruption bug. I don't know if that's related to this or not but sounds suspiciously close. So... #1 .dump the database #2 .import

Re: [sqlite] Query help - two similar queries

Ian Hardingham wrote: > Thanks Igor. Can i get custom results like > > GamesWonByPlayer1 > > By using getColumn in the normal way? I'm not familiar with the term "custom result". GamesWonByPlayer1 is just a column alias which a) is completely optional, you could safely drop

Re: [sqlite] EXTERNAL:Re: Strange Corruption

Well indeed it wasn't 3.7.X that created the database originally. But it was always 3.7.2 that made the INSERTS/UPDATES that lead to the state in which the database couldn't be backed up anymore. So what do you mean in fact: 3.7.X maybe can't handle database structures created with older

Re: [sqlite] threads and last_insert_rowid()

i got following contradictory replies to my question > if i execute query like " insert into tbl( filed ) VALUES ( 1 ); Select > last_insert_rowid() as li;" would be it atomic? or it anyway would suffer > from threads? from borgan: > Hi, i think this will probably be "atomic". > What i mean

Re: [sqlite] Query help - two similar queries

Thanks Igor. Can i get custom results like GamesWonByPlayer1 By using getColumn in the normal way? That may be a stupid question - I guess what I mean is, are those custom identifiers treated as column names when reading back from the select? Thanks, Ian On 16/11/2010 13:04, Igor Tandetnik

Re: [sqlite] Query help - two similar queries

Ian Hardingham wrote: > I have a badly designed structure for a table which records /games > played/ by people. It looks like: > > id > player1 > player2 > score > > If score > 0, player 1 won the game. If score < 0, player 2 won it. > (Score of 0 is a draw). > > I wish to

[sqlite] Query help - two similar queries

Hey guys. I have a badly designed structure for a table which records /games played/ by people. It looks like: id player1 player2 score If score > 0, player 1 won the game. If score < 0, player 2 won it. (Score of 0 is a draw). I wish to find the total record in games between two specific

Re: [sqlite] EXTERNAL:Re: Strange Corruption

I thought of another test you should try. Do an .export of your original database using 3.6.23.1 and .import it (constructing a new database). Then try your backup. If that works then you're just seeing corruption in the original database that 3.6.23.1 handles (since it created it). If it

Re: [sqlite] Strange Corruption

No, this is definitely not the reason in my case as I can reproduce this issue on every 3.7.2/3.7.3 machine I've tested after copying the database file (and only the database file) to these machines. Am 15.11.2010 15:41, schrieb Kirk Clemons: > Not sure if it helps but I would see this quite

Re: [sqlite] Trouble with TRIGGERS

On 11/16/2010 06:25 AM, Bernard Ertl wrote: > Hi, > > I'm experiencing some performance issues with triggers at the moment and > hoping someone can help shed some light on what is happening. > > I have a database with ~20 tables and>100 triggers. I noticed a severe > performance degradation

[sqlite] Trouble with TRIGGERS

Hi, I'm experiencing some performance issues with triggers at the moment and hoping someone can help shed some light on what is happening. I have a database with ~20 tables and >100 triggers. I noticed a severe performance degradation after adding the last few triggers and it puzzled me

Re: [sqlite] Strange Corruption

Not sure if it helps but I would see this quite frequently when an old journal file would be left behind in the same directory as the backup database. This could be why making a change to the database such as vacuum would prevent the corruption. ___