Re: [sqlite] PRAGMA database_list: insert into table?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/23/2010 07:47 PM, ve3meo wrote: > Is it possible to store the results of a PRAGMA statement, especially PRAGMA > database_list in a SQLite temporary table using only SQLite commands? No. Is there any particular reason your code can't copy

[sqlite] PRAGMA database_list: insert into table?

Is it possible to store the results of a PRAGMA statement, especially PRAGMA database_list in a SQLite temporary table using only SQLite commands? I have tried every combination I can think of without success. Tom ___ sqlite-users mailing list

Re: [sqlite] Help with complex UPDATE question

Jim, I see what you mean. Would it be faster then if I read the from the table, do the math in a program, and then insert the values back into the table? Or would it faster to amend the table as you suggest and use SQL UPDATE? I wonder. Jim Morris-4 wrote: > > AYou must add additional data

Re: [sqlite] Help with complex UPDATE question

You must add additional data to the rows so you can refer to them unambiguously. table1 (KEY, COL1, ord) 0, 1,1 0, 2,2 1, 3,1 1, 4,2 2, 5,1 2, 6,2 3, 7,1 3, 8,2 On 7/23/2010 12:16 PM, peterwinson1 wrote: > Jim you maybe correct that I don't have enough data to unambiguously identify > the

Re: [sqlite] Help with complex UPDATE question

Jim you maybe correct that I don't have enough data to unambiguously identify the rows. But just in case I was not very clear the first time. What I want to do is take the COL1 values of the first 2 rows [1, 2] and subtract them from the COL1 values, two rows at a time. so [1, 2] - [1, 2],

Re: [sqlite] Help with complex UPDATE question

On 7/23/2010 10:09 AM, Jim Morris wrote: > What you are trying to do is unclear to me. It seems that table1 > doesn't have enough data to unambiguously identify the rows. > > On 7/23/2010 8:03 AM, peterwinson1 wrote: >> Thanks Eric and Alan for your help. I tried to apply your code to my

Re: [sqlite] Help with complex UPDATE question

What you are trying to do is unclear to me. It seems that table1 doesn't have enough data to unambiguously identify the rows. On 7/23/2010 8:03 AM, peterwinson1 wrote: > Thanks Eric and Alan for your help. I tried to apply your code to my problem > and it works to a limited extent because the

Re: [sqlite] Recovering crashed database using C++ interface

On Fri, Jul 23, 2010 at 12:10 PM, Robert McVicar wrote: > I have the occasional instance of my application crashing, leaving > behind a journal file. However, on restart and opening the database > again, it doesn't seem to do any rollback action and I am unable to >

[sqlite] Recovering crashed database using C++ interface

I have the occasional instance of my application crashing, leaving behind a journal file. However, on restart and opening the database again, it doesn't seem to do any rollback action and I am unable to write to the database. Is there a C++ function that is necessary to trigger the rollback

Re: [sqlite] Help with complex UPDATE question

Thanks Eric and Alan for your help. I tried to apply your code to my problem and it works to a limited extent because the problem is more complicated than the example I gave in the post. I tries to simplify my exact problem but that didn't work out. So here is the problem that I trying to

Re: [sqlite] Couple of questions about WAL

On Jul 23, 2010, at 8:56 PM, Doug wrote: > Thanks for your explanations Dan. The new WAL feature sounds great > and I'm > excited to try it. Two questions below: > >> When in WAL mode, clients use file-locks to implement a kind of >> robust (crash-proof) reference counting for each database

Re: [sqlite] Couple of questions about WAL

Thanks for your explanations Dan. The new WAL feature sounds great and I'm excited to try it. Two questions below: > When in WAL mode, clients use file-locks to implement a kind of > robust (crash-proof) reference counting for each database file. > When a client disconnects, if it is the only

Re: [sqlite] Re ferring to subselect multiple times

> Is there a way? a) Temporary table b) Do it in your application instead of SQL - that's pretty easy. Pavel On Fri, Jul 23, 2010 at 5:17 AM, westmeadboy wrote: > > I have a complex query which returns multiple rows of a single TEXT column. > > I want to filter this so that

Re: [sqlite] Coping with database growth/fragmentation

On 23 Jul 2010, at 2:11am, Taras Glek wrote: > Recently I spent some time investigating sqlite IO patterns in Mozilla. > Two issues came up: keeping sqlite files from getting fragmented and > fixing fragmented sqlite files. If I understand correctly, there are two levels of fragmentation

Re: [sqlite] Coping with database growth/fragmentation

Hello Taras, List, I have been fighting the same problems described here for a long time, and have no real elegant solution. So, the proposed solution of the OP below would be ideal for me too. The proposed pragma could also define a number of pages to be allocated at once instead of a

Re: [sqlite] EXTERNAL:Re: Very Slow DB Access After Reboot on Windows

On 23 Jul 2010, at 12:22pm, Black, Michael (IS) wrote: > #1 Are you using the same database file on both Windows and Linux or are you > creating it independently on each? I'm wondering if your Windows system is > fragmented (much more likely than your Linux system is to frag). How full is >

Re: [sqlite] EXTERNAL:Re: Very Slow DB Access After Reboot on Windows

Hmmm...I would've thought copy would cache the file...learned something new (again)but at least you've identified the problem as caching vs disk i/o. #1 Are you using the same database file on both Windows and Linux or are you creating it independently on each? I'm wondering if your

Re: [sqlite] Bug: Column Constraint

Not a bug. See the 4th paragraph at http://www.sqlite.org/lang_createtable.html#rowid On Thu, Jul 22, 2010 at 8:03 AM, sanjiv wrote: > When I use the following command string: >"CREATE TABLE tbl1 (Id INT CONSTRAINT PK_Id PRIMARY KEY > AUTOINCREMENT NOT NULL ,Name

[sqlite] Coping with database growth/fragmentation

Hello, Recently I spent some time investigating sqlite IO patterns in Mozilla. Two issues came up: keeping sqlite files from getting fragmented and fixing fragmented sqlite files. First on fixing fragmentation: Currently we write pretty heavily to our databases. This causes the databases to

[sqlite] Bug: Column Constraint

When I use the following command string: "CREATE TABLE tbl1 (Id INT CONSTRAINT PK_Id PRIMARY KEY AUTOINCREMENT NOT NULL ,Name VARCHAR(20) )" I get the following SQLite exception: "AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY" wherease no exception is thrown if INT in the

[sqlite] Version compatibility

Hi, I've been reading about version compatibility between different versions of sqlite at the link below: http://www.sqlite.org/formatchng.html It states the expected behaviour for old and new with a different first number, and a different second number, but not a different third number. I

[sqlite] Re ferring to subselect multiple times

I have a complex query which returns multiple rows of a single TEXT column. I want to filter this so that only the longest strings are returned. In other words, if the longest text is N chars long, then I want to return all rows with N chars. If the query was on a table then this would be easy:

Re: [sqlite] Very Slow DB Access After Reboot on Windows

I have tried changing the extension and also "copy my.db nul:". The result does not change a bit. Still 40sec. I also tried reading the file with fread before openning db connection like; FILE* fp = fopen(fileName.c_str(), "rb"); if ( fp ) { char pBuffer[1024*32]; while (

Re: [sqlite] Couple of questions about WAL

On Thursday, July 22, 2010 6:14 PM, Dan Kennedy wrote: > On the other hand, if the only client connected to a database > does not disconnect cleanly (i.e. it crashes, the system crashes, > or the client exits without calling sqlite3_close()), then it > leaves the *-wal file in place. In this