Re: [sqlite] Help speed up query

ribbon campaign against html e-mail /\ www.asciiribbon.org > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of ven...@intouchmi.com > Sent: Monday, 15 April, 2013 15:20 > To: sqlite-users@sqlite.org > Subject: Re: [sq

Re: [sqlite] Help speed up query

SQLite was much slower on many queries AFTER analyze was executed. here are the referenced tables, # of rows, primary key, and additional indexes: Objects = 5495 rows, Primary key=Object_ID, other indexes on Address_ID, Dimension_ID, DisplayFormat_ID, Limit_ID, and Object_ID; DTC_Statuses = 5234

Re: [sqlite] Help speed up query

On 15 Apr 2013, at 9:47pm, ven...@intouchmi.com wrote: > The integrity check came back with OK. Good. > My software was not locked up totally. It did finally come back. For some > reason executing analyze slow other queries way down. I am surprised by anyone reporting that ANALYZE has subst

Re: [sqlite] Help speed up query

I corrected the "DTC" to 'DTC' and undid the analyze since that seemed to be slowing things down quite a bit. Unfortunately, the speed did not improve. Here is the latest explain query plan: 0|0|6|SEARCH TABLE Object_Types USING COVERING INDEX sqlite_autoindex_Object_Types_1 (Object_Type=?) (~1

Re: [sqlite] Help speed up query

The integrity check came back with OK. My software was not locked up totally. It did finally come back. For some reason executing analyze slow other queries way down. Here is the new explain query plan: 0|0|6|SEARCH TABLE Object_Types USING COVERING INDEX sqlite_autoindex_Object_Types_1 (Obje

Re: [sqlite] Help speed up query

On 15 Apr 2013, at 9:40pm, Igor Tandetnik wrote: > On 4/15/2013 4:31 PM, Simon Slavin wrote: >> I also note this: >> >>> WHERE ((([Object_Types].[Object_Type])="DTC")) >> >> This is technically incorrect. It should use single quotes around DTC, not >> double quotes. > > Actually - does any

Re: [sqlite] Help speed up query

On 4/15/2013 4:31 PM, Simon Slavin wrote: I also note this: WHERE ((([Object_Types].[Object_Type])="DTC")) This is technically incorrect. It should use single quotes around DTC, not double quotes. Actually - does any of the tables participating in the statement have a column named DTC, b

Re: [sqlite] Help speed up query

On 15 Apr 2013, at 9:22pm, ven...@intouchmi.com wrote: > This is the create index statement: > CREATE INDEX IF NOT EXISTS > "DTC_Statuses_1_index" ON > "DTC_Statuses" ("Object_ID"); > > For some reason, when I execute analyze, my software locks up somewhere > when running its test queries. I'l

Re: [sqlite] Help speed up query

On Mon, Apr 15, 2013 at 10:04 PM, Dominique Devienne wrote: > On Mon, Apr 15, 2013 at 9:45 PM, wrote: > >> Igor, >> >> As a follow up, I added the other two indexes with no real inprovement. >> Here is the new explain query plan: >> 0|0|6|SEARCH TABLE Object_Types USING COVERING INDEX >> sqlite_a

Re: [sqlite] Help speed up query

This is the create index statement: CREATE INDEX IF NOT EXISTS "DTC_Statuses_1_index" ON "DTC_Statuses" ("Object_ID"); For some reason, when I execute analyze, my software locks up somewhere when running its test queries. I'll delve further into this. Vance on Apr 15, 2013, Igor Tandetnik wro

Re: [sqlite] Help speed up query

Are you sure? The query plan looks like Objects(Object_Type_ID) was added but DTC_Statuses(Object_ID) was not. Igor Tandetnik On 4/15/2013 3:57 PM, ven...@intouchmi.com wrote: DTC_Statuses(Object_ID) and Objects(Object_Type_ID) Vance on Apr 15, 2013, Igor Tandetnik wrote: On 4/15/2013 3:4

Re: [sqlite] Help speed up query

On Mon, Apr 15, 2013 at 9:45 PM, wrote: > Igor, > > As a follow up, I added the other two indexes with no real inprovement. > Here is the new explain query plan: > 0|0|6|SEARCH TABLE Object_Types USING COVERING INDEX > sqlite_autoindex_Object_Types_1 (Object_Type=?) (~1 rows) > 0|1|1|SCAN TABLE D

Re: [sqlite] Help speed up query

DTC_Statuses(Object_ID) and Objects(Object_Type_ID) Vance on Apr 15, 2013, Igor Tandetnik wrote: > >On 4/15/2013 3:45 PM, ven...@intouchmi.com wrote: >> As a follow up, I added the other two indexes with no real inprovement. > >Which two indexes? >-- >Igor Tandetnik > >_

Re: [sqlite] Help speed up query

On 4/15/2013 3:45 PM, ven...@intouchmi.com wrote: As a follow up, I added the other two indexes with no real inprovement. Which two indexes? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailma

Re: [sqlite] Help speed up query

On 4/15/2013 3:27 PM, ven...@intouchmi.com wrote: Here is the result of the explain query plan: 0|0|1|SCAN TABLE DTC_Statuses (~100 rows) 0|1|0|SEARCH TABLE Objects USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|2|6|SEARCH TABLE Object_Types USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|3|

Re: [sqlite] Help speed up query

Igor, As a follow up, I added the other two indexes with no real inprovement. Here is the new explain query plan: 0|0|6|SEARCH TABLE Object_Types USING COVERING INDEX sqlite_autoindex_Object_Types_1 (Object_Type=?) (~1 rows) 0|1|1|SCAN TABLE DTC_Statuses (~100 rows) 0|2|0|SEARCH TABLE Objects

Re: [sqlite] Help speed up query

Thanks Igor! I did create indexes for the SQLite DB version based on the indexes in the MS Access DB. Object_Texttables does have an index on Object_ID and Object_Transactions also has an index on - Object_ID. Just to double check, my index declarations are: CREATE INDEX IF NOT EXISTS "Object_T

Re: [sqlite] Help speed up query

...@intouchmi.com Sent: Monday, April 15, 2013 12:08 PM To: sqlite-users@sqlite.org Subject: [sqlite] Help speed up query Hi, I've been trying to convince my boss that SQLite should be faster than MS Access. I wrote a conversion program that reads a Access DB and generates an SQL statement files and then

Re: [sqlite] Help speed up query

On 4/15/2013 2:20 PM, ven...@intouchmi.com wrote: here is the output from explain: I asked about EXPLAIN QUERY PLAN, not EXPLAIN. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/

Re: [sqlite] Help speed up query

On 4/15/2013 2:12 PM, ven...@intouchmi.com wrote: That's odd, I had listed the tables and keys in the original message. Here is that section again: which generates about 16000 rows. The row counts and primary keys for the referenced tables are: Objects = 5495, Object_ID; DTC_Statuses = 5234, DT

Re: [sqlite] Help speed up query

here is the output from explain: 0|Trace|0|0|0||00| 1|String8|0|1|0|DTC|00| 2|Goto|0|80|0||00| 3|OpenRead|1|17|0|8|00| 4|OpenRead|0|1027|0|10|00| 5|OpenRead|6|1025|0|2|00| 6|OpenRead|7|16|0|2|00| 7|OpenRead|2|170|0|4|00| 8|OpenRead|8|171|0|keyinfo(1,BINARY)|00| 9|OpenRead|3|731|0|6|00| 10|OpenRead

Re: [sqlite] Help speed up query

That's odd, I had listed the tables and keys in the original message. Here is that section again: which generates about 16000 rows. The row counts and primary keys for the referenced tables are: Objects = 5495, Object_ID; DTC_Statuses = 5234, DTC_Status_ID; Object_TextTables = 15718, Object_Trans

Re: [sqlite] Help speed up query

On 4/15/2013 1:07 PM, ven...@intouchmi.com wrote: I've been trying to convince my boss that SQLite should be faster than MS Access. I wrote a conversion program that reads a Access DB and generates an SQL statement files and then sends the table creation and insert statements to SQLite. I also

[sqlite] Help speed up query

Hi, I've been trying to convince my boss that SQLite should be faster than MS Access. I wrote a conversion program that reads a Access DB and generates an SQL statement files and then sends the table creation and insert statements to SQLite. I also wrote a C++ wrapper that I can call from VB6 i

Re: [sqlite] help with select

Yes that works great. Thanks Igor! On 11 February 2013 22:24, Igor Tandetnik wrote: > On 2/11/2013 10:45 AM, e-mail mgbg25171 wrote: > >> Perhaps I'm making a meal of my question... >> "All" I want to do is >> select only records that have field f1 = '' where it's other field f2 >> matches the

Re: [sqlite] help with select

On 2/11/2013 10:45 AM, e-mail mgbg25171 wrote: Perhaps I'm making a meal of my question... "All" I want to do is select only records that have field f1 = '' where it's other field f2 matches the value of f3 only in other records (1 or more) which have a non-'' f1 value. select * from MyTable w

[sqlite] help with select

I think this does it sql_s = "select * from " & _ "(select * from tbl_tv a where new_tnode = '') " & _ "where pnode = 0 or pnode in " & _ "(select tnode from tbl_tv where new_tnode <> '' )" & _ "and anode = 0 or anode in " &

[sqlite] help with select

Perhaps I'm making a meal of my question... "All" I want to do is select only records that have field f1 = '' where it's other field f2 matches the value of f3 only in other records (1 or more) which have a non-'' f1 value. To explain the f1 field shows whether or not the record has been written

[sqlite] help with select

Here's a chopped down query that demonstrates my difficulty do sql_s = "select * from tbl_tv where id in " & _ "(" & _ "select id from tbl_tv where " & _ "new_tnode = '' and " & _ "pnode = 0 or " & _

Re: [sqlite] Help with slow queries

On 6 Feb 2013, at 5:53pm, Jamie Norrish wrote: > On Tue, 2013-02-05 at 22:59 +, Simon Slavin wrote: > >>>(SELECT tn.ngram >>> FROM TextNGram tn, Text t >>> WHERE t.label != Text.label >>> AND t.label != '' >>> AND tn.text = t.id >>>

Re: [sqlite] Help with slow queries

On Tue, 2013-02-05 at 22:59 +, Simon Slavin wrote: > This might be faster if you have an index for TextNGram(text, size, > ngram). Thanks, I'll definitely try that! > > (SELECT tn.ngram > >FROM TextNGram tn, Text t > >WHERE t.label != Text.label > >

Re: [sqlite] Help with slow queries

On 5 Feb 2013, at 10:27pm, Jamie Norrish wrote: > WHERE Text.label IN (%s) > AND TextNGram.size BETWEEN ? AND ? > AND TextNGram.text = Text.id > ... > ORDER BY TextNGram.size, TextNGram.ngram This might be faster if you have an index for TextNGram(text, size, ngram).

[sqlite] Help with slow queries

I would appreciate any help with improving the query times I'm experiencing. The database is composed of n-grams drawn from a set of texts, which are dynamically labelled (some with no label, which are not counted) and differences and intersections are found between the n-grams of each labelled set

Re: [sqlite] Help needed with query to tell a FK from a PK

On 4 January 2013 05:35, Petite Abeille wrote: > > On Jan 3, 2013, at 10:19 AM, nospam.nospam.nos...@gmail.com wrote: > >> I'm trying to create two queries so I can determine which of the keys >> for a given sqlite3 table is the primary key and which keys are >> foreign. > > pragma table_info() re

Re: [sqlite] Help needed with query to tell a FK from a PK

On Jan 3, 2013, at 10:19 AM, nospam.nospam.nos...@gmail.com wrote: > I'm trying to create two queries so I can determine which of the keys > for a given sqlite3 table is the primary key and which keys are > foreign. pragma table_info() returns a 'pk' attribute. You can use that to identify the

Re: [sqlite] Help needed with query to tell a FK from a PK

> Hi, > > I'm a complete sql novice so please go easy :) > > I'm trying to create two queries so I can determine which of the keys > for a given sqlite3 table is the primary key and which keys are > foreign. Hello, I did not fully review your question detail, but perhaps this may help. The MyJSQL

Re: [sqlite] Help needed with query to tell a FK from a PK

nospam.nospam.nos...@gmail.com wrote: > I'm trying to create two queries so I can determine which of the keys > for a given sqlite3 table is the primary key SQLite does not give you this information. PRAGMA index_list gives you only indexes, but if the primary key is the ROWID, there is no separa

[sqlite] Help needed with query to tell a FK from a PK

Hi, I'm a complete sql novice so please go easy :) I'm trying to create two queries so I can determine which of the keys for a given sqlite3 table is the primary key and which keys are foreign. I understand that sqlite's pragmas aren't queryable so I have some code that moves the result set from

Re: [sqlite] Help needed for validating SQLite under Linux/Cygwin

On 11/20/2012 06:42, brijesh_philips wrote: I need to validate few tools including Sqlite on Linux environment. Currently i'm using "cygwin" for validating the tools. Don't do that. Cygwin tries hard to be Linux compatible, but there are several cases where Cygwin simply cannot behave like L

[sqlite] Help needed for validating SQLite under Linux/Cygwin

Hello, I need to validate few tools including Sqlite on Linux environment. Currently i'm using "cygwin" for validating the tools. I have downloaded the latest (3.7.14.1) SQLite amalgamation file from SQLite website and i'm able to create a library out of it. I need guidance/clarification on follo

Re: [sqlite] Help to create view of my data

Daniel Polski wrote: > Clemens Ladisch skrev 2012-11-09 17:00: >> SELECT 'SELECT units.id AS "unit id"' AS SQL >> UNION ALL >> SELECT ', cv' || id || '.value AS ' || quote(name) FROM configtypes >> UNION ALL >> SELECT 'FROM units JOIN unit_types ON units.unit_type_id = unit_types.id' >> UNION ALL >

Re: [sqlite] Help to create view of my data

Simon Slavin skrev 2012-11-09 17:32: In answer to the original query, my instinct is to say that you should be using your own software to collect the results of several SELECTs, not trying to do everything inside a database engine. That's an interesting point. I still think it's the most suit

Re: [sqlite] Help to create view of my data

Clemens Ladisch skrev 2012-11-09 17:00: Thanks alot for the help! :) Are you sure that the normalization hasn't gone a *little* bit too far? Yes, it makes perfect sense in other parts of the application. FOREIGN KEY( unit_id ) REFERENCES unit_types ( id ) Why is this column not called unit

Re: [sqlite] Help to create view of my data

On 9 Nov 2012, at 4:16pm, Clemens Ladisch wrote: > Just use SQL to generate the SQL query: > > SELECT 'SELECT units.id AS "unit id"' AS SQL > UNION ALL > SELECT ', cv' || id || '.value AS ' || quote(name) FROM configtypes > UNION ALL > SELECT 'FROM units JOIN unit_types ON units.unit_type_id =

Re: [sqlite] Help to create view of my data

Clemens Ladisch wrote: > it is not possible to generate columns automatically from data values. "All problems in computer science can be solved by another level of indirection." (David Wheeler) Just use SQL to generate the SQL query: SELECT 'SELECT units.id AS "unit id"' AS SQL UNION ALL SELECT

Re: [sqlite] Help to create view of my data

Daniel Polski wrote: > I'm redesigning parts of my database to normalize it better [...] > The SELECT query should return all units, and for each configuration > option it should return either the stored value (if available) or null > if no such option exist for the unit. SQLite does not have pivo

[sqlite] Help to create view of my data

Hello, I'm redesigning parts of my database to normalize it better and to make a more general solution available for other parts of the system. My goal now is to create a view which will let me do a select in the style of: SELECT (unit id) (general option value) (color value) (number value)

Re: [sqlite] Help: database/disk full in creating index

Thank you Simon! >Can you explain what SQLite calls you are using to do this ? Do you mean you >are using a CREATE INDEX command ? Yes indeed. The command reads: "CREATE INDEX data_idx ON data (key ASC);" >Also, can you execute "PRAGMA journal_mode" on your database and tell us the >result ?

Re: [sqlite] Help: database/disk full in creating index

On Mon, Jul 23, 2012 at 5:24 PM, Wang, Gao wrote: > 2) is there a way to re-set such a tmp directory path? > export TMPDIR=/new/temporary/dir/with-lots-of-space -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Help: database/disk full in creating index

On 23 Jul 2012, at 10:24pm, "Wang, Gao" wrote: > I am having problems creating index for my database. Can you explain what SQLite calls you are using to do this ? Do you mean you are using a CREATE INDEX command ? Also, can you execute "PRAGMA journal_mode" on your database and tell us the

Re: [sqlite] Help: database/disk full in creating index

Dear sqlite experts, I am having problems creating index for my database. The error says: "database or disk is full", although I am sure there is sufficient space left on the disk where the database is located. However the Linux /tmp and /var/tmp folders are mounted to a 40GB small disk (my un-ind

Re: [sqlite] help

> 11       COMMENT         VARCHAR(150)       0                            0 > > So it is possible: ? > UPDATE t1 SET comment = 'here the text of 160 characters long' Yes, that's possible. You can put text of any length into VARCHAR or TEXT field no matter what size you declare for it (you can eve

Re: [sqlite] Help

23 апреля 2012 г. 15:07 пользователь Ковригина Ольга: > From Russian developer you respect!  =) куда же без них ;) -- Regards,   niXman ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Help

Dear,Simon  James! Thank you very much. Worked!Similarly, the type has no size! From Russian developer you respect!  =) 2012/4/23 Ковригина Ольга : > Hello,support! > Tell me how to resize columns in a table? > I do like this: > ALTER TABLE t1 MODIFY (c1 TYPE VARCHAR(2500)); > or > ALTER TABLE t

