// SQLite DataSet Navigation
// Deep dive into the TDataSet class methods for accessing data.

uses System.Data;

var db := DataBase.Create('SQLite', [':memory:']);
db.Exec('CREATE TABLE Log (ID INTEGER PRIMARY KEY, Msg TEXT, Level INTEGER)');

for var i := 1 to 5 do
  db.Exec(
    'INSERT INTO Log (Msg, Level) VALUES (?, ?)', 
    [ 'Event ' + IntToStr(i), i * 10 ]
  );

var ds := db.Query('SELECT * FROM Log');

PrintLn('<h3>DataSet Operations</h3>');

PrintLn('Field Count: ' + IntToStr(ds.FieldCount));
PrintLn('');

// Forward-only iteration
PrintLn('<h4>Forward Iteration</h4>');
// ds.Step() is a practical alternative to Eof/Next. 
// It moves to the next record and returns True if successful.
while ds.Step do begin
  PrintLn(Format(
    '[%d] %s (Level: %d)',
    [ ds.AsInteger('ID'), ds.AsString('Msg').ToHtml, ds.AsInteger('Level') ]
  ));
end;

// Field access types
PrintLn('<h4>Typed Access</h4>');
// Execute a new query to reset the cursor to the first record.
ds := db.Query('SELECT Msg, Level FROM Log LIMIT 1');
if ds.Step then begin
  PrintLn("AsString('Msg'): " + ds.AsString('Msg').ToHtml);
  PrintLn("AsInteger('Level'): " + IntToStr(ds.AsInteger('Level')));
  PrintLn("IsNull('Msg'): " + (if ds.IsNull('Msg') then 'True' else 'False'));
end;