Re: [sqlite] Recommended (Windows/Linux) SQLite utilities
On Tue, Apr 29, 2008 at 10:10 PM, <[EMAIL PROTECTED]> wrote: > P Kishor, > > > http://www.sqlite.org/cvstrac/wiki?p=ManagementTools > > Oops, that's embarrassing! > > Do you have a favorite on this page that you would recommend? > > Thank you, > Malcolm tkSQLite at http://reddog.s35.xrea.com/wiki/TkSQLite.html Tcl script as well as standalone executable binary versions for both Linux and Microsoft Windows. BSD license. Updated frequently, current version wraps SQLite 3.5.7. Robert Wishlaw ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select row names that start with this string...
On 4/9/08, Richard Klein <[EMAIL PROTECTED]> wrote:
> > Richard Klein <[EMAIL PROTECTED]>
> > wrote:
> >> One question: I thought that the '\' character is not part of the
> >> SQL standard. (That's why I specified '/' instead as my ESCAPE
> >> character).
> >
> > I'm not sure I understand. You can use any character as an escape
> > character. E.g. LIKE '2!_%' ESCAPE '!'. A backslash is as good as any.
> >
> > Igor Tandetnik
>
> I'm a SQL newbie, so I may very well be wrong about this ...
>
> My understanding is that you can use any legal SQL character as an escape
> character, but that a backslash is not a legal SQL character.
>
> I got this impression from a sentence I read on the SQLite website (in
> the description of literal numeric values):
>
> "C-style escapes using the backslash character are not supported because
> they are not standard SQL."
>
If I understand correctly Igor Tandetnik's comment "A backslash is as
good as any.", in the above quote the phrase "they are not standard
SQL" refers to C-style escapes and not to the use of the backslash
character as an escape defined with ESCAPE.
LIKE '2\_%'
is not valid.
LIKE '2\_%' ESCAPE '\'
is valid.
Robert Wishlaw
> So then I checked the spec, and found syntax definitions (reproduced at
> the end of this email) that do seem to confirm that backslash ('\') is
> not a legal SQL language character.
>
> I haven't checked to see whether SQLite accepts backslash as an escape
> character.
>
> - Richard Klein
>
> ==
>
>::=
>
>|
>|
>
>::=
>
>|
>
>::=
> A | B | C | D | E | F | G | H | I | J | K | L | M | N |
> O
>| P | Q | R | S | T | U | V | W | X | Y | Z
>
>::=
> a | b | c | d | e | f | g | h | i | j | k | l | m | n |
> o
>| p | q | r | s | t | u | v | w | x | y | z
>
>::=
>0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9
>
>::=
>
>|
>|
>|
>|
>|
>|
>|
>|
>|
>|
>|
>|
>|
>|
>|
>|
>|
>|
>|
>|
>
>::= !! space character in character set in use
>
>::= "
>
>::= %
>
>::= &
>
>::= '
>
>::= (
>
>::= )
>
>::= *
>
>::= +
>
>::= ,
>
>::= -
>
>::= .
>
>::= /
>
>::= :
>
>::= ;
>
>::= <
>
>::= =
>
>::= >
>
>::= ?
>
>::= [
>
>::= ]
>
>::= _
>
>::= |
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Q: Importing text containing nonbreaking spaces
On 2/16/08, Coatimundi <[EMAIL PROTECTED]> wrote: > > I hope this questions isn't a FAQ... > > I have a tab-delimited text file which I .import using .sep "\t" into a > freshly declared table whose columns are of type TEXT. > > The program which generates the text file exports each "empty" field (if > any) as a single non-breaking space, i.e. 0xA0. > > SQLite 3.5.4 .import's the data which complaining, not surprisingly. > But the non-breaking space is rendered in SELECT output as a lower-case > acute-a, aka U+00E1. > This is a Windows code page problem. Your output data to is being displayed as CodePage 437 which displays an acute-a for OxA0. For details see http://en.wikipedia.org/wiki/Code_page_437 Postgresql also had this output problem but solved it with a patch discussed around this thread node http://archives.postgresql.org/pgsql-hackers-win32/2005-01/msg00227.php Robert Wishlaw > I'm certainly no Unicode expert. I end up doing an UPDATE on each > column to replace á with an empty string. > > Obviously, I could also preprocess my textfile to remove the 0xA0. But > can someone help me understand what's going on here? > > Thanks! > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Precompiled binaries for Windows Not Version 3.5.5
Under the Precompiled binaries for Windows section of the downloads page, the sqlite-3.5.5.zip, sqlitedll-3.5.5.zip and tclsqlite-3.5.5.zip files contain 3.5.4 binaries from December 12, 2007. Robert Wishlaw - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] .import NULL
On Jan 23, 2008 7:54 AM, Dennis Cote <[EMAIL PROTECTED]> wrote: > Robert Wishlaw wrote: > > When .import parses an empty field in the csv file, that is , a comma > > immediately followed by another comma, is the cell, in the database, > > that corresponds to the empty field a NULL cell? > > > Robert, > > No, it inserts a text field containing an empty string. Thank you Dennis, this is what I needed to know. > > > All the fields inserted by .import are strings. If they are inserted > into columns with numeric affinity they are converted to numeric values > when stored into the database. The column was created "AS REAL". > > > > The reason I ask is that when I query the column with > > > > SELECT COUNT(*) FROM table WHERE temp ISNULL; > > > > 0 is returned but there are 5 empty cells in that column in the csv > file. > > > > > This confirms that you don't have any NULL values in that column. My expected value of 5 is returned from the query SELECT COUNT(*) FROM table WHERE temp = ""; or SELECT COUNT(*) FROM table WHERE temp <> 0 AND NOT temp; > > > > Also when I query > > > > SELECT temp FROM table WHERE temp > 0; > > > > the empty cells are returned as well as the cells containing > > temperatures over 0. > > > > > > > This is because you are comparing a numeric value to a string. It's an > empty string, but a string none the less. All strings are greater than > any numeric value (see section 3 Comparison Expressions at > http://www.sqlite.org/datatype3.html for the details of how SQLite > compares values) so in these rows, the empty string is greater than the > literal numeric value of 0. Thank you again for your concise yet comprehensive explanation of the consequences of using .import on an empty field in a .csv file. Robert Wishlaw > > > HTH > Dennis Cote > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
[sqlite] .import NULL
Using sqlite3 3.5.4 I have created a SQLite database and loaded it from a csv file using the sqlite .import statement with a comma separator. When .import parses an empty field in the csv file, that is , a comma immediately followed by another comma, is the cell, in the database, that corresponds to the empty field a NULL cell? The reason I ask is that when I query the column with SELECT COUNT(*) FROM table WHERE temp ISNULL; 0 is returned but there are 5 empty cells in that column in the csv file. Also when I query SELECT temp FROM table WHERE temp > 0; the empty cells are returned as well as the cells containing temperatures over 0. Robert Wishlaw - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)
On 12/11/07, Robert Wishlaw <[EMAIL PROTECTED]> wrote: > On 12/11/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > > --- [EMAIL PROTECTED] wrote: > > > The current name resolution rules for SQLite are that it > > > first tries to resolve names using just the tables in the > > > join. If that fails, then it looks at result column aliases. > > > I think that approach continues to work on WHERE. But I need > > > to reverse the search order on ORDER BY - the column aliases > > > need to take precedence over tables in the join. > > > > Should GROUP BY follow the WHERE resolution rules or the proposed > > new ORDER BY rules? > > > > Given: > > > > create table t1(a INT, b INT, c INT); > > insert into t1 values(1, 2, 4); > > insert into t1 values(2, -1000, 5); > > > > mysql> select a, a+b AS c from t1 group by c, a order by c, a; > > +--+--+ > > | a| c| > > +--+--+ > > |2 | -998 | > > |1 |3 | > > +--+--+ > > > > postgres=> select a, a+b AS c from t1 group by c, a order by c, a; > > ERROR: column "t1.b" must appear in the GROUP BY clause or be used in an > > aggregate function > > SQL Server Compact Edition Version 3.5, Note well ! Output is unformatted from VB.NET WriteLine in an ExecuteReader. CREATE TABLE t1(a int, b int, c int); INSERT INTO t1 VALUES(1,2,4); INSERT INTO t1 VALUES(2, -1000, 5); SELECT a+b AS c FROM t1 WHERE c=4; SQL Server Compact Edition Version 3.5 returns 3 select a AS foo from t1 where foo=1; SQL Server Compact Edition Version 3.5 returns Unhandled Exception: The column name is not valid. [ Node name (if any) = ,Column name = foo ] select a AS foo from t1 where "foo"=1; SQL Server Compact Edition Version 3.5 returns Unhandled Exception: The column name is not valid. [ Node name (if any) = ,Column name = foo ] select a AS "foo" from t1 where "foo"=1; SQL Server Compact Edition Version 3.5 returns Unhandled Exception: The column name is not valid. [ Node name (if any) = ,Column name = foo ] select a AS "foo" from t1 union select b from t1 order by foo; SQL Server Compact Edition Version 3.5 returns -1000 1 2 select a+b AS "c" from t1 union select b from t1 order by c; SQL Server Compact Edition Version 3.5 returns -1000 -998 2 3 select * from t1; SQL Server Compact Edition Version 3.5 returns 1 2 4 2 -1000 5 select a, a+b AS "c" from t1 order by c; SQL Server Compact Edition Version 3.5 returns 2 -998 1 3 select a, a+b AS c from t1 order by c; SQL Server Compact Edition Version 3.5 returns 2 -998 1 3 select a AS foo from t1 union select b from t1 order by foo; SQL Server Compact Edition Version 3.5 returns -1000 1 2 select a, a+b AS c from t1 UNION ALL select a AS c, c AS a from t1 order by c; SQL Server Compact Edition Version 3.5 returns 2 -998 1 3 1 4 2 5 select a, a+b AS c from t1 group by c, a order by c, a; SQL Server Compact Edition Version 3.5 returns Unhandled Exception: In aggregate and grouping expressions, the SELECT clause can contain only aggregates and grouping expressions. [ Select clause = ,b ] Robert Wishlaw - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)
On 12/11/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > --- [EMAIL PROTECTED] wrote: > > The current name resolution rules for SQLite are that it > > first tries to resolve names using just the tables in the > > join. If that fails, then it looks at result column aliases. > > I think that approach continues to work on WHERE. But I need > > to reverse the search order on ORDER BY - the column aliases > > need to take precedence over tables in the join. > > Should GROUP BY follow the WHERE resolution rules or the proposed > new ORDER BY rules? > > Given: > > create table t1(a INT, b INT, c INT); > insert into t1 values(1, 2, 4); > insert into t1 values(2, -1000, 5); > > mysql> select a, a+b AS c from t1 group by c, a order by c, a; > +--+--+ > | a| c| > +--+--+ > |2 | -998 | > |1 |3 | > +--+--+ > > postgres=> select a, a+b AS c from t1 group by c, a order by c, a; > ERROR: column "t1.b" must appear in the GROUP BY clause or be used in an > aggregate function > IBM DB2 9.5 -- Commands Entered -- select a, a+b AS c from t1 order by c; select a AS foo from t1 union select b from t1 order by foo; select a, a+b AS c from t1 UNION ALL select a AS c, c AS a from t1 order by c; select a, a+b AS c from t1 group by c, a order by c, a; -- select a, a+b AS c from t1 order by c A C --- --- 2-998 1 3 2 record(s) selected. select a AS foo from t1 union select b from t1 order by foo SQL0206N "FOO" is not valid in the context where it is used. SQLSTATE=42703 select a, a+b AS c from t1 UNION ALL select a AS c, c AS a from t1 order by c SQL0206N "C" is not valid in the context where it is used. SQLSTATE=42703 select a, a+b AS c from t1 group by c, a order by c, a SQL0119N An expression starting with "B" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified. SQLSTATE=42803 Robert Wishlaw - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)
On 12/10/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > --- [EMAIL PROTECTED] wrote: > > Dennis Cote <[EMAIL PROTECTED]> wrote: > > > [EMAIL PROTECTED] wrote: > > > > > > > > Can you please tell me what > > > > other databases do with this: > > > > > > > >CREATE TABLE t1(a,b,c); > > > >INSERT INTO t1 VALUES(1,2,4); > > > >SELECT a+b AS c FROM t1 WHERE c==4; > > > > > > > > In the WHERE clause, should the "c" resolve to > > > > the column "c" or to the "a+b" expression? I'm > > > > guessing the "a+b" expression. But SQLite is > > > > currently resolving the name to the column "c" > > > > in table t1. Thus SQLite currently answers > > > > "3" to the SELECT statement, when I think it > > > > should give an empty set. Or maybe it should give > > > > an error? > > > > > > > > Opinions, anyone? > > > > > > According to the where clause definition in the SQL:1999 standard the > > > "c" in the where clause should refer to the column in table "t1" which > > > is the result of the preceding from clause. To conform to the standard > > > SQLite should return 3. > > > > I never would have guessed things worked that way. But then > > again, SQL is not noted for making a whole lot of sense. > > I figure if you get agreement between many different databases, they > probably follow the standard. Or is it vice versa? > > It doesn't appear to be possible to use column aliases in the WHERE > clause of postgres and MySQL. So they seem to have interpreted the > standard in the same way. I thought sqlite's useful WHERE clause column > alias extension was common. Perhaps not. > > postgres=> select a AS foo from t1 where foo=1; > ERROR: column "foo" does not exist > postgres=> select a AS foo from t1 where "foo"=1; > ERROR: column "foo" does not exist > postgres=> select a AS "foo" from t1 where "foo"=1; > ERROR: column "foo" does not exist > > mysql> select a AS foo from t9 where foo=1; > ERROR 1054 (42S22): Unknown column 'foo' in 'where clause' > mysql> select a AS foo from t9 where "foo"=1; > Empty set, 1 warning (0.00 sec) > > MySQL seems to interpret the WHERE clause "foo" as the string literal 'foo'. > > But ORDER BY is a different story: > > mysql> select a AS "foo" from t1 union select b from t1 order by foo; > +--+ > | foo | > +--+ > |1 | > |2 | > +--+ > > postgres=> select a AS "foo" from t1 union select b from t1 order by foo; > foo > - >1 >2 > IBM DB2 9.5 select a AS "foo" from t1 union select b from t1 order by foo SQL0206N "FOO" is not valid in the context where it is used. SQLSTATE=42703 > postgres=> select a+b AS "c" from t1 union select b from t1 order by c; > c > --- > 2 > 3 > IBM DB2 9.5 select a+b AS "c" from t1 union select b from t1 order by c SQL0206N "C" is not valid in the context where it is used. SQLSTATE=42703 > Let's add another row to table t1... > > postgres=> insert into t1 values(2, -1000, 5); > INSERT 0 1 > test=> select * from t1; > a | b | c > ---+---+--- > 1 | 2 | 4 > 2 | -1000 | 5 > > postgres=> select a, a+b AS "c" from t1 order by c; > a | c > ---+-- > 2 | -998 > 1 |3 > > > mysql> select * from t1; > +--+---+--+ > | a| b | c| > +--+---+--+ > |1 | 2 |4 | > |2 | -1000 |5 | > +--+---+--+ > > mysql> select a, a+b AS "c" from t1 order by c; > +--+--+ > | a| c| > +--+--+ > |2 | -998 | > |1 |3 | > +--+--+ > > which differs from: > > SQLite version 3.5.1 > sqlite> select * from t1; > a b c > -- -- -- > 1 2 4 > 2 -1000 5 > > sqlite> select a, a+b AS "c" from t1 order by c; > a c > -- -- > 1 3 > 2 -998 > > Which database is correct? > IBM DB2 9.5 select * from t1; a b c -- -- -- 1 2 4 2 -1000 5 select a, a+b AS "c" from t1 order by c; a c -- -- 1 3 2 -998 Robert Wishlaw - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)
On 12/8/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > --- Robert Wishlaw <[EMAIL PROTECTED]> wrote: > > > >CREATE TABLE t1(a,b,c); > > > IBM DB2 9.5 > > > > INSERT INTO t1 VALUES(1,2,4); > > SELECT a+b AS c FROM t1 WHERE c=4; > > > > returns > > > > C > > > >3 > >3 > >3 > >3 > >4 record(s) selected > > How could more than one row be returned if t1 only has one row? > t1 has 4 rows. Why does t1 have 4 rows? Blame me. Robert Wishlaw - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)
On Dec 8, 2007 11:49 PM, Trey Mack <[EMAIL PROTECTED]> wrote:
> > Can you please tell me what
> > other databases do with this:
> >
> >CREATE TABLE t1(a,b,c);
> >INSERT INTO t1 VALUES(1,2,4);
> >SELECT a+b AS c FROM t1 WHERE c==4;
>
> SQL Server returns a 3.
> ORACLE 9 returns a 3.
>
>
IBM DB2 9.5
INSERT INTO t1 VALUES(1,2,4);
SELECT a+b AS c FROM t1 WHERE c=4;
returns
C
3
3
3
3
4 record(s) selected
Robert Wishlaw
Re: [sqlite] Suggests for improving the SQLite website
Reduce the size of the logo. The proposed combined size of the logo and Navbar takes up too much of the page. I like frames so that the Navbar is always visible. This is a real convenience with long pages. If you don't like frames then another alternative is to have the Navbar at the top and bottom of the page. Robert Wishlaw
Re: [sqlite] UTF-8 BLOB
On Nov 6, 2007 8:55 AM, Joe Wilson <[EMAIL PROTECTED]> wrote: > It works in a Linux xterm. > There's probably some UTF or codepage issue with the Windows console. > Try using another command-line shell. > Thank you for your response Joe. It is a codepage issue. Using sqlite3.exe 3.5.2 from a Windows XP cmd.exe box, I tried SELECT blb FROM textblob WHERE blb IS NOT NULL; and got codepage 437 representations of the BLOB data in the blb field. Using the same query with tclsh and the tclsqlite3.dll 3.5.2 the output in the Windows XP cmd.exe box was a codepage 850 representation of the data. Also the sqlite3.exe 3.5.2 query SELECT dec, hex FROM textblob WHERE blb = 'À0'; worked when I substituted the codepage 437 glyphs for À0. I have yet to suceed with tclsh. Thanks again for the hint. I doubt that I would have remembered these codepage discrepancies if you hadn't reminded me. It has been several years since I last had this problem. Robert Wishlaw > --- Robert Wishlaw <[EMAIL PROTECTED]> wrote: > > > Using sqlite 3.5.2 on Windows XP, I have a textblob.csv file > > > > 192,C0,À0,À0 > > 193,C1,Á0,Á0 > > 254,FE,þ0,þ0 > > 255,FF,ÿ0,ÿ0 > > > > which I have imported into a new database > > > > sqlite3 textblob.db < textblob.sql > > > > via a file named textblob.sql > > > > .separator , > > create Table textblob(dec INTEGER, hex BLOB, txt TEXT, blb BLOB); > > .import 'textblob.csv' textblob > > > > When I run the query > > > > SELECT dec, hex FROM textblob WHERE blb = 'À0'; > > > > there is no result. Likewise > > > > SELECT dec, hex FROM textblob WHERE blb = "À0"; > > > > does not work. > > > > SELECT dec, hex FROM textblob WHERE hex = "C0"; > > > > returns > > > > 192|C0 > > > > Any idea how to get the BLOB data? Or is the problem that the BLOB > > data is not there because .import is filtering out invalid UTF-8? > > > > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] UTF-8 BLOB
Using sqlite 3.5.2 on Windows XP, I have a textblob.csv file 192,C0,À0,À0 193,C1,Á0,Á0 254,FE,þ0,þ0 255,FF,ÿ0,ÿ0 which I have imported into a new database sqlite3 textblob.db < textblob.sql via a file named textblob.sql .separator , create Table textblob(dec INTEGER, hex BLOB, txt TEXT, blb BLOB); .import 'textblob.csv' textblob When I run the query SELECT dec, hex FROM textblob WHERE blb = 'À0'; there is no result. Likewise SELECT dec, hex FROM textblob WHERE blb = "À0"; does not work. SELECT dec, hex FROM textblob WHERE hex = "C0"; returns 192|C0 Any idea how to get the BLOB data? Or is the problem that the BLOB data is not there because .import is filtering out invalid UTF-8? Robert Wishlaw - To unsubscribe, send email to [EMAIL PROTECTED] -
