Unit 3 · Lesson 7 Intermediate ⏱ ~50 minutes

Database Access

Welcome to Unit 3 — Building Real Applications. In this lesson you'll learn how to store and retrieve data using a SQLite database. Databases let your application remember information between sessions, handle large amounts of structured data, and power features like contact lists, inventory systems, and user accounts.

🎯 Learning Objectives

Understand what script-local variables are and why they're useful
Open a SQLite database with revOpenDatabase
Create a table with revExecuteSQL
Insert records using SQL INSERT statements
Query records with revQueryDatabase and iterate through results
Clean up properly with revCloseCursor and revCloseDatabase

🗄️ Overview

HyperXTalk uses the Database library to communicate with databases. It supports SQLite, MySQL, PostgreSQL, and ODBC connections. In this lesson we'll use SQLite — a file-based database that requires no server setup, making it perfect for desktop applications.

The workflow for any database operation in HyperXTalk follows the same pattern:

-- 1. Open a connection → get a database ID
-- 2. Execute SQL (INSERT, UPDATE, DELETE) or
--    Query the database (SELECT) → get a cursor ID
-- 3. If querying, iterate through the result rows
-- 4. Close the cursor (if you opened one)
-- 5. Close the database connection

💡 This lesson assumes basic familiarity with SQL. If you haven't used SQL before, the key commands are: CREATE TABLE to define a table structure, INSERT INTO to add records, and SELECT to retrieve them.

📌 Script-Local Variables

Before we look at database code, there's an important new concept to understand: script-local variables. You've already seen local variables (exist only while a handler runs) and global variables (exist for the lifetime of the stack). Script-local variables sit in between — they persist for as long as the script's object exists, and are accessible to all handlers within that script, but are invisible to everything else.

You declare them at the top of a script, outside any handler, using the local keyword:

card script
-- Declared at the top of the script, outside any handler
local sDatabaseID
local sCursorID

-- Both handlers can now share these variables
command databaseConnect
   put revOpenDatabase("sqlite", tPath) into sDatabaseID
end databaseConnect

command databaseClose
   revCloseDatabase sDatabaseID   -- still accessible here
end databaseClose

Script-local variable names conventionally start with s (for "script"). This makes it immediately clear when you're reading code that a variable is shared across handlers in the same script.

🟢 For database work, script-local variables are ideal — you open the connection once, store the database ID in sDatabaseID, and every other handler in the script can use it without you needing to pass it around as a parameter.

🔌 Connecting to a Database

revOpenDatabase opens a connection to a database and returns a database ID — an integer you use to refer to that connection in all subsequent calls. For SQLite, you pass the path to the database file. If the file doesn't exist yet, SQLite creates it automatically.

card script
command databaseConnect
   local tDatabasePath, tDatabaseID

   ## The database must be in a writeable location
   put specialFolderPath("documents") & "/HyperXTalkSample.sqlite" \
      into tDatabasePath

   ## Open a connection to the database
   ## If the database does not already exist it will be created
   put revOpenDatabase("sqlite", tDatabasePath) into tDatabaseID

   ## Store the database ID so other handlers can access it
   put tDatabaseID into sDatabaseID
end databaseConnect

specialFolderPath("documents") returns the path to the user's Documents folder — a safe, writable location on all platforms. The database ID is stored in the script-local sDatabaseID so every other handler can use it.

⚠️ revOpenDatabase returns an integer on success, or an error message string on failure. In production code, always check whether the return value is a number before proceeding — if it isn't, the connection failed and subsequent calls will error.

📋 Creating a Table

Use revExecuteSQL to run any SQL statement that doesn't return rows — CREATE TABLE, INSERT, UPDATE, and DELETE all use this command. You pass it the database ID and the SQL string:

Script editor showing databaseConnect and databaseCreateTable handlers

The databaseConnect and databaseCreateTable handlers — click to enlarge

card script
command databaseCreateTable
   ## Add user_details table to the database
   local tSQL
   put "CREATE TABLE user_details (name char(50), login char(50))" \
      into tSQL
   revExecuteSQL sDatabaseID, tSQL
end databaseCreateTable

Notice that the SQL string is built in a local variable first. This keeps things readable and makes it easy to build dynamic SQL by putting values into the string before executing it.

➕ Inserting Records

Inserting records also uses revExecuteSQL. You can run multiple SQL statements by appending them to the same string — each statement must end with a semicolon:

Script editor showing the databaseInsertUserDetails handler

Inserting two records with a single revExecuteSQL call — click to enlarge

card script
command databaseInsertUserDetails
   ## Insert names and email addresses into the database
   local tSQL
   put "INSERT into user_details VALUES ('Emily-Elizabeth Howard','emily-elizabeth.howard@email.com');" \
      into tSQL
   put "INSERT into user_details VALUES ('anonymous','user@email.com');" \
      after tSQL
   revExecuteSQL sDatabaseID, tSQL
end databaseInsertUserDetails

The second INSERT is appended with after tSQL rather than into it, so both statements end up in the string one after the other. A single revExecuteSQL call then executes them both.

🔍 Querying Records

For SELECT queries that return rows, use revQueryDatabase. It returns a cursor ID — an integer pointing to the result set. You then iterate through the rows using revQueryIsAtEnd as the loop condition and revMoveToNextRecord to advance:

