Databases
Describe a connection with has, connect to it, then run SQL with execute and query. Values are passed as named parameters, never pasted into the SQL text.
Not part of the certified Otter 1.0 surface. Database and SQL support is deferred to Otter 1.2 or later (see the release scope matrix). It is in the current source tree as an experimental preview: its syntax and behavior may change, and it does not work on every platform.
Connecting
Otter
database has
provider is "sqlite"
connection is ":memory:"
.
connect database into db
A connection is a value like any other. disconnect db closes it. Using a connection after it is closed, or naming a table that does not exist, produces a readable Otter error.
Changing data
execute runs a statement that changes data. parameter "name" is value supplies @name in the SQL safely; into stores a result you can inspect.
Otter
execute db with
"create table if not exists tasks (id integer primary key autoincrement, title text, completed integer, priority text)"
execute db with
"insert into tasks (title, completed, priority) values (@title, @completed, @priority)"
parameter "title" is "Buy groceries"
parameter "completed" is 0
parameter "priority" is "high"
into insertResult
say "Rows affected:" rows affected of insertResult
say "New id:" last inserted id of insertResult
A parameter can be gone, which is stored as a database NULL.
Reading data
query returns a list of row objects. Each column is a property of a row.
Otter
query db with
"select id, title, completed, priority from tasks"
into allTasks
each task in allTasks
say title of task
if priority of task is gone
say "Priority: none"
otherwise
say priority of task
.
.
Looking at the schema
Otter
get tables from db into tables
each table in tables
say kind of table
say name of table
get columns from table in db into columns
each column in columns
say name of column
say type of column
.
.
Databases are a console and desktop feature. The web target reports database statements as unsupported.
Next: the query language (1.1 preview)