SQLite JSON Integration

uses System.Data;

var db := DataBase.Create('SQLite', [':memory:']);

// Setup sample data
db.Exec('CREATE TABLE Products (ID INTEGER, Name TEXT, Price FLOAT, Category TEXT)');
db.Exec('INSERT INTO Products VALUES (1, "Mechanical Keyboard", 120.50, "Peripherals")');
db.Exec('INSERT INTO Products VALUES (2, "Gaming Mouse", 59.99, "Peripherals")');
db.Exec('INSERT INTO Products VALUES (3, "UltraWide Monitor", 450.00, "Display")');

PrintLn('<h3>JSON Stringification</h3>');

// 1. StringifyAll: Convert the entire result set into a JSON array of objects.
PrintLn('<h4>1. StringifyAll (Multiple Records)</h4>');
var dsAll := db.Query('SELECT Name, Price FROM Products WHERE Category = ?', ['Peripherals']);
PrintLn('<pre>' + dsAll.StringifyAll + '</pre>');

// 2. Stringify: Convert the CURRENT record only into a JSON object.
PrintLn('<h4>2. Stringify (Current Record)</h4>');
var dsOne := db.Query('SELECT * FROM Products WHERE ID = 3');
if not dsOne.Eof then
   PrintLn('<pre>' + dsOne.Stringify + '</pre>');

// 3. Stringify with parameters
// Some datasets might be large, you can use StringifyAll to quickly build APIs.
PrintLn('<h4>3. Result as Table</h4>');
// You can still use the Dataset normally
var ds := db.Query('SELECT Name, Price FROM Products');
PrintLn('<table class="table table-sm">');
PrintLn('<tr><th>Name</th><th>Price</th></tr>');
while ds.Step do begin
   PrintLn(Format(
     '<tr><td>%s</td><td>$%.2f</td></tr>',
     [ ds.AsString(0).ToHtml, ds.AsFloat(1) ]
   ));
end;
PrintLn('</table>');