Addons/data/sqlite/Nulls
< Addons | data/sqlite
Jump to navigation
Jump to search
Overview | Basic Usage | Create | Definitions | zFns | Installation | Nulls | Examples | Quirks
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│ └────────────────────┴───┴────┴────┘