Database Manual · Chapter 5

INSERT · UPDATE · DELETE

We walk through the 4 mutating-SQL functions in Data.xms.

Function	Role	Pattern
`DB_InsertSample`	INSERT one new order	parameter binding
`DB_UpdateSelected`	Mark selected row as Done	transaction + parameter binding
`DB_DeleteSelected`	Delete selected row	parameter binding
`DB_InsertInitialSamples`	Bulk-INSERT 5 rows into empty table	transaction (multi-row)

Two foundations first.

1) Parameter Binding — `RunSqlQueryParam(sql, paramArray)`

Instead of building SQL through string concatenation, pass values as an array against ? placeholders. SQL injection is blocked, and quote-escaping headaches disappear.

array p[] = {""};
p.Clear();
p.Add(value1);
p.Add(value2);
// ... the number of ? must match the array length exactly
 
string sql = "INSERT INTO ... VALUES(?, ?, ?, ?)";
DB["local"].RunSqlQueryParam(sql, p);

Types convert automatically (int / double / string / bool).

2) Transaction — `BeginTransaction` / `Commit` / `Rollback`

Use this when running multiple SQLs as one unit, or when you want to undo all changes on failure. Always Rollback on a mid-transaction failure — otherwise the next transaction is affected.

if( DB["local"].BeginTransaction() == false ) return false;
 
if( DB["local"].RunSqlQueryParam(sql, p) == false )
{
   DB["local"].Rollback();
   return false;
}
 
if( DB["local"].Commit() == false ) return false;

DB_InsertSample — Single INSERT

The function called by the Add button. Menu cycles through 7, order number is auto-incremented by OrderSeq.

FUNCTION DB_InsertSample()
{
   if( DB["local"].IsOpen == false )
   {
      ShowMessage(EB_Ok, "DB is not open. Press [Open] first.");
      return false;
   }
 
   array menuPool[] = {"Americano", "Latte", "Cappuccino", "Espresso",
                      "Mocha", "Green Tea", "Lemonade"};
   OrderSeq = OrderSeq + 1;
   int menuIdx = OrderSeq % 7;
 
   array p[] = {""};
   p.Clear();
   p.Add($"O{OrderSeq}");          // order_no
   p.Add(menuPool[menuIdx]);       // menu_name
   p.Add(SYS.DateTimeString);      // start_time
   p.Add("");                      // end_time (work in progress)
   p.Add(0);                       // weight_g
   p.Add("Pending");               // result
   p.Add(0);                       // is_error
 
   string sql = "INSERT INTO order_history(order_no, menu_name, start_time, end_time, weight_g, result, is_error) VALUES(?,?,?,?,?,?,?)";
   if( DB["local"].RunSqlQueryParam(sql, p) == false )
   {
      LogError($"DB_InsertSample failed : {DB["local"].LastError}");
      ShowMessage(EB_Ok, $"DB Insert failed : {DB["local"].LastError}");
      return false;
   }
 
   Log($"DB_InsertSample : O{OrderSeq} {menuPool[menuIdx]} inserted");
 
   return DB_Refresh();
}

Key points:

Always guard with IsOpen — sending commands to a closed connection just piles up LastError.
On failure, output to both LogError (for tracing) and ShowMessage (for on-floor alert).
End by calling DB_Refresh() to reload the DataGrid (Ch. 6).

DB_UpdateSelected — Transactional UPDATE

Called by the Update button. Reads the PK of the DataGrid's selected row and marks it Done.

FUNCTION DB_UpdateSelected()
{
   if( DB["local"].IsOpen == false )
   {
      ShowMessage(EB_Ok, "DB is not open. Press [Open] first.");
      return false;
   }
 
   if( SelectIndex < 0 || SelectIndex >= DB["local"].RowCount )
   {
      ShowMessage(EB_Ok, "Select a row first.");
      return false;
   }
 
   // Fetch PK(id) of the selected DataGrid row
   int targetId = DB["local"].GetValueInt(/*row*/SelectIndex, /*colName*/"id");
 
   if( DB["local"].BeginTransaction() == false )
   {
      LogError($"BeginTransaction failed : {DB["local"].LastError}");
      return false;
   }
 
   array p[] = {""};
   p.Clear();
   p.Add(SYS.DateTimeString);   // end_time
   p.Add(250);                  // weight_g (demo fixed value)
   p.Add("Done");               // result
   p.Add(targetId);             // WHERE id = ?
 
   string sql = "UPDATE order_history SET end_time=?, weight_g=?, result=? WHERE id=?";
   if( DB["local"].RunSqlQueryParam(sql, p) == false )
   {
      DB["local"].Rollback();
      LogError($"DB_UpdateSelected failed : {DB["local"].LastError}");
      return false;
   }
 
   if( DB["local"].Commit() == false )
   {
      LogError($"Commit failed : {DB["local"].LastError}");
      return false;
   }
 
   Log($"DB_UpdateSelected : id={targetId} updated");
 
   return DB_Refresh();
}

SelectIndex is the DataGrid's selected-row index, covered in Ch. 7. The GetValueInt(SelectIndex, "id") pattern is the key idea — read a cell by row index and column name.

DB_DeleteSelected — Plain DELETE

FUNCTION DB_DeleteSelected()
{
   // (same two guard lines)
 
   int targetId = DB["local"].GetValueInt(/*row*/SelectIndex, /*colName*/"id");
 
   array p[] = {""};
   p.Clear();
   p.Add(targetId);
 
   string sql = "DELETE FROM order_history WHERE id=?";
   if( DB["local"].RunSqlQueryParam(sql, p) == false )
   {
      LogError($"DB_DeleteSelected failed : {DB["local"].LastError}");
      return false;
   }
 
   Log($"DB_DeleteSelected : id={targetId} deleted");
   SelectIndex = -1;
 
   return DB_Refresh();
}

We reset SelectIndex = -1 because reusing the same index after a delete would now point at a different row.

DB_InsertInitialSamples — Multi-row in a Transaction

Called once on the first Open of an empty table. Wrap all 5 rows in a single transaction to gain both consistency and speed.

if( DB["local"].BeginTransaction() == false ) return false;
 
string sql = "INSERT INTO order_history(...) VALUES(?,?,?,?,?,?,?)";
 
for( i, 0, 4 )
{
   array p[] = {""};
   p.Clear();
   p.Add(/* ... */);
 
   if( DB["local"].RunSqlQueryParam(sql, p) == false )
   {
      DB["local"].Rollback();
      return false;
   }
}
 
if( DB["local"].Commit() == false ) return false;

The difference looks small at 5 rows, but for production loads of hundreds to thousands of history rows, transactional vs. non-transactional execution differs by a wide margin.

Next Chapter

Reading (SELECT) is what's left — RunSqlSelect, GetRowArray, and the single-value RunSqlScalarInt.