I highly recommend the marvelous, free, relational database engine Sqlite. It is not only free of a commercial license, it is also free of the restrictions imposed by the Free Software Foundation GNU GPL license, i.e., you can do whatever you want with it, incorporate it into your application, whether your app is free, or, dare I say it, costs money. However there is a bug, which I discovered recently. The ISNULL() function doesn’t work the way it is supposed to, or the way you would expect it to. If you have a field in your database file that may be NULL, and you want to do a select statement that substitutes some string (‘blah’) in the case where the field is NULL, you would expect to be able to do something like this:
SELECT field1,field2,ISNULL(‘blah’,field3) FROM tablename;
But that won’t work, because of the bug. Instead say this:
SELECT field1,field2,(CASE WHEN field3 ISNULL THEN ‘blah’ ELSE field3 END) FROM tablename;
And it will work exactly the way you expect it to. If field3 is NULL it will substitute the string ‘blah’. If field 3 is not NULL, it will select the contents of field3.
Nick…
SQLLite includes the every so handy COALESCE method. Assuming your Userstable looked like this:
id | name | favColor
——————-
1 | andy | blue
2 | nick | null
The above query would look like this:
SELECT id, name, COALESCE(favColor,’purple’) AS FavColor
FROM Users
And the resulting data set would look like this:
id | name | favColor
——————-
1 | andy | blue
2 | nick | purple