Re: [sqlite] Text column: never used vs. set to empty string

[Simon Slavin]

On 17 Feb 2014, at 7:08pm, Tim Streater  wrote:

> Yes. I'm concluding that there's something fishy with the way my 
> data-gathering page is operating. I've recently added "use strict"; to my 
> javascript and that may be exposing something.

My web apps involve a hand-off between JavaScript (running on the user's 
browser) and PHP (running on the server, accessing the SQLite database).  Over 
the years the two have communicated various ways as standards have evolved, 
HTTPS has become available, and both languages have improved.  And each time I 
change how it works I run into some unexpected weird encoding problem.  Every 
single time.

The new debugging tools built into Chrome, FireFox and Safari are wonderful.  I 
mean completely unbelievable even a decade ago.  And these three lines for PHP 
transform it out of sight:

error_reporting(E_ALL);
ini_set('display_errors', '1');
ini_set('log_errors', '1');

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Text column: never used vs. set to empty string

[Tim Streater]

On 17 Feb 2014 at 18:38, Simon Slavin  wrote:

> On 17 Feb 2014, at 4:57pm, Tim Streater  wrote:
>
>> Thanks. I should perhaps have made it clearer that I'm looking at an issue a
>> user has. The application gathers some data from the user via a set of fields
>> they complete in a browser window, which data is then gathered up and sent
>> using ajax to be processed by a PHP script, which writes it to an sqlite db.
>> The user complains that some of this data doesn't make it, so I want to pin
>> down where in the chain this might be failing. Hence my Q.
>
> Ah.  In that case it's more likely to be writing zero-length strings.  In
> other words "a
> column into which no data has ever been entered" does not happen.
>
> I'm with Mister Beal and possibly yourself: your problem is outside of SQLite.

Yes. I'm concluding that there's something fishy with the way my data-gathering 
page is operating. I've recently added "use strict"; to my javascript and that 
may be exposing something.



--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Text column: never used vs. set to empty string

[Simon Slavin]

On 17 Feb 2014, at 4:57pm, Tim Streater  wrote:

> Thanks. I should perhaps have made it clearer that I'm looking at an issue a 
> user has. The application gathers some data from the user via a set of fields 
> they complete in a browser window, which data is then gathered up and sent 
> using ajax to be processed by a PHP script, which writes it to an sqlite db. 
> The user complains that some of this data doesn't make it, so I want to pin 
> down where in the chain this might be failing. Hence my Q.

Ah.  In that case it's more likely to be writing zero-length strings.  In other 
words "a
column into which no data has ever been entered" does not happen.

I'm with Mister Beal and possibly yourself: your problem is outside of SQLite.  
My only answer is to convert to Unicode as fast as possible then handle 
everything with Unicode-safe APIs.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Text column: never used vs. set to empty string

[RSmith]

Forgot to add: My headache was essentially UTF-8 encoding, but the same would happen with others, though invalid chars do not really 
exist in UTF7 or ANSI, but in the higher level encodings they are plentiful.



On 2014/02/17 19:35, RSmith wrote:


