Local Database Queries

To connect to a Local database using the DBSQLiteConnect component in Clomosy, you can follow these basic steps:

• Saving database activity:

The Clomosy.EventLog property is set to True, indicating that it will start logging database activity.

Clomosy.EventLog := True;

• Set the SQL Server Connection String:

First you need to prepare the connection string that you will use to connect to the local database. This string should contain the database and connection details such as its password.

Clomosy.DBSQLiteConnect(DB, Password);

The DB variable is assigned to specify the path to the SQLite database file. This ensures that the database is created in the folder where the application files are located.

• Define the TClSQLiteQuery component:

To execute queries in local, you can use the TClSQLiteQuery component. It is defined within your application.

var
 Qry : TClSQLiteQuery;

• Specify the SQL query you want to run:

Qry := Clomosy.DBSQLiteQueryWith('SELECT * FROM table_name');

Example:
This code snippet represents a simple application managing student exam information using SQLite database. The application first establishes a database connection and checks for the existence of a table named StudentExamInformation. If the table does not exist, it creates it automatically and populates it with sample data.

After completing database operations, the GetData function retrieves student information through SQL queries and dynamically adds them to the user interface. Each student's details, including name, surname, lesson, midterm and final grades, are displayed using individual TclLabel components.

This code snippet serves as a straightforward example demonstrating how to handle database operations and present data on a user interface effectively.

TRObject Syntax

 var
  MyForm : TclForm;
  vScroll:TCLVertScrollBox;
  sLabel : TclLabel; 
  
  void SqLiteInsertData;
  {
    try
      Clomosy.DBSQLiteQuery.Sql.Text = '
    INSERT INTO StudentExamInformation (schollID, name, surname,lesson,v_note,f_note) VALUES (123456, ''John'', ''Doe'', ''Mathematics'',85, 90);
    INSERT INTO StudentExamInformation (schollID, name, surname,lesson,v_note,f_note) VALUES (789456, ''Jane'', ''Smith'', ''Physics'', 78, 82);
    INSERT INTO StudentExamInformation (schollID, name, surname,lesson,v_note,f_note) VALUES (753951, ''Michael'', ''Brown'', ''Chemistry'', 88, 91);
    INSERT INTO StudentExamInformation (schollID, name, surname,lesson,v_note,f_note) VALUES (852654, ''Emily'', ''Davis'', ''Biology'', 92, 89);
    INSERT INTO StudentExamInformation (schollID, name, surname,lesson,v_note,f_note) VALUES (956754, ''Daniel'', ''Wilson'', ''History'', 81, 84);
    INSERT INTO StudentExamInformation (schollID, name, surname,lesson,v_note,f_note) VALUES (124326, ''Emma'', ''Johnson'', ''English'', 87, 93);';
      Clomosy.DBSQLiteQuery.OpenOrExecute;
      
      ShowMessage('Adding data to the table was successful!');
    except
     ShowMessage('Exception Class: '+LastExceptionClassName+' Exception Message: '+LastExceptionMessage);
    }
  }
  
  void SqLiteConnectionCreateTable;
  var
    TableExists: Boolean;
  {
    try
      Clomosy.DBSQLiteConnect(Clomosy.AppFilesPath + 'DBScholl.db3', '');
 
      // Check if the table exists
      Clomosy.DBSQLiteQuery.Sql.Text = 'SELECT name FROM sqlite_master WHERE type="table" AND name="StudentExamInformation";';
      Clomosy.DBSQLiteQuery.OpenOrExecute;
      
      // Check the results
      TableExists = not Clomosy.DBSQLiteQuery.Eof;
      
      // Create the table if it does not exist
      if not (TableExists)
      {
        Clomosy.DBSQLiteQuery.Sql.Text = 'CREATE TABLE StudentExamInformation(schollID INTEGER NOT NULL,
        name TEXT NOT NULL,
        surname TEXT NOT NULL,
        lesson TEXT NOT NULL,
        v_note INTEGER NOT NULL,
        f_note INTEGER NOT NULL)';
        Clomosy.DBSQLiteQuery.OpenOrExecute;
        
        ShowMessage('Table successfully added to the database!');
        SqLiteInsertData;
      } else
      {
        ShowMessage('The Products table already exists.');
      }

    except
     ShowMessage('Exception Class: '+LastExceptionClassName+' Exception Message: '+LastExceptionMessage);
    }
  }
  
  void GetData;
  var
    Qry : TClSQLiteQuery;
    i : Integer;
  {
    try
      Qry = Clomosy.DBSQLiteQueryWith('SELECT * from StudentExamInformation');
      Qry.OpenOrExecute;
      
      if (Qry.Found)
      {
        for i = 0 to Qry.RecordCount-1
        {
          sLabel = MyForm.AddNewLabel(vScroll,'Label'+IntToStr(i),'');
          sLabel.StyledSettings = ssFamily;
          sLabel.TextSettings.Font.Size=20;
          sLabel.Align = alTop;
          sLabel.Margins.Left= 5;
          sLabel.Margins.Top= 5; 
          sLabel.Height = 50;
          sLabel.Text = '* '+Qry.FieldByName('schollID').AsString + '  '+ Qry.FieldByName('name').AsString + ' '+ 
          Qry.FieldByName('surname').AsString + '  '+ Qry.FieldByName('lesson').AsString + '   '+ 
          Qry.FieldByName('v_note').AsString + '-'+ Qry.FieldByName('f_note').AsString;
          
          sLabel.AutoSize = True;
          sLabel.WordWrap = True;
          Qry.Next;
        }  
      }
      
    except
      ShowMessage('Exception class: '+LastExceptionClassName+' Exception Message: ' +LastExceptionMessage);
    } 
  }


  {
    MyForm = TclForm.Create(Self);
    vScroll = MyForm.AddNewVertScrollBox(MyForm,'vScrollBox');
    vScroll.Align = alMostTop;
    vScroll.Height = MyForm.clheight-70;
    SqLiteConnectionCreateTable;
    GetData;
    
    MyForm.Run;
  }

