[sqlite] Extraction SQL query from a sqlite3_stmt

Hi all, Once a sqlite3_stmt has been prepared and variables bound, is there some way of extracting the SQL query? I have a query that runs fine on command line and if I prepare and run it without variables. So something in the way I am binding the variables is returns zero rows. I would like to

Re: [sqlite] Commit frequency and performance

On Wed, Feb 04, 2009 at 10:10:15PM -0700, Gerry Snyder scratched on the wall: > pri...@gmail.com wrote: > > Yes, there are 3 indexes being created > > > > I'll post again after figuring out which of the changes improved the > > performance. Thanks for the clues! > > Since you are able to

Re: [sqlite] Commit frequency and performance

pri...@gmail.com wrote: > Yes, there are 3 indexes being created > > I'll post again after figuring out which of the changes improved the > performance. Thanks for the clues! > Since you are able to do some experimenting, try entering the data without the indices, and then create them.

Re: [sqlite] Commit frequency and performance

Yes, there are 3 indexes being created. For 2 indexes, the data is in order. For the 3rd index, the data is not in order. I can understand how that will cause extra seeking. It's a good point. I just ran another test after downloading the latest SQLite version, changed the page size to 4K on

Re: [sqlite] Limits in SQLite

On Feb 4, 2009, at 2:58 PM, Ha Le wrote: > The question I have is how/by how much one must adjust these limits > (in a rather optimal way) for a given, specific real machine. > The limits in SQLite are carefully chose to work well across a wide range of applications and machines. If you

[sqlite] About Alter table add column

Hi all, I have a question about alter table. for example, I create a table person(ID integer primary key, name text, addr text), Then I want to upgrade the table and add some columns, "alter table person add age integer", I want to know the changed table structure will have a big effect on the

Re: [sqlite] Commit frequency and performance

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 pri...@gmail.com wrote: > I still can't understand how 1 long transaction (no syncs) can be > slower than periodic 5-second commits. Any ideas? I'd suggest posting a link to your code/representative data so that others can try to reproduce it.

Re: [sqlite] Commit frequency and performance

Thanks Roger. There is no other activity on this machine. I have run vmstat 5 during all of the different cases. For plain sequential files, Linux is able to almost completely overlap I/O with CPU, so the real time is close to user+sys time. With SQLite, there are often periods where the CPU

Re: [sqlite] Commit frequency and performance

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jim Wilcoxson wrote: > Can anyone shed light on why building a database inside a single > transaction would be slower than periodically commiting? If you look at the user and sys times then each approach is almost the same. The real times differing

Re: [sqlite] Saving and loading SQLite pages from a buffer

On Feb 4, 2009, at 6:38 PM, Brown, Daniel wrote: > Hi Richard, > > Thanks for the advice: I will take a look into a barebones VFS > implementation for backup purposes. I assume it won't need the usual > locking functionality that normal VFS implementations require: pretty > much just read,

[sqlite] Commit frequency and performance

I am creating an SQLite database via Python, and trying to understand some performance issues. This application does 3.8M inserts. Most inserts are to a main database that ends up being around 293MB. Around 740K of the inserts with larger data records are to 25 related databases of around

Re: [sqlite] Saving and loading SQLite pages from a buffer

Hi Richard, Thanks for the advice: I will take a look into a barebones VFS implementation for backup purposes. I assume it won't need the usual locking functionality that normal VFS implementations require: pretty much just read, write, open and close? What is the ETA for 3.6.11? Daniel >

Re: [sqlite] Saving and loading SQLite pages from a buffer

On Feb 4, 2009, at 6:09 PM, Brown, Daniel wrote: > > I currently use SQLite by opening ":memory:" and then copying the > tables > I want from read only storage into ":memory:" via an attached read > only > database, which I drop after copying is complete. My issue is saving > and loading the

Re: [sqlite] Saving and loading SQLite pages from a buffer

-Original Message- >The hot backup API has not yet been released. Look for version 3.6.11. I will look forward to seeing that release! Is there anything I can do to help with the development? >If you open your database using the special name ":memory:" then it is >held entirely in

Re: [sqlite] Saving and loading SQLite pages from a buffer

> That depends. If the change was made using the same database > connection that was passed into sqlite3_backup_init(), then only those > pages that changed are recopied. However, if an independent database > connection made the change, then the backup process has no way of > knowing

Re: [sqlite] Saving and loading SQLite pages from a buffer

On Feb 4, 2009, at 5:48 PM, Nicolas Williams wrote: > On Wed, Feb 04, 2009 at 05:19:13PM -0500, D. Richard Hipp wrote: >> http://www.sqlite.org/draft/c3ref/backup_finish.html > > Does this work at the page level? Or can one have pre-created the > destination DB with a different page size than

Re: [sqlite] Saving and loading SQLite pages from a buffer

On Feb 4, 2009, at 5:51 PM, Eric Minbiole wrote: > > "If the source database is modified [...] then the backup will be > transparently restarted by the next call to sqlite3_backup_step()" > > Does this mean that the backup is restarted *to the beginning*, or > is it > able to re-copy only those

Re: [sqlite] Saving and loading SQLite pages from a buffer

On Wed, Feb 04, 2009 at 05:19:13PM -0500, D. Richard Hipp wrote: > http://www.sqlite.org/draft/c3ref/backup_finish.html Does this work at the page level? Or can one have pre-created the destination DB with a different page size than the source DB? Nico --

Re: [sqlite] Saving and loading SQLite pages from a buffer

