Re: [sqlite] Sqlite with MFC SDI
SQLite as an application file format is one of the suggested uses for SQLite at http://www.sqlite.org/whentouse.html. I've done this (though using Tcl/Tk as the programming language, not C++) and it has worked *very* well. I use it as the native format in BitPim. BitPim is written in Python. The original versions of BitPim use Python dictionaries (known as hash tables or maps in other languages) with a fairly complex data structure (the values can include lists of other hash tables). These were dumped as is to plain text files on the disk. It was fairly easy to write some code that can convert a complex Python dictionary into various tables, only adding new rows if values have changed. That means the rest of the code continues to operate on Python dictionaries, while one module dumps them into or out of the SQLite database instead of plain text files. The one thing I don't store in the database is images and ringtones. They average 8 to 50KB. I just use the filesystem for that. They would store perfectly in a SQLite database as a blob, but there doesn't seem to be any point since users typically won't care about old values as they would with phonebook and calendar entries. One *really* important feature that SQLite gives me is the ability to safely run multiple copies of my program. I frequently log into the same computer concurrently in two different ways. For example I may be logged in on the console, and also via network display (VNC for Linux, Terminal Services for Windows) and nothing winds me up more than programs that refuse to have two instances even if they are on different displays. SQLite lets you safely access the data from as many concurrent instances of your program as you want. Roger
Re: [sqlite] debugging a locking problem
GOT IT!! Thanks for responding! Its embarrassing but hopefully others can learn from my mistakes. It turns out I was not ALWAYS calling finalize. One little "return nil;" snuck into a case statement (see below). Bad form! sqlite_prepare (...); ... switch (type) { case SQLITE_INTEGER: value = [NSNumber numberWithInt:(sqlite3_column_int (stmt, 0))]; break; case SQLITE_FLOAT: value = [NSNumber numberWithDouble:(sqlite3_column_double (stmt, 0))]; break; case SQLITE_TEXT: value = [NSString stringWithUTF8String:(sqlite3_column_text (stmt, 0))]; break; case SQLITE_BLOB: case SQLITE_NULL: return nil; default: value = [super valueForKey:key]; break; } ... sqlite3_finalize(...); Thanks again for your pointers; they did help me focus in on the problem. On Jan 30, 2005, at 9:06 PM, Ulrik Petersen wrote: Jason Jobe wrote: I did find some where I wasn't doing that but I think I got them all. Sometimes I use sqlite3_exec; other times, when I need to get the rows I use sqlite3_step (with the finalize). Then perhaps you are calling sqlite3_exec in between an sqlite3_step and its corresponding sqlite3_finalize? HTH Ulrik P. PS: Please use bottom-posting, i.e., writing replies after replies, not before. It helps when one wants to follow the thread of the conversation. Should I wrap either or both of these in a transaction? I've also tried it backed by a file as well as the in-memory version; same results. On Jan 30, 2005, at 8:10 PM, D. Richard Hipp wrote: On Sun, 2005-01-30 at 19:56 -0500, Jason Jobe wrote: Hey out there. I'm having a dickens of a time trying to debug a locking issue. I thought I was doing something simple enough; accessing a database from within one process with no threading. 2005-01-30 19:28:10.736[5716] sqlite:ERROR database table is locked Trying again I get 2005-01-30 19:28:10.737[5716] sqlite:ERROR cannot commit transaction - SQL statements in progress I can't figure why the db thinks it should be locked. Any pointers would be most appreciated. Did you remember to sqlite3_finalize() statements that you were finished with? -- D. Richard Hipp <[EMAIL PROTECTED]> - jason [EMAIL PROTECTED] - jason [EMAIL PROTECTED]
Re: [sqlite] debugging a locking problem
Jason Jobe wrote: I did find some where I wasn't doing that but I think I got them all. Sometimes I use sqlite3_exec; other times, when I need to get the rows I use sqlite3_step (with the finalize). Then perhaps you are calling sqlite3_exec in between an sqlite3_step and its corresponding sqlite3_finalize? HTH Ulrik P. PS: Please use bottom-posting, i.e., writing replies after replies, not before. It helps when one wants to follow the thread of the conversation. Should I wrap either or both of these in a transaction? I've also tried it backed by a file as well as the in-memory version; same results. On Jan 30, 2005, at 8:10 PM, D. Richard Hipp wrote: On Sun, 2005-01-30 at 19:56 -0500, Jason Jobe wrote: Hey out there. I'm having a dickens of a time trying to debug a locking issue. I thought I was doing something simple enough; accessing a database from within one process with no threading. 2005-01-30 19:28:10.736[5716] sqlite:ERROR database table is locked Trying again I get 2005-01-30 19:28:10.737[5716] sqlite:ERROR cannot commit transaction - SQL statements in progress I can't figure why the db thinks it should be locked. Any pointers would be most appreciated. Did you remember to sqlite3_finalize() statements that you were finished with? -- D. Richard Hipp <[EMAIL PROTECTED]> - jason [EMAIL PROTECTED]
Re: [sqlite] debugging a locking problem
I did find some where I wasn't doing that but I think I got them all. Sometimes I use sqlite3_exec; other times, when I need to get the rows I use sqlite3_step (with the finalize). Should I wrap either or both of these in a transaction? I've also tried it backed by a file as well as the in-memory version; same results. On Jan 30, 2005, at 8:10 PM, D. Richard Hipp wrote: On Sun, 2005-01-30 at 19:56 -0500, Jason Jobe wrote: Hey out there. I'm having a dickens of a time trying to debug a locking issue. I thought I was doing something simple enough; accessing a database from within one process with no threading. 2005-01-30 19:28:10.736[5716] sqlite:ERROR database table is locked Trying again I get 2005-01-30 19:28:10.737[5716] sqlite:ERROR cannot commit transaction - SQL statements in progress I can't figure why the db thinks it should be locked. Any pointers would be most appreciated. Did you remember to sqlite3_finalize() statements that you were finished with? -- D. Richard Hipp <[EMAIL PROTECTED]> - jason [EMAIL PROTECTED]
Re: [sqlite] debugging a locking problem
On Sun, 2005-01-30 at 19:56 -0500, Jason Jobe wrote: > Hey out there. > > I'm having a dickens of a time trying to debug a locking issue. I > thought I was doing something simple enough; accessing a database from > within one process with no threading. > > 2005-01-30 19:28:10.736[5716] sqlite:ERROR database table is locked > > Trying again I get > > 2005-01-30 19:28:10.737[5716] sqlite:ERROR cannot commit transaction - > SQL statements in progress > > I can't figure why the db thinks it should be locked. > > Any pointers would be most appreciated. > Did you remember to sqlite3_finalize() statements that you were finished with? -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] debugging a locking problem
Hey out there. I'm having a dickens of a time trying to debug a locking issue. I thought I was doing something simple enough; accessing a database from within one process with no threading. 2005-01-30 19:28:10.736[5716] sqlite:ERROR database table is locked Trying again I get 2005-01-30 19:28:10.737[5716] sqlite:ERROR cannot commit transaction - SQL statements in progress I can't figure why the db thinks it should be locked. Any pointers would be most appreciated. thanks, - jason [EMAIL PROTECTED]
[sqlite] SQLite 3 kicks butt!!!
small comment..this is so awesome!!! Accessing a db through C..(new to me..I do Oracle Forms & VB devel on Windows @ work).. ok..I'll go back to my linux box to continue playing! -Alex
Re: [sqlite] compiling the demo example
You are THE MAN That worked. thank you VERY MUCH!! I cant wait to incorporate this into code. Thanks again to all who replied :) -Alex On Sun, 2005-01-30 at 23:21 +0100, Ulrik Petersen wrote: > > > man ldconfig
Re: [sqlite] compiling the demo example
Alex Bartonek wrote: LOL.. ok I'm getting farther... the problem was in my makefile.. I can actually create a executable (SuSE 9.2) but when I run it I get: ~/workspace/test> ./a.out ./a.out: error while loading shared libraries: libsqlite3.so.0: cannot open shared object file: No such file or directory the library is located in /usr/local/lib .. how would I make this work? man ldconfig
Re: [sqlite] Sqlite with MFC SDI
Hi Ming, [EMAIL PROTECTED] wrote: Hi All, I am using Sqlite 3.1.0 with a MFC SDI project. I want Sqlite database to be my file format of SDI, which I can open, close and save through (File) menu. Does anyone have experience with this and know how to do it? It sounds like you may get more help from an MFC forum (do they exist still?) than from an SQLite mailinglist. It's been four years since I've done any serious MFC programming, so if my answers seem a bit vague, you now know why. That said, here are some pointers: 1) You need to derive your own document class from CDocument. On this class, override the methods with names such as OnSave, OnFileNew, OnFileOpen, OnClose. In these methods, you can do your SQLite magic. 2) You need to figure out a mapping from your application data model into the relational database model, and back again. 3) Jeff Prosise is your friend. If you are serious about using MFC, then I can highly recommend getting a copy of his book, "Programming Windows with MFC". It taught me everything I knew about MFC, and was well worth the price -- it paid for itself several times over in terms of productivity. 4) Are you sure you want to program with MFC? MFC is, so far as I know, an outdated, non-supported technology from 1998. If you want a modern application framework, I can recoomend the free, Open Source WxWidgets: http://www.wxwidgets.org Porting an MFC application over to WxWidgets isn't that difficult, as the frameworks are very similar. Except that WxWidgets is richer, clearner, and more modern. And cross-platform, too. HTH Ulrik P. -- Ulrik Petersen, Denmark
Re: [sqlite] compiling the demo example
LOL.. ok I'm getting farther... the problem was in my makefile.. I can actually create a executable (SuSE 9.2) but when I run it I get: ~/workspace/test> ./a.out ./a.out: error while loading shared libraries: libsqlite3.so.0: cannot open shared object file: No such file or directory the library is located in /usr/local/lib .. how would I make this work?
Re: [sqlite] Sqlite with MFC SDI
On Sun, 2005-01-30 at 16:49 -0400, [EMAIL PROTECTED] wrote: > I am using Sqlite 3.1.0 with a MFC SDI project. I want Sqlite database > to be my file format of SDI, which I can open, close and save through > (File) menu. Does anyone have experience with this and know how to do it? > SQLite as an application file format is one of the suggested uses for SQLite at http://www.sqlite.org/whentouse.html. I've done this (though using Tcl/Tk as the programming language, not C++) and it has worked *very* well. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] AUTOINCREMENT of a not PRIMARY KEY
On Sat, 29 Jan 2005 09:56:03 -0800, Will Leshner <[EMAIL PROTECTED]> wrote: On Sat, 29 Jan 2005 12:07:31 +0100, Serge Ratke <[EMAIL PROTECTED]> wrote: [...] Couldn't you make 'id' the primary key and then create a UNIQUE index for parent_id,name? I think, basically, PRIMARY KEY(parent_id, name) is just creating such an index anyway. Thank you for your suggestion/solution. Exactly what i was searching for :) -- Serge Ratke
[sqlite] Sqlite with MFC SDI
Hi All, I am using Sqlite 3.1.0 with a MFC SDI project. I want Sqlite database to be my file format of SDI, which I can open, close and save through (File) menu. Does anyone have experience with this and know how to do it? Thank you in advance. Ming
Re: [sqlite] Memory usage (3.1.0 alpha)
Hi Clive, To: sqlite-users@sqlite.org cc:(bcc: clive/Emultek) Subject: Re: [sqlite] Memory usage (3.1.0 alpha) Hi Clive, [EMAIL PROTECTED] wrote: I am benchmarking sqlite3 as a potential database for Windows and embedded applications. I am running the following code in a Rapid development environment that calls the equivalent sqlite3 functions in a Window's DLL that I built from the release . I am seeing that memory usage goes up and up with every loop, until Windows runs out of virtual memory. Am I doing something wrong? while(true) SQL exec: 'BEGIN TRANSACTION'; for from 1 to 1000 step 1 SQL query: 'INSERT INTO Contacts values(''aaa'',''bbb'',''4'')'; SQL exec: 'COMMIT TRANSACTION'; It looks like you've wrapped it in some sort of Visual Basic. Is that true? If you are using the sqlite3_prepare/sqlite3_step/sqlite3_finalize API, yhe behavior you experience may be because you don't call sqlite3_finalize. Do you use that API? HTH Ulrik P. [EMAIL PROTECTED] wrote: The environment I am using is RapidPlus. It makes calls directly to the DLL. I changed the sqlite3 functions just to return in order to eliminate the possibility of it being a problem with the environment, and there was no memory loss. Sorry, I don't understand what you mean. Have you changed the SQLite3 code at all? Since I am using sqlite3_exec I do not think I need to use sqlite3_finalize. Is that correct? That is correct. Perhaps the normal behaviour of sqlite3 is to use system memory until there is non left? No, that is not the case. I cannot find a #define that specifies how many database pages are cached in memory. It is not a #define, it's PRAGMA: http://www.sqlite.org/pragma.html Search the page for "cache_size" and "default_cache_size". The behavior you experience would be exhibited if: 1) The sqlite3_exec function returned an error, and you did not call sqlite3_free on the error message. (See http://www.sqlite.org/capi3ref.html#sqlite3_exec ) 2) You sqlite3_open'ed a new connection every time without sqlite3_close'ing it. That's all I can think of right now. HTH Ulrik