[sqlite] Consequences of resetting sqlite_sequence.seq

Hello, I would like to store rows with an id column that is unique (at least most of the time, see below), but must be below some maximum value, and I would like to do so in a very space efficient way. My idea is to use the rowid as the id column and declare it as autoincrement. After every

Re: [sqlite] Referring to column alias

On 10/28/2011 4:28 PM, Pete wrote: I have another variation of this issue: SELECT col1 - col2 as Total, Total * price FROM tst ... gives an error " no such column: Total". I can just repeat "col1 - col2" of course, but wondering if there is a way to refer to Total within the SELECT. This is

Re: [sqlite] Referring to column alias

I have another variation of this issue: SELECT col1 - col2 as Total, Total * price FROM tst .. gives an error " no such column: Total". I can just repeat "col1 - col2" of course, but wondering if there is a way to refer to Total within the SELECT. Thanks, Pete > > > > Message: 6 > >

Re: [sqlite] Using SQLite on Windows 64bit

Thank you, but it seems to be working now - I didn't have VC++2010 redist. Regards Jonathan - Original Message - From: Dimiter 'malkia' Stanev To: General Discussion of SQLite Database Sent: Friday, October 28, 2011 12:26 AM Subject: Re: [sqlite] Using SQLite on Windows

Re: [sqlite] Unique id

On 28 Oct 2011, at 8:20am, Tamara Cattivelli wrote: > how can I let the database generate a generally unique id(guid, String)? The database will generate a unique integer for the row itself whenever you save a row without specifying the id. You can insert a row then tell what integer it used

Re: [sqlite] Unique id

Hi, how can I let the database generate a generally unique id(guid, String)? Thanks, Tamara 2011/10/27, Simon Slavin : > > On 27 Oct 2011, at 10:04pm, Kees Nuyt wrote: > >> On Thu, 27 Oct 2011 21:47:17 +0100, Simon Slavin >> wrote: >> >>> On 27 Oct

Re: [sqlite] Using SQLite on Windows 64bit

I haven't used the SQLite .net modules, a coworker of mine did, and he ran into the same issue. It looks like that the bundled Managed .DLL (or was it native .DLL) was compiled for 32-bit, and his .NET application was set to "Default Mode". Once it was forced to be for 32-bit, it worked.

[sqlite] Minor inconsistency between sqlite3_value_numeric_type() and sqlite3_value_double(), etc.

Not that this is a big difference, but I noticed when looking at the code for sqlite3_value_numeric_type() that it checks for numeric types in text values, but not in blobs values, but that sqlite3_value_double(), sqlite3_value_int() and sqlite3_value_int64() appear to look for numeric strings

Re: [sqlite] ORDER BY disregarded for GROUP BY

your problem is that v refers to data in the original row-- which v you see when you group is totally arbitrary, and there's no requirement on sql to use the same one for ordering. You need to specify an aggregate on the non-grouping columns to really have any sort of defined behavior. What I

Re: [sqlite] EXT : ORDER BY disregarded for GROUP BY

I think this is (or ought to be) an FAQ. v is considered to be random and unreliable -- you only want one record and you're asking it to pick one-from-N without any logic (you assume order by does this but it still returns a set and not a single value). I don't know if mysql will give you a

Re: [sqlite] ORDER BY disregarded for GROUP BY

On 10/28/2011 12:42 PM, Tobias Sjösten wrote: But when I group it by 'g' it completely disregards the ordering: SELECT g,v FROM t GROUP BY g ORDER BY v ASC; a|3 b|3 What seems to be the problem? The resultset is ordered by the second column, isn't it? What did you expect to happen

[sqlite] ORDER BY disregarded for GROUP BY

I have a table: CREATE TABLE t ( i INT, g VARCHAR(1), v INT ); And the data: INSERT INTO t (i,g,v) VALUES (1,'a',2); INSERT INTO t (i,g,v) VALUES (1,'a',1); INSERT INTO t (i,g,v) VALUES (1,'a',3); INSERT INTO t (i,g,v) VALUES (1,'b',2); INSERT INTO t (i,g,v) VALUES (1,'b',1); INSERT INTO t

Re: [sqlite] cache size and insert-only

On Fri, Oct 28, 2011 at 5:36 AM, Jay A. Kreibich wrote: > On Fri, Oct 28, 2011 at 01:33:31PM +0200, Gert Corthout scratched on the wall: >> we have a database that only performs insert statements on a table >> (database contains only this table), is it in this case a good idea >>

Re: [sqlite] Segmentation Fault on SQLITE3_exex

http://www.sqlite.org/faq.html#q6 seems misleading when also reading http://www.sqlite.org/threadsafe.html Looks like you need to finalize() inside your mutex boundary according to the FAQ. But, if you use serialized mode the other page says "no restriction". Make sure you use serialized mode

Re: [sqlite] Segmentation Fault on SQLITE3_exex

Many thanks for your instant help so far! I will run a few tests actually I use on single class instantiation and each thread has an pointer to the object. The code I put below is incomplete but describes the standard behavoir in all the database methods, I don't want to give full access to the

Re: [sqlite] cache size and insert-only

On Fri, Oct 28, 2011 at 07:36:24AM -0500, Jay A. Kreibich scratched on the wall: > On Fri, Oct 28, 2011 at 01:33:31PM +0200, Gert Corthout scratched on the wall: > > > > hello all, > > > > we have a database that only performs insert statements on a table > > (database contains only this

Re: [sqlite] Segmentation Fault on SQLITE3_exex

What you're showing should basically work as long it's not a single class instantiation being used by multiple threads. Also, your missing a bind on the 2nd statement. And you're not retrieving the results of the query (I assume you left that out for brevity). What I would do is this to

Re: [sqlite] cache size and insert-only

On Fri, Oct 28, 2011 at 01:33:31PM +0200, Gert Corthout scratched on the wall: > > hello all, > > we have a database that only performs insert statements on a table > (database contains only this table), is it in this case a good idea > to reduce the cache size to, say, 5? No, that would be

[sqlite] cache size and insert-only

hello all, we have a database that only performs insert statements on a table (database contains only this table), is it in this case a good idea to reduce the cache size to, say, 5? Because if I understand things correctly the cache doesn't really help you when you do nothing but inserts,

Re: [sqlite] Segmentation Fault on SQLITE3_exex

On Fri, Oct 28, 2011 at 10:07 AM, Christian wrote: > static char command [1024]; > ... > sprintf (command, "SELECT * from model where id=?"); > FYI: that can be simplified to: static char const * command = "SELECT * from model where id=?"; There's no reason why the overhead

Re: [sqlite] Segmentation Fault on SQLITE3_exex

First of all thanks for your detailed reply! According to the documentation its not recommended to reuse a preparedStatement after finalizing it. So my initial guess to do something like this: sqlite3_stmt *preparedStatement = 0; static char command [1024]; sprintf (command, "SELECT * from