Re: [sqlite] sqlite-users Digest, Vol 53, Issue 9

On Wed, May 9, 2012 at 9:28 PM, YAN HONG YE wrote: > On Wed, 9 May 2012 02:24:58 +0100 > Simon Slavin wrote: > > > alter table myref add stkcode varchar(30); > > update myref set stkcode = dzhhq.stkcode; > > > > this sql command couldn't run in my

Re: [sqlite] sqlite-users Digest, Vol 53, Issue 9

YAN HONG YE wrote: > update myref set upfu =dzhhq.upfu where exists (select * from dzhhq where > myref.stkname=dzhhq.stkname); You are probably looking for something like this: update myref set upfu = (select dzhhq.upfu from dzhhq where myref.stkname=dzhhq.stkname);

Re: [sqlite] sqlite-users Digest, Vol 53, Issue 9

> update myref set upfu =dzhhq.upfu where exists (select * from dzhhq where > myref.stkname=dzhhq.stkname); > but it said no such column: dzhhq.upfu > but dzhhq.upfu really exist. No, it does not. The cursor is the updateable table myref only. You can set a column in myref to be any

Re: [sqlite] sqlite-users Digest, Vol 53, Issue 9

On 10 May 2012, at 2:28am, YAN HONG YE wrote: > I means: > use another table column, to update the current table column: like this: > alter table myref add upfu; > update myref set upfu =dzhhq.upfu where exists (select * from dzhhq where > myref.stkname=dzhhq.stkname); >

Re: [sqlite] sqlite-users Digest, Vol 53, Issue 9

On Wed, 9 May 2012 02:24:58 +0100 Simon Slavin wrote: > alter table myref add stkcode varchar(30); > update myref set stkcode = dzhhq.stkcode; > > this sql command couldn't run in my sqlite. It does not know which row from the table dzhhq it is meant to use. Simon. I

Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE <> '' returns value

On May 9, 2012, at 9:52 PM, Kit wrote: > Version of SQLite2 is still 2.8.17. Yes, 2.8.17 seems to be the latest release of the SQLite2 series. Still, it's 6 years old. Why use it today for new development? In any case, the OP seems to have found a solution to its conundrum, so all is good

Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE <> '' returns value

2012/5/9 Petite Abeille : > On May 9, 2012, at 9:21 PM, Kit wrote: >> $ sqlite -version >> 2.8.17 > Perhaps the OP got confused between sqlite and sqlite3? :D >> $ sqlite3 -version >> 3.6.23 > Better, but still over two years old, March 9, 2010: I have installed both

Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE <> '' returns value

On May 9, 2012, at 9:21 PM, Kit wrote: > $ sqlite -version > 2.8.17 Perhaps the OP got confused between sqlite and sqlite3? :D > $ sqlite3 -version > 3.6.23 Better, but still over two years old, March 9, 2010: http://www.sqlite.org/changes.html#version_3_6_23 > openSUSE 11.3 According to

Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE <> '' returns value

2012/5/9 Petite Abeille : > On May 9, 2012, at 8:32 PM, Black, Michael (IS) wrote: >> That doesn't appear to work for 2.8.17. > 2.8.17?  Current as of December 19, 2005?!? Oh, well... :D > http://www.sqlite.org/changes.html#version_2_8_17 $ sqlite -version 2.8.17 $

Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE <> '' returns value

On May 9, 2012, at 8:32 PM, Black, Michael (IS) wrote: > That doesn't appear to work for 2.8.17. 2.8.17? Current as of December 19, 2005?!? Oh, well... :D http://www.sqlite.org/changes.html#version_2_8_17 ___ sqlite-users mailing list

Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE <> '' returns value

from Black, Michael (IS) But using round() does work. NULL stays NULL and space (or any non-numeric string) becomes zero. CREATE TABLE "maillist"(recordID,userID,name,email); INSERT INTO "maillist" VALUES(1,1,'John','j...@somehwere.com'); INSERT INTO "maillist"

Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE <> '' returns value

On 9 May 2012, at 7:32pm, "Black, Michael (IS)" wrote: > SQLite version 2.8.17 Just to note the above. Are we really talking about SQLite version 2 ? If so, please ignore everything I wrote. I have never used a version of SQLite before version 3. Simon.

Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE <> '' returns value

I ran this through the shell: CREATE TABLE "maillist"(recordID,userID,name,email); INSERT INTO "maillist" VALUES(1,1,'John','j...@somehwere.com'); INSERT INTO "maillist" VALUES(2,2,'Mike','mi...@example.com'); INSERT INTO "maillist" VALUES(3,3,'Bill','b...@example.com'); INSERT INTO "maillist"

Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE <> '' returns value