Yeah, I too have had real problems with this - It isn't limited to Latin1 either - but in my case I found one invalid character 
that doesn't conform to the specified encoding would drop the entire string in any encoding.  This was hard to trace because 99 
conversions would succeed with all kinds of weird and wonderful encoded characters, and then one suddenly returns an empty string.
I eventually traced this down to the OS's C API for encoding conversions which seems to simply drop the entire value and not even 
cause an error - just returns empty string. Peculiar behaviour if you ask me (though that is probably meant to prompt an error 
from the high-level code's design, but nobody told me...).  This seems the case for both Windows and OSX, I can't speak for Linux, 
Android, etc. maybe someone else knows?


Anyway, adding a conversion check is the key, but the point is moot if you rely on PHP or such to do the conversions for you. 
Scripts don't have GUIs... they cant really do much about it. Maybe an error log somewhere contains some info?


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Text column: never used vs. set to empty string

[RSmith]

On 2014/02/17 19:01, Stephan Beal wrote:

On Mon, Feb 17, 2014 at 5:57 PM, Tim Streater  wrote:


complete in a browser window, which data is then gathered up and sent
using ajax to be processed by a PHP script, which writes it to an sqlite
db. The user complains that some of this data doesn't make it, so I want to
pin down where in the chain this might be failing. Hence my Q.


FWIW, i have seen a similar problem in a legacy app which uses latin1
encoding in the DB. Latin1 doesn't always survive round-trip through PHP's
JSON APIs. My case was similar to yours, and we eventually determined that
the fields which got "lost" (set to null or empty values) were those which
came out of the latin1-encoded MySQL db containing invalid UTF8 characters
- the whole values were getting dropped upon transforming to JSON.

TL;DR: double-check all encodings.



Yeah, I too have had real problems with this - It isn't limited to Latin1 either - but in my case I found one invalid character that 
doesn't conform to the specified encoding would drop the entire string in any encoding.  This was hard to trace because 99 
conversions would succeed with all kinds of weird and wonderful encoded characters, and then one suddenly returns an empty string.
I eventually traced this down to the OS's C API for encoding conversions which seems to simply drop the entire value and not even 
cause an error - just returns empty string. Peculiar behaviour if you ask me (though that is probably meant to prompt an error from 
the high-level code's design, but nobody told me...).  This seems the case for both Windows and OSX, I can't speak for Linux, 
Android, etc. maybe someone else knows?


Anyway, adding a conversion check is the key, but the point is moot if you rely on PHP or such to do the conversions for you. 
Scripts don't have GUIs... they cant really do much about it. Maybe an error log somewhere contains some info?


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Text column: never used vs. set to empty string

[Tim Streater]

On 17 Feb 2014 at 17:01, Stephan Beal  wrote:

> FWIW, i have seen a similar problem in a legacy app which uses latin1
> encoding in the DB. Latin1 doesn't always survive round-trip through PHP's
> JSON APIs. My case was similar to yours, and we eventually determined that
> the fields which got "lost" (set to null or empty values) were those which
> came out of the latin1-encoded MySQL db containing invalid UTF8 characters
> - the whole values were getting dropped upon transforming to JSON.

I'm not using JSON, and I've been careful to have everything in UTF8 
throughout. I'm asking the user to send me the db, so I can look at it. But you 
make a good point. I said I've been careful, but the possibility I overlooked 
something related to that is not excluded.



--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Text column: never used vs. set to empty string

[Stephan Beal]

On Mon, Feb 17, 2014 at 5:57 PM, Tim Streater  wrote:

> complete in a browser window, which data is then gathered up and sent
> using ajax to be processed by a PHP script, which writes it to an sqlite
> db. The user complains that some of this data doesn't make it, so I want to
> pin down where in the chain this might be failing. Hence my Q.
>

FWIW, i have seen a similar problem in a legacy app which uses latin1
encoding in the DB. Latin1 doesn't always survive round-trip through PHP's
JSON APIs. My case was similar to yours, and we eventually determined that
the fields which got "lost" (set to null or empty values) were those which
came out of the latin1-encoded MySQL db containing invalid UTF8 characters
- the whole values were getting dropped upon transforming to JSON.

TL;DR: double-check all encodings.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Text column: never used vs. set to empty string

[Tim Streater]

On 17 Feb 2014 at 14:10, Simon Slavin  wrote: 

> On 17 Feb 2014, at 11:37am, Tim Streater  wrote:
>
>> If I have a text column defined as it might be as MYCOL TEXT (that is with no
>> default value), is there a way to distinguish in some row or other between a
>> column into which no data has ever been entered, and a column that might have
>> been set to a string, but later set to the empty string? (or even only ever
>> set to the empty string, perhaps).
>
> In SQL, "no data" means "NUL" whereas an empty string is a string with zero
> characters in.
>
> So just do
>
> SELECT typeof(t) FROM myTable
>
> and see whether you get 'text' or not.

Thanks. I should perhaps have made it clearer that I'm looking at an issue a 
user has. The application gathers some data from the user via a set of fields 
they complete in a browser window, which data is then gathered up and sent 
using ajax to be processed by a PHP script, which writes it to an sqlite db. 
The user complains that some of this data doesn't make it, so I want to pin 
down where in the chain this might be failing. Hence my Q.




--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Text column: never used vs. set to empty string

[Clemens Ladisch]

Tim Streater wrote:
> If I have a text column defined as it might be as MYCOL TEXT (that is
> with no default value)

All columns have a default value.  With no _explicitly_ specified
default value, the column's default value is NULL.

> is there a way to distinguish ... between ... no data ... and ... the
> empty string?

When you have never entered data, the value is NULL, which is distinct
from '', the empty string.

As long as your program takes care to never confuse these two values, it
is possible to assign the meaning "never entered data" to the NULL value
and have it work as you want.

(Please note that in SQL, the NULL value does _not_ have any predefined
meaning such as "unknown" or "missing" or "invalid".  SQL defines only
how the NULL value behaves; it is your job as the programmer or database
designer to determine if you can assign a meaning to the NULL value that
matches this behaviour with the intended usage.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Text column: never used vs. set to empty string

[Simon Slavin]

On 17 Feb 2014, at 11:37am, Tim Streater  wrote:

> If I have a text column defined as it might be as MYCOL TEXT (that is with no 
> default value), is there a way to distinguish in some row or other between a 
> column into which no data has ever been entered, and a column that might have 
> been set to a string, but later set to the empty string? (or even only ever 
> set to the empty string, perhaps).

In SQL, "no data" means "NUL" whereas an empty string is a string with zero 
characters in.

So just do

SELECT typeof(t) FROM myTable

and see whether you get 'text' or not.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Text column: never used vs. set to empty string

[Donald Griggs]

On Mon, Feb 17, 2014 at 6:37 AM, Tim Streater  wrote:

> If I have a text column defined as it might be as MYCOL TEXT (that is with
> no default value), is there a way to distinguish in some row or other
> between a column into which no data has ever been entered, and a column
> that might have been set to a string, but later set to the empty string?
> (or even only ever set to the empty string, perhaps).
>
> 

Hi, Tim,

I would think of two ways:

1) You might default the field to a value you know from your data to be
impossible.  E.g.
  MYCOL2 TEXT DEFAULT '#UNUSED#'
Your initial building of the table would then OMIT the field name
MYCOL2 in order to invoke the default, e.g.
INSERT INTO MyTable (MyCol1, Mycol3) VALUES ('a', 'b');

2) You might set and/or default the field to NULL.   (Importantly, but as
you may already know, NULL is not like an ordinary value.  It means
something like 'Value is unknown.'   As a result, testing for a value equal
to NULL will ALWAYS return FALSE, for example.   If this would be your
first exposure to NULL you'd want to read up on it first.)

Now if instead what you want is to:
   Initialize a value to the empty string
   set it to something else
   set it back to the empty string
   Then have sqlite "invoke a past memory of that value" then, no, sql
won't do that directly.

If that's what you need then perhaps you could
   define a new boolean column to keep track of whether MyCol had ever been
used.
   Update that new column directly or via a trigger.

Others on this list may have better advice.

Donald
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Text column: never used vs. set to empty string

[Tim Streater]

If I have a text column defined as it might be as MYCOL TEXT (that is with no 
default value), is there a way to distinguish in some row or other between a 
column into which no data has ever been entered, and a column that might have 
been set to a string, but later set to the empty string? (or even only ever set 
to the empty string, perhaps).

Thanks,

--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users