Re: [sqlite] Reducing SQLite Memory footprint(!)

2008-08-21 Thread Mihai Limbasan
Brown, Daniel wrote: Perhaps, when is the next release due? I'd be interested to see the differences, if an upgrade reduces memory overhead that significantly it would be most excellent :) 3.6.2 is (tentatively) due this Monday :) ___ sqlite-users

[sqlite] How to use the ROWID of the last inserted row for FK insert into other tables?

2008-08-21 Thread alp
Hello, I have to tables(TBL_OBJECTS, TBL_TAGS) and I want to use the ROWID of the last inserted row from table TBL_OBJECTS to insert a new row in TBL_TAGS table with the ID_OBJECT foreign key set to the ROWID value from the precedent table. This is the tables definition: CREATE TABLE

[sqlite] Table Lock Error

2008-08-21 Thread Alex Katebi
Hi, For a in memory connection I get table lock error 6. I have one in memory connection with many prepared statements for different tables. Depending how the statements are interleaved I get a table lock error when dropping a table. What is causing of this? I did turn on tracing. I am

Re: [sqlite] Table Lock Error

2008-08-21 Thread Igor Tandetnik
"Alex Katebi" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > For a in memory connection I get table lock error 6. > I have one in memory connection with many prepared statements for > different tables. > Depending how the statements are interleaved I get a table lock error > when

Re: [sqlite] How to use the ROWID of the last inserted row for FK insert into other tables?

2008-08-21 Thread Francis GAYREL
Why two tables? Define a unique table and redefine your tables as views. alp a écrit : > Hello, > > I have to tables(TBL_OBJECTS, TBL_TAGS) and I want to use the ROWID of the > last inserted row from table TBL_OBJECTS to insert a new row in TBL_TAGS > table with the ID_OBJECT foreign key set to

Re: [sqlite] How to use the ROWID of the last inserted row for FK insert into other tables?

2008-08-21 Thread Igor Tandetnik
"alp" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I have to tables(TBL_OBJECTS, TBL_TAGS) and I want to use the ROWID > of the last inserted row from table TBL_OBJECTS to insert a new row > in TBL_TAGS table with the ID_OBJECT foreign key set to the ROWID > value from the

Re: [sqlite] How to use the ROWID of the last inserted row for FK insert into other tables?

2008-08-21 Thread alp
Igor Tandetnik wrote: > > "alp" <[EMAIL PROTECTED]> wrote in > message news:[EMAIL PROTECTED] >> I have to tables(TBL_OBJECTS, TBL_TAGS) and I want to use the ROWID >> of the last inserted row from table TBL_OBJECTS to insert a new row >> in TBL_TAGS table with the ID_OBJECT foreign key set to

Re: [sqlite] How to use the ROWID of the last inserted row for FK insert into other tables?

2008-08-21 Thread P Kishor
On 8/21/08, alp <[EMAIL PROTECTED]> wrote: > > Hello, > > I have to tables(TBL_OBJECTS, TBL_TAGS) and I want to use the ROWID of the > last inserted row from table TBL_OBJECTS to insert a new row in TBL_TAGS > table with the ID_OBJECT foreign key set to the ROWID value from the > precedent

Re: [sqlite] Correct SQL name resolution on AS clauses in a SELECT?

2008-08-21 Thread Chris Wedgwood
MySQL: mysql> SELECT a AS b, b AS a FROM t1 ORDER BY a; +--+--+ | b| a| +--+--+ |1 |2 | |9 |8 | +--+--+ 2 rows in set (0.00 sec) mysql> SELECT b AS a, a AS b FROM t1 ORDER BY a; +--+--+ | a| b| +--+--+ |2 |

[sqlite] inserting pixbuf in sqlite3 database

2008-08-21 Thread jitu singh
hi, I am using a GdkPixbuf and want to store the pixbuf created to the sqlite3 database . can you tell me how to store a pixbuf in sqlite3 database. Thanks in advance.Regards,Jitender Singh ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Reducing SQLite Memory footprint(!)

2008-08-21 Thread Dennis Cote
Brown, Daniel wrote: > > I just tried with rebuilt data from SQLite Analyzer in SQLite 3.6.1, I'm > still seeing a memory usage that is roughly three times the size of the > source database file, looking at your changes to my test there doesn't > seem to be any fixes that would resolve that. >