That doesn't appear to work for 2.8.17. But using round() does work. NULL stays NULL and space (or any non-numeric string) becomes zero. Tried typeof() but it always returns numeric. SQLite version 2.8.17 Enter ".help" for instructions sqlite> CREATE TABLE

Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE <> '' returns value

On May 9, 2012, at 8:01 PM, Shorty wrote: > So, now the question is how do I write a statment to filter out strings so > the max() only evaluates the numeric values? SELECT max( cast( userID as integer ) ) AS highest_userID FROM maillist; ___

Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE <> '' returns value

WHOOPS!!! I just re-created the database with a shell like Michael describes and it WORKS!! I dumped my original database and now see the problem, my data entry form that I used is making the blank userID field a space instead of blank string or null. Thanks a bunch Michael !

Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE <> '' returns value

Simon / Mircea -- Great suggestions !! They aren't what I am after, but they are great ideas that I will use elsewhere and didn't know about those before. Database: recordID userID name email 11Johnj...@somewhere.com 22Mikem...@example.com 33Bill

Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE <> '' returns value

Hmmm...works for meare you SURE you're using the correct database after you made the changes? I used the 2.8.17 shell that I got from http://www.gamefront.com/files/service/thankyou?id=4833830 and it works just fine. CREATE TABLE "maillist"(recordID,userID,name,email); INSERT INTO

Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE <> '' returns value

Reading whole emails is a skill I haven't fully mastered ;) Simon pointed out that the records should never contain NULLs. I previously used a .CSV flat text file to store the data and am migrating over to sqlite2. It might not be very good database design, but the reason I do is so the

Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE <> '' returns value

On 9 May 2012, at 4:38pm, "Shorty" wrote: > Simon pointed out that the records should never contain NULLs. I previously > used a .CSV flat text file to store the data and am migrating over to > sqlite2. It might not be very good database design, but the reason I do

Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE <> '' returns value

