Database Commands

You will explore the fundamental commands used for database management in the TRObject programming language. Database applications provide many important functions to facilitate data access and management. Commands such as Open, Close, Insert, and Edit interact with data sets, enabling the reading, updating, and addition of new records. Additionally, navigation commands like First, Next, Last, and Prior allow for traversing through data sets and locating specific records. Comprehensive information about the commands you can use when performing database operations is provided.

Open

The result set obtained from a database query is used to open or prepare the data for use. The connection must be active before interacting with the database.

Clomosy.DBSQLServerQuery.Open;

Clomosy.DBSQLiteQuery.Open;

Query1.Open; // Query1 opens the database query.

OpenOrExecute

It is a method used to open or execute a database query. This method automatically opens the query if it is not already open; if it is open, it executes the query.

Clomosy.DBSQLServerQuery.OpenOrExecute;

Clomosy.DBSQLiteQuery.OpenOrExecute;

Query1.OpenOrExecute;

First

It is used to mark the first record in a dataset. This command allows you to navigate to the first record of the dataset when retrieving the query result.

Clomosy.DBSQLServerQuery.First;

Clomosy.DBSQLiteQuery.First;

Query1.First;

Next

It is used to move to the next record in a dataset. This command allows you to navigate to the record following the current record position.

Clomosy.DBSQLServerQuery.Next;

Clomosy.DBSQLiteQuery.Next;

Query1.Next;

Last

It is used to mark the last record in a dataset. This command allows you to navigate to the last record of the dataset when retrieving the query result.

Clomosy.DBSQLServerQuery.Last;

Clomosy.DBSQLiteQuery.Last;

Query1.Last;

Prior

It is used to move to the previous record in a dataset. This command allows you to navigate to the record preceding the current record position.

Clomosy.DBSQLServerQuery.Prior;

Clomosy.DBSQLiteQuery.Prior;

Query1.Prior;

Free

It allows an object to be freed from memory. When this command is used, all resources related to the object are released, and the object becomes unusable.

Clomosy.DBSQLServerQuery.Free;

Clomosy.DBSQLiteQuery.Free;

Query1.Free;

Close

It allows a database query to be closed. This command releases the dataset of an opened query and closes the connection.

Clomosy.DBSQLServerQuery.Close;

Clomosy.DBSQLiteQuery.Close;

Query1.Close;

Found

It is used to check whether the searched record exists when performing a search operation in a dataset. It returns `True` if there is a record in the result set of a query, and `False` if there is not.

if(Query1.Found)
  ShowMessage('Record found.')
else
  ShowMessage('Record not found.');

EOF

It is used to check whether the end of a dataset has been reached. If the end of the dataset has been reached, the value of `EOF` is `True`; otherwise, it is `False`.

while (not Query1.EOF)  // Loop until the end of the dataset is reached
{
   // Process the records
   Query1.Next;  // Move to the next record
}

RecordCount

It is used to return the total number of records in a dataset. This command is useful for determining the number of records in the result of a query.

TclSqliteQuery:

SqliteQuery1.SQL.Text = 'SELECT * FROM Users';
SqliteQuery1.Open; 
ShowMessage('Total Record Count: ' + IntToStr(SqliteQuery1.RecordCount));
SqliteQuery1.Close;

TclSqlQuery:

SqlQuery1.SQL.Text = 'SELECT * FROM Orders';
SqlQuery1.Open; 
ShowMessage('Total Record Count: ' + IntToStr(SqlQuery1.RecordCount));
SqlQuery1.Close;

TclJsonQuery:

JsonQuery1.SQL.Text = 'SELECT * FROM Products';
JsonQuery1.Open; 
ShowMessage('Total Record Count: ' + IntToStr(JsonQuery1.RecordCount));
JsonQuery1.Close;

Edit

It is used to update the current record in a dataset. When this command is called, it switches the active record to edit mode, allowing changes to be made to the record.
TclSqliteQuery:

SqliteQuery1.SQL.Text = 'SELECT * FROM Users WHERE ID = 1';
SqliteQuery1.Open; 
if (not SqliteQuery1.EOF)
{
  SqliteQuery1.Edit; // Switch to edit mode
  SqliteQuery1.FieldByName('Name').AsString = 'New Name'; // Make changes
  SqliteQuery1.Post; // Save changes
}
SqliteQuery1.Close;

TclSqlQuery:

SqlQuery1.SQL.Text = 'SELECT * FROM Orders WHERE ID = 1';
SqlQuery1.Open; 
if (not SqlQuery1.EOF)
{
  SqlQuery1.Edit; // Switch to edit mode
  SqlQuery1.FieldByName('Quantity').AsInteger = 10; // Make changes
  SqlQuery1.Post; // Save changes
}
SqlQuery1.Close;

TclJsonQuery:

JsonQuery1.SQL.Text = 'SELECT * FROM Products WHERE ID = 1';
JsonQuery1.Open; 
if (not JsonQuery1.EOF)
{
  JsonQuery1.Edit; // Switch to edit mode
  JsonQuery1.FieldByName('Price').AsFloat = 19.99; // Make changes
  JsonQuery1.Post; // Save changes
}
JsonQuery1.Close;

Insert

The existing records in the dataset are preserved, and the fields are prepared to add a new record.

TclSqliteQuery:

SqliteQuery1.SQL.Text = 'SELECT * FROM Users'; // Select the Users table
SqliteQuery1.Open; // Open the query

SqliteQuery1.Insert; // Switch to edit mode to add a new record
SqliteQuery1.FieldByName('Name').AsString = 'John Doe'; // Set field values
SqliteQuery1.FieldByName('Age').AsInteger = 30;

SqliteQuery1.Post; // Save changes
SqliteQuery1.Close; // Close the query

TclSqlQuery:

SqlQuery1.SQL.Text = 'SELECT * FROM Orders'; // Select the Orders table
SqlQuery1.Open; // Open the query

SqlQuery1.Insert; // Switch to edit mode to add a new record
SqlQuery1.FieldByName('ProductID').AsInteger = 101; // Set field values
SqlQuery1.FieldByName('Quantity').AsInteger = 5;

SqlQuery1.Post; // Save changes
SqlQuery1.Close; // Close the query

TclJsonQuery:

JsonQuery1.SQL.Text = 'SELECT * FROM Products'; // Select the Products table
JsonQuery1.Open; // Open the query

JsonQuery1.Insert; // Switch to edit mode to add a new record
JsonQuery1.FieldByName('Name').AsString = 'New Product'; // Set field values
JsonQuery1.FieldByName('Price').AsFloat = 19.99;

JsonQuery1.Post; // Save changes
JsonQuery1.Close; // Close the query

Post

It is used to save changes (insertion, update) made to a dataset to the database. When this command is called, the current record state is sent to the database, and the changes become permanent.

TclSqliteQuery:

TclSqlQuery:

TclJsonQuery: