Re: [sqlite] "override" table?

Don't you simply want this? sqlite> create table real (id integer primary key, value integer); sqlite> create table shadow (id integer primary key, value integer); sqlite> insert into real (id, value) VALUES (1, 1); sqlite> insert into real (id, value) VALUES (2, 2); sqlite> insert into real (id,

Re: [sqlite] "override" table?

Don't you simply want this? sqlite> create table real (id integer primary key, value integer); sqlite> create table shadow (id integer primary key, value integer); sqlite> insert into real (id, value) VALUES (1, 1); sqlite> insert into real (id, value) VALUES (2, 2); sqlite> insert into real (id,

Re: [sqlite] UTF-8

I suggest you try using wchar_t* or std::wstring as std::string is not Unicode afaik. Regards, Jonas On Thu, Dec 16, 2010 at 2:40 PM, Ming Lu wrote: > Hello Martin, > > thank you very much for your suggestion. > > i tried again with the firefox plugin works very well

Re: [sqlite] How can I query a sqlite3_stmt tofind outif ithas been run through the sqlite3_prepare_v2 function?

Ok, my mistake. On Tue, Mar 9, 2010 at 7:37 PM, Jay A. Kreibich <j...@kreibi.ch> wrote: > On Tue, Mar 09, 2010 at 07:30:54PM +0100, Jonas Sandman scratched on the wall: >> Doesn't it return an array of sqlite3_stmt pointers? > >  No. > >> If you prepare this s

Re: [sqlite] How can I query a sqlite3_stmt tofind outif ithas been run through the sqlite3_prepare_v2 function?

Doesn't it return an array of sqlite3_stmt pointers? If you prepare this statement: "BEGIN; UPDATE something SET this='that'; COMMIT;" Then the array will contain the statement handles for the three statements BEGIN, UPDATe and COMMIT. /Jonas On Tue, Mar 9, 2010 at 7:17 PM, Igor Tandetnik

Re: [sqlite] Index on joined statements

Okay, thanks! I think I understand better now, how to proceed. Regards, Jonas On Fri, Nov 27, 2009 at 3:19 AM, Igor Tandetnik <itandet...@mvps.org> wrote: > Jonas Sandman wrote: >> Yes, but considering that I first join on typeid and then have name >> and subspecies i

Re: [sqlite] Index on joined statements

Yes, but considering that I first join on typeid and then have name and subspecies in the where, wouldn't that index be optimal for that query? Jonas On Thu, Nov 26, 2009 at 4:42 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > Jonas Sandman wrote: >> Doesn't that mean t

Re: [sqlite] Index on joined statements

wrote: > Jonas Sandman wrote: >> But I guess the answer is that only the "where" parts should be >> indexed, not the id's in the joins? > > No, that's generally not true. ON clauses in joins are basically a syntactic > sugar (though there's a subtle difference

Re: [sqlite] Index on joined statements

26, 2009 at 3:38 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > Jonas Sandman wrote: >> If you have a join in an SQL-query, how do you add the statements to >> optimize the query in an index? >> >> For example: >> >> select a.* from animals

[sqlite] Index on joined statements

Hello, If you have a join in an SQL-query, how do you add the statements to optimize the query in an index? For example: select a.* from animals a join animaltype at on at.id=a.typeid where a.name='Monkey' and a.subspecies=2 do I add the index like this: "create index idx_animals on

Re: [sqlite] any keyword

Don't you just want to use IN? SELECT G.id,name FROM Genre G WHERE G.id IN (SELECT S.genre_id FROM Song S) ORDER BY name ASC; /Jonas On Wed, Nov 11, 2009 at 9:48 AM, Artur Reilin wrote: > Does it required the any key? Doesn't it work without it? > > greetings > >

Re: [sqlite] update - select

On Thu, Aug 20, 2009 at 12:01 PM, Gerald Ebner wrote: > Dear all, > > it seems that sqlite does not allow update statements of this kind: > > UPDATE table_1 SET (field_a, field_b, field_c, field_d) = ( >    SELECT field_a, field_b, field_c, field_d FROM table_2 WHERE … > )

Re: [sqlite] SQLite caching

Isn't it more likely that your database is pulled up into the OS disk cache? Try rebooting the computer between runs and the cache should be cleared. On Mon, Feb 23, 2009 at 8:44 AM, manohar s wrote: > Hi, > I am doing some performance analysis on my SQLite queries. The

Re: [sqlite] basic problem...

I'd think it's more a programming skill if you can "use SQLite". You need some basic knowledge of SQL though which I guess would be considered "a database skill". But that's just imho... On Thu, Feb 5, 2009 at 3:48 PM, Mihai Limbasan wrote: > Oh, I understand now. Probably

Re: [sqlite] convert sql for sqlite 2.8.17

Shouldn't it be select f.type, f.variety, f.price from fruits f where rowid in (select rowid from fruits where type = f.type order by price desc limit 1) ? No need for the 'AS' there. /Jonas On Tue, Dec 9, 2008 at 12:54 AM, Hariyanto Handoko <[EMAIL PROTECTED]> wrote: > I want to get one

Re: [sqlite] how to add multiple columns at a time

I don't think you can add multiple columns in just one command. /Jonas On Sat, Oct 25, 2008 at 2:34 PM, 灵感之源 <[EMAIL PROTECTED]> wrote: > Hi, > > > I want to add multiple columns, the following works for only one > column: > > ALTER TABLE Data ADD COLUMN Password TEXT > > but not

Re: [sqlite] Multiple Selects

If it's completely arbitrary I think you are stuck with using union unless it's an order that you might know beforehand. Then you can add an extra column with the index. /Jonas On Sat, Oct 18, 2008 at 7:05 PM, Andrew Gatt <[EMAIL PROTECTED]> wrote: > Jonas Sandman wrote: >> Ju

Re: [sqlite] Multiple Selects

Just to point out the obvious, have you tried ORDER BY? "SELECT name FROM table ORDER BY name;" will return your list in alphabetical order. /Jonas On Sat, Oct 18, 2008 at 6:53 PM, Andrew Gatt <[EMAIL PROTECTED]> wrote: > Andrew Gatt wrote: >> I'm not sure if i'm missing something, but is there

[sqlite] BEGIN IMMEDIATE returns SQLITE_ERROR

Hello, I am a little confused, below is my code, a bit taken out of its context but anyway, all variables are present and it compiles okay. m_stmt = NULL; m_pTail = NULL; sqlite3_prepare16_v2(m_db, L"BEGIN IMMEDIATE", -1, _stmt, (const void**)_pTail); int err = sqlite3_step(m_stmt); assert(err

Re: [sqlite] SQLite takes too much memory

On Sat, Jun 28, 2008 at 8:43 AM, Mihai Limbasan <[EMAIL PROTECTED]> wrote: > The posted code does not create 100 prepared statements. It creates just > one, fetches the data, finalizes the statement, then loops. Very true, my mistake. /Jonas ___

Re: [sqlite] Problem with sqlite overall or not?

Isn't this simply a vector problem? You have a vector where you store char pointers. The pointers returned from sqlite3_column_text will be destroyed when you step afaik. Try making it a vector instead. Then you will implicitly make copies of the text in your vector instead. /Jonas On Tue, Jun

Re: [sqlite] table names from subselects?

Isn't proper SQL select * from (select user.id from user ) blah where blah.id=1 ; ? On Fri, Mar 14, 2008 at 11:40 PM, Jay Sprenkle <[EMAIL PROTECTED]> wrote: > I'm not sure if this counts as a bug or not. > > SQLite version 3.5.2 > Enter ".help" for instructions > sqlite> select * from

Re: [sqlite] SQL Newbie problem I guess...

Thanks for the input. The 'Folder' vs 'Folders' problem was merely a spelling mistake here, not in the code. My problem was that '%' was being escaped in my code. Putting a '%%' fixed the problem. Regards, Jonas On Tue, Mar 11, 2008 at 5:37 PM, Dennis Cote <[EMAIL PROTECTED]> wrote: >

Re: [sqlite] memory issue with large amount of inserts

I guess it's not as simple as that you're application is hogging the Java VM so much that the garbage collector is never running? You can always force it to run explicitly if so... /Jonas On Fri, Feb 1, 2008 at 1:46 PM, zqzuk <[EMAIL PROTECTED]> wrote: > > Hi, I am using SQLite 3.5.4, my

Re: [sqlite] startswith and contains

Oh I have no idea. I thought LIKE with '%' was a standard, % being the wildcard. /Jonas On Jan 24, 2008 1:25 PM, Pavel Kosina <[EMAIL PROTECTED]> wrote: > I was still trying "*jup*" > Could you show me documentation page, where this is mentioned? > > Pavel

Re: [sqlite] startswith and contains

1. SELECT * FROM some WHERE xyz LIKE '%jup%' 2. SELECT * FROM SOME WHERE zyx LIKE 'jul%' should work. On Jan 24, 2008 12:44 PM, Pavel Kosina <[EMAIL PROTECTED]> wrote: > Hello, > > How to do following queries?: > > 1/ select * from some where xyz CONTAINS "jup" (anywhere in xyz could be > text

Re: [sqlite] ORDER BY Performance on 30,000 records

Sorting the returned 30 000 records maybe takes 3-4 seconds? /Jonas On 12/2/07, Ofir Neuman <[EMAIL PROTECTED]> wrote: > Hi All, > > I have some performance problem when adding ORDER BY to my query, hope you > can help me speed things up. > > This is my table: > > TABLE1 > { > ID TEXT >

Re: [sqlite] Is possible put a password in database ?

How much is it for the encrypted version? Regards, Jonas On Nov 8, 2007 1:12 PM, Trevor Talbot <[EMAIL PROTECTED]> wrote: > On 11/8/07, paulito santana <[EMAIL PROTECTED]> wrote: > > > is possible i put a password in the file that represents a SQLite database > > ?? There is any instruction in

Re: [sqlite] Threads in 3.5.0

Well I am certainly a newbie in this context, threads must certainly be used when writing Windows application since you are not at liberty to splinter your process like you can on a *nix based system, no? On 10/4/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > John Stanton <[EMAIL PROTECTED]>

Re: [sqlite] FW: BLOB data retrieval

Still would be nice to know why you feel like stepping through the whole lot to count rows? On 6/28/07, Krishnamoorthy, Priya (IE10) <[EMAIL PROTECTED]> wrote: Thanks all for the help. I fixed the problem by stepping through one row after another. Thanks a lot. Priya -Original Message-

Re: [sqlite] FW: BLOB data retrieval

You need to know the number of rows, don't you already have that in b.numRows() ? /Jonas On 6/28/07, Trevor Talbot <[EMAIL PROTECTED]> wrote: On 6/27/07, Krishnamoorthy, Priya (IE10) <[EMAIL PROTECTED]> wrote: > for (int i=1;i <= b.numRows() ; i++) >

Re: [sqlite] "Make" with VisualStudio/Windows

external symbol _sqlite3AlterFinishAddColumn referenced in function _yy_reduce error LNK2019: unresolved external symbol _sqlite3AlterRenameTable referenced in function _yy_reduce Best regards, Jonas On 6/13/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote: On 6/13/07, Jonas Sandman &

[sqlite] "Make" with VisualStudio/Windows

Hello, Is it possible to get the latest SQLite somehow which doesn't require you to actually run "make"? With the amalgamation (?) you can just compile the whole thing in Visual Studio but it seems that the compiler isn't very successful in optimizing when it's run like that since my

Re: [sqlite] Stack usage

Is it wise to have a database with 2000 columns? Wouldn't it be better to split this into several tables and query them separately? Jonas On 6/5/07, [EMAIL PROTECTED] < [EMAIL PROTECTED]> wrote: This is very worrying since it means that the statement cannot be compiled on a low memory

Re: [sqlite] Re: Re: Re: COLLATE without ORDER BY?

Thanks Igor, A custom function was exactly what I needed :-) Best regards, Jonas On 5/25/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote: Jonas Sandman <[EMAIL PROTECTED]> wrote: >> ext1 is one type of extension, in my case 'mp3'. I guess it doesn't >> make > s

Re: [sqlite] Re: Re: COLLATE without ORDER BY?

e the most confused is that SQLite takes it as a valid prepared statement. No error or anything. I am using SQLite 3.3.17, which should be recent enough (latest?) Best regards, Jonas On 5/25/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote: Jonas Sandman <[EMAIL PROTECTED]> wrote: > Howev

Re: [sqlite] Re: COLLATE without ORDER BY?

collation isn't called. If I add an ORDER BY COLLATE STRIP_ACCENT to the select, the collation is called, but I'll assume that is just for determining the order and not for the comparison when retrieving the results. Best regards, Jonas On 5/25/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

[sqlite] Override LIKE

Hello, I am using LIKE to make a simple query for filename '%path%' and it works well as long as I am only using ANSI characters, LIKE thinks 'a' and 'A' are the same. When coming up on Greek characters (i.e.) it doesn't work that well anymore. Suddenly LIKE is case-sensitive (since LIKE use

Re: [sqlite] Still getting "Insertion failed because database isfull." errors

Or you can close the database connection if it's idle for a little bit (less than it takes for it to sleep)? On 4/18/07, Joel Cochran <[EMAIL PROTECTED]> wrote: OK, then I won't be worrying about Transactions for SELECT statements, it doesn't really apply to our application. And some

Re: [sqlite] Efficiency and Pragmas

What is the default setting for PRAGMA cache_size ? Where is it defined? On 4/18/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: cache is per-connection so if you open and close connections a lot then a large cache won't help your program. The command line app is a single connection so a large

Re: [sqlite] Still getting "Insertion failed because database is full." errors

could not. The last time it happened in the field, I had him stop using the device and bring it to me so that I could see the Stack Trace (which I sent to the list). With neither his device nor mine can I recreate the problem in DEBUG. It is very frustrating. Thanks, Joel On 4/17/07, Jonas Sandman &

Re: [sqlite] Still getting "Insertion failed because database is full." errors

What is the guy on in the field doing that you are not? Are you using his device for the testing? Since it takes minutes for him to encounter the error it can't be that hard to recreate. Follow him around for an hour or so and see how he uses the program. It could easily be something he's doing

Re: [sqlite] Performance analysis of SQLite statements

Anyone know a good bench-marking (preferably free or cheap) which can be used to benchmark C/C++ code in Windows? Best regards, Jonas On 4/12/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: Are there any tools to help analyze the performance of components with a particular SQLite statement?

Re: [sqlite] Optimize my database

On 3/29/07, Rich Shepard <[EMAIL PROTECTED]> wrote: On Thu, 29 Mar 2007, Jonas Sandman wrote: > I guess I could split it into more tables, but would that really make > anything faster? It would make upgrades, changes, and searching faster. > The whole key is the fi

Re: [sqlite] Optimize my database

I should add that I am scanning a folder-tree for the files and are storing the metadata in the database like that so I don't have to read the actual files. That's why I used the path as primary key to begin with. No duplicate entries. On 3/29/07, Jonas Sandman <[EMAIL PROTECTED]> wrote:

[sqlite] Optimize my database

Hello again, I am quite new at database and how to set them up properly so keep in mind that the obvious might stare into your face but just point it out to me :-) I am having this database with mediafiles (even anyone recall my previous mails) and I create it like this: CREATE TABLE Files

Re: [sqlite] The IN keyword

with sqlite3_prepare16_v2 before executing it? Best regards, Jonas On 3/28/07, Alan Barkway <[EMAIL PROTECTED]> wrote: On Wed, 28 Mar 2007 08:38:55 +0100, Jonas Sandman <[EMAIL PROTECTED]> wrote: [...] > > SELECT * FROM Files WHERE extension IN (:extension) Just a guess here but might it be

Re: [sqlite] The IN keyword

I am binding it like that yes, but Dan said I had to make it like this: SELECT * FROM Files WHERE extension IN (:extension1, :extension2) is it possible to do this and maintain a dynamic number of extensions? On 3/28/07, Brownie <[EMAIL PROTECTED]> wrote: > > > SELECT * FROM Files WHERE

Re: [sqlite] The IN keyword

I am using sqlite3_bind_text16() and it as I said it works fine with the regular extension=:extension. But maybe it doesn't understand how to parse the 'mp3','avi' ? a wchar_t is defined as 'unsigned short' so it's a 16-bit, is it not? On 3/28/07, Dan Kennedy <[EMAIL PROTECTED]> wrote: > I

[sqlite] The IN keyword

Hello, I am programming Visual C++ in VS2003 on Windows XP SP2 and I am working on a database which is storing media files of any sort. Among other things I am storing metadata but in the query that I am having problems with I only look at one specific column: extension The extension stores

Re: [sqlite] Re: Re: Re: Re: sqlite3_prepare16 and LIKE

On 10/30/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote: Jonas Sandman <[EMAIL PROTECTED]> wrote: > #define SELECT_STATEMENT_TEXTSORT L"SELECT * FROM Data WHERE > (Data.titleLIKE (SELECT '%%' || ? || '%') OR > Data.artist LIKE (SELECT '%' || ? || '%')

Re: [sqlite] Re: Re: Re: sqlite3_prepare16 and LIKE

3_step(exc)) == SQLITE_ROW) { // process data here like: // df->SetFileName((wchar_t*)sqlite3_column_text16(exc, 0)); } path and query are wchar_t variables. Jonas On 10/30/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote: Jonas Sandman <[EMAIL PROTECTED]> wrote: &

Re: [sqlite] Re: Re: sqlite3_prepare16 and LIKE

On 10/30/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote: Jonas Sandman <[EMAIL PROTECTED]> wrote: >> Data.artist LIKE (SELECT '%' || ? || '%') OR Data.album LIKE (SELECT >>> '%' || ? || '%') OR Data.genre LIKE (SELECT '%' || ? || '%') OR &g

Re: [sqlite] Re: sqlite3_prepare16 and LIKE

On 10/30/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote: Jonas Sandman <[EMAIL PROTECTED]> wrote: > I have created a database like this: > > CREATE TABLE Data (path VARCHAR(512) PRIMARY KEY, title VARCHAR(512), > artist VARCHAR(512), album VARCHAR(512), length INTE

[sqlite] sqlite3_prepare16 and LIKE

I have created a database like this: CREATE TABLE Data (path VARCHAR(512) PRIMARY KEY, title VARCHAR(512), artist VARCHAR(512), album VARCHAR(512), length INTEGER, genre VARCHAR(512), comment VARCHAR(512), track INTEGER, year INTEGER, bitrate INTEGER, playcount INTEGER, changed INTEGER, size