On 3/12/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote:
"P Kishor" wrote,
> On 3/12/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote:
>>
>> Ok, I have figured out that I do not have null values on the data of my
>> table, even though I do have empty strings. Here is my schema,
>>
>> sqlite>
>> sqlite> .schema
>> CREATE TABLE LSOpenJobs
>> (
>> id integer primary key, ProjID integer, parent, children, login,
>> cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, pmuk, lang,
>> vendor,
>> vEmail, invoice, ProjFund, A_No, wDir, BiliDir, TMDir, DeliveryDir, paid,
>> notes, status
>>
>> );
>>
>> And here is a call for a SELECT for a null value:
>>
>> sqlite> select * from LSOpenJobs where notes IS NULL;
>> sqlite>
>>
>> Now, I know for a fact that there are notes empty on some of the records
>> there. Why is sqlite thinking that I do not have null values?
>
> you know for a fact that some of the notes are empty, but what makes
> you think they are null? Did you create them as null or did you get
> this db from someone?
Well, at the beginning I created them as null values. However, I have been
adding data to the records, so it may be that the library that I am using
may not be setting those values to the correct null value. Here is a record
with a few null values:
sqlite> select * from LSOpenJobs where id = '166';
166|166|1172158922388||us|MER|3.0 Cards|Technical|
Project|2007-02-21|2007-02-21|2007-02-22|318||en2es,en2br||karina|||klmLD|D:\Projects\166||||||c
the last column is the status, which has the value c and the previus column
is notes, which is empty or null. So, my question is what is NULL for
SQLite? This is a library call ddbi for the D language.
> Here is a simple test --
This test below works on a new created DB, but doing a SELECT on my database
using your syntax for null value is not working. I can provide you the DB,
if you want and you will see that I am not crazy. I have been spending two
days on this and I am going to create a new program that shows this bug to
the folks that wrote the library.
I still would like to know what is a null value. It may have to do with
this.
no, you are not crazy, and I believe you that it doesn't work for you.
You don't have to send your db to me.
What you are seeing is probably because what you _think_ is NULL is
actually an empty string. It is way too tedious and boring to figure
explain what NULL is, let alone what NULL is to SQLite. There are
many, many discussions on this archived in the list. Just search for
them. As far as I am concerned, I think of NULL as the absence of
value.
It is likely that your ddbi library is inserting empty strings instead
of NULL as default values for columns that are not explicitly
specified during INSERTs or UPDATEs. Just do a small test with it and
send the test to the ddbi developers.
>
> Lucknow:~ punkish$ sqlite3
> SQLite version 3.3.8
> Enter ".help" for instructions
> sqlite> CREATE TABLE foo (a, b);
> sqlite> .s
> CREATE TABLE foo (a, b);
> sqlite> INSERT INTO foo (a) VALUES ('blah');
> sqlite> INSERT INTO foo (b) VALUES ('blech');
> sqlite> .headers on
> sqlite> .mode columns
> sqlite> SELECT * FROM foo;
> a b
> ---------- ----------
> blah
> blech
> sqlite> SELECT * FROM foo WHERE a IS NULL;
> a b
> ---------- ----------
> blech
> sqlite> SELECT * FROM foo WHERE b IS NULL;
> a b
> ---------- ----------
> blah
> sqlite> UPDATE foo SET b = '' WHERE a = 'blah';
> sqlite> UPDATE foo SET a = '' WHERE b = 'blech';
> sqlite> SELECT * FROM foo;
> a b
> ---------- ----------
> blah
> blech
> sqlite> SELECT * FROM foo WHERE a IS NULL;
> sqlite> SELECT * FROM foo WHERE b IS NULL;
> sqlite>
>
>
>>
>>
>> ----- Original Message -----
>> From: "jose isaias cabrera" <[EMAIL PROTECTED]>
>> To: <sqlite-users@sqlite.org>
>> Sent: Saturday, March 10, 2007 11:40 PM
>> Subject: [sqlite] UPDATE colomns based on their values
>>
>>
>> >
>> > Greetings!
>> >
>> > So, I would like to update some columns based on whether or not they
>> > are
>> > null. Some of you folks have helped me with ifnull and IS NULL, and I
>> > happened to see coalesce, but none of these are working. If I set the
>> > value, they will work, but with the checks, they do not get UPDATEd.
>> >
>> > Here is the call:
>> >
>> > BEGIN;
>> > UPDATE LSOpenJobs SET bdate = '2007-03-02' WHERE ProjID = '215' AND
>> > bdate
>> > IS NULL;
>> > UPDATE LSOpenJobs SET ddate = coalesce(ddate, '2007-03-05') WHERE
>> > ProjID =
>> > '215';
>> > UPDATE LSOpenJobs SET edate = ifnull(edate,'2007-03-05') WHERE ProjID =
>> > '215';
>> > COMMIT;
>> >
>> > As you can see, I am using 3 different checks and none of these are
>> > working. I know it's something simple, but what it is?
>> >
>> > Any ideas?
>> >
>> > Maybe the other question is, what defines "IS NULL" or "ifnull" or
>> > "coalesce"?
>> >
>> > thanks,
>> >
>> > josé
>> >
>> >
-----------------------------------------------------------------------------
>> > To unsubscribe, send email to [EMAIL PROTECTED]
>> >
-----------------------------------------------------------------------------
>> >
>>
>>
>> -----------------------------------------------------------------------------
>> To unsubscribe, send email to [EMAIL PROTECTED]
>> -----------------------------------------------------------------------------
>>
>>
>
>
> --
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation http://www.osgeo.org/education/
> ---------------------------------------------------------------------
> collaborate, communicate, compete
> =====================================================================
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [EMAIL PROTECTED]
> -----------------------------------------------------------------------------
>
>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
---------------------------------------------------------------------
collaborate, communicate, compete
=====================================================================
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
