This forum has been archived. All content is frozen. Please use KDE Discuss instead.

how to enter NULL

Tags: None
(comma "," separated)
User avatar
Robert Leleu
Registered Member
Posts
91
Karma
0

how to enter NULL

Tue May 20, 2014 3:07 pm
Due to previous life of my data, some fields have a lot of NULL occurrences.
But when I erase a value in such a field I have just an empty field.

How could I manage (the simpler would be to be able to enter NULL when erasing
User avatar
jstaniek
Moderator
Posts
1027
Karma
2
OS

Re: how to enter NULL

Tue May 20, 2014 8:46 pm
Hi Robert,
You have presented the behaviour corrently: empty ("") i.e. zero-length Text values are inserted when text is deleted. NULLs are only used when I skip entering data in given "cell".

For values of types other than Text, deleting the cell's content sets NULL. We basically have no other option for these types.

Summing up, I have nothing against changing the proposed behaviour but we're talking about changing the default behaviour. I propose that everybody interested shares opinion here.
The problem is that I can have a table where some cells already have empty text "" values. When I open the table using Kexi that changed the default behaviour, and when I delete some values in cells, I will stay with table that has some empty text "" values and some NULL values in the same column. This means a query that selects records with non-filled values should both search for NULLs and for "" values. Any pre-existing queries need to be adapted by the user...

(See how nontrivial is handling NULLs, examples for MS Access but are generic enough: http://allenbrowne.com/casu-12.html)

So I propose to file a wish "Add option to set NULL when value of Text type is deleted".

PS: Related thing is that as I remember in April 2013 we added the following option to the Import CSV tool: "Import missing text values as texts" (instead of NULLs).

PS2: Workaround for you, Robert: Close Kexi first to be 100% safe, and make a backup copy of the database.
Then to change "" values to nulls in a table T and column C, open the database using admin tool (mysql or postgresql admin for server databases, and sqlite3 or sqliteman for .kexi sqlite-based file), and type:

update T set C = NULL where C = "";

To change in the opposite direction, type:

update T set C = "" where C IS NULL;


Best regards,
Jarosław Staniek
• Qt Certified Specialist
KEXI - Open Source Visual DB Apps Builder
• Request a feature or fix for KEXI here
May I help you? Please mention your app's version and OS when asking for help


Bookmarks



Who is online

Registered users: bancha, Bing [Bot], Evergrowing, Google [Bot], lockheed, mesutakcan, sandyvee, Sogou [Bot]