mORMot Open Source

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,682

Website

NULL handling in SQLite3 and in the framework

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

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