Database Manual · Chapter 6

SELECT Patterns

Read SQL boils down to two flows.

Pattern	API	Use
A) Single value	`RunSqlScalarInt(sql)` and friends	Single-cell results like `COUNT(*)`
B) Bulk SELECT	`RunSqlSelect(sql)` + `RowCount` + `GetRowArray(i)` / `GetValue(i, col)`	Filling DataGrid, multi-row processing

The sample uses both.

Pattern A — Single Value

The empty-table check inside DB_Open() is the canonical example.

int rowCnt = DB["local"].RunSqlScalarInt("SELECT COUNT(*) FROM order_history");
if( rowCnt == 0 )
{
   DB_InsertInitialSamples();
}

Functions by type:

Function	Return type
`RunSqlScalarInt(sql)`	`int`
`RunSqlScalarDouble(sql)`	`double`
`RunSqlScalar(sql)`	`string` (universal)

Empty results return 0 / 0.0 / "". Check LastError to distinguish failure.

Pattern B — Bulk SELECT (DB_Refresh)

The sample's DB_Refresh() shows the canonical flow.

FUNCTION DB_Refresh()
{
   if( DB["local"].IsOpen == false )
   {
      LogError($"DB_Refresh : DB is not open");
      return false;
   }
 
   string sql = "SELECT id, order_no, menu_name, start_time, end_time, weight_g, result, is_error FROM order_history ORDER BY id ASC";
   if( DB["local"].RunSqlSelect(sql) == false )
   {
      LogError($"DB_Refresh select failed : {DB["local"].LastError}");
      return false;
   }
 
   DispData.Clear();
 
   int rows = DB["local"].RowCount;
   for( i, 0, rows-1 )
   {
      // GetRowArray : returns one row as an XArray in column order
      // One CSV line (comma-joined) = one DataGrid row
      array row = DB["local"].GetRowArray(/*row*/i);
      string line = $"{row[0]},{row[1]},{row[2]},{row[3]},{row[4]},{row[5]},{row[6]},{row[7]}";
      DispData.Add(line);
   }
 
   Log($"DB_Refresh : {rows} rows loaded");
   return true;
}

4-step flow

RunSqlSelect(sql) — caches result in memory. false = failure.
RowCount — cached row count.
GetRowArray(i) — i-th row as an array in column order.
Convert — here we glue into a CSV line and push into DispData.

The cached result is valid until the next SELECT executes or the connection closes.

Cell-level access

When you want a cell instead of a whole row:

DB["local"].RunSqlSelect("SELECT id, menu_name, weight_g FROM order_history WHERE id=?", p);
 
int    id   = DB["local"].GetValueInt(/*row*/0, /*colName*/"id");
string menu = DB["local"].GetValue(/*row*/0, /*colName*/"menu_name");
double w    = DB["local"].GetValueDouble(/*row*/0, /*colName*/"weight_g");

The sample's DB_OpenModifyDlg (Ch. 7) uses this pattern to copy every column of the selected row into edit fields (Edit*).

Function	Meaning
`GetValue(row, col)`	string (universal)
`GetValueInt(row, col)`	integer
`GetValueDouble(row, col)`	double
`GetValueBool(row, col)`	boolean

row is a 0-based index, col accepts either column name or index (name recommended — survives column reordering).

SELECTs You'll Reuse

Sample-domain ready — feel free to copy/paste:

// 1) Last 50 (for DataGrid)
"SELECT id, order_no, menu_name, end_time, result " +
"FROM order_history ORDER BY id DESC LIMIT 50"
 
// 2) Counts per result
"SELECT result, COUNT(*) FROM order_history GROUP BY result"
 
// 3) Errors only
"SELECT id, order_no, menu_name FROM order_history WHERE is_error = 1"
 
// 4) Time range
"SELECT id, order_no FROM order_history " +
"WHERE start_time >= ? AND start_time <  ?"
// → pass [from, to] array as the 2nd arg to RunSqlSelect

Parameterized SELECT

RunSqlSelect also takes a parameter array as the second argument — same pattern as INSERT/UPDATE.

array p[] = {""};
p.Clear();
p.Add(targetId);
 
DB["local"].RunSqlSelect("SELECT * FROM order_history WHERE id=?", p);
 
if( DB["local"].RowCount == 1 )
{
   string menu = DB["local"].GetValue(0, "menu_name");
   // ...
}

Common Pitfalls

RowCount is meaningful only right after a SELECT — after INSERT / UPDATE, you must SELECT again to get the right count. The sample sidesteps this by calling DB_Refresh() at the end of every mutating function.
Spell out column names in SELECT — SELECT * may reorder columns across environments, which is dangerous when using GetRowArray by index.
Empty results — GetValue(0, ...) against a 0-row result returns an empty string, but it's logically meaningless. Always guard.

Next Chapter

Now that read and write code are both covered, it's time to put data on screen — touching DispData alone refreshes the XDataGrid automatically. That binding is the next chapter.