From Lazarus wiki
Revision as of 11:25, 7 March 2014 by BigChimp (talk | contribs) (more tips)


mssqlconn is an fcl-db unit containing connector code for TMSSQLConnection (MS SQL Server) and TSybaseConnection (Sybase ASE) Lazarus uses mssqlconn to present an MS SQL connector and Sybase ASE connector.


mssqlconn uses the dblib low-level unit which requires the FreeTDS library (.dll/.dylib/.so) to connect to the server.


The only thing you need is a FreeTDS dll/dylib/so for your platform

  • On Linux, download the freetds package which provides (e.g. in Debian this is provided by package libsybdb5) (and development package) using your package manager. You might also need to add a symbolic link to
  • On Windows, you can download a recent 32 or 64 bit version of the FreeTDS library dblib.dll here: [1]

Advanced use: by modifying FPC file dblib.pas SQLDB could use the "native" library ntwdblib.dll instead of the default FreeTDS library dblib.dll (in this case FPC needs to be recompiled).


Official documentation: FPC documentation on mssqlconn

Limitations and tips

  • Sybase port: the default port for Sybase ASE is 5000. As FreeTDS/mssqlconn uses 1433 by default, you may want to add :5000 to the hostname proprerty automatically or after feedback from the end user.

As the documentation specifies:

  • you might need to tweak some parameters for BLOB support.
  • MS SQL multiple resultsets (MARS) are not supported.

Also, TMSSQLConnection and TSybaseConnection do not support handling of return status and output parameters of stored procedures.

Some more tips:

  • You might want to do an YourConnection.ExecuteDirect('SET ANSI_NULL_DFLT_ON ON'); if you want to automatically allow NULL values for columns when creating tables. See MS documentation:
  • You might want to similarly set SET ANSI_PADDING to ON to enable better compatibiity with sqldb text comparison functions.


Creating a database

The example below creates a database on an MS SQL Server. Note that for this to work, the connection's parameter AutoCommit must be set to on. For normal subsequent connection you can switch it off again and use regulart StartTransaction and Commit.

// Set up a form with a button called DBButton, 
// textbox called DatabaseNameEdit,
// '''MSSQLConnection''' called FConn
// ***make sure the Autocommit parameter is on for the create db part before connectiong:
procedure TForm1.CreateDBButtonClick(Sender: TObject);
  CurrentDB: string;
  if DatabaseNameEdit.Text='' then
    showmessage('Empty database name. Please specify database name first.');

    // For FPC 2.6.1+
    FConn.ExecuteDirect('CREATE DATABASE '+DatabaseNameEdit.Text);
    // This works with FPC 2.7.1, not on 2.6.1
    on E: Exception do
      showmessage('Exception running CreateDB: '+E.Message);