Just curious: why cannot you use the AUTOINCREMENT option (http://sqlite.org/autoinc.html) Mircea On 09/05/2012 11:38 AM, Shorty wrote: I now realize that my data entry form was making the Steve entry a blank string, and that is why the <> '' worked. Original database recordID userID name

Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE <> '' returns value

I now realize that my data entry form was making the Steve entry a blank string, and that is why the <> '' worked. Original database recordID userID name email 11Johnj...@somewhere.com 22Mikem...@example.com 33Billb...@example.com 4''Steve

Re: [sqlite] [sqlite-dev] Explain what 'transaction' means in javascript for sqlite, please?

STW: http://stackoverflow.com/questions/740523/getting-a-webkit-executesql-transaction-to-return-a-value other references http://stackoverflow.com/questions/61972/javascript-sqlite On Wed, May 9, 2012 at 10:32 AM, Pavel Ivanov wrote: > And again you wrote to the wrong

Re: [sqlite] SQLite Tcl Extension

No: here is info from my mac - Mac OSX (run in terminal) : ~ 596 % sqlite3 test.db SQLite version 3.7.7 2011-06-25 16:35:41 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .e ~ 597 % wish % set tcl_version 8.5 % package require sqlite3 3.7.5 % exit As you can

Re: [sqlite] [sqlite-dev] Explain what 'transaction' means in javascript for sqlite, please?

And again you wrote to the wrong mailing list. Use "Reply" button please. Pavel On Wed, May 9, 2012 at 10:27 AM, Andrew Lewis wrote: > Hi Pavel, > > I disagree I'm afraid. It is instrumental in getting SQLIte up and running > in a javascript client-side environment,

Re: [sqlite] [sqlite-dev] Explain what 'transaction' means in javascript for sqlite, please?

I forward this to the correct list. Hopefully someone here knows what "javascript for mobiles" is and will be able to help you. But the question sounds more appropriate for some mailing list/forum discussing this particular programming language as there's no SQLite-related issue here. Pavel On

Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULLBut... WHERE <> '' returns value

Shorty wrote: > Next Question - if I use the statment: > SELECT max(userID) AS 'highest_userID' FROM maillist WHERE userID <> '' > > Would that produce the same results for both NULL numeric entries and empty > strings? NULL compares smaller than any other value. So,

Re: [sqlite] Having in prepared statement with parameters

Doing exact matches on floating point values will get you in trouble quite frequently. Most float numbers cannot be exactly represented internally so can cause such behavior. Hopefully your application doesn't depend on thisif so you need to redesign what you're doing. But to fix your

Re: [sqlite] Having in prepared statement with parameters

On 9 May 2012 14:17, Pavel Ivanov wrote: >> As the last three statements are exactly the same (in meaning). >> Can someone explain what's happening? > > I think you've got trapped by double value storage imprecision. > Comparing double values for exact equality is pretty

Re: [sqlite] Cache all pages

On 9 May 2012, at 1:30pm, Navaneeth.K.N wrote: > On Wed, May 9, 2012 at 5:15 PM, Simon Slavin wrote: > >> >> Sure. That would cache the data. And then the next thing that needs to >> be cached might overwrite it all again. You're messing with

Re: [sqlite] Having in prepared statement with parameters

> As the last three statements are exactly the same (in meaning). > Can someone explain what's happening? I think you've got trapped by double value storage imprecision. Comparing double values for exact equality is pretty dangerous - two numbers can differ in one last bit, be not equal and be

Re: [sqlite] Cache all pages

> I was thinking that caching of pages is SQLite's implementation and nothing > to do with the OS. I'd think the cache is associated with each database > connection and Sqlite caches all the pages it reads until the maximum > limit. Is this the correct understanding? Yes, this is the correct

Re: [sqlite] Cache all pages

Hello, On Wed, May 9, 2012 at 5:15 PM, Simon Slavin wrote: > > Sure. That would cache the data. And then the next thing that needs to > be cached might overwrite it all again. You're messing with something that > your OS thinks it has sole control over. > > > Are there

[sqlite] HAVE_UTIME test backwards ?

According to systems I have access to, utime is obsolescent, and code should now use utimes. Is it reasonable to have an explicit test to set HAVE_UTIME and "default" to utimes if we don't find utime ? This does look backwards to me... ___ sqlite-users

[sqlite] Having in prepared statement with parameters

I have code that looks like that: --- begin main.cpp --- #include #include #include #include void print( sqlite3_stmt * pStmt ) { const char* col1name = sqlite3_column_name( pStmt, 0 ); printf("res: "); while ( sqlite3_step( pStmt ) == SQLITE_ROW ){ printf("%lf ",

Re: [sqlite] ROLLBACK TO savepoint cancels earlier RELEASEs

On 9 May 2012, at 10:33am, wrote: > 1: SAVEPOINT 1 > 2: insert A > 3: RELEASE SAVEPOINT 1 > 4: SAVEPOINT 2 > 5: insert B > 6: ROLLBACK TO SAVEPOINT 2 > > ROLLBACK in line 6 cancels also line 2 in addition to line 5. > > I want to cancel just line 5. What it

Re: [sqlite] Cache all pages

On 9 May 2012, at 7:07am, "Navaneeth.K.N" wrote: > I have a SQLite database which has got 60 pages. For performance reasons, I > am thinking of making SQLite cache all of this 60 pages, so for further > queries no disk read will be performed. I believe when all pages are

Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE <> '' returns value

On 9 May 2012, at 6:11am, "Shorty" wrote: > Oh that is interesting !! > > When I created the table I had the following column declarations: > recordID INTEGER PRIMARY KEY, > userID INTEGER UNIQUE, > name CHAR, > email CHAR A quick note that SQLite does not have a CHAR

Re: [sqlite] ROLLBACK TO savepoint cancels earlier RELEASEs

On Wed, May 9, 2012 at 5:33 AM, wrote: > Hi All, > > > > 1: SAVEPOINT 1 > > 2: insert A > > 3: RELEASE SAVEPOINT 1 > > 4: SAVEPOINT 2 > > 5: insert B > > 6: ROLLBACK TO SAVEPOINT 2 > > > > ROLLBACK in line 6 cancels also line 2 in addition to line 5. > > I want

Re: [sqlite] ROLLBACK TO savepoint cancels earlier RELEASEs

On 9 May 2012 10:33, wrote: > Hi All, > > 1: SAVEPOINT 1 > 2: insert A > 3: RELEASE SAVEPOINT 1 > 4: SAVEPOINT 2 > 5: insert B > 6: ROLLBACK TO SAVEPOINT 2 > > ROLLBACK in line 6 cancels also line 2 in addition to line 5. > > I want to cancel just line 5. > >

[sqlite] ROLLBACK TO savepoint cancels earlier RELEASEs

Hi All, 1: SAVEPOINT 1 2: insert A 3: RELEASE SAVEPOINT 1 4: SAVEPOINT 2 5: insert B 6: ROLLBACK TO SAVEPOINT 2 ROLLBACK in line 6 cancels also line 2 in addition to line 5. I want to cancel just line 5. What am I doing wrong ? br, Esko

[sqlite] Cache all pages

Hello, I have a SQLite database which has got 60 pages. For performance reasons, I am thinking of making SQLite cache all of this 60 pages, so for further queries no disk read will be performed. I believe when all pages are cached, SQLIte just has to read the cached pages and would be faster. To

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

On Mon, May 07, 2012 at 01:34:13PM -0400, peter korinis wrote: > So, if I use gawk to change my comma separated file to | (pipe) delimiter . > will it work? If you use gawk, you can easily change your comma separated file to the series of correct SQL INSERT statements (besides ensuring