Re: [sqlite] SELECT COUNT (DISTINCT column_name FROM table)

On 03/03/2012 10:30 AM, Sreekumar TP wrote: Could someone throw some light on this issue too? I can't see from the stack trace why this is crashing. Does it crash if you run the query from the sqlite shell? Maybe try building the shell without optimizations, and then running it under

Re: [sqlite] SELECT COUNT (DISTINCT column_name FROM table)

Could someone throw some light on this issue too? Sreekumar On Mar 2, 2012 10:05 AM, "Sreekumar TP" wrote: > The backtrace > === > > > Program received signal SIGSEGV, Segmentation fault. > 0x2b657288 in sqlite3Parser (yyp=0x2d401e40, yymajor=119,

Re: [sqlite] DISTINCT bug with 3.7.10

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/03/12 15:20, Steven Russell wrote: > I obviously don't expect the duplicate results here. If you create the > table without the UNIQUE clause, then the results are as expected (1 > and 2 both only show up once). BTW the team agreed it was a

Re: [sqlite] how to replace connection string at runtime

Thanks very much. After trying many of the suggestions among the search results, the solution that worked was: http://social.msdn.microsoft.com/Forums/fi-FI/wpf/thread/b7d8a3dd-031e-481f-94b7-919373c61f4b The only problem is that the settings.designer.cs file seems to be auto-generated,

Re: [sqlite] DISTINCT bug with 3.7.10

Simon Slavin wrote and quoted: >> Would the output of 'PRAGMA compile_options;' be sufficient ? > > That's a cute and useful feature I had forgotten. It would be sufficient to show the preprocessor variable values that were in effect as the amalgamation was compiled. However, for something

Re: [sqlite] DISTINCT bug with 3.7.10

On 3 Mar 2012, at 12:46am, Larry Brasfield wrote: >> On 3 Mar 2012, at 12:29am, Larry Brasfield >> wrote: >> >> > That's approaching a pretty good "bug" report. However, I would suggest a >> > little more to promote a resolution of this problem. You do not state

Re: [sqlite] DISTINCT bug with 3.7.10

On 3 Mar 2012, at 12:29am, Larry Brasfield wrote: > That's approaching a pretty good "bug" report. However, I would suggest a little more to promote a resolution of this problem. You do not state the compilation options. Optimization settings and preprocessor variable settings are critical

Re: [sqlite] DISTINCT bug with 3.7.10

On 3 Mar 2012, at 12:29am, Larry Brasfield wrote: > That's approaching a pretty good "bug" report. However, I would suggest a > little more to promote a resolution of this problem. You do not state the > compilation options. Optimization settings and preprocessor

Re: [sqlite] DISTINCT bug with 3.7.10

Steven Russell wrote: I just grabbed the 3.7.10 amalgamation source and built it, but have run into an issue where DISTINCT doesn't appear to actually return distinct values on a table that includes a UNIQUE clause. It unexpectedly returns duplicates instead. My build environment is: - Mac

Re: [sqlite] Sqlite3 command shell dump possible bug

On 2 Mar 2012, at 10:45pm, Steffen Mangold wrote: > I now delete the malform message and the rollback command from the *.sql file > and run ".read". Okay ... > Sqlite shell runs complete and the shell ask me for new command "> " (DB file > seems to have the

Re: [sqlite] Sqlite3 command shell dump possible bug

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/03/12 15:32, Steffen Mangold wrote: > how to "replace" this? Change the abort to commit as others pointed out. The dump code does the following: print BEGIN foreach table in the database: foreach row in the table: print the row print

Re: [sqlite] Sqlite3 command shell dump possible bug

> > You should be able to, yes. Just type in "END;" (without quotes, but with > semicolon). > Ok thank you i will try :) (in a few hours because DB is so big. :) ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Sqlite3 command shell dump possible bug

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/03/12 14:45, Steffen Mangold wrote: > I'm happy now and enter command ".exit" and bam Db file has 0kb?!?!? > What have i done wrong? Any Commit command or something? What you don't seem to understand is that your original database is corrupt.

[sqlite] DISTINCT bug with 3.7.10

I just grabbed the 3.7.10 amalgamation source and built it, but have run into an issue where DISTINCT doesn't appear to actually return distinct values on a table that includes a UNIQUE clause. It unexpectedly returns duplicates instead. My build environment is: - Mac OS X 10.6.8 - Xcode

Re: [sqlite] Sqlite3 command shell dump possible bug

On 3/2/2012 5:57 PM, Steffen Mangold wrote: If you have a BEGIN command in your script, then you should also have END or COMMIT at the end (the two are synonyms). can i do this by shell command after ".read" if my SQL script has miss that? You should be able to, yes. Just type in "END;"

Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

> I have created my own function  which uses a global variable for the default > locale. (see attached) I guess I received your attachment because you sent this email to me directly but generally this list doesn't allow attachments - you should include your code into email. And about your code:

Re: [sqlite] Sqlite3 command shell dump possible bug

> > If you have a BEGIN command in your script, then you should also have END or > COMMIT at the end (the two are synonyms). > can i do this by shell command after ".read" if my SQL script has miss that? Steffen ___ sqlite-users mailing list

Re: [sqlite] Sqlite3 command shell dump possible bug

On 3/2/2012 5:45 PM, Steffen Mangold wrote: WHAT THE ! I now delete the malform message and the rollback command from the *.sql file and run ".read". Sqlite shell runs complete and the shell ask me for new command "> " (DB file seems to have the right size. I'm happy now and enter

Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

That's a wrong approach. First, you don't need to modify functions in SQLite code, you need to create your own. Your function will convert using locale saved in some variable in your application. I have created my own function which uses a global variable for the default locale. (see

Re: [sqlite] Sqlite3 command shell dump possible bug

WHAT THE ! I now delete the malform message and the rollback command from the *.sql file and run ".read". Sqlite shell runs complete and the shell ask me for new command "> " (DB file seems to have the right size. I'm happy now and enter command ".exit" and bam Db file has 0kb?!?!? What

Re: [sqlite] how to replace connection string at runtime

-- -- -- --ô¿ô-- K e V i N On Fri, Mar 2, 2012 at 2:47 PM, Agrawal, Manish wrote: > My question is: what is the simplest way to replace the connection string > in a C# application with the file location at runtime? I do want the > convenience

[sqlite] ADO.NET provider and sqlite3.exe (command shell) .load command

Hi, Is it possible to create a managed library with custom SQLite functions (based on System.Data.SQLite.SQLiteFunction class) and load it into sqlite3.exe command shell (using .load command) for ease of testing and visual data examination? Thanks, - Levi

Re: [sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/03/12 10:55, Rose, John B wrote: > In this particular example, a particular researcher would be creating > the initial version of the database on their own computer, but once > they got out in the field they may update the copy they have on

[sqlite] how to replace connection string at runtime

Hello I suspect my question may already have been answered many times before, but I could not find a way to search through the archives of the sqlite users mailing list at: http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/. Is there a way to search the archives? My question is:

Re: [sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

On 2 Mar 2012, at 7:40pm, "Rose, John B" wrote: >> So once again, how would you answer Roger's question ? Suppose changes were >> made on both computers at the same time. How would you reconcile the two >> copies of the databases ? If you want to use the copy from the

Re: [sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

> So once again, how would you answer Roger's question ? Suppose changes were > made on both computers at the same time. How would you reconcile the two > copies of the databases ? If you want to use the copy from the computer to > /update/ the copy in the mobile device then the mobile device

Re: [sqlite] Sqlite3 command shell dump possible bug

On 2 Mar 2012, at 7:08pm, Steffen Mangold wrote: > Ok maybe i found it in the sql file is written (file end): > > [...] > INSERT INTO "InverterData" VALUES(2478,'2012-02-28 >

Re: [sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

On 2 Mar 2012, at 6:55pm, "Rose, John B" wrote: > Thanks for the very thorough reply. > >> Android is a red herring in this and your approach is not a good one. How >> would you do this using two different regular computers? How would you >> deal with changes being made on

Re: [sqlite] Sqlite3 command shell dump possible bug

Ok maybe i found it in the sql file is written (file end): [...] INSERT INTO "InverterData" VALUES(2478,'2012-02-28 15:00:00',1435.73,429173.78,170.28,170.75,169.38,397.56,397.38,396.69,NULL,210976,31,NULL,NULL,1,304,NULL,NULL,NULL,694,NULL,NULL,NULL); / ERROR: (11) database disk image is

Re: [sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

Thanks for the very thorough reply. > Android is a red herring in this and your approach is not a good one. How > would you do this using two different regular computers? How would you > deal with changes being made on both machines at the same time? How would > you copy databases ensuring

Re: [sqlite] Sqlite3 command shell dump possible bug

> > Is it very long ? Can you read it with a dump utility or a text editor > (don't try it with a word processor) and see the SQL commands in it ? > Yes 14 GB. 4 Table, roundabout 200.000.000 inserts. I opened it with a textviewer for large files. Sql seams well formed and readable till the

Re: [sqlite] Sqlite3 command shell dump possible bug

On 2 Mar 2012, at 6:30pm, Steffen Mangold wrote: >> Are you saying it creates a database file but doesn't put anything into it >> (zero filesize) or that it doesn't even create a blank file ? > > With dump its write the complete DB File new but nearly at the end

Re: [sqlite] WinRT (WAS: Status)

Steven Nesbit wrote: > > What is the status of this effort? We actually need to have the > platform determined at runtime since we need to run on WinRT, > Android and iOS. > I'm not really following you here... Those are completely different platforms. -- Joe Mistachkin

Re: [sqlite] Sqlite3 command shell dump possible bug

> > Are you saying it creates a database file but doesn't put anything into it > (zero filesize) or that it doesn't even create a blank file ? > With dump its write the complete DB File new but nearly at the end (new DD file size compared to the malformed) Sqlite shell breaks and set the file

Re: [sqlite] Sqlite3 command shell dump possible bug

On 2 Mar 2012, at 6:16pm, Steffen Mangold wrote: >> There's no magic tool for repairing damaged database files. But by using >> the .dump command (if necessary on each individual table and view) then >> creating a new database file and using the .read command you

Re: [sqlite] Sqlite3 command shell dump possible bug

> > Ok, with .dumb i now created a "db.sql" file successfully. > but I don't get the read command!? How create a new DB file with that command? > With "sqlite> .read db.sql" it does much reading but no file is created. > Ok I get it, must attach a DB first. now sqlite writes the data to the DB,

Re: [sqlite] Sqlite3 command shell dump possible bug

> > There's no magic tool for repairing damaged database files. But by using the > .dump command (if necessary on each individual table and view) then creating > a new database file and using the .read command you can often rescue some or > all of the data in the original > > database. > Ok,

Re: [sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

On 2 Mar 2012, at 5:58pm, Roger Binns wrote: > On 02/03/12 09:40, Simon Slavin wrote: >> What ContentProvider is, as far as I can work out, is a way of >> accessing a SQLite database file in a form convenient for Android >> apps. > > No, it is a way for Android app

Re: [sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/03/12 09:40, Simon Slavin wrote: > What ContentProvider is, as far as I can work out, is a way of > accessing a SQLite database file in a form convenient for Android > apps. No, it is a way for Android app components to expose and manipulate

Re: [sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [I reordered your message in my response] On 02/03/12 08:59, Rose, John B wrote: > I had assumed we just moved the .db file back and forth between our > desktop and Android and the simplicity is part of the "coolness" of > SQLite. Yes, Android

Re: [sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

On 2 Mar 2012, at 4:59pm, "Rose, John B" wrote: > Someone else in our group came across something called "ContentProvider" and > is under the impression we have to do some file conversion of the .db file > use ContenProvider somehow if we want to use the original .db file

Re: [sqlite] Sqlite Bug Report!

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > When I compile sqlite3 in VC++6.0, it does not work, but in VS2010 it > can work. http://www.beiww.com/doc/oss/smart-questions.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux)

Re: [sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

Someone else in our group came across something called "ContentProvider" and is under the impression we have to do some file conversion of the .db file use ContenProvider somehow if we want to use the original .db file created on our desktop. Frankly we are not understanding it. I had assumed we

Re: [sqlite] Views and Performance

On 3/2/2012 11:38 AM, Duquette, William H (318K) wrote: On 3/2/12 8:29 AM, "Igor Tandetnik" wrote: On 3/2/2012 11:29 AM, Pavel Ivanov wrote: If I am querying data just from t1, is there a performance penalty for using myview in the query? Or will the query planner

Re: [sqlite] Views and Performance

On 3/2/12 8:31 AM, "Simon Davies" wrote: >On 2 March 2012 16:23, Duquette, William H (318K) > wrote: >> Howdy! >> >> Suppose I have two related tables, t1 and t2, and I write a view like >>this: >> >>CREATE VIEW myview AS SELECT

Re: [sqlite] Views and Performance

On 3/2/12 8:29 AM, "Igor Tandetnik" wrote: >On 3/2/2012 11:29 AM, Pavel Ivanov wrote: >>> If I am querying data just from t1, is there a performance penalty >>> for using myview in the query? Or will the query planner generate >>> approximately the same bytecode as it

Re: [sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

> Is there an example(s?) of a step-by-step for moving/using an SQLite .db file > interchangeably between a desktop and an Android mobile device? What kind of example you want? SQLite's database format is the same for any platform. So just copy the file (when it's not open by any application)

Re: [sqlite] Views and Performance

> What kind of JOIN is used when it a type (INNER, OUTER, etc.) is not > specified? INNER is default. Pavel On Fri, Mar 2, 2012 at 11:37 AM, Rob Richardson wrote: > What kind of JOIN is used when it a type (INNER, OUTER, etc.) is not > specified? > > RobR > >

[sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

Hello We are new to SQLite and are working on an intro tutorial for people here. We would like to create an example where we create an SQLite database on our desktop and access it there via command line, GUI like Navicon, and a web based application, then upload the .db file to our Android

Re: [sqlite] Views and Performance

What kind of JOIN is used when it a type (INNER, OUTER, etc.) is not specified? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Duquette, William H (318K) Sent: Friday, March 02, 2012 11:23 AM To: Discussion of

Re: [sqlite] Views and Performance

On 3/2/2012 11:29 AM, Pavel Ivanov wrote: If I am querying data just from t1, is there a performance penalty for using myview in the query? Or will the query planner generate approximately the same bytecode as it would if I'd simply queried t1? Yes, there is performance penalty and no it

Re: [sqlite] Views and Performance

On 2 March 2012 16:23, Duquette, William H (318K) wrote: > Howdy! > > Suppose I have two related tables, t1 and t2, and I write a view like this: > >    CREATE VIEW myview AS SELECT * FROM t1 JOIN t2 USING (some_column); > > If I am querying data just from t1, is

Re: [sqlite] Views and Performance

> If I am querying data just from t1, is there a performance penalty for using > myview in the query?  Or will the query planner generate approximately the > same bytecode as it would if I'd simply queried t1? Yes, there is performance penalty and no it can't generate the same bytecode. If you

[sqlite] Views and Performance

Howdy! Suppose I have two related tables, t1 and t2, and I write a view like this: CREATE VIEW myview AS SELECT * FROM t1 JOIN t2 USING (some_column); If I am querying data just from t1, is there a performance penalty for using myview in the query? Or will the query planner generate

Re: [sqlite] Create Temp Table from Query

CREATE TEMPORARY TABLE XYZ AS SELECT ... Is that what you're looking for? > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Joe Bennett > Sent: Friday, March 02, 2012 10:44 AM > To: sqlite-users@sqlite.org >

[sqlite] Create Temp Table from Query

Hi, I am looking for some info on how to take an sqlite query result and move that into a temp table. My hope is to focus the subsequent queries down to a smaller dataset... I've been searching Google a bit but have not been able to find what I am loking for... I'm not sure if that means this is

Re: [sqlite] Status

Sorry about that, WinRT Sqlite Steve ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Status

Sorry... What effort? > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Steven Nesbit > Sent: Friday, March 02, 2012 10:35 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Status > > What is the status of this

[sqlite] Status

What is the status of this effort? We actually need to have the platform determined at runtime since we need to run on WinRT, Android and iOS. Steve ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Sqlite3 command shell dump possible bug

On 2 Mar 2012, at 2:36pm, Steffen Mangold wrote: > I read in some forums that .dumb is the best way to repair "malformed" DBs. > Do you have an other way? It doesn't repair anything. And the .dump command may, or may not, work on a malformed database file. It

Re: [sqlite] TEXT PRIMARY KEY

On Fri, Mar 02, 2012 at 02:21:19PM +0100, Benoit Mortgat scratched on the wall: > On Fri, Mar 2, 2012 at 13:59, Jay A. Kreibich wrote: > > On Fri, Mar 02, 2012 at 10:44:20AM +0100, Christoph P.U. Kukulies > > scratched on the wall: > > > > ??Kind of. ??It implies uniqueness in the

Re: [sqlite] Sqlite3 command shell dump possible bug

> > First, get all the other databases done, so you're worried only about the one > which doesn't work. > > Then do the .dump part for that database, putting the output into a file on > disk, which should leave you with a huge file of SQL commands which should > rebuild it. > > It's likely that

Re: [sqlite] Sqlite3 command shell dump possible bug

On 2 Mar 2012, at 1:20pm, Steffen Mangold wrote: > i have a problem with the sqlite2.exe under windows. > Ok, I have here 20 corrupted DBs and want to repair they all. > I do this with CMD and the command ".dump | sqlite3 rebuild.db3 | sqlite3 > rebuild.temp" > >

[sqlite] Sqlite Bug Report!

Hello. (I am Chinese, not good at English. ^_^) When I compile sqlite3 in VC++6.0, it does not work, but in VS2010 it can work. It report a runtime errro. My code as following. #include #include #include #include #include "sqlite3.h" #include #include int main() { char db[] = "db.db";

Re: [sqlite] TEXT PRIMARY KEY

On Fri, Mar 2, 2012 at 13:59, Jay A. Kreibich wrote: > On Fri, Mar 02, 2012 at 10:44:20AM +0100, Christoph P.U. Kukulies > scratched on the wall: > >  Kind of.  It implies uniqueness in the SQL sense, which does not >  include NULLs (remember, NULL != NULL). Actually, NULL !=

[sqlite] Sqlite3 command shell dump possible bug

Hi guys, i have a problem with the sqlite2.exe under windows. Ok, I have here 20 corrupted DBs and want to repair they all. I do this with CMD and the command ".dump | sqlite3 rebuild.db3 | sqlite3 rebuild.temp" This works perfect for all DBs except one. The DB where it is not working has a

Re: [sqlite] TEXT PRIMARY KEY

Hmmm...works for me... On Windows: SQLite version 3.7.9 2011-11-01 00:52:41 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table test(a text primary key); sqlite> insert into test values('1'); sqlite> insert into test values('1'); Error: column a is

Re: [sqlite] TEXT PRIMARY KEY

On Fri, Mar 02, 2012 at 10:44:20AM +0100, Christoph P.U. Kukulies scratched on the wall: > When defining a column TEXT PRIMARY KEY (is that possible on TEXT?), > would this imply uniqueness? Kind of. It implies uniqueness in the SQL sense, which does not include NULLs (remember, NULL !=

Re: [sqlite] compressing BLOB

On Fri, Mar 2, 2012 at 12:54 PM, Benoit Mortgat wrote: > SQLite does not compress your blob and you will have to do that > programatically. However you can define your own with > sqlite_create_function_v2(): the prototype of your function would be > There's an implementation

Re: [sqlite] compressing BLOB

SQLite does not compress your blob and you will have to do that programatically. However you can define your own with sqlite_create_function_v2(): the prototype of your function would be void compress(sqlite3_context *context, int argc, sqlite3_value **argv) { assert(argc==1); void *data =

Re: [sqlite] TEXT PRIMARY KEY

Am 02.03.2012 11:03, schrieb Oliver Peters: sorry I meant CREATE TABLE test( a TEXT PRIMARY KEY ); (without INTEGER, usually I write INTEGER and not TEXT :-) ) Am 02.03.2012 10:44, schrieb Christoph P.U. Kukulies: When defining a column TEXT PRIMARY KEY (is that possible on TEXT?), yes

Re: [sqlite] TEXT PRIMARY KEY

Am 02.03.2012 10:44, schrieb Christoph P.U. Kukulies: When defining a column TEXT PRIMARY KEY (is that possible on TEXT?), yes would this imply uniqueness? yes Or would I have to write something like TEXT PRIMARY KEY UNIQUE ? no and that doesn't make sense I'd say [...] simply

Re: [sqlite] TEXT PRIMARY KEY

2012/3/2, Christoph P.U. Kukulies : > When defining a column TEXT PRIMARY KEY (is that possible on TEXT?), Yes. > would this imply uniqueness? Yes. > Or would I have to write something like TEXT PRIMARY KEY UNIQUE ? > Christoph Kukulies No. PRIMARY KEY is always UNIQUE. --

Re: [sqlite] TEXT PRIMARY KEY

On Fri, 02 Mar 2012 10:44:20 +0100, "Christoph P.U. Kukulies" wrote: > When defining a column TEXT PRIMARY KEY > (is that possible on TEXT?), Yes that is possible on any data type. > would this imply uniqueness? It would. > Or would I have to write something like TEXT

[sqlite] TEXT PRIMARY KEY

When defining a column TEXT PRIMARY KEY (is that possible on TEXT?), would this imply uniqueness? Or would I have to write something like TEXT PRIMARY KEY UNIQUE ? -- Christoph Kukulies ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] compressing BLOB

Since I'm inserting large files into the DB I'm wondering whether Sqlite can do compression on the data BLOB by itself or whether I should do that by programming when creating the BLOB? -- Christoph Kukulies ___ sqlite-users mailing list

Re: [sqlite] Possible Solution to Sqlite3BTreeMovetoUnpacked SQLite SELECT Statement Workaround paradox

Dan Kennedy, I discovered yesterday that one can use sqlite prepared SQLITE statements to reduce the CPU and memory utilization of parsing SELECT statements used to substitute for Sqlite3BTreeMovetoUnpacked and sqlite3_blob_reopen. Thank you From: frank_chan...@hotmail.com To: