Re: [sqlite] Database size

This is so good to hear! The advances in database theory and practice have put the old ideas to rest. Hooray for today! Ted On 09/01/2012 07:08 PM, Richard Hipp wrote: > On Sat, Sep 1, 2012 at 6:34 PM, Ted Rolle, Jr. wrote: > >> Back in the olden days we predicted a database

Re: [sqlite] Database size

Most companies got through it with few problems --- a tribute to the programming staff. Ted On 09/01/2012 07:51 PM, Simon Slavin wrote: > On 1 Sep 2012, at 11:34pm, "Ted Rolle, Jr." wrote: > >> Back in the olden days we predicted a database's storage to be about 5 >

Re: [sqlite] How do you load a ".csv" and skip the first line?

Text editor to remove the offending line(s)? This way you can see what you've removed. The other solutions (tail, less, more) would work better for LARGE files. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/l

[sqlite] Database size

Back in the olden days we predicted a database's storage to be about 5 times the size of the data. By 'olden' I mean IBM's IMS, VSAM, DB2. ..., 70s, 80s. I hope this is still not the case... Ted ___ sqlite-users mailing list sqlite-users@sqlite.org http:

Re: [sqlite] Which data type is better for date?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 And in addition, the TZ offset might be handy to convert to UTC. Local time is locally determined while UTC is constant, and other local offsets can be applied to display time in local terms. For example, EST is UTC-5; EDT is UTC-4; PST is UTC-8; PDT

Re: [sqlite] Length=10

So simple. I never thought of using the command-line sqlite3... > in the sqlite3 shell, set output mode to file, and then select as > above. All your select output will go to your file. > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://s

[sqlite] Length=10

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have a table of UPCs with lengths varying from 6 to 12. I'd like to print those with length=10 to a file for printing. SELECT * FROM UPCs WHERE LENGTH(UPC)=10; works just fine. But when I export the table I naturally get the whole table. Would

[sqlite] Removing duplicate entries from a database

I just found an elegant query to identify duplicate entries in a table. It is from Microsoft. http://support.microsoft.com/kb/139444 Here's the information: create table t1(col1 int, col2 int, col3 char(50)) insert into t1 values (1, 1, 'data value one') insert into t1 values (1, 1, 'data value

Re: [sqlite] Numbers as CHARs.

On Tue, 6 Jul 2010 17:13:44 -0500 P Kishor wrote: > I have no idea why you would say that. It works just fine. > > sqlite> CREATE TABLE UPCs (UPC TEXT); > sqlite> INSERT INTO UPCs VALUES ('043000205563'); > sqlite> SELECT * FROM UPCs; > UPC > > 043000205563 > sqlite> Oh, by the way,

Re: [sqlite] Numbers as CHARs.

On Tue, 6 Jul 2010 17:13:44 -0500 P Kishor wrote: > sqlite> CREATE TABLE UPCs (UPC TEXT); > sqlite> INSERT INTO UPCs VALUES ('043000205563'); > sqlite> SELECT * FROM UPCs; > UPC > > 043000205563 > sqlite> I did as you said with sqlite and it worked perfectly. Thank you. This is pr

[sqlite] Numbers as CHARs.

I, (or more to the point, SQLite) can't seem to retain leading zeros on numbers. The receiving field is defined as CHAR; I'm using the SQLite Manager in Firefox. I've also tried sqlite3 from the command line. Here's a typical (and minimal) statement: UPDATE UPCs SET UPC=043000205563; UPDATE UPCs S

Re: [sqlite] Oracle joins the SQLite Consortium

On Fri, 25 Jun 2010 01:46:08 +0100 Simon Slavin wrote: > > On 24 Jun 2010, at 10:38pm, Ted Rolle, Jr. wrote: > > > I can't see anything good coming out of this if there are lawyers > > involved. > > Big North American companies like Oracle don't do /anyt

Re: [sqlite] Oracle joins the SQLite Consortium

I can't see anything good coming out of this if there are lawyers involved. -- __ 3.14159265358979323846264338327950 Let the spirit of pi 2884197169399375105820974944592307 spread all over the world! 81640628620899862803482534

Re: [sqlite] Why is there no test suite for ''make check' ?

Does the TEA tarball have the tests? -- __ 3.14159265358979323846264338327950 Let the spirit of pi 2884197169399375105820974944592307 spread all over the world! 8164062862089986280348253421170679 http://pi314.at PI VOBISCUM!

Re: [sqlite] UTC/GMT conversion back and forth

On Fri, 28 May 2010 20:12:54 +0300 Andrejs Igumenovs wrote: Finally! A question that I can answer. For all practical (and most impractical) (Drum roll) UTC = GMT, or alternatively, GMT = UTC UTC is Coordinated Universal Time, which one would think would be abbreviated CUT, but one French-speakin

Re: [sqlite] Leading zeros disappear

Thanks to all who replied! Single quotes? How simple...I used double quotes... Ted -- __ 3.14159265358979323846264338327950 Let the spirit of pi 2884197169399375105820974944592307 spread all over the world! 8164062862089986

[sqlite] Leading zeros disappear

I'm entering UPCs. 0071318119 becomes 71318119. This is not good, or expected, for that matter . The column is specified as TEXT. I thought that TEXT values went in 'as entered'. After reading the docs, Section 2.0: "If numerical data is inserted into a column with TEXT affinity it is converted

Re: [sqlite] copy one row to another

I tried to do that, and hit walls all over the place. My solution was to import the table into OpenOffice Calc and move the columns around there. Not too elegant, but it worked. Ted On Wed, Apr 7, 2010 at 4:33 PM, P Kishor wrote: > is there a canonical way of copying all the columns (except fo

[sqlite] INSERT failing.

Here's the table definition: 1 CREATE TABLE jokes ( 2 sourceTEXT, 3 language TEXT, 4 question TEXT, 5 answerTEXT 6 ); This works. This doesn't: INSERT INTO jokes VALUES ("Radio Eriwan","de_DE","Frage an Radio Eriwan: An welchem Tag genau starb Josef Stalin?","Man we

[sqlite] Living dangerously.

How/where do I set the 'Living Dangerously' flag? Is it case-sensitive? -- _ 3.14159265358979323846264338327950Let the spirit of pi 2884197169399375105820974944592307 spread all over the world! 81640628620899862

Re: [sqlite] .importing file into a BLOB

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 09 Dec 2009 11:48:31 -0800 Roger Binns wrote: > http://catb.org/~esr/faqs/smart-questions.html What wasn't clear? I apologize for any murkiness. My understanding is that a BLOB takes any piece of data and stores it away intact. Is it the co

[sqlite] .importing file into a BLOB

When I insert a gpg-encrypted .asc file command-line sqlite3 inserts each line as a separate BLOB. This is not what I want. I want the whole file to be in the BLOB. I tried inserting a .gpg file but x'0a' signals a new line with resulting 'Expected 1 column but found 2'. Where to from here? --- ___

[sqlite] NULL vs ''

When I .import a file into a table, || results in a value of '' for the data item. How can I get a NULL value stored? This also happens when the item is at the end (EOL) of the row. Currently, I: UPDATE table SET value=NULL where value=''; Ted --- _

Re: [sqlite] Putting images into SQLite.

On Sun, 6 Dec 2009 17:57:06 -0800 Stephan Wehner wrote: Base64? Then just add it with an .import statement? I thought about that with MIME encoding. Ted --- 3.14159265358979323846264338327950 Let the spirit of pi spread 28841971

[sqlite] Putting images into SQLite.

>From what I read, it is necessary to have a programmatic interface to put images into a database. True? --- 3.14159265358979323846264338327950 Let the spirit of pi spread 2884197169399375105820974944592307 all around the world! 8

[sqlite] It's done, except for this nit:

I need for computed total price to display with leading and trailing zeros. E.g. 1 -> 1.00 2.5 -> 2.50 .9 -> 0.90 The extended price is computed with this statement: round(purchase.qty*inventory.price/100.0,2) Ted 3.1415926535897

[sqlite] SQLite data types.

I'm laboring under a false impression. I thought that the SQLite data types were limited to text, integer, float, and something else. On the SQLite site there's Currency, date-time with and without timestamps. Where can I find a listing of these? Ted ___

Re: [sqlite] Removing the 'Delete message(s)' confirmation.

On Tue, 17 Nov 2009 11:39:43 -0500 Pavel Ivanov wrote: Oopsie... senior moment. I've since posted it to the correct group, Claws-Mail. Ted ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-u

[sqlite] Removing the 'Delete message(s)' confirmation.

I'd like to remove the delete confirmation when I delete a message or messages. Is this in Configuraton->Preferences? Am I just missing something? Ted ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo

Re: [sqlite] WASP

On Sun, 8 Nov 2009 14:48:08 -0600 P Kishor wrote: > DBD::SQLite Hmmm...no 4th step. That's one of the most important steps. But then I'm used to 12 steps... :-) I don't know Perl. Is Perl 'under the covers' when I use PHP? Ted ___ sqlite-users maili

[sqlite] 5-second display in SQLite Manager

When I put the cursor over a field that is too big to fit in the column on the screen, it displays for about 5 seconds. What can I do to prolong the display? Or to make it display until I move the cursor to another line. Ted ___ sqlite-users mailing list

Re: [sqlite] Column as a substring

On Sun, 8 Nov 2009 10:31:05 -0500 "Igor Tandetnik" wrote: > > SELECT * > > FROM fm > > WHERE name LIKE '%Juiian%' > > AND info LIKE '%' || name || '%' > > ORDER by name; > > Thanks for seeing the oversight. When I use SELECT * FROM fm WHERE name LIKE '%Julian%' OR info LIKE '%' || name |

Re: [sqlite] Column as a substring

On Sun, 08 Nov 2009 15:06:24 +0100 Jean-Christophe Deschamps wrote: > select * from lemma where 'Whoever you love' like '%' || base || '%'; I first tried that with this SQL statement: SELECT * FROM fm WHERE name LIKE '%Juiian%' AND info LIKE '%' || name || '%' ORDER by name; returned 0

Re: [sqlite] Firefox SQLite Manager extension troubles.

On Sun, 08 Nov 2009 06:40:14 +0100 Gary_Gabriel wrote: > > Go to Database -> Connect Database -> Select SQLite Database -> > Browse to the new file. > It opens this SQLite file. > > - Gary Thank you for your kind answer. I've done as you said many times, but SQLite Manager still looks for the

Re: [sqlite] WASP

On Sat, 7 Nov 2009 20:39:22 -0500 "Igor Tandetnik" wrote: > Ted Rolle wrote: > > First there was LAMP (Linux, Apache, MySQL, PHP), then WAMP > > (Windows, Apache MySQL, PHP). > > Now we need a WASP. > > > > I don't relish the thought of convert

[sqlite] WASP

First there was LAMP (Linux, Apache, MySQL, PHP), then WAMP (Windows, Apache MySQL, PHP). Now we need a WASP. I don't relish the thought of converting my SQLite database to MySQL just to keep some package happy. If my Linux box were up, it would be a no-brainer. I'd compile it for SQLite. But a

[sqlite] Firefox SQLite Manager extension troubles.

I'm using the Firefox SQLite Manager extension. I've renamed the database file. I get this message: 'The file does not exist anymore: E:\Documents and Settings\ted\Books\William R. Denslow\10' Well, duh. Where is the SQL database file name stored in the Firefox SQLite Manager extension? I've check

Re: [sqlite] How to make LIKE searches case-sensitive

On Sat, 31 Oct 2009 18:43:46 +0100 Sylvain Pointeau wrote: > http://www.sqlite.org/pragma.html#pragma_case_sensitive_like Thanks, Sylvain! I'm more impressed with SQLite every day. Ted ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqli

[sqlite] How to make LIKE searches case-sensitive

I'd like to make LIKE '%Blah%/ only find 'Blah', and not 'blah'. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to skip the first field on .import

On Fri, 30 Oct 2009 12:17:53 -0700 Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Ted Rolle wrote: > > How do I let this start out at the default value and auto increment? > > My column separator is '|'. > > Create a temporar

[sqlite] How to skip the first field on .import

The first field in my table is ID primary integer autoincrement. I read that if it is set to NULL it defaults to the maximum value possible. Not a Good Thing(tm). How do I let this start out at the default value and auto increment? My column separator is '|'. Ted

Re: [sqlite] [Windows] Good GUI alternative to sqlite.exe?

On Fri, 30 Oct 2009 13:25:50 +0100 Gilles Ganault wrote: > On Mon, 26 Oct 2009 15:07:38 -0400, Sam Carleton > wrote: > >There is a WONDERFUL Firefox plug-in that I use, I love it! I am > >guessing it works on all OS's but I use it on Windows Vista and > >Windows 7. > > Thanks. I assume it's th

Re: [sqlite] Late data typing. Am I missing something?

On Thu, 29 Oct 2009 17:25:34 -0400 Pavel Ivanov wrote: > > For example, there seems to be no > > path to http://www.sqlite.org/lang_datefunc.html from > > http://.sqlite.org. > > Go to Documentation -> SQL Syntax -> date and time functions. > > Pavel > Uh...RTFM? Thanks! Ted

Re: [sqlite] Late data typing. Am I missing something?

I'm learning so much from this discussion! I've come to believe that late (lazy) typing is a Good Thing(tm). Um...how do I go to the page that describes the date-time information without just 'knowing' it's there? For example, there seems to be no path to http://www.sqlite.org/lang_datefunc.html

[sqlite] Late data typing. Am I missing something?

Doesn't dynamic data typing lead to bad data? And proliferation of home-grown editing routines? It seems that a strict data typing at column definition time would be MUCH better. For instance, date-editing routines... Ted ___ sqlite-users mailing list s

Re: [sqlite] Degree character not displayed correctly.

On Mon, 26 Oct 2009 16:06:33 +0100 Jean-Christophe Deschamps wrote: > Ted, > > > >I didn't insert it. I 'inherited' it from a (mercifully nameless) > >predecessor. > >I want to put this data into a database to make it easily accessible > > I'm no SQLite guru (really NO), but here is my 2 cent

Re: [sqlite] Degree character not displayed correctly.

On Mon, 26 Oct 2009 10:12:03 -0400 Pavel Ivanov wrote: > How do you insert it? How do you retrieve it? How do you display it? > I bet the problem is in the first question, not in the last one. > > Pavel I didn't insert it. I 'inherited' it from a (mercifully nameless) predecessor. I want to pu

[sqlite] Degree character not displayed correctly.

How can I get the degree character ° (0xB0) (as in 32 degrees Farenheit) to display correctly. My text editors (Vim and TextPad) and Claws-mailer display this character correctly. TextPad uses the 'ANSI' character set. Ted ___ sqlite-users mailing list s