Local Database Queries: Difference between revisions

Revision as of 10:57, 11 October 2024

Local Database query, typically refers to a data query conducted on a local database or an in-memory database. Local databases are often employed to store small-scale data within the memory or file system of the device where an application is running. These databases are designed for quick access, but they often provide fewer features compared to larger and more complex databases.

Such local database queries are used to access and query data in these types of local databases. These queries are generally written using simple SQL queries or database query languages (for example, SQL for SQLite).

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 1:
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;

Example 2:
The example demonstrates working with an SQLite database by creating a table, inserting data, updating records, and querying. The SqLiteConnectionCreateTable procedure checks if the specified table exists in the database and creates it if it does not. Then, it calls the SqLiteInsertData procedure to insert initial data. Next, a specific record (STOCK_CODE='002') is queried in the database; if the record does not exist, it is inserted, and if it does, it is updated. Finally, all records in the database are read in a loop and displayed using ShowMessage.

TRObject Syntax

var
  Sifre,DB  : String;
  Qry : TClSQLiteQuery;
void SqLiteInsertData;
{
  try
    Clomosy.DBSQLiteQuery.Sql.Text = '
  INSERT INTO TBLSTOCKSB (STOCK_CODE, STOCK_NAME) VALUES (''001'', ''Stock1'');
  INSERT INTO TBLSTOCKSB (STOCK_CODE, STOCK_NAME) VALUES (''002'', ''Stock2'');
  INSERT INTO TBLSTOCKSB (STOCK_CODE, STOCK_NAME) VALUES (''003'', ''Stock3'');
  INSERT INTO TBLSTOCKSB (STOCK_CODE, STOCK_NAME) VALUES (''004'', ''Stock4'');
  INSERT INTO TBLSTOCKSB (STOCK_CODE, STOCK_NAME) VALUES (''005'', ''Stock5'');
  INSERT INTO TBLSTOCKSB (STOCK_CODE, STOCK_NAME) VALUES (''006'', ''Stock6'');';
    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 + 'DBStock.db3', '');

    // Check if the table exists
    Clomosy.DBSQLiteQuery.Sql.Text = 'SELECT name FROM sqlite_master WHERE type="table" AND name="TBLSTOCKSB";';
    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 TBLSTOCKSB(STOCK_CODE TEXT PRIMARY KEY,
      STOCK_NAME TEXT 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);
  }
}

{ 
  SqLiteConnectionCreateTable;
  
  
  try 
    Clomosy.DBSQLiteQuery.Sql.Text = 'SELECT * FROM TBLSTOCKSB WHERE STOCK_CODE='+ QuotedStr('002');
    Clomosy.DBSQLiteQuery.OpenOrExecute;
    
    If Not (Clomosy.DBSQLiteQuery.Found) 
    {
      Clomosy.DBSQLiteQuery.Sql.Text = 'INSERT INTO TBLSTOCKSB (STOCK_CODE,STOCK_NAME) VALUES ('+QuotedStr('005')+','+QuotedStr('BİR TANE DAHA MALZEME')+')';
      Clomosy.DBSQLiteQuery.Exec;
    }
  
    
    Clomosy.DBSQLiteQuery.Sql.Text = 'SELECT * FROM TBLSTOCKSB   WHERE STOCK_CODE='+ QuotedStr('002');
    Clomosy.DBSQLiteQuery.OpenOrExecute;
    If (Clomosy.DBSQLiteQuery.Found) 
    Clomosy.DBSQLiteQuery.Edit 
    else
      Clomosy.DBSQLiteQuery.Insert;
    
    Clomosy.DBSQLiteQuery.FieldByName('STOCK_CODE').AsString = '002';
    Clomosy.DBSQLiteQuery.FieldByName('STOCK_NAME').AsString = 'TEST 2';
    Clomosy.DBSQLiteQuery.Post;
    
    Qry = Clomosy.DBSQLiteQueryWith('SELECT * FROM TBLSTOCKSB');
    Qry.OpenOrExecute;
    while (not Qry.Eof) 
    {
      ShowMessage(Qry.FieldByName('STOCK_NAME').AsString);
      Qry.Next;
    }
    
   except
   ShowMessage('Exception Class: '+LastExceptionClassName+' Exception Message: '+LastExceptionMessage);
   }
}