[sqlite] Help

2012/4/23 Ковригина Ольга : > Hello,support! > Tell me how to resize columns in a table? > I do like this: > ALTER TABLE t1 MODIFY (c1 TYPE VARCHAR(2500)); > or > ALTER TABLE t1 ALTER (c1 TYPE VARCHAR(2500)); > But it does not work. >   The column does not have a size: http://www.sqlite.org/datatyp

[sqlite] help

Mon, 23 Apr 2012 11:33:47 +0100 от Simon Davies : > 2012/4/23 Ковригина Ольга : > > Hello,support! > > Tell me how to resize columns in a table? > > I do like this: > > ALTER TABLE t1 MODIFY (c1 TYPE VARCHAR(2500)); > > or > > ALTER TABLE t1 ALTER (c1 TYPE VARCHAR(2500)); > > But it does not work.

Re: [sqlite] help

2012/4/23 Ковригина Ольга : > Hello,support! > Tell me how to resize columns in a table? > I do like this: > ALTER TABLE t1 MODIFY (c1 TYPE VARCHAR(2500)); > or > ALTER TABLE t1 ALTER (c1 TYPE VARCHAR(2500)); > But it does not work. > The column does not have a size: http://www.sqlite.org/datatype

Re: [sqlite] Help me about sqlite3_open(),please!

