[sqlite] sqlite3_next_stmt in SQLite 3.5.9
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 ){
sqlite3_finalize(pStmt);
}
sqlSt= sqlite3_close(pDb);
but the codes didn't return the syntax for sqlite3_next_stmt. Is
sqlite3_next_stmt is valid command in SQLite 3.5.9
Thanks
JP
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to check whether the database file opend or closed?
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: http://www.nabble.com/how-to-check-whether-the-database-file-opend-or-closed--tp19101495p19101495.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] newbie command line question sqlite3
> 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 working on an empty database and won't be able to modify your tables. Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] question about sqlite3_step
"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]. > 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? You can check column names after prepare, without even calling step first. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
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 7645 KB database file turn into 22735 KB with a high of 25138 KB at runtime. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Thursday, August 21, 2008 5:24 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) 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 projects out there and use that as your test database. First download a pre- compiled fossil binary: http://www.fossil-scm.org/download.html Then clone a repository: http://www.sqlite.org/experimental (~5MB) http://www.sqlite.org/docsrc (~2MB) http://www.fossil-scm.org/index.html (~6MB) D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] question about sqlite3_step
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? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_close
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 ){
sqlite3_finalize(pStmt);
}
sqlSt= sqlite3_close(pDb);
but the codes didn't return the syntax for sqlite3_next_stmt. Is
sqlite3_next_stmt is valid command in SQLite 3.5.9
Thanks
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 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 program, you seem to store the database handle in the same
global variable for each openDb call. If you call openDb twice, the
second handle overwrites the first, so now there's no way to call
sqlite3_close on the first handle. Hence the leak. The situation is not
much different from this:
int* p = new int;
p = new int;
delete p;
// the first allocation leaks - the pointer to it is lost.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
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 projects out there and use that as your test database. First download a pre- compiled fossil binary: http://www.fossil-scm.org/download.html Then clone a repository: http://www.sqlite.org/experimental (~5MB) http://www.sqlite.org/docsrc (~2MB) http://www.fossil-scm.org/index.html (~6MB) D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
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 mind sharing that I could fling at my test application to try and rule out the data? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Thursday, August 21, 2008 4:23 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) 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 a similar size along with his test code to do my tests. In my tests sqlite behaves as expected. Daniel is seeing much higher memory usage reported from sqlite itself using the same version of sqlite, the same test code, and the same OS. I see a memory usage of about 18 MB for a database copied from a file that is 17.4 MB (1K pages) or 12.2 MB (4K pages). I get an expansion factor of 1.03 or 1.48. Daniel is seeing memory usage of 22.2 MB for a database copied from a file that is 9 MB. Daniel gets an expansion factor of 2.47. This seems high. Since the major difference seems to be the database file we are copying, I would like to repeat his test with his database file if possible. If not possible (perhaps the data is proprietary or personal), then it might make sense to see what factors effect this memory expansion ratio. I was surprised by the magnitude of the change in the size of my database file by simply changing the page size. I also tried to change the page size used for the memory database, but that had no effect (Which is not what I expected, perhaps the page size pragma is ignored for memory databases). Changing the cache size reduced the highwater memory requirement, but didn't change the memory required to hold the database after the copy was completed. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
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:", &m_pDataBase );' in my test but before the database file is attached or anything is copied or created. The rebuilt database file is 7.46 MB and the memory usage I'm seeing is now: 22.20 MB with a high water of 24.55 MB as reported by the sqlite_memory_* functions. I'm not using the amalgamation version of the pre-processed source; I'm using the individual source files of 3.6.1 on Windows XP with Visual Studio 2005. I'm afraid I can't give you a copy of the test database as it's a drop from a live product, could it be using the third party SQLite Analyzer application to import from excel be the issue? Are there any other tools from importing from a .xls to a SQLite database (converting each sheet to a table)? I just tried a "vacuum" after I detach the database from file and that didn't reduce the memory usage either but it did double the high water mark which after reading the documentation sounds about right for making a temporary copy. How do I rebuild a database file for another page size or did the pragma do that already? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Nicolas Williams Sent: Thursday, August 21, 2008 3:13 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) 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 slightly higher highwater mark of 26418 KB used. Again this > was all done with the default page cache size. > > Note, I also tried to vacuum the original file to see if there were a > lot of unused pages in the original 17.5 MB file. After the vacuum it > was reduced to only 17.4 MB, so there were very few free pages in the > database. This database just fits much better on the larger 4K pages. 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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
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 a similar size along with his test code to do my tests. In my tests sqlite behaves as expected. Daniel is seeing much higher memory usage reported from sqlite itself using the same version of sqlite, the same test code, and the same OS. I see a memory usage of about 18 MB for a database copied from a file that is 17.4 MB (1K pages) or 12.2 MB (4K pages). I get an expansion factor of 1.03 or 1.48. Daniel is seeing memory usage of 22.2 MB for a database copied from a file that is 9 MB. Daniel gets an expansion factor of 2.47. This seems high. Since the major difference seems to be the database file we are copying, I would like to repeat his test with his database file if possible. If not possible (perhaps the data is proprietary or personal), then it might make sense to see what factors effect this memory expansion ratio. I was surprised by the magnitude of the change in the size of my database file by simply changing the page size. I also tried to change the page size used for the memory database, but that had no effect (Which is not what I expected, perhaps the page size pragma is ignored for memory databases). Changing the cache size reduced the highwater memory requirement, but didn't change the memory required to hold the database after the copy was completed. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] newbie command line question sqlite3
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 to > now manipulate the db to manually remove a corrupt record. > > Once opened sqlite2 shows: > > sqlite> > > I need to know the commnad / string / line to remove remoce the effected > ticket (record) from the ticket table. > > Can someone please help me with the terminogoly? > > I tried: .tables to show trables but nothing happens > I tried DELETE FROM ticket WHERE 102 > > I think I am missing the boat here...is there an easy way to show the tables > / database as text so I can manually remove problem? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] newbie command line question sqlite3
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 commnad / string / line to remove remoce the effected ticket (record) from the ticket table. Can someone please help me with the terminogoly? I tried: .tables to show trables but nothing happens I tried DELETE FROM ticket WHERE 102 I think I am missing the boat here...is there an easy way to show the tables / database as text so I can manually remove problem? -- View this message in context: http://www.nabble.com/newbie-command-line-question-sqlite3-tp19098632p19098632.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transactions and Threads
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? Yes. Transactions are per-connection, not per-thread. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Transactions and Threads
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 can have it's own transaction pending on the database? Cheers, Shawn Wilsher ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
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 slightly higher highwater mark of 26418 KB used. Again this > was all done with the default page cache size. > > Note, I also tried to vacuum the original file to see if there were a > lot of unused pages in the original 17.5 MB file. After the vacuum it > was reduced to only 17.4 MB, so there were very few free pages in the > database. This database just fits much better on the larger 4K pages. 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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
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, but if the cache is > cold then a full table scan just might fill the cache. > > In this case we have full table scans in the process of copying on-disk > tables to a memory DB. And it looks like the cache is cold in this > case. > > The default cache size is 2000 pages (there's a way to persist a cache > size). The default page size is 1024 bytes, but it's hard to tell > exactly what it is in the OP's case. > > So it looks like the cache size should be ~20MB. And the DB size is > ~9MB. The cache is plenty large enough to hold a copy of the on-disk > DB. > > So we have: 9MB will be consumed in the page cache, and 9MB will be > consumed by the memory DB (I assume memory DB pages aren't cached). Add > in the overhead per-page, which seems to be .5KB, and you have > > 18MB + 9MB / 2 = 22.5MB > > That seems close to what the OP claimed. > But my testing using the same code, but a different database file, uses only slightly more memory than required to hold the database file. This code uses the default cache size of 2000 pages. 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 slightly higher highwater mark of 26418 KB used. Again this was all done with the default page cache size. Note, I also tried to vacuum the original file to see if there were a lot of unused pages in the original 17.5 MB file. After the vacuum it was reduced to only 17.4 MB, so there were very few free pages in the database. This database just fits much better on the larger 4K pages. Changing the cache size from the default 2000 to a much smaller 100 pages reduced the highwater mark to 18544 KB, which is only slightly higher than the 18102 KB of memory used after the table is built. The actual memory used is exactly the same (as expected since it is storing the same tables). Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .import with .separator and quoted strings
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 source is available, you may want to modify it as needed. I know you prefer not to pre-process your input file, so enhancing the source may be your best option. In my case, *all* the fields were quoted in the input file, and so I replaced occurances of: ","(quote comma quote) with a vertical bar | and trimmed the quotes from the beginning and end of each line. You can even perform this using an sqlite3 script itself if you don't mind a bit of madness. -Set the separator to something very odd such as '@$%' -Import the original text to a temporary table with a single field to contain the entire row. -UPDATE each row, using REPLACE() to change "," to | (perhaps after first checking for any actual virgules in the original data). -Use SUBSTR() to remove the two remaining quotes at each end of the line. -Set the separator to | and export to a temp file. Delete the temporary table and .import the data into your real table. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wilson, Ron P Sent: Thursday, August 21, 2008 4:37 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] .import with .separator and quoted strings 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 integer, name); .mode csv .import sqlite> csvtest.csv names csvtest.csv line 1: expected 2 columns of data but found 3 sqlite> .quit Clearly it is parsing the comma in the name column as a record delimiter. Is there a mode that causes the .import command to honor quoted entries? RW sqlite>select level from sqlGuruOMeter where name="Ron Wilson"; 2 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wilson, Ron P Sent: Tuesday, August 19, 2008 4:49 PM To: sqlite-users@sqlite.org Subject: [sqlite] .import with .separator and quoted strings I'm trying to import a table using the command line tool. sqlite> .separator , sqlite> .import export.csv library export.csv line 1: expected 53 columns of data but found 77 sqlite> .mode csv sqlite> .import export.csv library export.csv line 1: expected 53 columns of data but found 77 All entries are quoted strings, but some of them have commas within the strings. It appears that SQLite is ignoring the string quoting and taking all commas literally. Is this intended? The same import works fine in Excel with 53 columns resulting. I have also tried tab delimited and apparently some of the strings in this dataset also contain tabs. sqlite> .mode tabs sqlite> .import export.txt library export.txt line 162: expected 53 columns of data but found 55 I don't control the data source, and I would really like to avoid pre-munging the data. RW sqlite>select level from sqlGuruOMeter where name="Ron Wilson"; 2 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_close
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 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 program, you seem to store the database handle in the same global variable for each openDb call. If you call openDb twice, the second handle overwrites the first, so now there's no way to call sqlite3_close on the first handle. Hence the leak. The situation is not much different from this: int* p = new int; p = new int; delete p; // the first allocation leaks - the pointer to it is lost. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
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 created. Daniel, can you run a "pragam page_size;" query on youyr database and let us know the results? Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_close
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 program, you seem to store the database handle in the same global variable for each openDb call. If you call openDb twice, the second handle overwrites the first, so now there's no way to call sqlite3_close on the first handle. Hence the leak. The situation is not much different from this: int* p = new int; p = new int; delete p; // the first allocation leaks - the pointer to it is lost. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_close
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 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 openDb) for > releasing the memory which is used by previous > sqlite3_open_v2(openDb). Otherwise the protential memory leak will be > in the codes. > Your response is greatly appreciated. So, what's your question? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .import with .separator and quoted strings
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 integer, name); sqlite> .mode csv sqlite> .import csvtest.csv names csvtest.csv line 1: expected 2 columns of data but found 3 sqlite> .quit Clearly it is parsing the comma in the name column as a record delimiter. Is there a mode that causes the .import command to honor quoted entries? RW sqlite>select level from sqlGuruOMeter where name="Ron Wilson"; 2 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wilson, Ron P Sent: Tuesday, August 19, 2008 4:49 PM To: sqlite-users@sqlite.org Subject: [sqlite] .import with .separator and quoted strings I'm trying to import a table using the command line tool. sqlite> .separator , sqlite> .import export.csv library export.csv line 1: expected 53 columns of data but found 77 sqlite> .mode csv sqlite> .import export.csv library export.csv line 1: expected 53 columns of data but found 77 All entries are quoted strings, but some of them have commas within the strings. It appears that SQLite is ignoring the string quoting and taking all commas literally. Is this intended? The same import works fine in Excel with 53 columns resulting. I have also tried tab delimited and apparently some of the strings in this dataset also contain tabs. sqlite> .mode tabs sqlite> .import export.txt library export.txt line 162: expected 53 columns of data but found 55 I don't control the data source, and I would really like to avoid pre-munging the data. RW sqlite>select level from sqlGuruOMeter where name="Ron Wilson"; 2 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Detecting other connections to DB?
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 >> 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 into a couple places where that won't work. >> >> Any help would be greatly appreciated! >> >> doug >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> ML> The database connection object is handled by the SQLite database engine ML> and stored in memory allocated by the database engine. ML> SQLite does not use a client-server architecture but is completely ML> contained in a library that you link with your application, thus ML> SQLite's functions run within your application process and the database ML> connection objects exist on a per-process basis. ML> Your question seems to suggest that your users might run multiple ML> instances of your application, potentially on separate machines. If that ML> holds true, there is no way to get a list of connections from SQLite ML> since those connection objects belong to separate processes potentially ML> on separate machines. You will have to use an IPC mechanism of your ML> choice to handle communication between instances of your app. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Detecting other connections to DB?
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 connections manually), but ran into a couple places where that won't work. Any help would be greatly appreciated! doug ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users The database connection object is handled by the SQLite database engine and stored in memory allocated by the database engine. SQLite does not use a client-server architecture but is completely contained in a library that you link with your application, thus SQLite's functions run within your application process and the database connection objects exist on a per-process basis. Your question seems to suggest that your users might run multiple instances of your application, potentially on separate machines. If that holds true, there is no way to get a list of connections from SQLite since those connection objects belong to separate processes potentially on separate machines. You will have to use an IPC mechanism of your choice to handle communication between instances of your app. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Detecting other connections to DB?
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 into a couple places where that won't work. You can create array for all connections and populate it by your application. Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_close
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 function, you've opened a connection but have lost the reference to it so you have no way to close it, thus you've created a memory leak. Is that what you want? Ugh, never mind, that was a big "I wasn't thinking" instance - shouldn't write stuff after 10 pm... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_close
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 connection but have lost the reference to it
so you have no way to close it, thus you've created a memory leak. Is
that what you want?
Joanne Pham wrote:
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
Subject: [sqlite] (no subject)
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 used by previous sqlite3_open_v2(openDb). Otherwise the
protential memory leak will be in the codes.
Your response is greatly appreciated.
JP
MonDb::openDb(const char *dbName){
int sqlSt;
const char* errMsg;
strcpy(name, dbName); //copy database name to the private name field
sqlSt = sqlite3_open_v2( name, &pDb, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, 0);
if(sqlSt != SQLITE_OK){
errMsg = sqlite3_errmsg(pDb);
// print out the error message
sqlite3_free((char*) errMsg);
return false;
}
/* Set database properties for better performance */
setDbProperties();
return true;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Multumesc,
Mihai Limbasan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_close
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 openDb) for > releasing the memory which is used by previous > sqlite3_open_v2(openDb). Otherwise the protential memory leak will be > in the codes. > Your response is greatly appreciated. So, what's your question? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_close
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
Subject: [sqlite] (no subject)
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 used by previous sqlite3_open_v2(openDb). Otherwise the
protential memory leak will be in the codes.
Your response is greatly appreciated.
JP
MonDb::openDb(const char *dbName){
int sqlSt;
const char* errMsg;
strcpy(name, dbName); //copy database name to the private name field
sqlSt = sqlite3_open_v2( name, &pDb, SQLITE_OPEN_READWRITE |
SQLITE_OPEN_CREATE, 0);
if(sqlSt != SQLITE_OK){
errMsg = sqlite3_errmsg(pDb);
// print out the error message
sqlite3_free((char*) errMsg);
return false;
}
/* Set database properties for better performance */
setDbProperties();
return true;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
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 used by previous sqlite3_open_v2(openDb). Otherwise the
protential memory leak will be in the codes.
Your response is greatly appreciated.
JP
MonDb::openDb(const char *dbName){
int sqlSt;
const char* errMsg;
strcpy(name, dbName); //copy database name to the private name field
sqlSt = sqlite3_open_v2( name, &pDb, SQLITE_OPEN_READWRITE |
SQLITE_OPEN_CREATE, 0);
if(sqlSt != SQLITE_OK){
errMsg = sqlite3_errmsg(pDb);
// print out the error message
sqlite3_free((char*) errMsg);
return false;
}
/* Set database properties for better performance */
setDbProperties();
return true;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Detecting other connections to DB?
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 manually), but ran into a couple places where that won't work. Any help would be greatly appreciated! doug ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
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 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, but if the cache is cold then a full table scan just might fill the cache. In this case we have full table scans in the process of copying on-disk tables to a memory DB. And it looks like the cache is cold in this case. The default cache size is 2000 pages (there's a way to persist a cache size). The default page size is 1024 bytes, but it's hard to tell exactly what it is in the OP's case. So it looks like the cache size should be ~20MB. And the DB size is ~9MB. The cache is plenty large enough to hold a copy of the on-disk DB. So we have: 9MB will be consumed in the page cache, and 9MB will be consumed by the memory DB (I assume memory DB pages aren't cached). Add in the overhead per-page, which seems to be .5KB, and you have 18MB + 9MB / 2 = 22.5MB That seems close to what the OP claimed. Perhaps to keep the memory footprint of SQLite3 low the OP should set the cache size way down during the copy-the-DB-into-memory part of the program using the cache_size pragma. I could be way off-track, but, try it and see. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite freestanding.
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 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Correct SQL name resolution on AS clauses in a SELECT?
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 ORDER BY a; b a 1 2 9 8 SELECT b AS a, a AS b FROM t1 ORDER BY a; a b 2 1 8 9 SELECT a, b AS a FROM t1 ORDER BY a; a a 1 2 9 8 SELECT a AS x, b AS x ORDER BY x; 201: A syntax error has occurred. SELECT a AS b, b AS a WHERE a=1; 201: A syntax error has occurred. SELECT a AS b, b AS a WHERE a=2; 201: A syntax error has occurred. SELECT a AS x, b AS x WHERE x=1; 201: A syntax error has occurred. This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Any equivalent to MSSQL's UPDATE..FROM.. clause ?
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 within the SET clause of the UPDATE statement, but that requires
duplicating the WHERE clause within the subselect which is a lot of extra
typing and I'm sure a lot of extra work for SQLite.
MSSQL:
UPDATE T1
SET
A = T2...,
B = T2...,
C = T2...,
FROM T1 INNER JOIN T2 ON
SQLite:
UPDATE T1
SET
A = (SELECT ... FROM T1 T1_Inner INNER JOIN T2 ON ... WHERE T1_Inner.RowID =
T1.RowID),
B = (SELECT ... FROM T1 T1_Inner INNER JOIN T2 ON ... WHERE T1_Inner.RowID =
T1.RowID),
C = (SELECT ... FROM T1 T1_Inner INNER JOIN T2 ON ... WHERE T1_Inner.RowID =
T1.RowID),
...
Here are samples of equivalent code in MSSQL and SQLite. Is there a way to
simplify the UPDATE statement in the SQLite code?
I'm not replacing the target row entirely, I don't think INSERT OR REPLACE
will work in this scenario.
Thanks,
Sam
--
-- MSSQL
--
CREATE TABLE #T1(
ID INTEGER PRIMARY KEY IDENTITY,
A VARCHAR(100),
B VARCHAR(100),
C VARCHAR(100));
CREATE TABLE #T2(
ID INTEGER PRIMARY KEY IDENTITY,
A VARCHAR(100),
B VARCHAR(100),
C VARCHAR(100));
INSERT INTO #T1 VALUES ('a1', 'b1', 'c1');
INSERT INTO #T1 VALUES (NULL, 'b2', 'c2');
INSERT INTO #T1 VALUES ('a3', NULL, 'c3');
INSERT INTO #T1 VALUES ('a4', 'b4', NULL);
INSERT INTO #T2 VALUES ('A1', 'B1', 'C1');
INSERT INTO #T2 VALUES ('A2', NULL, 'C2');
INSERT INTO #T2 VALUES ('A3', 'B3', NULL);
INSERT INTO #T2 VALUES (NULL, 'B4', 'C4');
SELECT * FROM #T1;
SELECT * FROM #T2;
UPDATE#T1
SETA = COALESCE(#T1.A, #T2.A),
B = COALESCE(#T1.B, #T2.B),
C = COALESCE(#T1.C, #T2.C)
FROM#T1 INNER JOIN #T2 ON #T1.ID = #T2.ID;
SELECT * FROM #T1;
DROP TABLE #T1;
DROP TABLE #T2;
--
-- SQLite
--
CREATE TEMP TABLE T1(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
A TEXT,
B TEXT,
C TEXT);
CREATE TEMP TABLE T2(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
A TEXT,
B TEXT,
C TEXT);
INSERT INTO T1 VALUES (NULL, 'a1', 'b1', 'c1');
INSERT INTO T1 VALUES (NULL, NULL, 'b2', 'c2');
INSERT INTO T1 VALUES (NULL, 'a3', NULL, 'c3');
INSERT INTO T1 VALUES (NULL, 'a4', 'b4', NULL);
INSERT INTO T2 VALUES (NULL, 'A1', 'B1', 'C1');
INSERT INTO T2 VALUES (NULL, 'A2', NULL, 'C2');
INSERT INTO T2 VALUES (NULL, 'A3', 'B3', NULL);
INSERT INTO T2 VALUES (NULL, NULL, 'B4', 'C4');
SELECT * FROM T1;
SELECT * FROM T2;
-- here's the ugly statement I'd like to simplfy
UPDATET1
SETA = (
SELECT COALESCE(InnerT1.A, T2.A)
FROM T1 InnerT1, T2
WHERE InnerT1.ID = T1.ID
AND T2.ID = T1.ID
),
B = (
SELECT COALESCE(InnerT1.B, T2.B)
FROM T1 InnerT1, T2
WHERE InnerT1.ID = T1.ID
AND T2.ID = T1.ID
),
C = (
SELECT COALESCE(InnerT1.C, T2.B)
FROM T1 InnerT1, T2
WHERE InnerT1.ID = T1.ID
AND T2.ID = T1.ID
)
;
SELECT * FROM T1;
DROP TABLE T1;
DROP TABLE T2;
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer in
the Washington D.C. Contact [EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
Just out of curiosity what happens if you call "PRAGMA page_size=4096" before running the import? On Thu, Aug 21, 2008 at 9:52 AM, Dennis Cote <[EMAIL PROTECTED]> wrote: > 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. >> > > No, I don't think any of my changes would have changed the behaviour of > your program (except for getting the correct memory values displayed). > >> I can see the memory being released when I close the SQLite database in >> the teardown stage of my test, so I'm fairly sure the memory is being >> used by SQLite and the built in memory profiling would seem to support >> that. I haven't had to make any changes locally to get the PC version >> of 3.6.1 compiling so I don't think that is the issue, could it be some >> sort of configuration or library issue? I'm building in Visual Studio >> 2005 SP1. >> > > Can you provide a copy of the database file you are using? If so they > usually compress quite well using a zip utility. > > Dennis Cote > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
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. > No, I don't think any of my changes would have changed the behaviour of your program (except for getting the correct memory values displayed). > I can see the memory being released when I close the SQLite database in > the teardown stage of my test, so I'm fairly sure the memory is being > used by SQLite and the built in memory profiling would seem to support > that. I haven't had to make any changes locally to get the PC version > of 3.6.1 compiling so I don't think that is the issue, could it be some > sort of configuration or library issue? I'm building in Visual Studio > 2005 SP1. > Can you provide a copy of the database file you are using? If so they usually compress quite well using a zip utility. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] inserting pixbuf in sqlite3 database
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 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Correct SQL name resolution on AS clauses in a SELECT?
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 |1 | |8 |9 | +--+--+ 2 rows in set (0.00 sec) mysql> SELECT a, b AS a FROM t1 ORDER BY a; ERROR 1052 (23000): Column 'a' in order clause is ambiguous mysql> SELECT a AS x, b AS x ORDER BY x; ERROR 1054 (42S22): Unknown column 'a' in 'field list' mysql> SELECT a AS b, b AS a WHERE a=1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE a=1' at line 1 mysql> SELECT a AS b, b AS a WHERE a=2; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE a=2' at line 1 mysql> SELECT a AS x, b AS x WHERE x=1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE x=1' at line 1 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use the ROWID of the last inserted row for FK insert into other tables?
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 table.
>
> This is the tables definition:
>
> CREATE TABLE TBL_OBJECTS (
> IDinteger PRIMARY KEY NOT NULL,
> DATA text,
> PATH text
> );
>
> CREATE TABLE TBL_TAGS (
> ID integer PRIMARY KEY NOT NULL,
> ID_TAG_TYPE integer NOT NULL,
> ID_OBJECTinteger NOT NULL,
> TAG_DATA text NOT NULL
> );
>
>
> My solution is:
>
> INSERT INTO TBL_OBJECTS
> (DATA,
> PATH)
> VALUES ('val1', 'val2');
>
> INSERT INTO TBL_TAGS
> (ID_TAG_TYPE,
> ID_OBJECT,
> TAG_DATA)
> VALUES (1, 2, 'a'); --doesn't count what value is inserted for ID_OBJECT
> as it will be changed in the next statement
>
> UPDATE TBL_TAGS SET ID_OBJECT = (SELECT MAX(ROWID) FROM TBL_OBJECTS)
> WHERE ROWID = (SELECT MAX(ROWID) FROM TBL_TAGS);
>
> but I am sure there is a less complex one that you can point out to me.
How about
INSERT INTO TBL_TAGS (ID_TAG_TYPE, TAG_DATA, ID_OBJECT)
VALUES (1, 'a', SELECT Max(ID) FROM TBL_OBJECTS)
>
> --
> View this message in context:
> http://www.nabble.com/How-to-use-the-ROWID-of-the-last-inserted-row-for-FK-insert-into-other-tables--tp19085514p19085514.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use the ROWID of the last inserted row for FK insert into other tables?
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 the ROWID
>> value from the precedent table.
>>
>> INSERT INTO TBL_OBJECTS
>> (DATA,
>> PATH)
>> VALUES ('val1', 'val2');
>>
>> INSERT INTO TBL_TAGS
>> (ID_TAG_TYPE,
>> ID_OBJECT,
>> TAG_DATA)
>> VALUES (1, 2, 'a'); --doesn't count what value is inserted for
>> ID_OBJECT as it will be changed in the next statement
>
> Why not just
>
> INSERT INTO TBL_TAGS
> (ID_TAG_TYPE,
> ID_OBJECT,
> TAG_DATA)
> VALUES (1, last_insert_rowid(), 'a');
>
> See http://sqlite.org/lang_corefunc.html
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
Thanks a lot Igor, didn't knew about the existence of last_insert_rowid()
function.
--
View this message in context:
http://www.nabble.com/How-to-use-the-ROWID-of-the-last-inserted-row-for-FK-insert-into-other-tables--tp19085514p19087337.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use the ROWID of the last inserted row for FK insert into other tables?
"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 precedent table.
>
> INSERT INTO TBL_OBJECTS
> (DATA,
> PATH)
> VALUES ('val1', 'val2');
>
> INSERT INTO TBL_TAGS
> (ID_TAG_TYPE,
> ID_OBJECT,
> TAG_DATA)
> VALUES (1, 2, 'a'); --doesn't count what value is inserted for
> ID_OBJECT as it will be changed in the next statement
Why not just
INSERT INTO TBL_TAGS
(ID_TAG_TYPE,
ID_OBJECT,
TAG_DATA)
VALUES (1, last_insert_rowid(), 'a');
See http://sqlite.org/lang_corefunc.html
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use the ROWID of the last inserted row for FK insert into other tables?
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 the ROWID value from the
> precedent table.
>
> This is the tables definition:
>
> CREATE TABLE TBL_OBJECTS (
> IDinteger PRIMARY KEY NOT NULL,
> DATA text,
> PATH text
> );
>
> CREATE TABLE TBL_TAGS (
> ID integer PRIMARY KEY NOT NULL,
> ID_TAG_TYPE integer NOT NULL,
> ID_OBJECTinteger NOT NULL,
> TAG_DATA text NOT NULL
> );
>
>
> My solution is:
>
> INSERT INTO TBL_OBJECTS
> (DATA,
> PATH)
> VALUES ('val1', 'val2');
>
> INSERT INTO TBL_TAGS
> (ID_TAG_TYPE,
> ID_OBJECT,
> TAG_DATA)
> VALUES (1, 2, 'a'); --doesn't count what value is inserted for ID_OBJECT
> as it will be changed in the next statement
>
> UPDATE TBL_TAGS SET ID_OBJECT = (SELECT MAX(ROWID) FROM TBL_OBJECTS)
> WHERE ROWID = (SELECT MAX(ROWID) FROM TBL_TAGS);
>
> but I am sure there is a less complex one that you can point out to me.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table Lock Error
"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 dropping a table. > What is causing of this? You are trying to drop a table while there's an active statement outstanding (one for which the last call was sqlite3_step). You need to reset or finalize all statements before you can change the schema. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Table Lock Error
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 confused by the tracing output. Sometimes it shows some zSql statements twice. I see inserts and selects two times. Even though my code is doing it once. Any help is appreciated. Thanks, -Alex ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to use the ROWID of the last inserted row for FK insert into other tables?
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 TBL_OBJECTS (
IDinteger PRIMARY KEY NOT NULL,
DATA text,
PATH text
);
CREATE TABLE TBL_TAGS (
ID integer PRIMARY KEY NOT NULL,
ID_TAG_TYPE integer NOT NULL,
ID_OBJECTinteger NOT NULL,
TAG_DATA text NOT NULL
);
My solution is:
INSERT INTO TBL_OBJECTS
(DATA,
PATH)
VALUES ('val1', 'val2');
INSERT INTO TBL_TAGS
(ID_TAG_TYPE,
ID_OBJECT,
TAG_DATA)
VALUES (1, 2, 'a'); --doesn't count what value is inserted for ID_OBJECT
as it will be changed in the next statement
UPDATE TBL_TAGS SET ID_OBJECT = (SELECT MAX(ROWID) FROM TBL_OBJECTS)
WHERE ROWID = (SELECT MAX(ROWID) FROM TBL_TAGS);
but I am sure there is a less complex one that you can point out to me.
--
View this message in context:
http://www.nabble.com/How-to-use-the-ROWID-of-the-last-inserted-row-for-FK-insert-into-other-tables--tp19085514p19085514.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
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 mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
