Easysoft Blog

Mon, 07 Aug 2017

Connecting Delphi on Linux to SQL Server

RAD Studio enables you build a Delphi application for both Windows and Linux platforms. You can, for example, build an ODBC application that uses a Microsoft ODBC driver on Windows and an Easysoft ODBC driver on Linux. In the following tutorial, which describes how to create a console application for Linux that retrieves SQL Server data, the components are:

Windows Machine
---------------
RAD Studio

Linux Machine
-------------
Platform Assistant Server
Delphi Application
unixODBC Driver Manager
SQL Server ODBC Driver

Windows Machine
---------------
SQL Server
program SQLServer;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Error,
  FireDAC.UI.Intf, FireDAC.Phys.Intf, FireDAC.Stan.Def, FireDAC.Stan.Pool,
  FireDAC.Stan.Async, FireDAC.Phys, FireDAC.Phys.ODBC, FireDAC.Phys.ODBCDef,
  FireDAC.DApt, Data.DB, FireDAC.Comp.Client, FireDAC.ConsoleUI.Wait;

var
    RHConnection: TFDConnection;
    RHQuery: TFDQuery;
    sValue: String;

begin
  try
    RHConnection:=TFDConnection.Create(nil);
    RHConnection.Params.Add('DriverID=ODBC');
    RHConnection.Params.Add('DataSource=SQLSERVER_SAMPLE');
    RHConnection.Connected:=true;

    sValue := RHConnection.ExecSQLScalar('select ''SQL Server from Linux'' as test_col');
    Writeln(sValue);

    ReadLn;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
end.
  1. Download the SQL Server ODBC driver for 64-bit Linux platforms. (Registration required.)
  2. Install and license the SQL Server ODBC driver on the machine where the Platform Assistant (PA Server) is or will be installed.

    For installation instructions, see the ODBC driver documentation.

    Note You need the unixODBC Driver Manager installed on your machine. The Easysoft distribution includes a version of the unixODBC Driver Manager that the Easysoft SQL Server ODBC driver has been tested with. The Easysoft driver setup program gives you the option to install unixODBC.

  3. Create an ODBC data source in /etc/odbc.ini that connects to the SQL Server database you want to access from Delphi. For example:
    [SQLSERVER_SAMPLE]
    Driver          = Easysoft ODBC-SQL Server
    Server          = my_machine\SQLEXPRESS
    User            = my_domain\my_user
    Password        = my_password
    # If the database you want to connect to is the default
    # for the SQL Server login, omit this attribute
    Database        = Northwind
  4. Use isql to test the new data source. For example:
    cd /usr/local/easysoft/unixODBC/bin
    ./isql.sh -v SQLSERVER_SAMPLE

    At the prompt, type "help" to display a list of tables. To exit, press return in an empty prompt line.

    If you are unable to connect, refer to this article and the SQL Server ODBC Driver Knowledge Base for assistance.

  1. If you have not already done so, install the PA Server on the machine where you have installed the SQL Server ODBC driver.
  2. Set the environment on this machine so that your Delphi program can load the SQL Server ODBC driver. For example:
    LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/easysoft/sqlserver:/usr/local/easysoft/lib:
    /usr/local/easysoft/unixODBC/lib
    export $LD_LIBRARY_PATH

    Start the PA Server. For example:

    cd ~/PAServer-19.0
    ./paserver

    If you did not install the unixODBC Driver Manager that is included in the SQL Server ODBC driver distribution, omit /usr/local/easysoft/unixODBC/lib from the environment variable value.

  3. In RAD Studio, create a new Delphi console application.
  4. Set the target platform for the application to be 64-bit Linux.
  5. Edit the profile properties for your target platform to specify the details for your PA Server.
  6. Insert the code shown at the start of this tutorial into the application.
  7. Run the application.