From Clomosy Docs
Only premium accounts can benefit from Clomosy’s cloud technology. To integrate your external cloud database, please visit the General Cloud Technology page.
Cloud databases are databases provided and managed through cloud computing services. These types of databases are typically offered by various cloud service providers and are usually scalable, backed up, and easily accessible. Users can access these databases over the internet.
On the Clomosy platform, pre-defined tables are available to users with a Premium account. These tables are referred to as Templates.
The platform offers many tables, each with its own fields. The tables include Items, Products, Types, Employees, Customers, Tasks, Groups, SurExams, and Threads.
Detailed information about Clomosy's pre-defined tables and their fields is explained on the Premium Plan page. Any desired table specific to the application being developed can be used.
The result of a query retrieved from the cloud database belongs to the TCLJSONQuery class. Therefore, to fetch data from the table, an object of this class must first be defined.
var clomosyQ1 :TCLJSONQuery;
The DBCloudSQLSelectWith function is used to execute an SQL query on the defined object.
Function DBCloudSQLSelectWith(SQLStr:String):TClJSonQuery;
Example
clomosyQ1 = Clomosy.DBCloudSQLSelectWith(SELECT Product_Name,Product_Code FROM tblProjectProducts);
The DBCloudQueryWith function should be used to retrieve a table from the database without executing an SQL query.
Function DBCloudQueryWith(CloudDataSource:TFormTemplate;Filter_GUID, FilterStr:String):TClJSonQuery;
Example
clomosyQ1 = Clomosy.DBCloudQueryWith(ftProducts,'','1=1');
In the DBCloudQueryWith function, the last parameter can be used to add criteria or filters to the query. An example of this process is shown below:
- Clomosy.DBCloudQueryWith(ftMembers,'','1=1 ORDER BY NEWID()');
In the example, the 1=1 statement acts as an unconditional filter, meaning all records are selected. ORDER BY NEWID() generates a new unique ID for each record, causing the results to be sorted randomly. As a result, the data is returned in a different order each time the query is executed.
Check the data, and if any is found, perform the desired operations and save the changes.
clomosyQ1.Post(True);
The DBCloudPostJSON function is used to send data to the table to add or update existing data in the template tables.
Function DBCloudPostJSON(CloudDataSource:TFormTemplate;DataJSON:String):Boolean;
Finally, this function sends the ftProducts data type and the JSON data generated by clomosyQ1.GetJSONString to the database.
Clomosy.DBCloudPostJSON(ftProducts,clomosyQ1.GetJSONString);
Instead of the DBCloudPostJSON function, the DBCloudPostQuery function can be used in the same way to add data to the template tables or update existing data.
Function DBCloudPostQuery(CloudDataSource:TFormTemplate; DataObject:TClJSonQuery):Boolean;
Clomosy.DBCloudPostQuery(ftProducts,clomosyQ1);
Use the filtering feature to apply a specific condition to the data. For example, filter out members whose Member_GUID is '4U97CUA6O6'.
clomosyQ1.Filter = 'Member_GUID <> '+QuotedStr('4U97CUA6O6');
To enable the filtering feature, set the Filtered property to true. This activates the filtering and creates a new list based on the filtering criteria. If you want to disable filtering, set the Filtered property to false. This will return the original list containing all members from the database.
clomosyQ1.Filtered = True;
Example
var
Form1 : TclForm;
btnSelect, btnInsert, btnUpdate, btnDelete : TclButton;
void btnUpdateClick;
var
clomosyQ1: TCLJSONQuery;
{
try
try
clomosyQ1 = Clomosy.DBCloudQueryWith(ftProducts, '', '1=1');
If (clomosyQ1.Found)
{
while (not clomosyQ1.EOF)
{
// If the `Product_Price` field is greater than 50, add 20.
if (clomosyQ1.FieldByName('Product_Price').AsInteger > 50)
{
clomosyQ1.Edit;
clomosyQ1.FieldByName('Product_Price').AsInteger =
clomosyQ1.FieldByName('Product_Price').AsInteger + 20;
clomosyQ1.Post(True); // Save the changes.
ShowMessage('The update operation has been completed.');
}
clomosyQ1.Next; // Move to the next record.
}
}
except
ShowMessage('Exception Class: '+LastExceptionClassName+' Exception Message: '+LastExceptionMessage);
}
finally
Clomosy.DBCloudPostJSON(ftProducts, clomosyQ1.GetJSONString); // Send to the server as JSON.
clomosyQ1.Free; // Free the query object.
}
}
void btnSelectClick;
var
clomosyQ1:TCLJSONQuery;
{
clomosyQ1 = Clomosy.DBCloudSQLSelectWith('SELECT Product_Name,Product_Code FROM tblProjectProducts');
//clomosyQ1 = Clomosy.DBCloudQueryWith(ftProducts,'','1=1'); //A table can also be retrieved using `DBCloudQueryWith`.
//The `Product_Name` field from the `tblProjectProducts` table has been displayed.
while (not clomosyQ1.EOF)
{
ShowMessage(clomosyQ1.FieldByName('Product_Name').AsString);
clomosyQ1.Next;
}
/*
with clomosyQ1 do
{
if (Found)
{
First;
ShowMessage(FieldByName('Product_Name').AsString);
}
}
*/
}
// INSERT operation void
void btnInsertClick;
var
clomosyQ2:TCLJSONQuery;
{
try
try
clomosyQ2 = Clomosy.DBCloudQueryWith(ftProducts, '', '1=1');
if (clomosyQ2.Found)
{
//If you run this code a second time, you should update the new record information.
clomosyQ2.Insert; // Add a new record.
clomosyQ2.FieldByName('Product_Name').AsString = 'New Product 3';
clomosyQ2.FieldByName('Product_Code').AsString = 'NP345';
clomosyQ2.FieldByName('Product_Price').AsInteger = 45;
clomosyQ2.Post(True); // "Save the data"
ShowMessage('Addition operation completed.');
}
except
ShowMessage('Exception Class: '+LastExceptionClassName+' Exception Message: '+LastExceptionMessage);
}
finally
Clomosy.DBCloudPostJSON(ftProducts, clomosyQ2.GetJSONString);
clomosyQ2.Free;
}
}
void btnDeleteClick
var
clomosyQ1: TCLJSONQuery;
{
try
try
clomosyQ1 = Clomosy.DBCloudQueryWith(ftProducts, '', '1=1');
If (clomosyQ1.Found)
{
while (not clomosyQ1.EOF)
{
//If the Product_Price field is less than 10, delete the record.
if (clomosyQ1.FieldByName('Product_Price').AsInteger < 10)
{
clomosyQ1.Delete; // Delete the record.
ShowMessage('Deletion operation completed.');
}
else
{
clomosyQ1.Next; // Move to the next record.
}
}
}
except
ShowMessage('Exception Class: '+LastExceptionClassName+' Exception Message: '+LastExceptionMessage);
}
finally
Clomosy.DBCloudPostJSON(ftProducts, clomosyQ1.GetJSONString); // Send the changes to the server.
clomosyQ1.Free; // Release the query object.
}
}
{
Form1 = TclForm.Create(Self);
btnSelect = Form1.AddNewButton(Form1,'btnSelect','SELECT Operation');
btnSelect.Align = alTop;
btnSelect.Height = 50;
Form1.AddNewEvent(btnSelect,tbeOnClick,'btnSelectClick');
btnInsert = Form1.AddNewButton(Form1,'btnInsert','INSERT Operation');
btnInsert.Align = alTop;
btnInsert.Height = 50;
Form1.AddNewEvent(btnInsert,tbeOnClick,'btnInsertClick');
btnUpdate = Form1.AddNewButton(Form1,'btnUpdate','UPDATE Operation');
btnUpdate.Align = alTop;
btnUpdate.Height = 50;
Form1.AddNewEvent(btnUpdate,tbeOnClick,'btnUpdateClick');
btnDelete = Form1.AddNewButton(Form1,'btnDelete','DELETE Operation');
btnDelete.Align = alTop;
btnDelete.Height = 50;
Form1.AddNewEvent(btnDelete,tbeOnClick,'btnDeleteClick');
Form1.Run;
}