Re: [sqlite] Bug? Empty string stored as NULL?

[Roger Binns]

-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?

[Marc L. Allen]

> 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?

[Luuk]

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?

[Roger Binns]

-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?

[Pavel Ivanov]

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. 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?
>
> 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?

[Puneet Kishor]

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?

[Marc L. Allen]

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