SQL Server Queries

To connect to a SQL Server database using the DBSQLServerConnect component in Clomosy, you can follow these basic steps:

• Set the SQL Server Connection String:

First, you'll need to prepare the connection string that you'll use to connect to the SQL Server database. This string should include the name of the SQL Server, the database name, authentication credentials, and other connection details.

Clomosy.DBSQLServerConnect('SQL Server','server_name','user_name','user_password','database_name',port);

• Define the TclSQLQuery component:

To execute queries in SQL Server, you can use the TclSQLQuery component. It is defined within your application.

var
LocalQ:TclSQLQuery;

• Specify the SQL query you want to run:

LocalQ := Clomosy.DBSQLServerQueryWith('SELECT model AS Product_Model FROM OperatingSystem');

• To go to the first record in the query's result set:

LocalQ.First;

• To advance to the next record in the data access component:

LocalQ.Next;

Example:

TRObject Syntax

 Var
 LocalQ:TUniQuery;
 
 { 
   Clomosy.DBSQLServerConnect('SQL Server','server_name','user_name','user_password','database_name',1433);
   LocalQ = Clomosy.DBSQLServerQueryWith('SELECT model AS Product_Model FROM OperatingSystem');
   LocalQ.First;
   with LocalQ do
   {
     While (NOT EOF)
     {
       ShowMessage(FieldByName('Product_Model').AsString);
       Next;
     }
   }
 }

Base Syntax

 Var
 LocalQ:TUniQuery;
 
 begin 
   Clomosy.DBSQLServerConnect('SQL Server','server_name','user_name','user_password','database_name',1433);
   LocalQ := Clomosy.DBSQLServerQueryWith('SELECT model AS Product_Model FROM OperatingSystem');
   LocalQ.First;
   with LocalQ do
   begin
     While NOT EOF Do
     Begin
       ShowMessage(FieldByName('Product_Model').AsString);
       Next;
     End;
   end;
 end;

Example 2:
As an example application, two comboboxes will be created, where the first one will list cities in Turkey and the second one will list districts. Cities will be retrieved from the Cities table in the testDb database. The district combobox will remain empty until a city is selected. When a city is selected, districts corresponding to the selected city will be retrieved from the Districts table based on a query performed on the database and populated into the second combobox.

In this example, initially, two tables named Cities and Districts will be created in the testDb database using Microsoft SQL Server Management Studio. The Cities table will consist of id, plate_code, and city_name fields. The Districts table will consist of id, city_id, and district_name fields.

Below are the tables created:

The Combo1 combobox object created for cities is populated with all the data from the Cities table as a query result from SQL.

SELECT plate_code,city_name FROM Cities

When the list in the Combo1 combobox is clicked, the corresponding data from the Districts table is retrieved based on the selected city id from the combobox and listed in the Combo2 object.

SELECT * FROM Districts WHERE city_id = '+getID+'

Code:

 var
   Combo1,Combo2 : TClComboBox;
   Form1:TclForm;
 
 void combo1DataLoad
 var
   citiesQuery :TclSQLQuery;
 {
   citiesQuery = TclSQLQuery.Create(Nil);
   
   try
     citiesQuery = Clomosy.DBSQLServerQueryWith('SELECT plate_code,city_name FROM Cities');
     citiesQuery.Open;
     while (not citiesQuery.Eof)
     {
       Combo1.AddItem(citiesQuery.FieldByName('city_name').AsString,citiesQuery.FieldByName('plate_code').AsString);
       citiesQuery.Next;
     }
   finally
     citiesQuery.Free;
   }
 }
 
 void combo2DataLoad(getID);
 var
   clDataQuery2 :TclSQLQuery;
 {
   clDataQuery2 = TclSQLQuery.Create(Nil);
   
   Combo2.Clear;
   try
     clDataQuery2 = Clomosy.DBSQLServerQueryWith('SELECT * FROM Districts WHERE city_id = '+getID+'');
     clDataQuery2.Open;
     
     while (not clDataQuery2.Eof)
     {
       Combo2.AddItem(clDataQuery2.FieldByName('district_name').AsString,clDataQuery2.FieldByName('id').AsString);
       clDataQuery2.Next;
     }
   finally
     clDataQuery2.Free;
   }
 }
 
 void ComboSelected;
 var
   cityID : String;
 {
   cityID = Combo1.GetValueIndex(Combo1.ItemIndex);
   combo2DataLoad(cityID);
 }
 
 {
   Form1 = TclForm.Create(Self);
   try
     Clomosy.DBSQLServerConnect('SQL Server','server_name','user_name','user_password','database_name',port);
   except
     ShowMessage('Exception Class: '+LastExceptionClassName+' Exception Message: '+LastExceptionMessage);
   }
   Combo1 = Form1.AddNewComboBox(Form1,'Combo1');
   Combo1.Align = alMostTop;
   Combo1.Width = 150;
   Combo1.Margins.Top=10;
   Combo1.Margins.Left =50;
   Combo1.Margins.Right =50;
   combo1DataLoad;
   Form1.AddNewEvent(Combo1,tbeOnChange,'ComboSelected');
   
   Combo2 = Form1.AddNewComboBox(Form1,'Combo2');
   Combo2.Align = alTop;
   Combo2.Width = 150;
   Combo2.Margins.Top=10;
   Combo2.Margins.Left =50;
   Combo2.Margins.Right =50;
   
   Form1.Run;
 }