Re: [sqlite] An interesting (strange) issue with selects

On 30/06/2012 8:22 PM, Kees Nuyt wrote: Mind you, there is a difference between the "special folders" (=symbolic path names) : AppDataFolder = Full path to the Roaming folder for the current user and CommonAppDataFolder = Full path to application data for all users. and

Re: [sqlite] An interesting (strange) issue with selects [solved]

On 30/06/2012 7:47 PM, Marcus Grimm wrote: Here is another theory: Maybe you run into an issue with Windows 7 Virtualization, I did run into a similar effect with the registry access/write sometime go - mainly with Windows 7 Home Editions, by reading this:

Re: [sqlite] An interesting (strange) issue with selects

On 30/06/2012 12:57 PM, Kees Nuyt wrote: Is the database file in a protected folder (that is, "\Program Files", or somewhere in the Windows system software tree) ? It shouldn't be. Data belongs somewhere else. Either in your userprofile/appdata or in a completely separate dirtree that

Re: [sqlite] An interesting (strange) issue with selects

On 30/06/2012 10:32 AM, Donald Griggs wrote: Regarding: Could it be that the .ext is used by the OS or other apps with some caching scheme? Well, this symptom is so amazingly strange, it undeniably belongs in the Ext Files. (To those outside the U.S -- this is just a joke on

Re: [sqlite] An interesting (strange) issue with selects

Never heard about tunnelling before this, but I tried to turn it off and it has no effect. I've also (numerously) deleted the whole folder and created it from scratch and I'd still get that contents. Interestingly, I now keep getting a different contents than before - it's now from the last

Re: [sqlite] An interesting (strange) issue with selects

On 30/06/2012 12:19 AM, Black, Michael (IS) wrote: It persists across a reboot? You can create a database, delete it, reboot, and your app will still see the original table? All I can say is wow...your system is really hosed. Even anti-virus shouldn't cause that. This would infer some

Re: [sqlite] An interesting (strange) issue with selects

To further confuse things: I've deleted all files from that old folder. Then using the shell: "sqlite3 mydb.ext" and ".dump" - shows the usual rubbish. The interesting bit here now is that there is no "mydb.ext" file in that folder (checked using a second command prompt), but there are

Re: [sqlite] An interesting (strange) issue with selects

- *From:* sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Dennis Volodomanov [i...@psunrise.com] *Sent:* Friday, June 29, 2012 6:47 AM *To:* General Discussion of SQLite Database *Subject:* EXT :Re: [sqlite] An interesting (strange) issue with s

Re: [sqlite] An interesting (strange) issue with selects

On 29/06/2012 9:52 PM, Simon Slavin wrote: When you're deleting the database file, look for any other files in the same folder with names that start with "mydbname" and anything after that, including any extension. (Marcus's theory) Tell us what they're called. Make sure you're quitting the

Re: [sqlite] An interesting (strange) issue with selects

On 29/06/2012 9:45 PM, Marcus Grimm wrote: Just a quess: Are you using wal mode ? And how to you "delete" the DB ? Are you removing also any journal or -wal files (if any) ? Yes, WAL mode and I do clear out all files. Dennis ___ sqlite-users

Re: [sqlite] An interesting (strange) issue with selects

Ok, tried using the shell and the result is the same as using my app. What I did is: 1) delete the database file ("mydbname.ext") from the DOS prompt, make sure the file is not there 2) run "sqlite3 .\mydbname.ext" from within that same folder, same DOS prompt 3) type .dump 4) see entries in

Re: [sqlite] An interesting (strange) issue with selects

On 29/06/2012 9:25 PM, Simon Slavin wrote: Your app or the shell tool is running while you delete the file, or do you quit, delete, then restart them ? When you specify the database file to open are you specifying a full path, from the 'C:\' on down, or are you relying on some default folder

Re: [sqlite] An interesting (strange) issue with selects

Ok, back on this topic. I've discovered one issue that is possibly the cause of this whole weirdness, but I can't explain what's going on. Perhaps someone has seen this happen. What I found is that after I delete the database file (using Windows explorer or from the command-line, doesn't

Re: [sqlite] An interesting (strange) issue with selects

On 24/06/2012 12:55 PM, Simon Slavin wrote: Depending on the order and timing of how the two threads/processes run, something will eventually happen to your second writer. It will probably reach whatever timeout you've set (which defaults to zero) and then return a result code indicating