[sqlite] Any equivalent to MSSQL's UPDATE..FROM.. clause ?

2008-08-21 Thread Samuel Neff
I'm trying to update records in one table based on joined data in another table. MSSQL has support for a "FROM" clause within an UPDATE statement which makes this type of thing very easy. Is there any equivalent in SQLite? The only way I've found to achive the same results is to use a subselect

Re: [sqlite] Correct SQL name resolution on AS clauses in a SELECT?

2008-08-21 Thread Griggs, Donald
On the miniscule chance anyone's itching to see Informix ver 9 behaviour, here are the results: IBM Informix Dynamic Server Version 9.40.TC7 CREATE temp TABLE t1(a INTEGER, b INTEGER); INSERT INTO t1 VALUES(1,2); INSERT INTO t1 VALUES(9,8); SELECT a AS b, b AS a FROM t1

[sqlite] Sqlite freestanding.

2008-08-21 Thread Ricardo Hawerroth Wiggers - Terceiro
Hello. Has anyone used sqlite in a freestanding embedded environment? If anyone had success with it, how about the footprint? And storage medium, direct flash access? Thanks, Ricardo ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Reducing SQLite Memory footprint(!)

2008-08-21 Thread Nicolas Williams
On Thu, Aug 21, 2008 at 10:32:23AM -0400, Jeffrey Becker wrote: > Just out of curiosity what happens if you call > "PRAGMA page_size=4096" > before running the import? I wonder too, what does the page cache do when doing full table scans? If the cache has an LRU/LFU page eviction algorithm then

[sqlite] Detecting other connections to DB?

