Typed SQLite data

SQLite is useful for persistent local data and application state. BASIL’s database connection is an owned resource. Ordinary queries can suspend while bounded workers execute them; the caller’s text and bytes remain valid until completion or cancellation has been acknowledged.

Insert values safely

db = OpenDatabase(":memory:")
Query(db, "CREATE TABLE customers (name TEXT, visits INTEGER)")
Query(db, "INSERT INTO customers VALUES (?, ?)", [SqlText("Ada"), SqlInteger(3)])
result = QueryValues(db, "SELECT name, visits FROM customers")
PRINT SqlGetText(result.Rows[0].Values[0])
PRINT SqlGetInteger(result.Rows[0].Values[1])

Use placeholders and a typed LIST OF SqlValue to keep SQL structure separate from user data. Do not concatenate input text into SQL. SqlText and SqlInteger construct alternatives of a statically known choice; the parameter list is homogeneous even though its SQL payloads differ. Arbitrary mixed ["Ada", 3] conversion is not implemented. Dictionary/record parameter adapters are future work.

Preserve NULL and storage classes

SqlValue distinguishes Null, Integer, Real, Text and Blob. NULL is not the same as an empty string, empty blob, integer zero or real zero. SQLite’s returned runtime storage class determines the case; a column’s declared affinity is not proof that every returned cell has that class.

db = OpenDatabase(":memory:")
result = QueryValues(db, "SELECT NULL, '', 0")
PRINT IsSqlNull(result.Rows[0].Values[0])
PRINT LEN(SqlGetText(result.Rows[0].Values[1]))
PRINT SqlGetInteger(result.Rows[0].Values[2])

Strict accessors reject the wrong kind rather than quietly converting text to numbers. Check row presence before indexing a result that may be empty. Use the actual typed accessor appropriate to your query and handle SqliteError. Values copied into BASIL results remain valid independently of SQLite’s statement buffers.

Use QueryValues for typed results when there are no parameters. The two-argument Query overload is a compatibility text-or-NULL API; passing a LIST OF SqlValue selects the typed Query overload. Do not feed legacy text cells into typed accessors.

Transactions and tasks

The materialized result model is appropriate for bounded queries. Limit rows in SQL when application memory must stay bounded; streaming result objects are not implied by worker execution. A cancelled waiter does not prove the database statement was rolled back. Cancellation and transaction commitment are separate events.

Explicitly begin/commit/rollback using your SQL transaction policy. A user resource can own the database and perform best-effort rollback in __Cleanup, while a checked Commit operation reports application-visible success. The complete transaction example demonstrates this pattern.

Give a task its own connection or transfer an appropriate owner; general borrowed parent-state captures are unsupported. The periodic writer combines a child-owned connection, timeout, Cancel and Join. It observes the retained outcome even if the timeout wins, so operational failure cannot disappear behind cancellation.