Re: [sqlite] An interesting (strange) issue with selects

On 24/06/2012 12:29 PM, Pavel Ivanov wrote: AFAIK, checkpoints are application-specific, but SQLite prohibits second writer until first one committed its transaction and released database lock. So there can't be such thing as "two writers, both writing to the same DB". If one writer writes,

Re: [sqlite] An interesting (strange) issue with selects

On 24/06/2012 11:38 AM, Pavel Ivanov wrote: Such thing shouldn't ever happen, otherwise SQLite has a serious bug. Pavel It could be just my code of course. I guess I need to write a simple console app that simulates this to see if this guess is valid or not in the first place. It does

Re: [sqlite] An interesting (strange) issue with selects

On 22/06/2012 9:48 AM, Dennis Volodomanov wrote: I'll see if the new compilation options still make this happen, but it takes a couple of hours for each test due to data volume and I'd need to run a few tests (unless it occurs right away of course). I'll post back. This hasn't occurred yet

Re: [sqlite] An interesting (strange) issue with selects

On 22/06/2012 9:33 AM, Pavel Ivanov wrote: I believe result of integrity_check won't depend on compilation flags, at least not on those you define. Maybe there's some bug surfacing when you use those compilation flags... Pavel I've also removed all transactions from the latest code. There

Re: [sqlite] An interesting (strange) issue with selects

On 22/06/2012 2:15 AM, Pavel Ivanov wrote: Then it should be okay to do such queries concurrently. So you are saying that two SELECTs you initially showed us give contradicting results from your application and work as expected from sqlite3 shell, right? The only ideas I have left to try are

Re: [sqlite] An interesting (strange) issue with selects

On 22/06/2012 1:37 AM, Dennis Volodomanov wrote: On 22/06/2012 1:29 AM, Pavel Ivanov wrote: Do you have mutex surrounding statement execution in these threads? You should use it or compile with SQLITE_THREADSAFE=1. Pavel No, I don't have mutexes for those, as I assumed that THREADSAFE=2

Re: [sqlite] An interesting (strange) issue with selects

On 22/06/2012 1:29 AM, Pavel Ivanov wrote: Do you have mutex surrounding statement execution in these threads? You should use it or compile with SQLITE_THREADSAFE=1. Pavel No, I don't have mutexes for those, as I assumed that THREADSAFE=2 would protect them. I'm not clear about the exact

Re: [sqlite] An interesting (strange) issue with selects

On 22/06/2012 12:57 AM, Pavel Ivanov wrote: OK. And now when you execute the original statements (SELECT COUNT(...) and SELECT ... WHERE ColC = 0) in SQLiteStudio and sqlite3 shell you still get the same results? 1 in the first statement and no rows in the second one? Pavel No, I get

Re: [sqlite] An interesting (strange) issue with selects

On 22/06/2012 12:30 AM, Pavel Ivanov wrote: Maybe SQLiteStudio, sqlite3 shell and your app don't use the same database file? I don't think there's any sane reason for SQLiteStudio to convert 0 to 64. Pavel On Thu, Jun 21, 2012 at 10:22 AM, Dennis Volodomanov <i...@psunrise.com>

Re: [sqlite] An interesting (strange) issue with selects

On 22/06/2012 12:00 AM, Pavel Ivanov wrote: You apparently executed above query on different dataset than you initially posted. 64 is not something quote(ColA) can return when column contains integer value 1 (and I guess Richard meant you should execute that query for ColC, not ColA). So

Re: [sqlite] An interesting (strange) issue with selects

On 22/06/2012 12:02 AM, Black, Michael (IS) wrote: Are you multi-threaded? It sounds like the database is being changed during your run...how is that being done? Inside your program? Are any deletes being done? Yes, the application is multi-threaded, but at this point, there's only

Re: [sqlite] EXT : An interesting (strange) issue with selects

On 21/06/2012 11:47 PM, Simon Slavin wrote: Are you examining the result code returned by the SELECT commands ? I bet in the cases where the select gets zero rows, it isn't returning SQLITE_OK but it's instead returning some sort of error code. Simon. Hi Simon, Yes, I do examine those and

Re: [sqlite] EXT : An interesting (strange) issue with selects

Michael, Thanks for the reply. I know, it's usually the user :) On 21/06/2012 11:31 PM, Black, Michael (IS) wrote: You don't show where you inserted your data. Are you postiive ColC is an integer and you didn't insert it as a string? You don't show a dump of your table which would be handy.

Re: [sqlite] An interesting (strange) issue with selects

On 21/06/2012 11:23 PM, Richard Hipp wrote: Looks like your data is a string: ' 0' - that is a space followed by ascii '0'. That is different from a numeric 0, so the second query should return zero rows. What does this show: SELECT typeof(ColA), quote(ColA) FROM

Re: [sqlite] An interesting (strange) issue with selects

On 21/06/2012 11:15 PM, Dennis Volodomanov wrote: SELECT ColA FROM CriteriaItemsToProcess WHERE ColC=0 LIMIT 1; Of course the table name here should be TableA (shortened for convenience). Dennis ___ sqlite-users mailing list sqlite-users

[sqlite] An interesting (strange) issue with selects

Hello all, I've been using SQLite for quite a few years, but have just recently started exploring WAL mode (may or may not be related to WAL) and I'm experiencing an interesting issue that perhaps is known to others, so I've decided to ask for your wisdom. This is the amalgamation 3.7.13.

[sqlite] Datatypes wiki note

Hello all, A small note - on this page: http://sqlite.org/datatype3.html it says CAST(expr TO type), but it should be CAST(expr AS type), right? Best regards, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Foreign keys question

> No performance gain for joins or anything like that. Using FK > constraints does not affect SELECT performance. They slow down > some INSERT/UPDATE/DELETE operations though. Thank you! I've done some timings as well (it takes a few hours to build a sizeable database) and found out pretty much

Re: [sqlite] Foreign keys question

Dan Kennedy wrote: > I don't think the triggers you are using can be implemented using > foreign keys. Your triggers are basically reference counting (or > garbage collecting, whatever you want to call it) - "when the > number of references to a data item drops to zero, delete the > data item". >

Re: [sqlite] Maintaining data and foreign keys question

Sorry for the double-post, I didn't see the first one appear for a bit and I assumed it didn't go out. Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] One more SQL statement question

> The not-in subselect could be expensive, depending on table size. If > it turns out to be expensive, you might consider reference counting. Thank you for the hint. It's done on an integer column with an index, so hopefully won't be too bad. Further tests will show though of course. Dennis

Re: [sqlite] One more SQL statement question

> sqlite> create trigger UpdateData after update on tablea begin >...> delete from tableb where id=old.refb and id not in ( select > refb from tablea ); Yep, that's exactly what I need. Thank you! Dennis ___ sqlite-users mailing list

Re: [sqlite] One more SQL statement question

> This statement makes no sense to me. Why not simply > > delete from TABLEB where ID=old.REFB and ID != new.REFB; But entries in TABLEB may be used by other entries in TABLEA and I don't want them deleted if they're used by at least one entry in TABLEA. Thanks, Dennis

[sqlite] One more SQL statement question

Hello, I have one more SQL query question - the people on this list have been very helpful in the past, so thank you! I'm trying to create a trigger that would delete unreferenced rows from a table. Let's say the schema is like this: TABLEA ( ID INTEGER PRIMARY KEY, REFB INTEGER ); TABLEB (

Re: [sqlite] Redundant conditions - are they parsed out?

> > Not sure how to test it exactly at the moment > > You run the query with the redundancy, and the equivalent query > without, > and time them. Run the same query many times in a loop if a single > execution is too fast to measure. Yes, that's the easy way out if I hard-code one query and make

Re: [sqlite] Redundant conditions - are they parsed out?

> In my experience, SQLite doesn't eliminate such redundant clauses, nor > remove trivial conditions (like 1=1 or 1 != 0), nor shortcircuit > logical > operations, nor hoist common subexpressions (those that don't depend on > the current row) out of the loop. An expression is translated into VDBE

[sqlite] Redundant conditions - are they parsed out?

Hello all, I was wondering whether the SQLite parser will parse out redundant conditions out of an SQL statement or will it process them as if they are all unique? I'm building dynamic SQL statements and sometimes I can't avoid (well, without re-working a lot of code) things like: SELECT *

Re: [sqlite] Asking for SQL statement help

> SELECT * FROM TABLEB WHERE ID IN ( > SELECT IDB FROM TABLEA WHERE IDC = 1 AND IDD IN (1, 3) > ) Yes, I think you are right - I'll do some extensive testing of course, but it looks good logically. Thank you! Dennis ___ sqlite-users mailing

