Local Database Queries: Difference between revisions

Revision as of 11:32, 18 October 2024

Local databases are typically databases that operate on a specific device or application, stored and managed on a single computer or device. These databases are usually housed within the user's computer or within an application and are accessible only by that device or application.

Establish a connection to SQLite before starting database operations. Use the DBSQLiteConnect function, which takes two string parameters: the first is the path to the database file, and the second is the password for the database. In this step, connect to the SQLite database file (DBSchool.db3).

Clomosy.DBSQLiteConnect(Clomosy.AppFilesPath + 'DBScholl.db3', '');

To ensure that database records are correctly entered in the appropriate area on mobile devices, the AppFilesPath method of the Clomosy class should be used. This is the preferred method.

Notice: In the following example, when the application is launched on Windows, a new database named yeni.db3 is created in the sqlite_db folder, which was previously created in the C drive. On mobile devices, when the application is launched, a database named yeni.db3 is created in the application's file path.

var
 Password, DB : String;
{
 Password = '';
 if (Clomosy.PlatformIsMobile)
 {
 DB = Clomosy.AppFilesPath + 'new.db3';
 } else
 {
 DB = 'C:\sqlite_db\new.db3';
 }
 Clomosy.DBSQLiteConnect(DB, Password);
 ShowMessage('Database has been successfully created.');
}

The result of a query obtained from the local database belongs to the TClSQLiteQuery class. Therefore, an object of this class must be defined first to retrieve data from the table.

var
 clomosyQ1 :TClSQLiteQuery;

The DBSQLiteQueryWith function is used to directly execute an SQL query on the defined object.

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

Another method is the DBSQLiteQuery function. This method is used to define the SQL query but does not execute it directly like the DBSQLiteQueryWith function. An additional operation is required to execute the query (such as Open or OpenOrExecute).

Clomosy.DBSQLiteQuery.Sql.Text = 'SELECT * FROM table_name';
Clomosy.DBSQLiteQuery.OpenOrExecute;

ADDITIONAL INFORMATION
It is recommended to check for conflicts or whether the table exists on different platforms. First, a connection request is made using DBSQLiteConnect. Then, it checks whether the specified table exists.
The following query can be used for this purpose.

Clomosy.DBSQLiteQuery.Sql.Text = 
'SELECT name FROM sqlite_master WHERE type="table" AND name= "the table to be checked” ;';
Clomosy.DBSQLiteQuery.OpenOrExecute;//OpenOrExecute is used to execute the defined query.

Notice:This query checks the database for tables and, if a table named "the table to be checked" exists, it avoids recreating it.

After running the SQL query, check if the table exists using the TableExists variable. If not, create a new table using an SQL query.

// Check the results
TableExists = not Clomosy.DBSQLiteQuery.Eof; //TableExists is a variable of type Boolean.

Afterward, based on the returned result, the table creation process can be performed, or the process can continue with inserting into the table (depending on the usage in the project).

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: @@
-<h2>Local Database</h2>
 Local databases are typically databases that operate on a specific device or application, stored and managed on a single computer or device. These databases are usually housed within the user's computer or within an application and are accessible only by that device or application.
 <br><br>
@@ Line 30: / Line 29: @@
 }
 </pre>
-First, check if a table named "the table to be checked” already exists. This SQL query checks the tables in the SQLite database and verifies if the specified table exists.
+The result of a query obtained from the local database belongs to the TClSQLiteQuery class. Therefore, an object of this class must be defined first to retrieve data from the table.<br>
 <pre>
-Clomosy.DBSQLiteQuery.Sql.Text =
+var
-'SELECT name FROM sqlite_master WHERE type="table" AND name= "the table to be checked” ;';
+ clomosyQ1 :TClSQLiteQuery;
-Clomosy.DBSQLiteQuery.OpenOrExecute;
 </pre>
-<div class="alert alert-ligth border border-3 border-primary-subtle rounded-5 p-4 shadow-sm" role="alert">
-<strong>Notice:</strong>This query checks the database for tables and, if a table named "the table to be checked" exists, it avoids recreating it.
+The DBSQLiteQueryWith function is used to directly execute an SQL query on the defined object.
-</div>
-After running the SQL query, check if the table exists using the TableExists variable. If not, create a new table using an SQL query.
 <pre>
-Clomosy.DBSQLiteQuery.Sql.Text =
+clomosyQ1 = Clomosy.DBSQLiteQueryWith('SELECT * FROM table_name');
-'SELECT name FROM sqlite_master
-WHERE type="table" AND name="kontrol edilecek tablo adı";';
 </pre>
-Run the query by executing the <b> Clomosy.DBSQLiteQuery.OpenOrExecute</b>command. Use this command to either retrieve data from the database or update it, depending on the type of query.
+Another method is the DBSQLiteQuery function. This method is used to define the SQL query but does not execute it directly like the DBSQLiteQueryWith function. An additional operation is required to execute the query (such as Open or OpenOrExecute).
 <pre>
+Clomosy.DBSQLiteQuery.Sql.Text = 'SELECT * FROM table_name';
 Clomosy.DBSQLiteQuery.OpenOrExecute;
 </pre>
-After creating the new table, call the SqLiteInsertData function to start inserting data into the table.
-<pre>
-SqLiteInsertData; // Inserting data into the table.</pre>
+<div class="alert alert-ligth border border-3 border-warning rounded-5 p-4 shadow-sm" role="alert">
+<strong>ADDITIONAL INFORMATION</strong><br>
+It is recommended to check for conflicts or whether the table exists on different platforms. First, a connection request is made using DBSQLiteConnect. Then, it checks whether the specified table exists.<br>
+The following query can be used for this purpose.
+<pre>
+Clomosy.DBSQLiteQuery.Sql.Text =
+'SELECT name FROM sqlite_master WHERE type="table" AND name= "the table to be checked” ;';
+Clomosy.DBSQLiteQuery.OpenOrExecute;//OpenOrExecute is used to execute the defined query.
+</pre>
+<div class="alert alert-warning" role="alert" data-bs-theme="light">
+<strong>Notice:</strong>This query checks the database for tables and, if a table named "the table to be checked" exists, it avoids recreating it.
+</div>
+After running the SQL query, check if the table exists using the TableExists variable. If not, create a new table using an SQL query.<br>
+<pre>
+// Check the results
+TableExists = not Clomosy.DBSQLiteQuery.Eof; //TableExists is a variable of type Boolean.
+</pre>
+Afterward, based on the returned result, the table creation process can be performed, or the process can continue with inserting into the table (depending on the usage in the project).
+</div>
+<b>Example 1</b><br>
+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.<br>
+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.<br>
+This code snippet serves as a straightforward example demonstrating how to handle database operations and present data on a user interface effectively.<br>
+<b>TRObject Syntax</b><br>
-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(<span style="color:red">DB, Password</span>);
-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:'''<br>
-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'''
 <pre>
   var
@@ Line 241: / Line 189: @@
-:'''Base Syntax'''
+<b>Base Syntax</b><br>
 <pre>
 var
@@ Line 354: / Line 301: @@
-'''Example 2:'''<br>
+<b>Example 2</b><br>
-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.
+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.<br>
-:'''TRObject Syntax'''
+<b>TRObject Syntax</b><br>
 <pre>
 var
@@ Line 454: / Line 401: @@
-:'''Base Syntax'''
+<b>Base Syntax</b><br>
 <pre>
 var
@@ Line 549: / Line 496: @@
 End;
 </pre>
+<h2>See Also</h2>
+* [[Clomosy_Class#Database_Connectivity|Database Connectivity]]

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