Base Syntax

var
  MyForm : TclForm;
  vScroll:TCLVertScrollBox;
  sLabel : TclLabel; 
  
  procedure SqLiteInsertData;
  begin
    try
      Clomosy.DBSQLiteQuery.Sql.Text := '
    INSERT INTO StudentExamInformation (schollID, name, surname,lesson,v_note,f_note) VALUES (123456, ''John'', ''Doe'', ''Mathematics'',85, 90);
    INSERT INTO StudentExamInformation (schollID, name, surname,lesson,v_note,f_note) VALUES (789456, ''Jane'', ''Smith'', ''Physics'', 78, 82);
    INSERT INTO StudentExamInformation (schollID, name, surname,lesson,v_note,f_note) VALUES (753951, ''Michael'', ''Brown'', ''Chemistry'', 88, 91);
    INSERT INTO StudentExamInformation (schollID, name, surname,lesson,v_note,f_note) VALUES (852654, ''Emily'', ''Davis'', ''Biology'', 92, 89);
    INSERT INTO StudentExamInformation (schollID, name, surname,lesson,v_note,f_note) VALUES (956754, ''Daniel'', ''Wilson'', ''History'', 81, 84);
    INSERT INTO StudentExamInformation (schollID, name, surname,lesson,v_note,f_note) VALUES (124326, ''Emma'', ''Johnson'', ''English'', 87, 93);';
      Clomosy.DBSQLiteQuery.OpenOrExecute;
      
      ShowMessage('Adding data to the table was successful!');
    except
     ShowMessage('Exception Class: '+LastExceptionClassName+' Exception Message: '+LastExceptionMessage);
    end;
  end;
  
  procedure SqLiteConnectionCreateTable;
  var
    TableExists: Boolean;
  begin
    try
      Clomosy.DBSQLiteConnect(Clomosy.AppFilesPath + 'DBScholl.db3', '');
 
      // Check if the table exists
      Clomosy.DBSQLiteQuery.Sql.Text := 'SELECT name FROM sqlite_master WHERE type="table" AND name="StudentExamInformation";';
      Clomosy.DBSQLiteQuery.OpenOrExecute;
      
      // Check the results
      TableExists := not Clomosy.DBSQLiteQuery.Eof;
      
      // Create the table if it does not exist
      if not TableExists then
      begin
        Clomosy.DBSQLiteQuery.Sql.Text := 'CREATE TABLE StudentExamInformation(schollID INTEGER NOT NULL,
        name TEXT NOT NULL,
        surname TEXT NOT NULL,
        lesson TEXT NOT NULL,
        v_note INTEGER NOT NULL,
        f_note INTEGER NOT NULL)';
        Clomosy.DBSQLiteQuery.OpenOrExecute;
        
        ShowMessage('Table successfully added to the database!');
        SqLiteInsertData;
      end else
      begin
        ShowMessage('The Products table already exists.');
      end;

    except
     ShowMessage('Exception Class: '+LastExceptionClassName+' Exception Message: '+LastExceptionMessage);
    end;
  end;
  
  procedure GetData;
  var
    Qry : TClSQLiteQuery;
    i : Integer;
  begin
    try
      Qry := Clomosy.DBSQLiteQueryWith('SELECT * from StudentExamInformation');
      Qry.OpenOrExecute;
      
      if Qry.Found then
      begin
        for i := 0 to Qry.RecordCount-1 do
        begin
          sLabel := MyForm.AddNewLabel(vScroll,'Label'+IntToStr(i),'');
          sLabel.StyledSettings := ssFamily;
          sLabel.TextSettings.Font.Size:=20;
          sLabel.Align := alTop;
          sLabel.Margins.Left:= 5;
          sLabel.Margins.Top:= 5; 
          sLabel.Height := 50;
          sLabel.Text := '* '+Qry.FieldByName('schollID').AsString + '  '+ Qry.FieldByName('name').AsString + ' '+ 
          Qry.FieldByName('surname').AsString + '  '+ Qry.FieldByName('lesson').AsString + '   '+ 
          Qry.FieldByName('v_note').AsString + '-'+ Qry.FieldByName('f_note').AsString;
          
          sLabel.AutoSize := True;
          sLabel.WordWrap := True;
          Qry.Next;
        end;  
      end;
      
    except
      ShowMessage('Exception class: '+LastExceptionClassName+' Exception Message: ' +LastExceptionMessage);
    end; 
  end;


  begin
    MyForm := TclForm.Create(Self);
    vScroll := MyForm.AddNewVertScrollBox(MyForm,'vScrollBox');
    vScroll.Align := alMostTop;
    vScroll.Height := MyForm.clheight-70;
    SqLiteConnectionCreateTable;
    GetData;
    
    MyForm.Run;
  end;