黃楨民 wrote: > 1. Will SQLite run sqlite3_open_v2 if I call sqlite3_open with 4 > arguments ? I mean, Did SQLite pick up the interface depends on number of > parameter ? Your compiler won't let you pass four arguments to a function delcared with two, whether this function is part of SQLite A

[sqlite] Help me about sqlite3_open(),please!

Dear all: 1. Will SQLite run sqlite3_open_v2 if I call sqlite3_open with 4 arguments ? I mean, Did SQLite pick up the interface depends on number of parameter ? 2. How to assign flag parameter in sqlite3_open_v2() with combination of 『SQLITE_OPEN_READWRITE

Re: [sqlite] Help request for a query...

Igor Tandetnik wrote: > Roger Andersson wrote: >> This seems to work but I do not fully understand why ;-) >> sqlite> .header on >> sqlite> CREATE TABLE IF NOT EXISTS History (Path TEXT UNIQUE NOT NULL, >> Hits INTEGER); >> sqlite> insert or replace into History (Path, Hits) VALUES ('c:\', >> (se

Re: [sqlite] Help request for a query...

On 02/19/12 23:04, Igor Tandetnik wrote: The same can be achieved in a less convoluted manner: insert or replace into History (Path, Hits) VALUES ('c:\', (select ifnull(Hits, 0) + 1 from History where path='c:\')); Thanks Igor! -- Roger ___ sqlite-

Re: [sqlite] Help request for a query...

Roger Andersson wrote: > This seems to work but I do not fully understand why ;-) > sqlite> .header on > sqlite> CREATE TABLE IF NOT EXISTS History (Path TEXT UNIQUE NOT NULL, > Hits INTEGER); > sqlite> insert or replace into History (Path, Hits) VALUES ('c:\', > (select count(*)+ifnull(Hits,1) fr

Re: [sqlite] Help with CHECK Constraint

On Feb 19, 2012, at 6:40 PM, Pete wrote: > I'm > really asking the general question what is possible within the CHECK > constraint? An expression: http://www.sqlite.org/lang_expr.html > Is it possible to base the check on a SELECT statement on > another table? Nope. No (sub)queries. E.g.:

Re: [sqlite] Help with CHECK Constraint

f the column is On Sun, Feb 19, 2012 at 9:00 AM, wrote: > Message: 2 > Date: Sun, 19 Feb 2012 01:29:52 +0100 > From: Petite Abeille > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Help with CHECK Constraint > Message-ID: <92bf7b2e-fe21-4cd3-a69c-44573b6

Re: [sqlite] Help request for a query...

On Feb 19, 2012, at 6:16 PM, Roger Andersson wrote: > insert or replace One thing to keep in mind when using "insert or replace" is that this will create an entirely new record each and every single time. Which means the rowid is always going to change. Which makes it a very poor candidate as

Re: [sqlite] Help request for a query...

On 02/19/12 16:59, Jörgen Hägglund wrote: Hi all! I'm not sure if it's my brain melting or what it might be... :-) I have a table defined as: CREATE TABLE IF NOT EXISTS History (Path TEXT UNIQUE NOT NULL, Hits INTEGER DEFAULT 0) Is there any way of making inserting data as follows; - If Path d

Re: [sqlite] Help request for a query...

On Feb 19, 2012, at 4:59 PM, Jörgen Hägglund wrote: > INSERT INTO History VALUES ('c:\', 1) > UPDATE History SET Hits = Hits + 1 WHERE Path = 'c:\' Short of a merge statement, which SQLite lacks, you will indeed need to use two statements. For example, you could turn your first insert stateme

[sqlite] Help request for a query...

Hi all! I'm not sure if it's my brain melting or what it might be... :-) I have a table defined as: CREATE TABLE IF NOT EXISTS History (Path TEXT UNIQUE NOT NULL, Hits INTEGER DEFAULT 0) Is there any way of making inserting data as follows; - If Path does not exist in the table; INSERT INTO His

Re: [sqlite] Help with CHECK Constraint

On Feb 19, 2012, at 1:24 AM, Pete wrote: > is it possible to > check if the value of Col1 exists in a column in a different table? Perhaps you are looking for foreign constraints: http://www.sqlite.org/foreignkeys.html ___ sqlite-users mailing list s

[sqlite] Help with CHECK Constraint

I;m trying to figure out just what I can do within the limits of a CHECK constraint. Is it possible to check the value of the column containing the CHECK constraint for presence in a column in a different table. For example, assuming the CHECK constraint is on TableA.Col1, is it possible to check

Re: [sqlite] Help with an SQL statement

Problem solved: a). I was using an old test directory with version 3.5.4 of sqlite3.exe, hence the errors in the condition (I suppose). b) Once updated to the 3.7.9 version, and went away the syntax errors, I mistakenly believed that the sentence did not produce any results because the dBase f

