View source for SQL Server Queries

Executing SQL queries in SQL Server is a fundamental operation when working with databases. SQL Server provides a powerful platform for managing and manipulating data, and executing queries is at the core of this functionality.<br>

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(<span style="color:red">'SQL Server','server_name','user_name','user_password','database_name',port</span>);

* 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(<span style="color:red">'SELECT model AS Product_Model FROM OperatingSystem'</span>);

* 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:'''<br>

:'''TRObject Syntax'''
  Var
  LocalQ:TUniQuery;
  
  { 
    Clomosy.DBSQLServerConnect(<span style="color:red">'SQL Server','server_name','user_name','user_password','database_name',1433</span>);
    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(<span style="color:red">'SQL Server','server_name','user_name','user_password','database_name',1433</span>);
    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:'''<br>
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:<br>
[[File:Tables.png|frameless|500px]]<br>

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

 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.<br>

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

'''Code:'''<br>
  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+<nowiki>''</nowiki>);
      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(<span style="color:red">'SQL Server','server_name','user_name','user_password','database_name',port</span>);
    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;
  }