// SQLite Database Example
// Demonstrates creating an in-memory database, basic tables, transactions, and JSON export.

uses System.Data;

// Create an in-memory SQLite database
// For a file-based DB, provide the path: DataBase.Create('SQLite', ['c:\path\to\db.sqlite'])
var db := DataBase.Create('SQLite', [':memory:']);

PrintLn('Database created.');

// Create a table
db.Exec('CREATE TABLE Users (ID INTEGER PRIMARY KEY, Name TEXT, Email TEXT)');

// Insert data using parameters (Safe against SQL Injection)
PrintLn('Inserting data...');

// Use a transaction for bulk inserts for better performance and integrity
db.BeginTransaction;
try
   db.Exec('INSERT INTO Users (Name, Email) VALUES (?, ?)', ['Alice', 'alice@example.com']);
   db.Exec('INSERT INTO Users (Name, Email) VALUES (?, ?)', ['Bob', 'bob@example.com']);
   db.Exec('INSERT INTO Users (Name, Email) VALUES (?, ?)', ['Charlie', 'charlie@example.com']);
   db.Commit;
except
   db.Rollback;
   PrintLn('Error inserting data!');
end;

// Query data
PrintLn('Querying data...');
var ds := db.Query('SELECT ID, Name, Email FROM Users WHERE Name LIKE ?', ['%a%']); // Names containing 'a'

// Iterate through results
// ds.Step moves to the next record and returns True if one exists.
while ds.Step do begin
   PrintLn(Format(
     'ID: %d, Name: %s, Email: %s', 
	 [ ds.AsInteger(0), ds.AsString(1).ToHtml, ds.AsString(2).ToHtml ] // Indexed access avoids field lookups
   ));
end;

// Export as JSON
PrintLn('');
PrintLn('JSON Export (StringifyAll):');
// StringifyAll returns the entire result set as a JSON string
var json := db.Query('SELECT Name, Email FROM Users').StringifyAll;
PrintLn(json);