From J Wiki
Jump to: navigation, search
Overview | Basic Usage | Create | Definitions | zFns | Installation | Examples | Quirks

Some sqlite quirks are:

  • a sqlite column may have more than one datatype (which is unusual for SQL databases). For example, a column of integers may have one or more values that are character strings. The sqlite interface assumes all values in the column are of the same datatype, as would be the case for a J array, and would be the case for any practical use of sqlite from J.
  • columns may have null values, and these are returned as -2^63 (integer), __ (float), or empty (text).

For example, create a table with int, float and text columns:

   db=: sqlcreate_psqlite_ '~temp/test.db'
   sqlcmd__db 'create table tab (a int,b float,c text)'
   sqlinsert__db 'tab';(;:'a b c');<1 2 3;3.1 5.4 6.9;<'one';'two';'three'
   sqlreads__db 'tab'
│a│b  │c    │
│1│3.1│one  │
│2│5.4│two  │

Now set the second row to NULLs, and the third row to text, text, integer types:

   sqlcmd__db 'update tab set a=NULL,b=NULL,c=NULL where rowid=2'
   sqlcmd__db 'update tab set a="alan",b="bill",c=123 where rowid=3'

The read becomes:

   sqlreads__db 'tab'
│a                   │b  │c  │
│                   1│3.1│one│
│_9223372036854775808│ __│   │
│                   0│  0│123│

Note that the values were written correctly to the database, as can be seen by using sqlite itself:

~$ sqlite3 test.db
sqlite> .header on
sqlite> .mode column
sqlite> .nullvalue null
sqlite> select * from tab;
a           b           c
----------  ----------  ----------
1           3.1         one
null        null        null
alan        bill        123