Database Manual · Chapter 1

Database Tutorial Start

This tutorial walks you end-to-end through the SampleProject/Database/DB_Sqlite project shipped with QMachineStudio, learning the database features as you go.

The sample runs on a single SQLite connection and is a small tool that registers, edits and deletes order processing history (order_history table) from the screen. Every code excerpt in this manual is taken directly from this project's RunScript/*.xms files.

Sample Project Location

SampleProject/Database/DB_Sqlite/
├── DB_Sqlite.xmp                  # Project file (contains DatabaseOption)
├── XDatabase/
│   ├── connections.json           # Connection settings (synced with DB Studio)
│   └── LocalDB.db                 # The actual SQLite file (auto-created)
└── RunScript/
    ├── Data.xms                   # DB_* functions (Open/Refresh/Insert/Update/Delete/...)
    ├── ViewRun.xms                # Button handlers (OnOpenClick / OnAddClick / ...)
    ├── ModifyDlg.xms              # Record-edit dialog
    └── (other Init / Mon / Event / ViewMain ...)

Screen Layout — ViewRun

The run page (ViewRun) is composed of 6 buttons and 1 XDataGrid.

Button	Function	Action
Open	`Data::DB_Open()`	Connect + insert 5 sample rows if empty + refresh DataGrid
Add	`Data::DB_InsertSample()`	INSERT one new order
Update	`Data::DB_UpdateSelected()`	Mark selected row as Done (end_time / result)
Modify	`Data::DB_OpenModifyDlg()`	Open dialog, free edit, UPDATE on OK
Delete	`Data::DB_DeleteSelected()`	DELETE selected row
Close	`Data::DB_Close()`	Close connection + clear DataGrid

A status label (Data::DbStatusText) at the top shows current connection state — ● OPEN / ● CLOSED.

The Key — `DB["connection name"]`

In scripts, every database is accessed by connection name. The sample has a single connection named local.

// Open the connection
DB["local"].Open();
 
// SELECT, then read with RowCount / GetRowArray
DB["local"].RunSqlSelect("SELECT id, order_no, menu_name FROM order_history ORDER BY id ASC");
int rows = DB["local"].RowCount;
 
// Single value
int total = DB["local"].RunSqlScalarInt("SELECT COUNT(*) FROM order_history");
 
// Parameter binding (prevents SQL injection)
array p[] = {""};  p.Clear();
p.Add("O1234"); p.Add("Latte"); p.Add(SYS.DateTimeString);
DB["local"].RunSqlQueryParam(
    "INSERT INTO order_history(order_no, menu_name, start_time) VALUES(?,?,?)",
    p);
 
// Transaction
DB["local"].BeginTransaction();
DB["local"].RunSqlQueryParam(/* ... */);
if( DB["local"].Commit() == false ) DB["local"].Rollback();
 
// Close
DB["local"].Close();

Same name-indexed convention you've seen with IO["..."] and MOTOR["..."] — nothing new to learn.

Learning Path

Chapter	Topic	Sample code
2	Connection settings — `connections.json` and project options	`DB_Sqlite.xmp` `DatabaseOption`
3	Table schema — `order_history`	`DB_InsertInitialSamples`
4	Validating with DB Studio's SQL · Data tabs	—
5	INSERT · UPDATE — parameter binding and transactions	`DB_InsertSample` · `DB_UpdateSelected`
6	SELECT — `RowCount` · `GetRowArray` · `RunSqlScalarInt`	`DB_Refresh`
7	DataGrid · ModifyDlg bindings	`DispData` · `SelectIndex` · `Edit*`
8	Button events — `ViewRun` handlers	`OnOpenClick` and 5 more
9	Backup · WAL · operational checkpoints	`DatabaseOption.BackupFolder` and more

We recommend running the sample alongside the chapters and seeing the results live.