Script editor showing the databaseQueryUserDetails handler

Querying the database and iterating through all rows — click to enlarge

card script
command databaseQueryUserDetails
   put revQueryDatabase(sDatabaseID, "SELECT * FROM user_details") \
      into sCursorID

   ## Get the names of all the columns in the database cursor
   local tColumnNames
   put revDatabaseColumnNames(sCursorID) into tColumnNames

   ## Loop through all rows in cursor
   local i, tResults
   repeat until revQueryIsAtEnd(sCursorID)
      add 1 to i

      ## Move all fields in row into next dimension of the array
      repeat for each item tColumnName in tColumnNames
         put revDatabaseColumnNamed(sCursorID, tColumnName) \
            into tResults[i][tColumnName]
      end repeat

      revMoveToNextRecord sCursorID
   end repeat
end databaseQueryUserDetails

There are several things worth understanding in this pattern:

revDatabaseColumnNames returns a comma-separated list of the column names in the result set. By getting these dynamically rather than hardcoding them, the handler works correctly even if the table structure changes.

repeat until revQueryIsAtEnd(sCursorID) — the loop runs until the cursor has moved past the last row. Note that revMoveToNextRecord is called at the end of the loop body, after reading the current row.

tResults[i][tColumnName] — results are stored in a two-dimensional array, indexed by row number and column name. After the loop, tResults[1]["name"] would contain the name from the first row, tResults[2]["login"] the login from the second, and so on.

🔒 Closing Up

Always close your cursor and database connection when you're done. Leaving connections open wastes resources and can cause problems if the stack is reopened:

card script
command databaseClose
   revCloseCursor sCursorID
   revCloseDatabase sDatabaseID
end databaseClose

Close the cursor before the database — the cursor belongs to the database connection, so closing the database first would leave the cursor in an invalid state.

🟢 A good pattern is to call databaseConnect from an openCard or openStack handler, and databaseClose from closeCard or closeStack. That way the connection lifecycle matches the stack lifecycle automatically.

🛠️ Exercise — A Simple Contact Book

In this exercise you'll build a simple contact book that stores names and email addresses in a SQLite database. The card script holds all the database handlers; buttons on the card call them.

Create a new stack. Add fields named NameInput and EmailInput for data entry, a scrolling output field named Results with Lock text enabled, and four buttons: Connect, Create Table, Add Contact, and Show All.
Open the card script (right-click the card background and choose Edit Card Script). At the very top, declare the script-local variables:

card script

local sDatabaseID local sCursorID
Add the databaseConnect and databaseCreateTable handlers to the card script as shown in the lesson above.

Connect & Create Table
Add an addContact handler that reads from the two input fields and inserts a record:

card script

command addContact local tName, tEmail, tSQL put the text of field "NameInput" into tName put the text of field "EmailInput" into tEmail put "INSERT INTO user_details VALUES ('" \ & tName & "','" & tEmail & "');" into tSQL revExecuteSQL sDatabaseID, tSQL end addContact

Insert handler
Add a showAllContacts handler that queries the database and displays results in the Results field:

card script

command showAllContacts put revQueryDatabase(sDatabaseID, "SELECT * FROM user_details") \ into sCursorID local tOutput repeat until revQueryIsAtEnd(sCursorID) put revDatabaseColumnNamed(sCursorID, "name") & " — " \ & revDatabaseColumnNamed(sCursorID, "login") \ & return after tOutput revMoveToNextRecord sCursorID end repeat set the text of field "Results" to tOutput revCloseCursor sCursorID end showAllContacts

Query handler
Edit each button's script to call the appropriate card handler — e.g. the Connect button should contain:

button "Connect"

on mouseUp databaseConnect end mouseUp
Switch to Browse mode. Click Connect, then Create Table, then type a name and email and click Add Contact. Click Show All to see your contacts appear in the Results field.

Bonus challenge: Add a Clear All button that runs DELETE FROM user_details via revExecuteSQL to wipe the table, then refreshes the Results field.

📝 Review Questions

Question 1

What does revOpenDatabase return on success, and how can you tell if it failed?

It returns an integer database ID on success. On failure it returns an error message string. Since the ID is always an integer and an error is never an integer, you can check with if the result is not a number then to detect failure.

Question 2

What is the difference between a script-local variable and a global variable?

A script-local variable is accessible to all handlers within the same script but invisible outside it. A global variable is accessible to any handler anywhere in the stack. Script-locals are preferable when you want to share state between handlers without exposing it to the whole stack.

Question 3

Which function do you use to run a SELECT query, and what does it return?

revQueryDatabase(databaseID, sqlQuery) — it returns a cursor ID (an integer) pointing to the result set. You then use revQueryIsAtEnd, revDatabaseColumnNamed, and revMoveToNextRecord to iterate through the rows.

Question 4

In what order should you close a cursor and a database connection, and why?

Close the cursor first with revCloseCursor, then the database with revCloseDatabase. The cursor belongs to the database connection, so closing the database first would leave the cursor in an invalid state.

Question 5

What does revDatabaseColumnNames return, and why is it useful?

It returns a comma-separated list of column names from the cursor's result set. Using it to drive a repeat for each item loop means your query handler works correctly regardless of how many columns the table has or what they're called — you don't need to hardcode column names.

← Lesson 6: Variables and Data Types Lesson 8: File Handling →