MY Table is:
"CREATE TABLE ARTIST(ArtistId INTEGER PRIMARY KEY,ArtistName TEXT NOT NULL
COLLATE NOCASE, ArtistTrackCount INTEGER, UNIQUE(ArtistName));"
"CREATE TABLE ALBUM(AlbumId INTEGER PRIMARY KEY,AlbumName TEXT NOT NULL
COLLATE NOCASE,AlbumTrackCount INTEGER,UNIQUE(AlbumName));"
"Mahalakshmi.m"
<[EMAIL PROTECTED]> wrote
in message
news:[EMAIL PROTECTED]
> I am using
> First 10 --> "SELECT * FROM ARTIST ORDER BY ArtistName LIMIT 10 ;"
> Next 10 ie., 11 to 20 --> "SELECT * FROM ARTIST WHERE ArtistName > ?
> ORDER BY ArtistName LIMIT 10 ;"
> Previous 10 -->"SELECT * FROM
any clarifications on the below statements?
-Shailesh
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Shailesh Birari
> Sent: Wednesday, April 16, 2008 11:30 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Transaction
http://sqlite.org/lockingv3.html
http://sqlite.org/sharedcache.html
http://sqlite.org/34to35.html(section 5.0)
I don't see a need to document this as its already done by the above. I think
you've missed the finer points.
See my comments embedded below:
HTH,
Ken
Robert L Cochran wrote:
> Where exactly do I install the sqlite3.exe (or should I use sqlite3.dll)
> file for Microsoft Windows such that the Windows version of PHP 5.x
> will find it and make use of it? Is it sufficient to create a C:\Program
> Files\SQLite3 folder and put the SQLite 3.5.8
Hi All,
Let's say I start a transaction and do bunch of insertions etc. Before my
commit I like to show (select) what I have configured.
How can I accompilish this?
Thanks,
-Alex
___
sqlite-users mailing list
sqlite-users@sqlite.org
Hi,
there is a pragma: PRAGMA read_uncommitted = 1;
You can select the uncommitted data and show ist before commit.
Have a look here: http://www.sqlite.org/pragma.html
Martin
Alex Katebi schrieb:
> Hi All,
>
> Let's say I start a transaction and do bunch of insertions etc. Before my
> commit I
I believe the pragma read_uncommitted only works for shared cache mode and
threads.
You'll have to select the data from the same connection that created the data.
"Martin.Engelschalk" <[EMAIL PROTECTED]> wrote: Hi,
there is a pragma: PRAGMA read_uncommitted = 1;
You can select the
I have been using SQLite for about a year now, and have been extremely
pleased. While testing 3.5.8, I noticed that a SELECT with LEFT OUTER
JOIN seemed to stop using an index, resorting to a (slow) full table
scan. A simple (contrived) example follows:
CREATE TABLE pets (
pet_id
Eric Minbiole wrote:
>
> However, I wanted to let others take a look, to see if the
> issue was with my query (quite possible), or with the new version.
>
This is definitely an issue with the new version. It is doing a nested
table scan instead of using the index for the left join.
Dennis
On Apr 17, 2008, at 12:31 PM, Dennis Cote wrote:
> Eric Minbiole wrote:
>>
>> However, I wanted to let others take a look, to see if the
>> issue was with my query (quite possible), or with the new version.
>>
>
> This is definitely an issue with the new version. It is doing a nested
> table scan
Martin,
Can you give an example on how to use this. Basically I want to see
(select) only the uncommited rows.
Thanks!
-Alex
On Thu, Apr 17, 2008 at 11:58 AM, Ken <[EMAIL PROTECTED]> wrote:
> I believe the pragma read_uncommitted only works for shared cache mode and
> threads.
>
> You'll
On Apr 17, 2008, at 12:04 PM, Eric Minbiole wrote:
> I have been using SQLite for about a year now, and have been extremely
> pleased. While testing 3.5.8, I noticed that a SELECT with LEFT OUTER
> JOIN seemed to stop using an index, resorting to a (slow) full table
> scan. A simple (contrived)
D. Richard Hipp wrote:
>
> Likely this has to do with ticket #3015.
> http://www.sqlite.org/cvstrac/tktview?tn=3015
>
Yes, I agree. It seems like the fix may have been too broad.
The problem with the query in that report was the use of an index on the
left table in the join condition.
In the
Alex Katebi wrote:
>Can you give an example on how to use this. Basically I want to see
> (select) only the uncommited rows.
>
You will have to keep track of the rows that have been changed yourself.
You can have SQLite do it for you if you create a change_log table and
then setup triggers
> Your work-around until I fix this is to say
>
> owners.owner_id = pets.owner_id
>
> instead if what you have. In other words, put the
> table on the left side of the join before the equals
> sign instead of after it.
Good idea: Swapping the terms of the JOIN expression does seem to
Actually I am not interested on rows that have been committed. I am
interested on the rows that have been changed but not commited yet. As I
understand the triggers trigger of of a commit.
The example that you are refering to is for undoing the already commited
rows. I am merely interested in
Is there a way to select rows that have not been committed yet?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
On Apr 17, 2008, at 2:35 PM, Alex Katebi wrote:
> Is there a way to select rows that have not been committed yet?
>
No. SQLite doesn't really commit rows. It commits pages. A
single page might hold multiple rows, only some of which might
have changed. Or a single row might span multiple
I will give a simple example:
create table t1(name);
insert into t1('Alex');
begin;
insert into t1 values ('Dennis');
select * from t1;
The above will show two rows. How can I see only the 'Dennis' row in this
simple example.
On Thu, Apr 17, 2008 at 2:57 PM, Dennis Cote <[EMAIL PROTECTED]>
On Thu, Apr 17, 2008 at 3:15 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:
> I will give a simple example:
>
> create table t1(name);
> insert into t1('Alex');
> begin;
> insert into t1 values ('Dennis');
> select * from t1;
>
> The above will show two rows. How can I see only the 'Dennis' row in
Hello,
In "index using and explain using question", Dennis Cote wrote:
"An EXPLAIN QUERY PLAN returns three columns. The output of explain
query plan is not documented (to the best of my knowledge anyway), but
is fairly self explanatory. It shows the order that tables are scanned
and which
Hi Richard,
create table t1 (name);
insert into t1 values ('Alex');
begin;
insert into t1 values ('Richard');
select * from t1;
How can I select only the second row in the above example?
If there is not an easy way to do this I would probably have to use another
connection then diff the two
Until the data is committed, it's not really in the database. If you
crash, it will be rolled back. So if it's really important to know
what data has been written to the database but not committed, why
don't you just track what you're writing to the database in an
in-memory data structure of
The reason I did not keep track in a seperate table was because I wanted to
do it using triggers. But triggers don't trigger until commit.
On Thu, Apr 17, 2008 at 3:36 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
> Until the data is committed, it's not really in the database. If you
> crash, it
Another approach could be to create an in-memory database (and in in-
memory table, like CREATE TABLE last_transaction(id INTEGER);)
and after each write operation save the rowid of the row using
sqlite3_last_insert_rowid (in C) or using SELECT last_insert_rowid();
(SQL) into that table.
---
I don't mean in a separate database table - I mean in an in-memory
hashtable or array or something of the sort. Depending on what the
real goal you're trying to accomplish is, you might use triggers to
call custom function to accomplish this.
You presumably desire to get this information in the
Alex Katebi wrote:
> But triggers don't trigger until commit.
>
That is not true.
Trigger code executes inline with the statement that caused the trigger
to fire.
Try a few triggers with the command line shell to convince yourself.
Dennis Cote
___
Alex Katebi wrote:
> I will give a simple example:
>
> create table t1(name);
> insert into t1('Alex');
> begin;
> insert into t1 values ('Dennis');
> select * from t1;
>
> How can I see only the 'Dennis' row in this simple example.
>
As I said before, use a trigger to keep track of the
If I do:
sqlite3_exec(..."UPDATE foo..."...)
and then:
numChanges = sqlite3_changes()
I get the number of updated rows back. My question is, if I changed
the statement in the single exec call to "BEGIN IMMEDIATE;UPDATE
foo...;COMMIT", will sqlite3_changes still return the same value as
Petite Abeille wrote:
>
> what's the meaning of the first two numeral columns in
> explain query plan?
>
The columns of the explain query plan output are labeled by SQLite as:
sqlite> .header on
sqlite> .mode column
sqlite> explain query plan select * from t;
order from
My problem is that triggers don't trigger until after commit.
On Thu, Apr 17, 2008 at 4:52 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:
> Alex Katebi wrote:
> > I will give a simple example:
> >
> > create table t1(name);
> > insert into t1('Alex');
> > begin;
> > insert into t1 values ('Dennis');
I remember trying it before but I will try it again. Maybe I was wrong. I
will let you know.
Thanks!
On Thu, Apr 17, 2008 at 4:43 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:
> Alex Katebi wrote:
> > But triggers don't trigger until commit.
> >
>
> That is not true.
>
> Trigger code executes
Alex Katebi wrote:
> My problem is that triggers don't trigger until after commit.
>
No, that is not your problem. You haven't tried anything yet.
This is a trace of SQlite executing the code I posted. There is not a
commit in sight, and yet it works as expected. I added an unqualified
select
Hi Dennis,
I am sorry I was wrong about triggers. My understanding of triggers was
incorrect. Triggers are part of the same connection. I will take your
suggestions. I will let you know.
Thanks so much for helping out!
-Alex
On Thu, Apr 17, 2008 at 6:11 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:
Yap I was wrong about triggers. Triggers are part of the same connection. So
I will try your suggestions. I will let you know how I made out. And thanks
so much for clearing my mistakes.
-Alex
On Thu, Apr 17, 2008 at 6:06 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:
> I remember trying it before
I am glad you asked. I am designing an interactive command line
interface to an ip router. A user will begin a transaction and start
configuring. At any time he can query for his configurations since the
begining of the transaction. When he is satisfied with his configuration he
will commit the
Scott,
Every user will have thier own sqlite connection. So multiple users are
allowed for configuration. There will be one router connection to actually
act on the commited configurations. The router will act on
individual configuration rows. The router and the users interact with each
other
What will happen if you use BEGIN is that multiple users can get into
the configuration mode, but once one user gets past BEGIN and runs
anything which updates the database, the updates in other transactions
will start throwing SQLITE_LOCKED. Spin up two sqlite3 command-line
tools against the
Just to be clear on "try it out", I mean something like the following,
where A) is in one shell, and B) in another.
A) ...> ./sqlite3 test.db
A) sqlite> CREATE TABLE t (id INTEGER AUTOINCREMENT PRIMARY KEY NOT
NULL, config TEXT);
B) ...> ./sqlite3 test.db
B) sqlite> BEGIN;
A) sqlite> BEGIN;
A)
Grr. Copy/paste error. The create statement was:
CREATE TABLE t (id INTEGER PRIMARY KEY AUTOINCREMENT, config TEXT);
On Thu, Apr 17, 2008 at 5:20 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
> Just to be clear on "try it out", I mean something like the following,
> where A) is in one shell, and
I am trying to learn EXPLAIN. I have a table like so with ~184K rows
sqlite> .s
CREATE TABLE sg_rivers (
ogc_fid INTEGER PRIMARY KEY,
wkt_geometry TEXT,
name TEXT,
xmin REAL,
ymin REAL,
xmax REAL,
ymax REAL
);
CREATE INDEX ix_xmax__sg_rivers ON sg_rivers (xmax);
CREATE INDEX
On Apr 18, 2008, at 9:37 AM, P Kishor wrote:
> I am trying to learn EXPLAIN. I have a table like so with ~184K rows
>
> sqlite> .s
> CREATE TABLE sg_rivers (
> ogc_fid INTEGER PRIMARY KEY,
> wkt_geometry TEXT,
> name TEXT,
> xmin REAL,
> ymin REAL,
> xmax REAL,
> ymax REAL
> );
>
43 matches
Mail list logo