Base Syntax

var
  Sifre,DB  : String;
  Qry : TClSQLiteQuery;
procedure SqLiteInsertData;
begin
  try
    Clomosy.DBSQLiteQuery.Sql.Text := '
  INSERT INTO TBLSTOCKSB (STOCK_CODE, STOCK_NAME) VALUES (''001'', ''Stock1'');
  INSERT INTO TBLSTOCKSB (STOCK_CODE, STOCK_NAME) VALUES (''002'', ''Stock2'');
  INSERT INTO TBLSTOCKSB (STOCK_CODE, STOCK_NAME) VALUES (''003'', ''Stock3'');
  INSERT INTO TBLSTOCKSB (STOCK_CODE, STOCK_NAME) VALUES (''004'', ''Stock4'');
  INSERT INTO TBLSTOCKSB (STOCK_CODE, STOCK_NAME) VALUES (''005'', ''Stock5'');
  INSERT INTO TBLSTOCKSB (STOCK_CODE, STOCK_NAME) VALUES (''006'', ''Stock6'');';
    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 + 'DBStock.db3', '');

    // Check if the table exists
    Clomosy.DBSQLiteQuery.Sql.Text := 'SELECT name FROM sqlite_master WHERE type="table" AND name="TBLSTOCKSB";';
    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 TBLSTOCKSB(STOCK_CODE TEXT PRIMARY KEY,
      STOCK_NAME TEXT 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;

begin 
  SqLiteConnectionCreateTable;
  
  
  try 
    Clomosy.DBSQLiteQuery.Sql.Text := 'SELECT * FROM TBLSTOCKSB WHERE STOCK_CODE='+ QuotedStr('002');
    Clomosy.DBSQLiteQuery.OpenOrExecute;
    
    If Not Clomosy.DBSQLiteQuery.Found Then 
    Begin
      Clomosy.DBSQLiteQuery.Sql.Text := 'INSERT INTO TBLSTOCKSB (STOCK_CODE,STOCK_NAME) VALUES ('+QuotedStr('005')+','+QuotedStr('BİR TANE DAHA MALZEME')+')';
      Clomosy.DBSQLiteQuery.Exec;
    End;
  
    
    Clomosy.DBSQLiteQuery.Sql.Text := 'SELECT * FROM TBLSTOCKSB   WHERE STOCK_CODE='+ QuotedStr('002');
    Clomosy.DBSQLiteQuery.OpenOrExecute;
    If Clomosy.DBSQLiteQuery.Found Then 
    Clomosy.DBSQLiteQuery.Edit 
    else
      Clomosy.DBSQLiteQuery.Insert;
    
    Clomosy.DBSQLiteQuery.FieldByName('STOCK_CODE').AsString := '002';
    Clomosy.DBSQLiteQuery.FieldByName('STOCK_NAME').AsString := 'TEST 2';
    Clomosy.DBSQLiteQuery.Post;
    
    Qry := Clomosy.DBSQLiteQueryWith('SELECT * FROM TBLSTOCKSB');
    Qry.OpenOrExecute;
    while not Qry.Eof Do 
    begin
      ShowMessage(Qry.FieldByName('STOCK_NAME').AsString);
      Qry.Next;
    End;
    
   except
   ShowMessage('Exception Class: '+LastExceptionClassName+' Exception Message: '+LastExceptionMessage);
   end;
End;

@@ Line 1: / Line 1: @@
+Local Database query, typically refers to a data query conducted on a local database or an in-memory database. Local databases are often employed to store small-scale data within the memory or file system of the device where an application is running. These databases are designed for quick access, but they often provide fewer features compared to larger and more complex databases.<br>
+Such local database queries are used to access and query data in these types of local databases. These queries are generally written using simple SQL queries or database query languages (for example, SQL for SQLite).<br>
 To connect to a Local database using the DBSQLiteConnect component in Clomosy, you can follow these basic steps:
 * Saving database activity:

Assistance

Clomosy

Account

TRObject

System Library

Clomosy Class

Arrays

Sorting Algorithms

File Handling

Cryptographic Encryption

Code Decoding Functions

Component Properties

Code Examples

Sensor Structures

Database

Clomosy Pro Tools: Management & Development

Other Languages

Navigation