2008-08-21 Thread Doug Porter
Is there a way to get a list of connections that are opened on a particular SQLite database file? Our software uses SQLite to save our data and we want to warn a user who opens a file that is already opened by another user. I tried a homebrewed approach (keep a table of open connections

[sqlite] (no subject)

2008-08-21 Thread Joanne Pham
Hi All, I have a question related toSQLite db handle(pDb in my codes). I have the function below to open the database connection. I have to  call sqlite3_close(sqlite3 *)(sqlite3_close(pDb) in my case) before open another  database connection( by calling openDb) for releasing the memory which is

[sqlite] sqlite3_close

2008-08-21 Thread Joanne Pham
Sorry! Resend an email because no subject in previous email. Again. Your help is greatly appreciated. Thanks, JP - Original Message From: Joanne Pham <[EMAIL PROTECTED]> To: General Discussion of SQLite Database Sent: Thursday, August 21, 2008 11:54:13 AM

Re: [sqlite] sqlite3_close

2008-08-21 Thread Igor Tandetnik
Joanne Pham <[EMAIL PROTECTED]> wrote: > I have a question related toSQLite db handle(pDb in my codes). > I have the function below to open the database connection. I have to > call sqlite3_close(sqlite3 *)(sqlite3_close(pDb) in my case) > before open another database connection( by calling

Re: [sqlite] sqlite3_close

2008-08-21 Thread Mihai Limbasan
Hello there. I've re-read your mail a few times, however you seem to have forgotten to actually *ask the question.* On a sidenote, though: You're storing the database connection in a local variable (sqlSt) instead of returning it - so when you return from the function, you've opened a

Re: [sqlite] sqlite3_close

2008-08-21 Thread Mihai Limbasan
Mihai Limbasan wrote: Hello there. I've re-read your mail a few times, however you seem to have forgotten to actually *ask the question.* On a sidenote, though: You're storing the database connection in a local variable (sqlSt) instead of returning it - so when you return from the

Re: [sqlite] Detecting other connections to DB?

2008-08-21 Thread Alexey Pechnikov
Hello! В сообщении от Thursday 21 August 2008 22:45:33 Doug Porter написал(а): > Our software uses SQLite to save our data and we want to warn a user > who opens a file that is already opened by another user. I tried a > homebrewed approach (keep a table of open connections manually), but > ran

Re: [sqlite] Detecting other connections to DB?

2008-08-21 Thread Mihai Limbasan
Doug Porter wrote: Is there a way to get a list of connections that are opened on a particular SQLite database file? Our software uses SQLite to save our data and we want to warn a user who opens a file that is already opened by another user. I tried a homebrewed approach (keep a table of open

Re: [sqlite] Detecting other connections to DB?

2008-08-21 Thread Doug Porter
Great answer, thank you very much. doug Thursday, August 21, 2008, 3:42:12 PM, you wrote: ML> Doug Porter wrote: >> Is there a way to get a list of connections that are opened on a >> particular SQLite database file? >> >> Our software uses SQLite to save our data and we want to warn a user >>

Re: [sqlite] .import with .separator and quoted strings

2008-08-21 Thread Wilson, Ron P
Here is an easy way to reproduce the symptom. Given the following file as input for the .import command: ---csvtest.csv--- "1","wilson, ron" "2","momma, your" - Here is the sqlite output: SQLite version 3.5.9 Enter ".help" for instructions sqlite> create table names (id

Re: [sqlite] sqlite3_close

2008-08-21 Thread Joanne Pham
Is it necessary to call only sqlite3_close(pDb) before open another connection. Thanks, JP - Original Message From: Igor Tandetnik <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Thursday, August 21, 2008 12:03:58 PM Subject: Re: [sqlite] sqlite3_close Joanne Pham <[EMAIL

Re: [sqlite] sqlite3_close

2008-08-21 Thread Igor Tandetnik
Joanne Pham <[EMAIL PROTECTED]> wrote: > Is it necessary to call only sqlite3_close(pDb) before open another > connection. Thanks, No (though it's not clear why you would want multiple connections open at the same time). You can open several connections and close them in any order. But in your

Re: [sqlite] Reducing SQLite Memory footprint(!)

2008-08-21 Thread Dennis Cote
Jeffrey Becker wrote: > Just out of curiosity what happens if you call > "PRAGMA page_size=4096" > before running the import? > As I expected, it has no effect. The page size pragma only effects the :memory: database he is copying into. The page size of the database file was set when it was

Re: [sqlite] sqlite3_close

2008-08-21 Thread Joanne Pham
Got it! Thanks a lot for your answer. JP. - Original Message From: Igor Tandetnik <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Thursday, August 21, 2008 1:52:15 PM Subject: Re: [sqlite] sqlite3_close Joanne Pham <[EMAIL PROTECTED]> wrote: > Is it necessary to call only

Re: [sqlite] .import with .separator and quoted strings

2008-08-21 Thread Griggs, Donald
Hi Ron, I've encountered that as well. You're using the sqlite3 commandline interface program, I'm sure. I think it was intended as a test and demo utility, but it's found its way into a number of released products. I'm not aware of a way to make this work with the current utility. Since the

Re: [sqlite] Reducing SQLite Memory footprint(!)

2008-08-21 Thread Dennis Cote
Nicolas Williams wrote: > > I wonder too, what does the page cache do when doing full table scans? > If the cache has an LRU/LFU page eviction algorithm then full table > scans should not be a big deal. Ideally it should not allow pages read > during a full table scan to push out other pages,

Re: [sqlite] Reducing SQLite Memory footprint(!)

2008-08-21 Thread Nicolas Williams
On Thu, Aug 21, 2008 at 04:02:32PM -0600, Dennis Cote wrote: > I built a copy of my test database using a 4096 byte page size and it > reduced the database file size from 17.5 MB to 12.2 MB. When I repeat > the tests using this database file I get the same 18102 KB of memory > used, but a

[sqlite] Transactions and Threads

2008-08-21 Thread Shawn Wilsher
Hey all, I'm looking to clarify the behavior of transactions when it comes to threads. When using the same sqlite3 object, and you begin a transaction on one thread, does it also group work that is being done on another thread until you end the transaction? Or is it the case that each thread

Re: [sqlite] Transactions and Threads

2008-08-21 Thread Igor Tandetnik
Shawn Wilsher <[EMAIL PROTECTED]> wrote: > I'm looking to clarify the behavior of transactions when it comes to > threads. When using the same sqlite3 object, and you begin a > transaction on one thread, does it also group work that is being done > on another thread until you end the transaction?

[sqlite] newbie command line question sqlite3

2008-08-21 Thread jojobo
hello. I am not a coder so I aplogise for my naivety. I am using TRAC with a sqlite db called trac.db I have the commanline sqlite3.exe in the same folder as the .db and need to now manipulate the db to manually remove a corrupt record. Once opened sqlite2 shows: sqlite> I need to know the

Re: [sqlite] newbie command line question sqlite3

2008-08-21 Thread Denis Crowther
Hi, Try DELETE FROM ticket WHERE ticket.FIELDNAME =102 Regards Denis On 08/22/2008 09:02 AM, jojobo wrote: > hello. > I am not a coder so I aplogise for my naivety. I am using TRAC with a sqlite > db called trac.db > > I have the commanline sqlite3.exe in the same folder as the .db and need

Re: [sqlite] Reducing SQLite Memory footprint(!)

2008-08-21 Thread Dennis Cote
Nicolas Williams wrote: > > I thought the DB was 9MB; forgive me for wasting your time then. If > it's 17.4MB then the memory usage seems a lot more reasonable. Daniel, the OP's, database is 9 MB. I don't have his database file, but I do have his test code. I used a database of my own that is

Re: [sqlite] Reducing SQLite Memory footprint(!)

2008-08-21 Thread Brown, Daniel
Ok so after reading your feedback I tried: 1. "PRAGMA cache_size =10" no change in memory usage. 2. "PRAGMA page_size = 4096" no change in memory usage. I'm doing both those queries (in C++) after the 'sqlite3_open( ":memory:", _pDataBase );' in my test but before the database file is attached or

Re: [sqlite] Reducing SQLite Memory footprint(!)

2008-08-21 Thread Brown, Daniel
1. Is anyone else on the list using Visual Studio 2005? It would be handy to see if they got similar results with the test application or not, that would rule out the build environment to an extent. 2. And the other thing to try would be if anyone has a fairly meaty test database they don't

Re: [sqlite] Reducing SQLite Memory footprint(!)

2008-08-21 Thread D. Richard Hipp
On Aug 21, 2008, at 7:33 PM, Brown, Daniel wrote: > if anyone has a fairly meaty test > database they don't mind sharing that I could fling at my test > application... A project repository with "fossil" is a (meaty) SQLite database. You could (for example) go clone one of the various fossil

Re: [sqlite] sqlite3_close

2008-08-21 Thread Joanne Pham
Hi Igor, I used SQLite versio n 3.5.9. I read the SQLite online document and the suggession that we need to finalize all the prepare statement associated with database connection before closing the connection as below         while( (pStmt = sqlite3_next_stmt(pDb, 0))!=0 ){          

[sqlite] question about sqlite3_step

2008-08-21 Thread Dave Dyer
Is it ever possible for subsequent calls to sqlite_step to return either a different number of column values or a different set of columns? In other words, if I'm doing something based on the column names, can I check only the first step, and assume the rest are the same?

Re: [sqlite] Reducing SQLite Memory footprint(!)

2008-08-21 Thread Brown, Daniel
Thanks I'll try the fossil database tomorrow morning. I just tried creating a new test application using Visual Studios built in C++ wizards (not our proprietary system) using Dennis's test application source code with a copy of the 3.6.1 amalgamation and my test database. I'm still seeing a

Re: [sqlite] question about sqlite3_step

2008-08-21 Thread Igor Tandetnik
"Dave Dyer" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Is it ever possible for subsequent calls to sqlite_step > to return either a different number of column values or > a different set of columns? No. The set of columns is fixed at the time you call sqlite3_prepare[_v2]. >

Re: [sqlite] newbie command line question sqlite3

2008-08-21 Thread Alexandre Courbot
> I have the commanline sqlite3.exe in the same folder as the .db and need to > now manipulate the db to manually remove a corrupt record. > I tried: .tables to show trables but nothing happens Don't forget to run sqlite3.exec followed by the name of your database file. Otherwise you will be

[sqlite] how to check whether the database file opend or closed?

2008-08-21 Thread kriscbe
hi all, once the database file is opened is some other function how to check the db file is opened or not opened? is there any function like "isopen()" in sqlite??? thanks kris -- View this message in context: