Local Database Queries

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.

function Clomosy.DBSQLiteConnect(ASQLiteDatabase, ASQLiteUPassword:String):Boolean;

The first parameter is the path to the database file, and the second is the password for the database.
In the example below, it attempts to connect to the "DBSchool.db3" database located in the project directory. It returns True if the connection is successful; otherwise, it returns False.

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.

Function DBSQLiteQueryWith(SQLStr:String):TClSQLiteQuery;

Example

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.

 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;
  }

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.

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);
   }
}

See Also

• Database Connectivity
• Database Commands