[sqlite] Help with an SQL statement

Hi all: Assuming a normal table: CREATE TABLE names (Id INTEGER PRIMARY KEY, Nm INTEGER); And an FTS4 table: CREATE VIRTUAL TABLE fts USING fts4 (name, tokenize=simple); I want populate the FTS4 table from the content of the names table using the second form of the INSERT statement: INSERT I

Re: [sqlite] Help. I change a row from one process, second process doesn't see it.

Worked like a charm :D Thank you again! Ale 2011/11/28 Alejandro Martínez : > Ok! > > I'll check all this points. > > Thank you all a lot. This is my first time working with sqlite and it > is so great finding out that it's got such an active and friendly > community. > > Cheers! > > > > On Mon,

Re: [sqlite] Help. I change a row from one process, second process doesn't see it.

Ok! I'll check all this points. Thank you all a lot. This is my first time working with sqlite and it is so great finding out that it's got such an active and friendly community. Cheers! On Mon, Nov 28, 2011 at 3:24 PM, Pavel Ivanov wrote: >> The thing is, it seems this "readers" don't ever

Re: [sqlite] Help. I change a row from one process, second process doesn't see it.

On 11/28/2011 12:21 PM, Alejandro Martínez wrote: The reader processes create all the papared statements at startup, so they would be parsed only once. then, whenever they need to read from the db, they just reset, bind and step the prepared statements i already had. Do it in a different order

