Skip to main content

Database Access

@ZeyOS — platform core (16 methods)​

Database access​

ZYMBA
// Get the ZeyOS database connection
$db = @ZeyOS.selectDatabase();

Session​

ZYMBA
$session = @ZeyOS.selectSession();

$value = @ZeyOS.getSessionVariable("userId");
$all   = @ZeyOS.listSessionVariables();

Configuration​

ZYMBA
$config = @ZeyOS.readConfig();
@ZeyOS.writeConfig($newConfig);

Authentication​

ZYMBA
$user = @ZeyOS.authenticateUser("username", "password");

Logging​

ZYMBA
@ZeyOS.log("Order processed", "info", 1);

File operations​

ZYMBA
@ZeyOS.sendFile("/path/to/report.pdf"); // Send file as HTTP response
$tmpPath = @ZeyOS.getTempPath();         // Get temp directory

Mail queueing​

ZYMBA
$msg = new @Mail.SimpleMessage();
$msg.setTo("user@example.com");
$msg.setSubject("Hello");
$msg.setBody("World");
@ZeyOS.queueMail($msg);

Cron management​

ZYMBA
@ZeyOS.resetCron("my-timing-service");

Share tokens​

ZYMBA
$token = @ZeyOS.createShareToken("contacts", $contactId, @Date.now() + 86400);

@SQL — query building (4 static methods)​

Always use parameterized queries to prevent SQL injection:

Prepared statements​

ZYMBA
$sql     = @SQL.prepare("SELECT * FROM contacts WHERE name = ? AND status = ?", $name, 1);
$results = $db.fetchAll($sql, true);

// Multiple parameters
$sql = @SQL.prepare(
    "SELECT * FROM transactions WHERE date >= ? AND date <= ? AND total > ?",
    $startDate, $endDate, $minTotal
);

Escaping​

ZYMBA
$safe   = @SQL.quoteLiteral($userInput);       // Escape and quote a value
$safeId = @SQL.quoteIdentifier("tableName");   // Escape an identifier

Full-text search​

ZYMBA
$condition = @SQL.createSearchCondition("john doe", ["name", "email", "notes"]);
// Creates a search condition across multiple fields

@SQL.Connection — database operations (32 methods)​

Obtained via @ZeyOS.selectDatabase().

Fetching data​

ZYMBA
$db = @ZeyOS.selectDatabase();

// Fetch all rows as array of associative arrays
$sql  = @SQL.prepare("SELECT name, email FROM contacts WHERE status = ?", 1);
$rows = $db.fetchAll($sql, true);

// Fetch single row
$user = $db.fetchOne(@SQL.prepare("SELECT * FROM contacts WHERE ID = ?", $id), true);
if ($user is null) {
    throw new @Exception("User not found");
}

// Fetch all values of a single field
$names = $db.fetchAllOfField(@SQL.prepare("SELECT name FROM contacts WHERE status = ?", 1), 0);
// ["Alice", "Bob", "Charlie"]

Convenience select methods​

ZYMBA
// selectAll — build SELECT without writing SQL
$users = $db.selectAll(
    "contacts",              // table
    "ID, name, email",      // fields
    "status = 1 AND type = 0", // conditions
    "name ASC",             // order by
    10,                     // limit
    0,                      // offset
    true                    // indexed (associative arrays)
);

// selectOne — single row
$user = $db.selectOne("contacts", "ID, name", "ID = 42", null, true);

// selectAllOfField — one column
$names = $db.selectAllOfField("contacts", "name", "status = 1", "name ASC");

Inserting data​

ZYMBA
$db.insert("contacts", [
    name:   "Alice Smith",
    email:  "alice@example.com",
    status: 1
]);

$newId = $db.getLastInsertID();

Deleting data​

ZYMBA
$db.delete("contacts", "ID = 42");

Checking existence​

ZYMBA
if ($db.exists("contacts", "email = 'alice@example.com'")) {
    echo "User exists";
}

echo $db.countRowsInTable("contacts"); // Total row count

Raw queries​

ZYMBA
$result = $db.query("UPDATE contacts SET status = 0 WHERE lastlogin < '2025-01-01'");
echo $db.countAffectedRows(); // Number of modified rows

Transactions​

ZYMBA
// Manual transaction control
$db.begin();
try {
    $db.insert("contacts", [name: "Alice"]);
    $db.insert("contacts", [name: "Bob"]);
    $db.commit();
} catch ($e) {
    $db.rollback();
    throw $e;
}

// Context manager (recommended)
with ($db.selectTransaction()) {
    $db.insert("contacts", [name: "Alice"]);
    $db.insert("contacts", [name: "Bob"]);
    // Auto-commits on success, auto-rolls back on error
}

// Savepoints for nested transactions
$db.savepoint("sp1");
try {
    $db.insert("items", [name: "Widget"]);
} catch ($e) {
    $db.rollbackToSavepoint("sp1");
}
$db.releaseSavepoint("sp1");

Database information​

ZYMBA
echo $db.getDatabaseName();
echo $db.getDatabaseSize();
echo $db.getVersion();
echo $db.getCharset();

$tables = $db.listTables();
$fields = $db.listFieldsInTable("contacts");

Production pattern: batch query with pagination​

ZYMBA
include 'zymba:sql,var';

$db      = @ZeyOS.selectDatabase();
$page    = +(@HTTP.getRequestQueryVariable("page") ?? 1);
$perPage = 50;
$offset  = ($page - 1) * $perPage;

$sql   = @SQL.prepare(
    "SELECT ID, name, email FROM contacts WHERE status = ? ORDER BY name LIMIT ? OFFSET ?",
    1, $perPage, $offset
);
$rows  = $db.fetchAll($sql, true);
$total = $db.fetchOne(@SQL.prepare("SELECT COUNT(*) AS n FROM contacts WHERE status = ?", 1), true);

echo @Var.toJSON([
    page:    $page,
    perPage: $perPage,
    total:   $total.n,
    rows:    $rows
]);