On Feb 4, 2009, at 5:48 PM, Brown, Daniel wrote: > > What version(s) of SQLite contain this experimental functionality? > I am > using version 3.6.10 non-amalgamation but I can't find the functions > mentioned on that page. If I understand this backup API correctly > then > its

Re: [sqlite] Saving and loading SQLite pages from a buffer

> http://www.sqlite.org/draft/c3ref/backup_finish.html This is excellent! I've been looking for a clean way to perform live backups. This (draft) API looks perfect. I have one clarification question about source database changes made during the backup. The documentation states: "If the

Re: [sqlite] Saving and loading SQLite pages from a buffer

Thanks for the link Richard, very interesting! What version(s) of SQLite contain this experimental functionality? I am using version 3.6.10 non-amalgamation but I can't find the functions mentioned on that page. If I understand this backup API correctly then its functionality is pretty close

Re: [sqlite] Saving and loading SQLite pages from a buffer

On Feb 4, 2009, at 5:12 PM, Brown, Daniel wrote: > Good Afternoon List, > > I've been looking at how best to save/load SQLite database that is > 100% > in memory to and from a memory buffer instead of a file via a VFS > operating system wrapper. I had initially thought that implementing a >

[sqlite] Saving and loading SQLite pages from a buffer

Good Afternoon List, I've been looking at how best to save/load SQLite database that is 100% in memory to and from a memory buffer instead of a file via a VFS operating system wrapper. I had initially thought that implementing a Virtual File System (VFS) was a solution but then I realised that

Re: [sqlite] Limits in SQLite

Hi Griggs,   Many thanks for your reply. The question I have is how/by how much one must adjust these limits (in a rather optimal way) for a given, specific real machine.   Best regards, Ha --- On Wed, 2/4/09, Griggs, Donald wrote: From: Griggs, Donald

Re: [sqlite] Encryption and decryption functionality ?

On Feb 4, 2009, at 2:08 PM, Lothar Behrens wrote: > Hi, > > I have got any information that sqlite supports encryption and > decryption. > > Does it ? > > If these are extensions, where to get ? > > http://www.hwaci.com/sw/sqlite/see.html D. Richard Hipp d...@hwaci.com

[sqlite] Encryption and decryption functionality ?

Hi, I have got any information that sqlite supports encryption and decryption. Does it ? If these are extensions, where to get ? Alternative variants available ? Thanks Lothar -- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de Lothar Behrens Heinrich-Scheufelen-Platz 2

Re: [sqlite] Group by week

Hi, perhaps you want to look at http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions or perhaps you would want to post a little more information about your problem Martin Moshe Sharon wrote: > Hi > > How can I select group by week > > moshe >

Re: [sqlite] Limits in SQLite

Greetings, Ha Le, When you asked "What are the factors which determine the different limits", I suspect I'm not understanding just what you're asking. You referenced   http://www.sqlite.org/limits.html And one of your two examples was "Maximum Depth Of An Expression Tree" The webpage

[sqlite] Group by week

Hi How can I select group by week moshe ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] drop table question ?

You may not have a column name; it might be a calculated column; for example, count(*) g -Original Message- From: John Machin [mailto:sjmac...@lexicon.net] Sent: Tuesday, February 03, 2009 2:05 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] drop table question ? On

[sqlite] Limits in SQLite

Hi,   What are the factors which determine the different limits (e.g., maximum length of an SQL statement, maximum depth of an expression tree) in SQLite as shown in     http://www.sqlite.org/limits.html ? Pointers to any technical papers or detailed discussion are much appreciated.   Regards, Ha

Re: [sqlite] Error message This program cannot be run in DOS mode

Is it sqlite3.ex1 or sqlite3.exe ? Before unziping, Unlock the downloaded file by righ-clicking it an selecting the UNBLOCK button from the window displayed, and unzip and then try. Unblock the EXE and the DLL file also, if needed. Rajesh Nair. On 2/4/09, Richard Hardwick

[sqlite] Error message This program cannot be run in DOS mode

Stupid Newbie problem. I'm running WinXP. I want to make a little database. So at http://www.sqlite.org/download.html (Precompiled Binaries For Windows) I downloaded sqlite-3_6_10.zip Unzipped it to sqlite3.exe. At DOS prompt I did sqlite3 ex1 And I got "This program cannot be run in

Re: [sqlite] journal_mode = off crash in 3.6.10

I think this may be a new one. See here: http://www.sqlite.org/cvstrac/tktview?tn=3636 Dan. On Feb 4, 2009, at 4:27 PM, Vivien Malerba wrote: > This has already been fixed, see > http://www.sqlite.org/cvstrac/tktview?tn=3603 > > Regards, > > Vivien > > 2009/2/4 Brodie Thiesfield

Re: [sqlite] journal_mode = off crash in 3.6.10

This has already been fixed, see http://www.sqlite.org/cvstrac/tktview?tn=3603 Regards, Vivien 2009/2/4 Brodie Thiesfield > Hi, > > I know that there has been a number of crashes involving > journal_mode = off. This problem continues for me in > sqlite 3.6.10

[sqlite] journal_mode = off crash in 3.6.10

Hi, I know that there has been a number of crashes involving journal_mode = off. This problem continues for me in sqlite 3.6.10 almalgamation though. A trigger seems to be the cause. The following test program crashes on the last line (sqlite3_step) with a NULL pointer dereference of

Re: [sqlite] Question on missing Entry Point for Sqlite 3

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 W Allan Edwards wrote: > Unhandled Exception: System.EntryPointNotFoundException: > sqlite3_column_origin_name Consult the documentation: http://www.sqlite.org/c3ref/column_database_name.html Note that SQLITE_ENABLE_COLUMN_METADATA must be defined