Re: [sqlite] Help. I change a row from one process, second process doesn't see it.

> The thing is, it seems this "readers" don't ever see any changes in > the db unless i shut them down and run them again. > But connecting with the sqlite3 shell shows the changes in the db as > they happen. The above two facts suggest that your "readers" do not close their reading transactions

Re: [sqlite] Help. I change a row from one process, second process doesn't see it.

On 11/28/2011 12:10 PM, Alejandro Martínez wrote: I'd appreciate some direction on how to make this work. I have one process that writes on a database. It "realoads" tables by filling a new table and then replacing the old one via a drop and a rename within a transaction. Then there are several

Re: [sqlite] Help. I change a row from one process, second process doesn't see it.

The reader processes create all the papared statements at startup, so they would be parsed only once. then, whenever they need to read from the db, they just reset, bind and step the prepared statements i already had. Is that wrong? On Mon, Nov 28, 2011 at 3:15 PM, Simon Slavin wrote: > > On

Re: [sqlite] Help. I change a row from one process, second process doesn't see it.

On 28 Nov 2011, at 5:10pm, Alejandro Martínez wrote: > I have one process that writes on a database. It "realoads" tables by > filling a new table and then replacing the old one via a drop and a > rename within a transaction. > > Then there are several processes that just read from this database

