Addons/data/sqlite/Nulls

User-defined nulls were added in v1.0.23.

Null values

To allow for nulls in data, the addon defines three "null value" globals in the psqlite locale. These are set whenever a database is opened. The default values are as follows, where the integer null is the largest possible negative integer:

SQLITE_NULL_INTEGER=: _2147483648            NB. J32
SQLITE_NULL_INTEGER=: _9223372036854775808   NB. J64
SQLITE_NULL_FLOAT=: __
SQLITE_NULL_TEXT=: 'NULL'                    NB. used for text and blob

For example, create a table:

   load 'data/sqlite/sqlitez'
   dbcreate jpath '~temp/test.db'
   dbcmd 'create table tab (a int,b float,c text, d blob)'
   t=. 'one';'two'
   s=. t;<t rplc each <'o',{.a.
   dbinsert 'tab';(;:'a b c d');<1 2;3.1 5.4;s
   dbreads 'tab'
┌─┬───┬───┬───┐
│a│b  │c  │d  │
├─┼───┼───┼───┤
│1│3.1│one│ ne│
│2│5.4│two│tw │
└─┴───┴───┴───┘

Now update the second row with a command to set SQL nulls, and then insert a new row using J nulls:

   dbcmd 'update tab set a=NULL,b=NULL,c=NULL,d=NULL where rowid=2'
   dat=. SQLITE_NULL_INTEGER;SQLITE_NULL_FLOAT;;~SQLITE_NULL_TEXT
   dbinsert 'tab';(;/'abcd');<dat

The read shows the null rows:

   dbreads 'tab'
┌────────────────────┬───┬────┬────┐
│a                   │b  │c   │d   │
├────────────────────┼───┼────┼────┤
│                   1│3.1│one │ ne │
│_9223372036854775808│ __│NULL│NULL│
│_9223372036854775808│ __│NULL│NULL│
└────────────────────┴───┴────┴────┘

Changing null values

Try a different set of J nulls. Note that this does not change the nulls in the database, but just the null values used by J:

   dbclose''
   SQLITE_NULL_INTEGER_psqlite_=: 23
   SQLITE_NULL_FLOAT_psqlite_=: 234.56
   SQLITE_NULL_TEXT_psqlite_=: 'Nullah'
   dbopen jpath '~temp/test.db'
   dbreads 'tab'
┌──┬──────┬──────┬──────┐
│a │b     │c     │d     │
├──┼──────┼──────┼──────┤
│ 1│   3.1│one   │ ne   │
│23│234.56│Nullah│Nullah│
│23│234.56│Nullah│Nullah│
└──┴──────┴──────┴──────┘

Restore J default nulls by reloading sqlite:

   dbclose ''
   load 'data/sqlite/sqlitez'
   dbopen jpath '~temp/test.db'
   dbreads 'tab'
┌────────────────────┬───┬────┬────┐
│a                   │b  │c   │d   │
├────────────────────┼───┼────┼────┤
│                   1│3.1│one │ ne │
│_9223372036854775808│ __│NULL│NULL│
│_9223372036854775808│ __│NULL│NULL│
└────────────────────┴───┴────┴────┘