SQL Server Queries

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is used to store, manage, query, and process data in the database. It is based on the relational database concept and is managed using SQL (Structured Query Language).

DBSQLServerConnect is a method used to create a connection object and connect to the database. This object contains the connection information and establishes a database connection based on this information.

function Clomosy.DBSQLServerConnect(ProviderName, ServerName, UserName, UserPassword, DatabaseName:String; Port:Integer=1433) :Boolean;

Use of:

Clomosy.DBSQLServerConnect('SQL
Server','CloData','testClo','123456','DBClo',1433);

The result of a query obtained from the database belongs to the TclSQLQuery class. Therefore, an object of this class must first be defined to retrieve data from the table.

var
 clomosyQ1 :TclSQLQuery;

To execute queries with the database using a global connection object, use the `DBSQLServerConnection` property. This property links an existing database connection to the query object you define.

`Clomosy.DBSQLServerConnection` is a globally defined and already established SQL Server connection. Once this connection is successfully set up elsewhere, you can execute your queries using this connection.

The goal is to avoid creating a new connection for each query, instead reusing the same connection (`DBSQLServerConnection`) across multiple queries. This improves efficiency and simplifies connection management.

clomosyQ1.Connection = Clomosy.DBSQLServerConnection;

The DBSQLServerQueryWith function is used to directly execute an SQL query on the defined object.

clomosyQ1 = Clomosy.DBSQLServerQueryWith('SELECT * FROM table_name');

Another method is the DBSQLServerQuery function. This method is used to define the SQL query, but it does not execute it directly like the DBSQLServerQueryWith function. An additional operation is required to execute the query (such as Open or OpenOrExecute).

Clomosy.DBSQLServerQuery.Sql.Text = 'SELECT * FROM table_name';
Clomosy.DBSQLServerQuery.Open;

ADDITIONAL INFORMATION
The DBXCopyRecord function is used to copy a record between two dataset objects.

This function takes two parameters. The first parameter returns the source query structure, while the second parameter is the target query. The query obtained from the source is copied to the target query.

procedure Clomosy.DBXCopyRecord(SourceQ, DestQ: TDataSet);

Example

var
  sourceQuery, targetQuery: TClSqlQuery;
begin
  sourceQuery := TClSqlQuery.Create(nil);
  targetQuery := TClSqlQuery.Create(nil);
  
  // Establish the database connection
  Clomosy.DBSQLServerConnect('SQL Server', 'CloData', 'testClo', '123456', 'DBClo', 1433);

  try
    // Assign the same connection to both queries
    sourceQuery.Connection := Clomosy.DBSQLServerConnection;
    targetQuery.Connection := Clomosy.DBSQLServerConnection;

    // Define and open the source query
    sourceQuery.SQL.Text := 'SELECT id, ad AS product_name, price AS product_price FROM ShippingProductInformation WHERE price = 125';
    sourceQuery.Open;

    // Define the target query structure
    targetQuery.SQL.Text := 'SELECT * FROM ProductInformation WHERE 1=0'; 
    targetQuery.Open; 

    // Edit the target query
    targetQuery.Edit;

    // Copy the record from the source query to the target query
    Clomosy.DBXCopyRecord(sourceQuery, targetQuery);

    // Save the changes to the target query
    targetQuery.Post;
  finally
    // Close and free the queries
    sourceQuery.Close;
    targetQuery.Close;
    sourceQuery.Free;
    targetQuery.Free;
  end;
end;

Note! In cases where copying operations will be performed, the fields of the tables must be equal and the field names must be the same.

---

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