[sqlite] Help. I change a row from one process, second process doesn't see it.

I'd appreciate some direction on how to make this work. I have one process that writes on a database. It "realoads" tables by filling a new table and then replacing the old one via a drop and a rename within a transaction. Then there are several processes that just read from this database. I don

Re: [sqlite] Help with sqlite3OsRead - porting to a new OS

Also, if this is debugger output (as it appears), it could be that an optimizer is confusing the parameters. I see that all the time on GCC i386 when I set a breakpoint at the start of a function. --David David Garfield writes: > Sounds like it could be a difference in calling convention... Che

Re: [sqlite] Help with sqlite3OsRead - porting to a new OS

Sounds like it could be a difference in calling convention... Check compile options and function declaration modifiers. --David Stuart Thomson writes: > Hi, > > I'm in the middle of porting sqlite3 to a new Operating System and have come > across a problem with the sqlite3OsRead function in th

[sqlite] Help with sqlite3OsRead - porting to a new OS

Hi, I'm in the middle of porting sqlite3 to a new Operating System and have come across a problem with the sqlite3OsRead function in the sqlite3PagerReadFileheader function body. Before the sqlite3OsRead function is called, the parameters are correctly populated for example: pPager->fd

[sqlite] Help please. Create and open encrypted sqlite database in java

