[sqlite] SQL 2003 in sqlite

Hello, The merge statement is really missing in sqlite... Is there any plan to integrate this SQL 2003 syntax in sqlite? Best regards, Sylvain ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] If prepare failed do I have to call finalize?

Hi, ALL, Consider this: std::string query = ""; if( ( result = sqlite3_prepare_v2( handle, query, -1 , 0 ) ) != SQLITE_OK ) { // Should there be call to sqlite3_finalize. I.e. can I safely assume that since it failed all memory is not being allocated } Thank you.

Re: [sqlite] SQL 2003 in sqlite

On 12 Nov 2013, at 8:45am, Sylvain Pointeau wrote: > The merge statement is really missing in sqlite... SQLite does have this form of the INSERT statement: INSERT (OR REPLACE/IGNORE) INTO table1 SELECT * FROM table2 Do you have specific requirements for the UPDATE

Re: [sqlite] If prepare failed do I have to call finalize?

Igor Korot wrote: > if( ( result = sqlite3_prepare_v2( handle, query, -1 , 0 ) ) != > SQLITE_OK ) > // Should there be call to sqlite3_finalize. I.e. can I safely assume that > since it failed all memory is not being allocated says: | *ppStmt is left

Re: [sqlite] If prepare failed do I have to call finalize?

On Tue, Nov 12, 2013 at 10:43 AM, Clemens Ladisch wrote: > Igor Korot wrote: > > if( ( result = sqlite3_prepare_v2( handle, query, -1 , 0 ) ) != > SQLITE_OK ) > > // Should there be call to sqlite3_finalize. I.e. can I safely assume > that since it failed all memory is not

[sqlite] Bug: Use of _msize() in SQLite is not automatic on Windows

Despite of the following comment in mem1.c: on Windows the _msize() function is not used at all, neither on MSVC neither on MinGW. The problem is that at the end of the @ifdef'ery, when HAVE_MALLOC_USABLE_SIZE is not defined,

Re: [sqlite] If prepare failed do I have to call finalize?

On 11/12/2013 5:11 AM, Stephan Beal wrote: A related point which bit me once: if the input SQL is empty, the statement will be NULL as well but 0 will be returned. More generally, if the input contains nothing but (possibly empty) sequence of comments. -- Igor Tandetnik

Re: [sqlite] Feature request: Better support for database file moving

Thanks for the replies. I'd like to address some of your comments so far. -- Stephan Beal: > If the app is sandboxed, how does the user possibly > get a chance to move the files? A sandboxed Mac OS X program can gain access to regular user-selected documents (files or directories). And of

Re: [sqlite] Ghost index?

On Tue, 12 Nov 2013 15:10:16 +1100, SongbookDB wrote: >I've notices if I make a mistake, the prompt turns to ...>, and then >legitimate commands no longer work. Is there a way to get back to the >normal prompt? The .h command tells you what you can do there. --

Re: [sqlite] Ghost index?

On Tue, 12 Nov 2013 15:00:36 +1100, SongbookDB wrote: >Ahh - ok. I was typing sqlite3 db.3sdb IN sqlite3, not command prompt. > >I can load and search the db now. > >So to drop the index 'index1' for the table 'table1', do I just type DROP >INDEX IF EXISTS 'index1';

Re: [sqlite] Enable WAL on a QNX system (Richard Hipp)

Hi, I took your advice and now I am setting only once the journal_mode=WAL, also locking_mode=EXCLUSIVE and temp_store=MEMORY. But still it doesn't work, and I get DB locks immediately. I enabled SQLite traces, and I also added some supplementary traces when the WAL is set. Even if the

Re: [sqlite] Ghost index?

Thanks Kees - I managed to get it working. On Tue, Nov 12, 2013 at 11:49 PM, Kees Nuyt wrote: > On Tue, 12 Nov 2013 15:00:36 +1100, SongbookDB > wrote: > > >Ahh - ok. I was typing sqlite3 db.3sdb IN sqlite3, not command prompt. > > > >I can load

[sqlite] Closing database connection after each transaction?

Hello SQLite users, I was wondering, if closing the database connection after each transaction is a good idea. Possibly I've missed the thread concerning this topic. If this is true, please supply me with this link. Otherwise, can anybody give a reliable advice to do it in this or that way. I

Re: [sqlite] Feature request: Better support for database file moving

If the database files are being handled as a document, what I would suggest then is to use SQLites Backup API, and work off of the backup API. http://www.sqlite.org/backup.html This means that the user is going to be free to do whatever they want with the file in their documents directory. When

Re: [sqlite] Closing database connection after each transaction?

On Tue, Nov 12, 2013 at 8:06 AM, Stelling Carsten < carsten.stell...@goerlitz.com> wrote: > Hello SQLite users, > > I was wondering, if closing the database connection after each transaction > is a good idea. > No, it is usually a bad idea. There are non-trivial costs associated with reopening

Re: [sqlite] Enable WAL on a QNX system (Richard Hipp)

On Mon, Nov 11, 2013 at 12:33 PM, Sandu Buraga wrote: > Hi, > > I took your advice and now I am setting only once the journal_mode=WAL, > also locking_mode=EXCLUSIVE and temp_store=MEMORY. But still it doesn't > work, and I get DB locks immediately. I enabled SQLite

Re: [sqlite] Enable WAL on a QNX system (Richard Hipp)

On Tue, Nov 12, 2013 at 8:12 AM, Richard Hipp wrote: > > > > On Mon, Nov 11, 2013 at 12:33 PM, Sandu Buraga wrote: > >> Hi, >> >> I took your advice and now I am setting only once the journal_mode=WAL, >> also locking_mode=EXCLUSIVE and temp_store=MEMORY.

Re: [sqlite] Feature request: Better support for database file moving

On 12 Nov 2013, at 12:24pm, L. Wood wrote: >> Besides which, you can write it yourself externally to SQLite: >> use FSEvent on the folder your database is in checking for >> kFSEventStreamEventFlagItemRemoved, and act accordingly. >> No need to put that facility inside SQLite.

[sqlite] SQLite Exception

Hi, We are using sqlite dll(64-bit) in our windows application and carrying the same dll with the application setup. Our application support different OS versions. We downloaded the precompiled binary from the link, (

Re: [sqlite] Feature request: Better support for database file moving

On 12 Nov 2013 at 13:49, Simon Slavin wrote: > Doesn't matter because OS X is, at heart, Unix. Unix is a bit weird in that > it allows you to move and delete a file which is open. I wouldn't describe this as weird, it should be SOP for an OS, IMO. It's (one) of the

Re: [sqlite] Closing database connection after each transaction?

Dear Richard, Thank you for your fast response and clarification on this topic. Regards, Carsten -Ursprüngliche Nachricht- Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] Im Auftrag von Richard Hipp Gesendet: Dienstag, 12. November 2013 14:08 An: General

Re: [sqlite] Feature request: Better support for database file moving

On 12 Nov 2013, at 2:25pm, Tim Streater wrote: > On 12 Nov 2013 at 13:49, Simon Slavin wrote: > >> Doesn't matter because OS X is, at heart, Unix. Unix is a bit weird in that >> it allows you to move and delete a file which is open. > > I

Re: [sqlite] Feature request: Better support for database file moving

**kind of off topic** @Tim> I'm no where near in thinking that it should be SOP. I'm somewhat appreciative of not being allowed to change the "file containers" visual representation a file while something has its fingers on it. Just as I tell my kids, "if you make the mess, you clean it up.

[sqlite] Is access to extension objects synchronized inside sqlite3.dll in serialized mode?

Hello all, I am writing an extension library that contains some extensions, i.e. custom functions and collations. Some of these objects use some private data members in order to perform their calculations. This extension library will be used by an embedded server that must handle many database

Re: [sqlite] Feature request: Better support for database file moving

On 12 Nov 2013 at 16:14, Stephen Chrzanowski wrote: > **kind of off topic** > > @Tim> I'm no where near in thinking that it should be SOP. > > I'm somewhat appreciative of not being allowed to change the "file > containers" visual representation a file while something has

[sqlite] Internal workings of reading/writing -journal files

* For each transaction, i.e. between _prepare_v2() and _finalize(), does SQLite on Mac OS X only use the POSIX open(), read(), write(), lseek() etc. functions to read/write into the -journal file? Or does it use some other functions? * For each transaction, i.e. between _prepare_v2() and

Re: [sqlite] Internal workings of reading/writing -journal files

On Tue, Nov 12, 2013 at 12:25 PM, L. Wood wrote: > * For each transaction, i.e. between _prepare_v2() and _finalize(), does > SQLite on Mac OS X only use the POSIX open(), read(), write(), lseek() etc. > functions to read/write into the -journal file? Or does it use some other

Re: [sqlite] Internal workings of reading/writing -journal files

On 12 Nov 2013, at 5:36pm, Richard Hipp wrote: > It opens and > closes the rollback journal file once per transaction. Which does /not/ mean once per _prepare() ... _finalize(). Simon. ___ sqlite-users mailing list

Re: [sqlite] Is access to extension objects synchronized inside sqlite3.dll in serialized mode?

On Tue, Nov 12, 2013 at 11:58 AM, Constantine Yannakopoulos < alfasud...@gmail.com> wrote: > I need to know whether SQLite synchronizes its > invocations to the callbacks of these objects or whether I have to take > care of protecting the data members these callbacks are using from > concurrent

Re: [sqlite] SQLite Exception

One possibility has to be that your one problem user is on 32-bit Windows. Are you sure that's not the case? Did you try using the sxstrace tool? Here's good documentation for how to do so -- http://www.fundootech.com/2013/01/how-to-use-sxstraceexe.html J. Merrill -Original Message-

Re: [sqlite] Is access to extension objects synchronized inside sqlite3.dll in serialized mode?

On Tue, Nov 12, 2013 at 7:38 PM, Richard Hipp wrote: > SQLite serializes callbacks to extension objects originating from a > single > database connection. But if you have multiple database connections running > in separate threads, then the same callback might be invoked

Re: [sqlite] Is access to extension objects synchronized inside sqlite3.dll in serialized mode?

On Tue, Nov 12, 2013 at 8:24 PM, Richard Hipp wrote: > On Tue, Nov 12, 2013 at 1:18 PM, Constantine Yannakopoulos < > alfasud...@gmail.com> wrote: > > > > > If I understand correctly what you say, each sqlite3* connection instance > > serializes its calls to its extension

Re: [sqlite] Is access to extension objects synchronized inside sqlite3.dll in serialized mode?

On Tue, Nov 12, 2013 at 1:18 PM, Constantine Yannakopoulos < alfasud...@gmail.com> wrote: > > If I understand correctly what you say, each sqlite3* connection instance > serializes its calls to its extension callbacks so each instance of the > extension objects does not need to perform any

Re: [sqlite] If prepare failed do I have to call finalize?

OK, so it's not required. Thank you. On Tue, Nov 12, 2013 at 3:33 AM, Igor Tandetnik wrote: > On 11/12/2013 5:11 AM, Stephan Beal wrote: >> >> A related point which bit me once: if the input SQL is empty, the >> statement >> will be NULL as well but 0 will be returned. > > >

Re: [sqlite] SQL 2003 in sqlite

On Nov 12, 2013, at 9:49 AM, Simon Slavin wrote: >> The merge statement is really missing in sqlite… Yes, very much so. > SQLite does have this form of the INSERT statement: http://www.sqlite.org/lang_conflict.html Sadly, none of these ‘on conflit’ options are of any

[sqlite] What is the best index to create?

Hi, ALL, sqlite> EXPLAIN QUERY PLAN SELECT DISTINCT playersinleague.playerid, leaguescore hitter.scoreid, leaguescorehitter.value, leaguescorepitcher.scoreid, leaguescore pitcher.value FROM playersinleague, leaguescorehitter, leaguescorepitcher WHERE playersinleague.id = 1 AND

Re: [sqlite] What is the best index to create?

On 12 Nov 2013, at 9:41pm, Igor Korot wrote: > I don't see what is wrong. It should be using foreign key constraints > on the last two searches, but it looks like it does not. Did you actually create the indexes which would be useful for those constraints, or did you just

[sqlite] EXPLAIN QUERY PLAN

Greetings and salutations. I am trying to speed up our queries and normalize our DB and I am reading, http://www.sqlite.org/eqp.html But, I am missing a lot. Where do I read about the results and how to make changes to the DB to speed up/enhance the DB response? Thanks. josé

Re: [sqlite] EXPLAIN QUERY PLAN

On 12 Nov 2013, at 10:47pm, jose isaias cabrera wrote: > I am trying to speed up our queries and normalize our DB and I am reading, > > http://www.sqlite.org/eqp.html > > But, I am missing a lot. Where do I read about the results and how to make > changes to the DB to

Re: [sqlite] EXPLAIN QUERY PLAN

On Tue, 12 Nov 2013 17:47:05 -0500, jose isaias cabrera wrote: > I am trying to speed up our queries and normalize our DB and I am > reading, > > http://www.sqlite.org/eqp.html > > But, I am missing a lot. Where do I read about the results and how to > make changes to the DB to speed

Re: [sqlite] What is the best index to create?

Hi, Simon, On Tue, Nov 12, 2013 at 2:18 PM, Simon Slavin wrote: > > On 12 Nov 2013, at 9:41pm, Igor Korot wrote: > >> I don't see what is wrong. It should be using foreign key constraints >> on the last two searches, but it looks like it does not. > >

Re: [sqlite] EXPLAIN QUERY PLAN

On Wed, Nov 13, 2013 at 12:20:42AM +, Walter Hurry scratched on the wall: > On Tue, 12 Nov 2013 17:47:05 -0500, jose isaias cabrera wrote: > > > I am trying to speed up our queries and normalize our DB and I am > > reading, > > > > http://www.sqlite.org/eqp.html > > > > But, I am missing a

Re: [sqlite] Enable WAL on a QNX system (Richard Hipp)

Hi, It is strange because it seems that the WAL journal mode was enabled: Trace 1: before setting WAL mode = Filename: /mnt/share/testApp/myDB.db State: WRITER_CACHEMOD errCode=0 Lock: EXCLUSIVE Locking mode: locking_mode=exclusive Journal mode:

[sqlite] How to fix sqlite issue

Hello, I am having problem with an error that is appearing whenever I startup my computer. This happened when I downloaded the latest update of iTunes. The issue is as follows: The procedure entry point sqlite3-wal-checkpoint could not be located in the dynamic library SQLite3.dll.

Re: [sqlite] How to fix sqlite issue

On Tue, Nov 12, 2013 at 1:22 PM, Norma Richey wrote: > Hello, > > > > I am having problem with an error that is appearing whenever I startup my > computer. This happened when I downloaded the latest update of iTunes. > > > > The issue is as follows: > > The procedure entry

Re: [sqlite] How to fix sqlite issue

Hi, perhaps, this can help: 1) download zip with latest SQLite DLL from http://sqlite.org/download.html; 2) unzip it to c:\temp; 3) copy c:\temp\sqlite3.dll to Windows\System32 folder; 4) if there is already sqlite3.dll in Windows\System32 and it's locked, overwrite it with InUse.exe