Re: [sqlite] improving performance on SELECT

I'm trying to improve SELECT queries on a db I created. Here's the part I think is relevant: SELECT fid, man_fsetid, pmfeature.allele, pmfeature.strand FROM featureSet, pmfeature WHERE man_fsetid IN (LONG LIST HERE) AND pmfeature.fsetid = featureSet.fsetid ORDER BY fid That list

Re: [sqlite] Re: Re[sqlite] garding software of SQlite2.1

On Sun, 28 Oct 2007 21:08:21 -0700 (PDT), Vijaya Lakshmi [EMAIL PROTECTED] wrote: Hi, Thank you very much for your response.I tried to convert SQLite2.1 version to SQlite3 version but unable to convert them .Could you please explain in which command prompt we need to convert them. From a

RE: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

The sqlite3_last_insert_rowid function is completely, 100% reliable in your scenario. The problem is that in your scenario you shouldn't be calling that function. The function is called sqlite3_last_insert_rowid, not sqlite3_last_insert_or_ignore_rowid, and not

[sqlite] Performance problem for a simple select with range

I think I have a performance problem for a simple select with range. My Tables: CREATE TABLE locations( locidINTEGER PRIMARY KEY, country TEXT, regionTEXT, cityTEXT, postalCode TEXT,

Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

I don't get an error code. So how should I decide if I should call sqlite3_last_insert_rowid() or not? :) That's the problem - I don't have any indication if an insert was actually performed or if it was simply ignored - thus I don't have any possibility to decide if the call is valid or not. This

[sqlite] Retrieve bound host parameters from statement?

Hello, I wonder if it is possible to retrieve bound host parameters from a prepared SQL statement? I am thinking of the opposite of the sqlite3_bind... family of functions like: int sqlite3_bound_int (sqlite3_stmt*, int*); int sqlite3_bound_double (sqlite3_stmt*, double*); They would be

Re: [sqlite] Mechanism for pre-allocating contiguous files for Sqlite?

I'm writing a VFS right now. One of the parameters to the open is the file type (various types of DBs and journals). You should be able to use that info. Also, look for a took called config on the sysinternals site. It allows you to pre-allocate contiguous files. Better, it allows you to

Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

Hi All, Following this thread, I was experimenting with last_insert_rowid(), and found the following, which does not look right: SQLite version 3.4.2 Enter .help for instructions sqlite sqlite create table tmp( a integer, b integer ); sqlite create unique index tmpIndex on tmp( a, b ); sqlite

Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

Michael Ruck wrote: I don't get an error code. So how should I decide if I should call sqlite3_last_insert_rowid() or not? :) That's the problem - I don't have any indication if an insert was actually performed or if it was simply ignored - thus I don't have any possibility to decide if the call

Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

Simon Davies wrote: Following this thread, I was experimenting with last_insert_rowid(), and found the following, which does not look right: SQLite version 3.4.2 Enter .help for instructions sqlite sqlite create table tmp( a integer, b integer ); sqlite create unique index tmpIndex on tmp( a, b

Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

Thanks for the explanation! On 29/10/2007, Dennis Cote [EMAIL PROTECTED] wrote: Simon Davies wrote: Following this thread, I was experimenting with last_insert_rowid(), and found the following, which does not look right: SQLite version 3.4.2 Enter .help for instructions sqlite

RE: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

This link gives a little more information: http://www.sqlite.org/autoinc.html Shawn -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Monday, October 29, 2007 10:57 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

RE: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

Adding primary key to column a results in the behavior I think you were first expecting. sqlite create table tmp (a integer primary key, b integer); sqlite create unique index tmpIndex on tmp (a, b); sqlite insert into tmp values (1, 1); sqlite insert into tmp values (2, 2); sqlite select

Re: [sqlite] Retrieve bound host parameters from statement?

--- Ralf Junker [EMAIL PROTECTED] wrote: I wonder if it is possible to retrieve bound host parameters from a prepared SQL statement? I am thinking of the opposite of the sqlite3_bind... family of functions like: int sqlite3_bound_int (sqlite3_stmt*, int*); int sqlite3_bound_double

Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Default] On Mon, 29 Oct 2007 15:00:51 +0100, Michael Ruck [EMAIL PROTECTED] wrote: I don't get an error code. So how should I decide if I should call sqlite3_last_insert_rowid() or not? :) That's the problem - I don't have any indication if an insert was actually performed or if it was simply

Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

On 29/10/2007, Odekirk, Shawn [EMAIL PROTECTED] wrote: Adding primary key to column a results in the behavior I think you were first expecting. sqlite create table tmp (a integer primary key, b integer); sqlite create unique index tmpIndex on tmp (a, b); sqlite insert into tmp values (1, 1);

Re: [sqlite] Performance problem for a simple select with range

[Default] On Mon, 29 Oct 2007 15:25:18 +0200, Dani Valevski [EMAIL PROTECTED] wrote: I think I have a performance problem for a simple select with range. My Tables: CREATE TABLE locations( locidINTEGER PRIMARY KEY, country TEXT, regionTEXT,

[sqlite] Re: Performance problem for a simple select with range

Dani Valevski [EMAIL PROTECTED] wrote: I think I have a performance problem for a simple select with range. My Tables: CREATE TABLE locations( locidINTEGER PRIMARY KEY, country TEXT, regionTEXT, cityTEXT,

[sqlite] Re: INSERT OR IGNORE and sqlite3_last_insert_rowid()

Michael Ruck michael.ruck-4ptYESVdgRZ0eaEml/[EMAIL PROTECTED] wrote: I don't get an error code. So how should I decide if I should call sqlite3_last_insert_rowid() or not? :) That's the problem - I don't have any indication if an insert was actually performed or if it was simply ignored

RE: [sqlite] Re: INSERT OR IGNORE and sqlite3_last_insert_rowid()

Ok, thanks I haven't seen this function. I'll try it. Mike -Ursprüngliche Nachricht- Von: Igor Tandetnik [mailto:[EMAIL PROTECTED] Gesendet: Montag, 29. Oktober 2007 17:01 An: SQLite Betreff: [sqlite] Re: INSERT OR IGNORE and sqlite3_last_insert_rowid() Michael Ruck

Re: [sqlite] Retrieve bound host parameters from statement?

I wonder if it is possible to retrieve bound host parameters from a prepared SQL statement? I am thinking of the opposite of the sqlite3_bind... family of functions like: int sqlite3_bound_int (sqlite3_stmt*, int*); int sqlite3_bound_double (sqlite3_stmt*, double*); You'd also need

AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

This does look like a solution indeed. I'll try this one later. Thank you! Mike -Ursprüngliche Nachricht- Von: Simon Davies [mailto:[EMAIL PROTECTED] Gesendet: Montag, 29. Oktober 2007 16:40 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] INSERT OR IGNORE and

Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

As has been carefully explained by several people, it is reliable. You just did not think through your application. You could make an extension to Sqlite and implement an sqlite3_last_insert_or_ignore_id function, but to blithely assume that you can use last_insert_id with INSERT OR IGNORE

Re: [sqlite] Race condition -- fixed?

But am I correct in assuming that one way that SQLite provides serializable transactions is by automatically rolling back transactions when necessary (and returning SQLITE_IOERR)? Thanks, - Richard Klein [EMAIL PROTECTED] wrote: Ken [EMAIL PROTECTED] wrote: BEGIN TRANSACTION; SELECT balance

[sqlite] Question on SQL to retrieve ROWIDs

Hello all, I can't seem to figure out a working SQL for the following condition, and I'm not sure if it's possible in SQL at all (but hope it is). Let's say I have a simple schema: CREATE TABLE MyTable ( ID INTEGER PRIMARY KEY, SomeData ) What I'd like to get is 5 records (for example) that

Re: [sqlite] Re: Question on SQL to retrieve ROWIDs

Ah, thank you! I had a similar one but was doing an asc to get the order I wanted, which doesn't make sense - I should sort the results after I get them on my own. Dennis Igor Tandetnik wrote: Dennis Volodomanov info-+Tq6r2yh00lWk0Htik3J/[EMAIL PROTECTED] wrote: Hello all, I can't seem

Re: [sqlite] Race condition -- fixed?

Its up to you to rollback the transaction. It would return a SQLITE_BUSY, not an IOERR. Richard Klein [EMAIL PROTECTED] wrote: But am I correct in assuming that one way that SQLite provides serializable transactions is by automatically rolling back transactions when necessary (and returning

Re: [sqlite] Re: Re: Question on SQL to retrieve ROWIDs

Yes, that looks good as well - thank you for the help! Dennis Igor Tandetnik wrote: Dennis Volodomanov info-+Tq6r2yh00lWk0Htik3J/[EMAIL PROTECTED] wrote: Igor Tandetnik wrote: Dennis Volodomanov info-+Tq6r2yh00lWk0Htik3J/[EMAIL PROTECTED] wrote: Let's say I have a simple schema: CREATE

[sqlite] Re: Re: Question on SQL to retrieve ROWIDs

Dennis Volodomanov info-+Tq6r2yh00lWk0Htik3J/[EMAIL PROTECTED] wrote: Igor Tandetnik wrote: Dennis Volodomanov info-+Tq6r2yh00lWk0Htik3J/[EMAIL PROTECTED] wrote: Let's say I have a simple schema: CREATE TABLE MyTable ( ID INTEGER PRIMARY KEY, SomeData ) What I'd like to get is 5 records (for

Re: [sqlite] Race condition -- fixed?

On 10/29/07, Richard Klein [EMAIL PROTECTED] wrote: But am I correct in assuming that one way that SQLite provides serializable transactions is by automatically rolling back transactions when necessary (and returning SQLITE_IOERR)? No. That will happen in some specific unavoidable cases, but

Re: [sqlite] Performance problem for a simple select with range

Dani Valevski [EMAIL PROTECTED] wrote: I think I have a performance problem for a simple select with range. My Tables: CREATE TABLE locations(locidINTEGER PRIMARY KEY, ...); CREATE TABLE blocks( startIpNum INTEGER, endIpNum INTEGER,

Re: [sqlite] Race condition -- fixed?

Trevor Talbot wrote: On 10/29/07, Richard Klein [EMAIL PROTECTED] wrote: But am I correct in assuming that one way that SQLite provides serializable transactions is by automatically rolling back transactions when necessary (and returning SQLITE_IOERR)? No. That will happen in some specific

Re: [sqlite] Race condition -- fixed?

On 10/29/07, Richard Klein [EMAIL PROTECTED] wrote: Perhaps the best solution is to follow these rules: IF THE TRANSACTION IS A WRITER (i.e. will eventually write to the db): -- (1) Begin the transaction with 'BEGIN

Re: [sqlite] Race condition -- fixed?

I wrote: I would still perform rollbacks for any errors other than the above expected SQLITE_BUSY cases, of course, since they indicate something else went wrong (such as running out of disk space). I think it's safe to say those are all unusual cases though. Hmm,