Re: [sqlite] Asking for SQL statement help

Thank you for the quick replies and sorry for not being too clear. I will try to state the problem more clearly, without my own attempts to solve it, as they are incorrect anyway. The simplified schemas again: CREATE TABLE TABLEA ( ID INTEGER PRIMARY KEY, IDB INTEGER, IDC INTEGER, IDD INTEGER

[sqlite] Asking for SQL statement help

Hello all, A small SQL problem, no doubt, for experts here. Let's say we've got 4 tables: CREATE TABLE TABLEA ( ID INTEGER PRIMARY KEY, IDB INTEGER, IDC INTEGER, IDD INTEGER ); CREATE TABLE TABLEB ( ID INTEGER PRIMARY KEY, DATA ); CREATE TABLE TABLEC ( ID INTEGER PRIMARY KEY, DATA ); CREATE

[sqlite] Conditional triggers

Hello all, Is it possible to create such an AFTER INSERT trigger that updates certain fields in a table based on the actual data being inserted? Let's say: CREATE TABLE abc(TypeID INTEGER) CREATE TABLE abcCount(TypeCountA, TypeCountB) CREATE TRIGGER CountTypeA AFTER INSERT ON abc /* when

Re: [sqlite] Conditional triggers

> create trigger CountType after insert on abc > begin > update abcCount set > TypeCountA = TypeCountA + (new.TypeID = 1), > TypeCountB = TypeCountB + (new.TypeID = 2); > end; Thank you - that looks great! Dennis ___

Re: [sqlite] Conditional triggers

> CREATE TRIGGER countOfTypesInabc AFTER INSERT ON abc > BEGIN > UPDATE countOfTypesInabc SET count = count+1 WHERE TypeID = > new.TypeID; > END I understand now what you meant - yes, that makes sense and is probably the easiest way to go. > You will have to add something for

Re: [sqlite] Conditional triggers

> Instead of keeping your total in abcCount, make another table > especially for counts > > countType number > abc 27 > def 14 > > and use the TRIGGER to update that table. I could do that, but I still don't know when to update abc and when to update def, based on the

[sqlite] Conditional triggers

Hello all, Is it possible to create such an AFTER INSERT trigger that updates certain fields in a table based on the actual data being inserted? Let's say: CREATE TABLE abc(TypeID INTEGER) CREATE TABLE abcCount(TypeCountA, TypeCountB) CREATE TRIGGER CountTypeA AFTER INSERT ON abc /* when

Re: [sqlite] Replying to posts (was: plans for completion of INSERT ORREPLACE INTO?)

> > He didn't make up the rule. Nor did I. It's part of the standard > > for mailing lists and usenet: > > > > To be fair, there's no such thing as a "standard" in this matter - the very first paragraph of that document says so. Each mailing list has its

Re: [sqlite] SQL question - not sure if it's possible at all

Igor Tandetnik wrote: > Well, there's no way to tell how records with the same Date are going to > end up ordered in the original query. If you impose some deterministic > order there, e.g. with > > ORDER BY Date, Id > > then you can do something like this: > > select count(*) > from Data d,

Re: [sqlite] SQL question - not sure if it's possible at all

Igor Tandetnik wrote: >> and let's say, "Data" has an "ID" field (primary index). >> >> Is it possible to find out the *position* in the returned result set >> of a Data with a given ID? >> > > select count(*) from Data > where PlotOnGraph=1 > and Date < (select Date from Data where

[sqlite] SQL question - not sure if it's possible at all

Hello all, I'm not sure if this can be done in SQL, but if it can, I'd appreciate your help. Let's say I've got this query: SELECT * FROM (SELECT * FROM Data WHERE PlotOnGraph=1 ORDER BY Date ASC) LIMIT ?2 OFFSET ?1 and let's say, "Data" has an "ID" field (primary index). Is it possible to

Re: [sqlite] SQL question - not sure if it's possible at all

Igor Tandetnik wrote: > Dennis Volodomanov <i...@psunrise.com> wrote: > >> Let's say I've got this query: >> >> SELECT * FROM (SELECT * FROM Data WHERE PlotOnGraph=1 ORDER BY Date >> ASC) LIMIT ?2 OFFSET ?1 >> > > Why an extra layer? Why not

[sqlite] SQL question - not sure if it's possible at all

Hello all, I'm not sure if this can be done in SQL, but if it can, I'd appreciate your help. Let's say I've got this query: SELECT * FROM (SELECT * FROM Data WHERE PlotOnGraph=1 ORDER BY Date ASC) LIMIT ?2 OFFSET ?1 and let's say, "Data" has an "ID" field (primary index). Is it possible to

Re: [sqlite] Question on errors - IOERR and CANTOPEN

Hi Filip, > what SQLite version and on what platform are you using? There was a > bug in SQLite 3.6.13 on Windows where SQLITE_CANTOPEN was incorrectly > returned during journal check when race condition between two threads > was hit. Also there could be some other software interfering with the >

[sqlite] Question on errors - IOERR and CANTOPEN

I sometimes get either a SQLITE_IOERR or a SQLITE_CANTOPEN when issuing BEGIN IMMEDIATE TRANSACTION or END TRANSACTION, however the database file is there and is being used by another thread. I thought I'd get the usual SQLITE_BUSY or SQLITE_LOCKED, but sometimes these file-related errors come up.

Re: [sqlite] read_uncommitted=on; question

> You're probably better off ignoring all this stuff and just getting a > working solution. Only then is it worth running some sort of > profiling system on your application to find out which bits are most > worth optimising. Since the sqlite3 library is pretty fast already > you might find that

Re: [sqlite] read_uncommitted=on; question

> > It's multiple threads all using the same sqlite3* handle. I call > > sqlite3_enable_shared_cache(1); before opening the database and have > > "PRAGMA read_uncommitted=1;" right after opening the database. > > None of that has any effect as long as you only have one connection. > For > the

Re: [sqlite] read_uncommitted=on; question

> Because catching and dealing with the duplication is handled within > the library function, using these things appropriately should mean > that you don't have to do any fancy worrying about threads, processes > or simultaneity at all: if anything funny goes on, only one of the > INSERT

Re: [sqlite] read_uncommitted=on; question

> Wrong. Statements on the same connection certainly see changes made on > that connection, committed or otherwise. > > Are you talking about the same connection, or two different connections > in shared cache mode? You started describing the latter, but now keep > mentioning the former. Which

Re: [sqlite] read_uncommitted=on; question

> How do you know the read on connection B doesn't actually happen right > _before_ the write on connection A? What kind of synchronization do you > employ between these two threads? I'm using a critical section (with a CSingleLock) to synchronize threads. However, just looking back at the code

Re: [sqlite] read_uncommitted=on; question

> > Ok, thank you for confirming that. It seems that connection B > > *sometimes* doesn't see data just inserted into a table by > > connection A. > > How long a time is 'just' ? You might want everything to be > completely up-to-date but does that record really matter if it didn't > exist a

Re: [sqlite] read_uncommitted=on; question

> > I thought (from reading the docs) that with read_uncommitted=1 and > > sqlite3_enable_shared_cache(1), if I INSERT something, it will be > > picked > > up if I do a SELECT on another thread's connection as being in the > > database, even if a COMMIT has not been issued yet. Am I wrong in my >

[sqlite] read_uncommitted=on; question

Hello all, Is it possible that with the read_uncommitted=1 and the shared cache mode turned on (multithreaded application), that some data that has been inserted into the database, but not yet committed, could not be picked up by another thread as being in the database? I thought (from reading

Re: [sqlite] How to process SQLITE_LOCKED_SHAREDCACHE error?

> SQLITE_LOCKED_SHAREDCACHE is like SQLITE_LOCKED and can be processed > in the same way, if you want. But if you get a > SQLITE_LOCK_SHAREDCACHE you also have the option of calling > sqlite3_unlock_notify(). You should not use sqlite3_unlock_notify() > on an ordinary SQLITE_LOCKED. Additional

[sqlite] How to process SQLITE_LOCKED_SHAREDCACHE error?

Hello, I'd like to ask how should the SQLITE_LOCKED_SHAREDCACHE be processed? Is it like a normal SQLITE_BUSY/SQLITE_LOCKED and I should sleep a bit and try again? Or is this a fatal error and requires me to abort the transaction? Thanks in advance, Dennis

[sqlite] Shared-cache mode doc page needs a version

On this page: http://sqlite.org/sharedcache.html in item 3.0 there's a missing version number at the end of the last sentence. Best regards, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Getting SQLITE_BUSY within atransactionandhow tohandle it

> sorry, I don't know if it is allowed to end a transaction from a > different connection than the one that obtained the lock. > I would not recommend that, sounds a bit unhealthy from the > application point of view. I'm pretty sure that a transaction is > partly connection related since

Re: [sqlite] Getting SQLITE_BUSY within a transactionandhow tohandle it

> I'm wondering on which statement your first thread returns busy as > well. both are waiting to get a transaction acquired? Or is the first > thread waiting for the commit? Both are waiting to start a transaction. > Maybe you can post some code or a little extraction of what you are > doing?

Re: [sqlite] Getting SQLITE_BUSY within a transaction andhow tohandle it

> You may also try this: > > http://www.sqlite.org/cvstrac/wiki?p=SampleCode > > I did use exclusive transactions there but you may change > accordingly and try to fit better in your scenario. > > If that still doesn't do the job you may post the results here again. Thank you, I've picked up

Re: [sqlite] Getting SQLITE_BUSY within a transaction and how tohandle it

> Are you at least reseting the statement? I hit this once before, and > reseting the statement fixed the issue for me. No... I'll try that, thanks! Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] Getting SQLITE_BUSY within a transaction and how to handle it

Hello all, I'm having this problem... I have one database and two threads opening it for reading/writing (in a simplistic scenario). Now, one of the threads issues (successfully) a "BEGIN IMMEDIATE TRANSACTION" and starts doing what it needs. The second thread also issues a "BEGIN IMMEDIATE

Re: [sqlite] How to determine if database is currently insideatransaction

> -Original Message- > From: Igor Tandetnik [mailto:itandet...@mvps.org] > Sent: Thursday, February 26, 2009 16:05 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] How to determine if database is currently > insideatransaction > > "Dennis Vo

[sqlite] How to determine if database is currently inside a transaction

Hello, I couldn't find an answer to my question, but maybe it's really simple... Is there any way to determine whether the database is currently inside a BEGIN...END transaction block? The only way I can think of at the moment is to check whether SQLITE_BUSY is returned, but I'm not sure

[sqlite] Triggers on attached databases

Hello all, Is it possible to create a trigger on an attached database? I can't seem to, so I was wondering whether my trigger SQL is wrong (although it works if I remove the attached database's name from the table). The only other way that I see to do what I need would be to create temporary