Hello blogger, I want to create encrypted sqlite database in java. I don't know whether it is possible or not. There is not much details about this in the net. I tried SQLite.Database db = new SQLite.Database(); try { db.key("Steer@123"); db.open("db3", 0666);

Re: [sqlite] help with a complicated join of two tables

On Sep 14, 2011, at 8:40 PM, Igor Tandetnik wrote: > Think about it this way. You have a phone book, where names are sorted by > last name, then first name. You want to find all people whose last name is > greater than 'Smith' and first name less than 'John'. The alphabetic order > helps you w

Re: [sqlite] help with a complicated join of two tables

On 9/14/2011 2:07 PM, Jan Hudec wrote: On Sep 12, 2011, at 6:51 AM, Igor Tandetnik wrote: select geo.id, min_age, max_age, age_bottom, age_top, name, color >from geo left join intervals i on i.id = ( select id from intervals where age_bottom>= (select age_bottom from intervals

Re: [sqlite] help with a complicated join of two tables

On Mon, Sep 12, 2011 at 12:16:55 -0400, Igor Tandetnik wrote: > On 9/12/2011 12:02 PM, Mr. Puneet Kishor wrote: > > > >On Sep 12, 2011, at 6:51 AM, Igor Tandetnik wrote: > >>Something like this: > >> > >>select geo.id, min_age, max_age, age_bottom, age_top, name, color > >>from geo left join interv

Re: [sqlite] help to get the value of file change counter

...@sqlite.org] On Behalf Of Akash Agrawal Sent: Tuesday, September 13, 2011 9:08 AM To: General Discussion of SQLite Database; Simon Slavin Subject: Re: [sqlite] help to get the value of file change counter hii thank you for suggestion. but when reading the value from sqlite header it printing same

Re: [sqlite] help to get the value of file change counter

hii thank you for suggestion. but when reading the value from sqlite header it printing same value no change i can see i pasting the c++ code below /CODE/ #include #include using namespace st

Re: [sqlite] help with a complicated join of two tables

On 9/12/2011 12:02 PM, Mr. Puneet Kishor wrote: On Sep 12, 2011, at 6:51 AM, Igor Tandetnik wrote: Something like this: select geo.id, min_age, max_age, age_bottom, age_top, name, color from geo left join intervals i on i.id = ( select id from intervals where age_bottom>= (sele

Re: [sqlite] help with a complicated join of two tables

On Sep 12, 2011, at 6:51 AM, Igor Tandetnik wrote: > Mr. Puneet Kishor wrote: The table geo can also have rows with min_age = max_age. I want a result set with geo.id, min_age, max_age, age_bottom, age_top, name, color like so: - every row should be for one and only on

Re: [sqlite] help to get the value of file change counter

On 12 Sep 2011, at 1:35pm, Richard Hipp wrote: > On Mon, Sep 12, 2011 at 8:02 AM, Igor Tandetnik wrote: > >> Simon Slavin wrote: >>> >>> >>> int sqlite3_total_changes(sqlite3*); >>> >>> My understanding (which might be wrong) is that this coun

Re: [sqlite] help to get the value of file change counter

On Mon, Sep 12, 2011 at 8:02 AM, Igor Tandetnik wrote: > Simon Slavin wrote: > > > > > > int sqlite3_total_changes(sqlite3*); > > > > My understanding (which might be wrong) is that this count includes all > changes made by all connections to that

Re: [sqlite] help to get the value of file change counter

Simon Slavin wrote: > > > int sqlite3_total_changes(sqlite3*); > > My understanding (which might be wrong) is that this count includes all > changes made by all connections to that database: not > only changes made using your connection but also

Re: [sqlite] help with a complicated join of two tables

Mr. Puneet Kishor wrote: >>> The table geo can also have rows with min_age = max_age. I want a result >>> set with geo.id, min_age, max_age, age_bottom, age_top, >>> name, color like so: >>> >>> - every row should be for one and only one geo record. I have 39K rows in >>> "geo" table, so the re

Re: [sqlite] help to get the value of file change counter

this is very helpful for me thank you -- Regards Akash Agrawal On Mon, Sep 12, 2011 at 12:39 PM, Simon Slavin wrote: > > On 12 Sep 2011, at 5:08am, Akash Agrawal wrote: > > > I'd like to be able to tell whether a SQLite database file has been > updated > > in any way. How would I go about imp

Re: [sqlite] help to get the value of file change counter

On 12 Sep 2011, at 5:08am, Akash Agrawal wrote: > I'd like to be able to tell whether a SQLite database file has been updated > in any way. How would I go about implementing that? > > I find about some file change counter but how i will get the value of file > change counter through C program <

[sqlite] help to get the value of file change counter

Hello, I'd like to be able to tell whether a SQLite database file has been updated in any way. How would I go about implementing that? I find about some file change counter but how i will get the value of file change counter through C program -- Akash Agrawal _

Re: [sqlite] help with a complicated join of two tables

On Sep 11, 2011, at 9:58 PM, Igor Tandetnik wrote: > Mr. Puneet Kishor wrote: >> geo table: 39K rows >> id max_age min_age >> --- --- >> 1 Holocene Holocene >> 5 Cambrian Silurian >> 12 Cambrian Ordovician >> 229 Cretaceous Quaternary >> >> intervals table: ~450 rows >> id age_bott

Re: [sqlite] help with a complicated join of two tables

Mr. Puneet Kishor wrote: > geo table: 39K rows > id max_age min_age > --- --- > 1 Holocene Holocene > 5 Cambrian Silurian > 12 Cambrian Ordovician > 229 Cretaceous Quaternary > > intervals table: ~450 rows > id age_bottom age_top name color > --- -- --- --- -

<    1   2   3   4   5   6   7   8   9   >