Re: [sqlite] Bug? Empty string stored as NULL?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/03/12 11:59, Marc L. Allen wrote: > I use Navicat Lite, and it apparently treats an empty string as a NULL > when displaying. It is far more likely to display the empty string as an empty string and to also display null as an empty string (by default) otherwise the nulls will litter your output which often isn't helpful. The SQLite shell does exactly the same thing by default. You can however tell the SQLite shell to display null as a string of your choice, but then you have the problem of telling the difference between that and a string of the same value. Navicat probably has a similar setting somewhere. Incidentally my Python SQLite wrapper includes a shell, which you can use without knowing anything about Python: http://apidoc.apsw.googlecode.com/hg/shell.html One of the things it does is to colour output which means you could tell the difference between the null value (shown in red) and a string (shown in yellowish). You can also tell the difference between the integer 3 and the string "3" due to the colour. Makes life a lot easier. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk9ZQ1kACgkQmOOfHg372QQbdACg1CrjbyGXu0UaZX+DzFDD+mCY /ssAoIct31OJ5JDZRilBuPtN9+6x9cro =066G -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug? Empty string stored as NULL?
> A good rule of thumb is that if you claim a bug in another component > you are almost certainly wrong. I don't have the command line version on this machine. I use Navicat Lite, and it apparently treats an empty string as a NULL when displaying. I always check my own code. I didn't expect Navicat to make such an error. Sorry.. and thanks. Marc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug? Empty string stored as NULL?
On 08-03-2012 20:48, Marc L. Allen wrote: > Is that expected? To me, '' is different than NULL. > > create table a > ( > a text > ); > > insert into a select ''; > select * from a; > > Is there a way to store an empty string? > insert into a values(null); .nullvalue NULL select * from a; a|length(a) |0 NULL|NULL ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug? Empty string stored as NULL?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 A good rule of thumb is that if you claim a bug in another component you are almost certainly wrong. On 08/03/12 11:48, Marc L. Allen wrote: > Is that expected? To me, '' is different than NULL. They are indeed different. > Is there a way to store an empty string? Yes, store an empty string. sqlite> create table a(a text); sqlite> insert into a select ''; sqlite> insert into a select null; sqlite> select * from a; sqlite> select typeof(a) from a; text null sqlite> select 'X' || a || 'Y' from a; XY sqlite> Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk9ZDqoACgkQmOOfHg372QSsAgCgjkuP3HrMs1eQA9IznpTEMYpw KTsAoMsDH9DONdp0eJJn82TWW5ZtQeT1 =nrBP -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug? Empty string stored as NULL?
Where do you see NULL? I see empty string. SQLite version 3.7.5 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table a (t text); sqlite> insert into a select ''; sqlite> .nullvalue NULL sqlite> select * from a; sqlite> Pavel On Thu, Mar 8, 2012 at 2:48 PM, Marc L. Allenwrote: > Is that expected? To me, '' is different than NULL. > > create table a > ( > a text > ); > > insert into a select ''; > select * from a; > > Is there a way to store an empty string? > > Marc > > -- > ** > * * * > * Marc L. Allen * "... so many things are * > * * possible just as long as you * > * Outsite Networks, Inc. * don't know they're impossible." * > * (757) 853-3000 #215 * * > * * * > * mlal...@outsitenetworks.com * -- The Phantom Tollbooth * > * * * > ** > ___ > 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] Bug? Empty string stored as NULL?
On Mar 8, 2012, at 1:48 PM, Marc L. Allen wrote: > Is that expected? To me, '' is different than NULL. > > create table a > ( >a text > ); > > insert into a select ''; > select * from a; > > Is there a way to store an empty string? > works for me punkish@mumbai ~$sqlite3 SQLite version 3.7.10 2012-01-16 13:28:40 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE t (a TEXT NOT NULL); sqlite> INSERT INTO t SELECT ''; sqlite> SELECT * FROM t; sqlite> SELECT Count(*) FROM t; 1 sqlite> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug? Empty string stored as NULL?
Is that expected? To me, '' is different than NULL. create table a ( a text ); insert into a select ''; select * from a; Is there a way to store an empty string? Marc -- ** * * * * Marc L. Allen * "... so many things are * * * possible just as long as you* * Outsite Networks, Inc. * don't know they're impossible." * * (757) 853-3000 #215 * * * * * * mlal...@outsitenetworks.com * -- The Phantom Tollbooth * * * * ** ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users