[sqlite] Triggers on attached databases

Hello all, Is it possible to create a trigger on an attached database? I can't seem to, so I was wondering whether my trigger SQL is wrong (although it works if I remove the attached database's name from the table). The only other way that I see to do what I need would be to create temporary

Re: [sqlite] SQL statement to get min/max values

Thank you Simon and Igor for suggesting the cast() - that works without me having to change anything! And of course I appreciate everyone's replies as well. Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] SQL statement to get min/max values

> Declaring the column as integer does not prevent you from storing strings: > > Yes, except for a small problem of updating all live databases with the new column type. I don't think I can update the column type without recreating the table, right? It's not hard, so if it comes down to

Re: [sqlite] SQL statement to get min/max values

> Works just fine with 3.6.1 if you declare the Value column to be > INTEGER. As it is, I have no idea what collation is used, but the > Value column will be declared to default to TEXT values, as shown by > > select typeof(value) from test_table; > I haven't tried that, but I cannot declare it

Re: [sqlite] SQL statement to get min/max values

Funny enough, but it doesn't work on real data using v3.6.1... Here's the table: sqlite> .dump test_table BEGIN TRANSACTION; CREATE TABLE test_table (ID INTEGER PRIMARY KEY, ExternalID2 INTEGER, ExternalID INTEGER, Value ); INSERT INTO "test_table" VALUES(1007,1,37,'-5'); INSERT INTO

Re: [sqlite] SQL statement to get min/max values

> Could it be that since you're not defining a type for Data it assumes > string? > Try creating the table with > id integer, externalid integer, data number (or numeric) That Data column could contain anything (int, double, string), it'll be up to the application's logic to only get Data for

Re: [sqlite] SQL statement to get min/max values

> What version of SQLite are you using? I'm using the 3.5.7 version that > came > with OS X 10.5, and I get -2 as expected. I'm using 3.3.5, I'll get the latest and see if works there or not in a few minutes. > Also, what's with the superfluous subquery? Why not just say > > SELECT max(Data)

Re: [sqlite] SQL statement to get min/max values

> Seems to work ok for me. What values were you expecting? Yes, that works. Bad example on my part, sorry. What doesn't work is this: 1|2|-7 2|2|-5 3|2|-20 4|2|-5 5|2|-2 SELECT max(Data) FROM (SELECT Data FROM test_table WHERE ExternalID=2); This returns a -5, while I'm expecting a -2. Thank

[sqlite] SQL statement to get min/max values

Hello all, I've tried a few SQL statements, but can't seem to get it to work properly, so I'd like to ask your help. Suppose I have a table like this: CREATE TABLE test_table (ID INTEGER PRIMARY KEY, ExternalID, Data); And some contents: 1| 2| -7 2| 2| 5 3| 1| 0 4| 2| -20 5| 2| -5 6| 2| 1 7|

