From Clomosy Docs
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.
Clomosy.DBSQLServerQuery.SQL.Text = 'SELECT * FROM Customers'; // SQL query defined Clomosy.DBSQLServerQuery.Open; // Opens the query and retrieves the result set
TclSqliteQuery:
Query1 = Clomosy.DBSQLiteQueryWith('SELECT * from StudentExamInformation');
Query1.OpenOrExecute;
TclSqlQuery:
Query1 = Clomosy.DBSQLServerQueryWith('SELECT plate_code,city_name FROM Cities');
Query1.Open;
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;
Query1.SQL.Text = 'SELECT * FROM Users'; Query1.Open; Query1.First; // Go to first record
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;
Query1.SQL.Text = 'SELECT * FROM Users'; Query1.Open; Query1.First; // Go to first record Query1.Next; // Go to second record
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;
Query1.SQL.Text = 'SELECT * FROM Users'; Query1.Open; Query1.Last; // Go to last record
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;
Query1.SQL.Text = 'SELECT * FROM Users'; Query1.Open; Query1.Last; // Go to last record Query1.Prior; //Go to previous record
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;
try
Query1.SQL.Text = 'SELECT * FROM Products';
Query1.Open;
// Transactions...
finally
Query1.Free; // Release from memory
}
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;
Query1.SQL.Text = 'SELECT * FROM Orders'; Query1.Open; // Transactions... Query1.Close; // Close query
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:
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 = 'Updated Name'; // Make changes
SqliteQuery1.Post; // Save changes to the database
}
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 to the database
}
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 to the database
}
JsonQuery1.Close;
SQL
It is a query language used to interact with the database. This command is used to access, insert, update, or delete data in the database.
Query1.SQL.Text = 'SELECT * FROM table_name';
FieldByName
It is used to access the value of a specific field in a dataset. This command allows you to reach the data in the current record by using the specified field name.
Query1.FieldByName('Price').AsString;
ExecSQL
It is used to execute an SQL command in the database, typically for data manipulation operations such as insertion, update, or deletion. This command applies the effect of the query (for example, adding a record) without returning a result set.
JsonQuery1.SQL.Text = 'DELETE FROM Products WHERE ID = :ID';
JsonQuery1.Params.ParamByName('ID').AsInteger = 5; // Set the parameter
JsonQuery1.ExecSQL; // Execute the query