Since you access Delphi properties, NULL doesn't exist as such (it's a SQL
concept). So you will have 0 for an integer field, nil for a field referring to
another record, and '' for a string field. At the SQL and JSON levels, the NULL
value does exist and are converted as expected. At higher level (Delphi code or
Javascript/AJAX code) the NULL value is to be handled explicitely.
So to answer your question, there is no direct way of making a difference
between NULL and '' for a string field, for example. It can be performed by
using a simple SQL statement, which can be added to your database class, as a
method common to all your application tables classes. Since I didn't have any
need for this feature, I didn't code such methods in the main SQLite3Commons
unit, but thanks to the true object orientation of the framework (and the fact
that all the source code is supplied), you can easily add such a feature.
But as you perfectly guess, NULL handling is not consistent among databases...
so I should recommend not using it in our database layout, or only in a 100%
compatible way.
In SQLite3 itself, NULL is handled as stated in http://www.sqlite.org/lang_expr.html
(see e.g. IS and IS NOT operators).
It's worth saying that you can use another database layer than SQLite3, if you
want to. Just implement some methods, like in the SQLite3.pas unit of the
framework (by reading this unit, you will notice that most of work is done in
the parent classes, so it's very easy adding a new database layer, or even
write a new one - a memory-only database engine is supplied in
SQLite3Commons.pas unit, which use JSON for disk persistency, and is very
fast).
Synopse SQLite3 Framework: NULL handling
2010-01-05. Permalink Open Source › mORMot Framework
Question posted in the embarcadero forum:
Is there a way in your wrapper to know whether the field is NULL (say, Integer or String field)?
In most databases '' and null are different values.
Thanks,
Michael
Add ping
Trackback URL : https://blog.synopse.info?trackback/101
one reaction
1 From A.Bouchez - 21/06/2010, 11:18