Re: [sqlite] Is it possible to do this using only SQL?

-Original Message- From: Dennis Volodomanov Sent: Tuesday, February 05, 2008 11:28 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is it possible to do this using only SQL? I'll try taking out the ANALYZE and VACUUM and see if it helps. I'm using transactions and I am creating

Re: [sqlite] Is it possible to do this using only SQL?

, 2008 11:17 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is it possible to do this using only SQL? On 2/4/08, Dennis Volodomanov <[EMAIL PROTECTED]> wrote: > Hmm, strange - my testing produces very slow results (it took over a > minute to update 120K rows). well, for one, yo

Re: [sqlite] Is it possible to do this using only SQL?

? (The database is around 900 MBs) Dennis -Original Message- From: P Kishor [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 05, 2008 11:04 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Is it possible to do this using only SQL? On 2/4/08, Dennis Volodomanov <[EM

Re: [sqlite] Is it possible to do this using only SQL?

? On 04-Feb-2008, at 3:41 PM, Dennis Volodomanov wrote: > Is that possible? If not, I'll have to do it in the code, but that > will > probably be slower and I'm expecting to have tens of thousands of > rows. Sure: sqlite> create table x(a); sqlite> insert into x(a) val

[sqlite] Is it possible to do this using only SQL?

Hello all, I'm not sure if it's possible to do this using only SQL, so I'd like to ask: I need to add a column to a table which will be populated with data from another column in that table, but converted to lower-case. So, in pseudo-code I need to do this: ALTER TABLE MyTable ADD

RE: [sqlite] Request for help with the SQLite Website

The navigation bar for me is on 2 lines - Support is wrapped to the second line. Is that intended? (doesn't look right :)) Dennis - To unsubscribe, send email to [EMAIL PROTECTED]

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 s

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: Hel

[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] PROBLEMS BUILDING 3.4.2 using Ms Visual Studio 2005

Would it help if you didn't have the "const" in the declaration? (I haven't tried - just an idea...) Dennis [EMAIL PROTECTED] wrote: "Cariotoglou Mike" <[EMAIL PROTECTED]> wrote: I am having problems building 3.4.2 from the amalgamated source,using Microsoft Visual Studio 2005 (this

[sqlite] SQL question regarding triggers updating values

Hello all, Let's say I have this schema: CREATE TABLE Table1 (FileID INTEGER NOT NULL, FileOrder INTEGER); And I need to go through it at change FileOrder so that it becomes FileOrder of the next (or previous) FileID and that's FileID FileOrder becomes current (to put it in words -

RE: [sqlite] Questions on views

> -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Wednesday, March 28, 2007 10:59 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Questions on views > > "Dennis Volodomanov" <[EMAIL PROTECTED]> wrote: > > Tha

RE: [sqlite] Questions on views

Sent: Wednesday, March 28, 2007 10:31 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Questions on views > > "Dennis Volodomanov" <[EMAIL PROTECTED]> wrote: > > One more question that I was pondering upon - does the > order of rows > > an

RE: [sqlite] Questions on views

gt; Subject: Re: [sqlite] Questions on views > > Dennis Volodomanov wrote: > > I might not go down the threading path at all, as in theory it > > wouldn't gain me that much - I'd still need to lock/unlock the > > database for each of the threads and I think the overhead > of

RE: [sqlite] Questions on views

s@sqlite.org > Subject: Re: [sqlite] Questions on views > > Dennis Volodomanov wrote: > > I might not go down the threading path at all, as in theory it > > wouldn't gain me that much - I'd still need to lock/unlock the > > database for each of the threads and I think the

  1   2   >