Difference between revisions of "mssqlconn"

From Lazarus wiki
(added example)
m (Installation)
 
(35 intermediate revisions by 9 users not shown)
Line 1: Line 1:
==Overview==
+
{{mssqlconn}}
mssqlconn is an fcl-db unit containing connector code for '''TMSSQLConnection''' (MS SQL Server) and '''TSybaseConnection''' (Sybase ASE)
+
 
 +
{{Infobox databases}}
 +
'''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.
 
Lazarus uses mssqlconn to present an MS SQL connector and Sybase ASE connector.
  
 
==DBLib==
 
==DBLib==
mssqlconn uses the dblib low-level unit which requires the FreeTDS library (.dll/.dylib/.so) to connect to the server.
+
 
 +
mssqlconn uses the dblib low-level unit which requires the [http://www.freetds.org FreeTDS] library (.dll/.dylib/.so) to connect to the server.
  
 
==Installation==
 
==Installation==
The only thing you need is a FreeTDS dll/dylib/so for your platform
+
 
* On Linux, download the freetds package which provides '''libsybdb.so''' (e.g. in Debian this is provided by package '''libsybd5''') (and development package) using your package manager.
+
The only thing you need is a FreeTDS dll/dylib/so for your platform.
* On Windows, you can download a recent 32 or 64 bit version of the FreeTDS library '''dblib.dll''' here: [ftp://ftp.freepascal.org/fpc/contrib/windows/]
+
* On Linux, download the freetds package which provides '''libsybdb.so''' (e.g. in Debian/Raspbian this is provided by package '''libsybdb5''') and the related development package using your package manager. You might also need to add a symbolic link libsybdb.so to libsybdb.so.5
 +
** Ubuntu 20.04
 +
**:<tt>sudo apt install libsybdb5</tt>
 +
**:<tt>cd /usr/lib/x86_64-linux-gnu</tt>
 +
**:<tt>sudo ln -s libsybdb.so.5.1.0  libsybdb.so</tt>
 +
* On Windows, you can download a recent 32 or 64 bit version of the FreeTDS library '''dblib.dll''' here: ftp://ftp.freepascal.org/fpc/contrib/windows/ or http://downloads.freepascal.org/fpc/contrib/windows/
  
 
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).
 
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).
  
 +
==Documentation==
  
==Documentation==
 
 
Official documentation: [http://www.freepascal.org/docs-html/fcl/mssqlconn/index.html FPC documentation on mssqlconn]
 
Official documentation: [http://www.freepascal.org/docs-html/fcl/mssqlconn/index.html FPC documentation on mssqlconn]
As the documentation specifies, you might need to tweak some parameters for BLOB support. Also, MS SQL multiple resultsets (MARS) are not supported.
+
 
 +
==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.
 +
 
 +
Change the drivername and/or position
 +
* Put dblib as first unit in your .lpr and then call dblib.initialisedblib('/position/to/libsybdb.so')
 +
 
 +
===Output parameters of stored procedures===
 +
 
 +
Also, '''TMSSQLConnection''' and '''TSybaseConnection''' do not support handling of return status and output parameters of stored procedures. For more informations see: http://www.freetds.org/faq.html#ms.output.parameters
 +
As a workaround you can use something like:
 +
 
 +
<syntaxhighlight lang=pascal>
 +
with SQLQuery1 do begin
 +
  SQL.Text := 'declare @param int; declare @ret int;' +
 +
              'exec @ret=MyStoredProc 1, @param OUTPUT;' +
 +
              'select @ret as return_status, @param as out_param';
 +
  Open;
 +
end;
 +
</syntaxhighlight>
 +
 
 +
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: http://msdn.microsoft.com/en-us/library/ms174979.aspx
 +
* You might want to similarly set SET ANSI_PADDING to ON to enable better compatibiity with sqldb text comparison functions. See MS documentation: http://msdn.microsoft.com/en-us/library/ms187403.aspx
 +
 
 +
==Error messages==
 +
 
 +
===Error 20009 : Unable to connect: Adaptive Server is unavailable or does not exist===
 +
 
 +
* Check if TCP/IP protocol is enabled on SQL Server side using "SQL Server Configuration Manager" - "SQL Server Network Configuration"
 +
* Check if TCP port 1433 is opened
 +
* If you connect to specific instance of SQL Server (this is also case of SQL Server Express), check if SQL Server Browser Service is running on server (service listens on UDP port 1434)
 +
 
 +
===Error 20019 : Attempt to initiate a new Adaptive Server operation with results pending===
 +
 
 +
* Set TSQLQuery.PacketRecords property to -1
 +
* For more information see: http://www.freetds.org/faq.html#pending
 +
 
 +
===Error 20047 : DBPROCESS is dead or not enabled===
 +
 
 +
* SQL Server periodically verifies (by default every 30 seconds) if idle TCP/IP connection is still intact by sending a keep alive packet to its peer. If the remote system is still reachable and functioning, a acknowledge packet is sent back. If not local TCP will reset connection.
 +
* Adjust SQL Server Configuration Manager -> SQL Server Network Configuration -> Protocols -> TCP/IP : "Keep Alive"
 +
 
 +
===EINOutError/Can not load DB-Lib client library "dblib.dll". Check your installation===
 +
 
 +
*Make sure you have all required dlls installed (e.g. '''dblib.dll''', '''libiconv2.dll''')
 +
*Make sure you have all required C/C++ runtime libraries installed that '''dblib.dll''' depends on
 +
*Make sure the bitness (32 or 64 bit) of the dblib library and your compiled program match
  
 
==Examples==
 
==Examples==
 +
 
===Creating a database===
 
===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.
+
 
<syntaxhighlight>
+
The example below creates a database on an MS SQL Server. 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 regular '''StartTransaction''' and '''Commit'''.
 +
 
 +
<syntaxhighlight lang=pascal>
 
// Set up a form with a button called DBButton,  
 
// Set up a form with a button called DBButton,  
 
// textbox called DatabaseNameEdit,
 
// textbox called DatabaseNameEdit,
Line 31: Line 95:
 
   CurrentDB: string;
 
   CurrentDB: string;
 
begin
 
begin
   if DatabaseNameEdit.Text='' then
+
   if DatabaseNameEdit.Text = '' then
 
   begin
 
   begin
 
     showmessage('Empty database name. Please specify database name first.');
 
     showmessage('Empty database name. Please specify database name first.');
Line 37: Line 101:
 
   end;
 
   end;
  
   CurrentDB:=FConn.DatabaseName;
+
   CurrentDB := FConn.DatabaseName;
 
   try
 
   try
 
     // For FPC 2.6.1+
 
     // For FPC 2.6.1+
     FConn.ExecuteDirect('CREATE DATABASE '+DatabaseNameEdit.Text);
+
     FConn.ExecuteDirect('CREATE DATABASE ' + DatabaseNameEdit.Text);
 
     {
 
     {
 
     // This works with FPC 2.7.1, not on 2.6.1
 
     // This works with FPC 2.7.1, not on 2.6.1
Line 48: Line 112:
 
     on E: Exception do
 
     on E: Exception do
 
     begin
 
     begin
       showmessage('Exception running CreateDB: '+E.Message);
+
       showmessage('Exception running CreateDB: ' + E.Message);
 
     end;
 
     end;
 
   end;
 
   end;
 
end;
 
end;
 
</syntaxhighlight>
 
</syntaxhighlight>
 
 
 
[[Category:Lazarus]]
 
[[Category:Databases]]
 
[[Category:FPC]]
 

Latest revision as of 08:43, 12 August 2021

English (en) español (es) français (fr) polski (pl)

Databases portal

References:

Tutorials/practical articles:

Databases

Advantage - MySQL - MSSQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos

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.

DBLib

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

Installation

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

  • On Linux, download the freetds package which provides libsybdb.so (e.g. in Debian/Raspbian this is provided by package libsybdb5) and the related development package using your package manager. You might also need to add a symbolic link libsybdb.so to libsybdb.so.5
    • Ubuntu 20.04
      sudo apt install libsybdb5
      cd /usr/lib/x86_64-linux-gnu
      sudo ln -s libsybdb.so.5.1.0 libsybdb.so
  • On Windows, you can download a recent 32 or 64 bit version of the FreeTDS library dblib.dll here: ftp://ftp.freepascal.org/fpc/contrib/windows/ or http://downloads.freepascal.org/fpc/contrib/windows/

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

Documentation

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.

Change the drivername and/or position

  • Put dblib as first unit in your .lpr and then call dblib.initialisedblib('/position/to/libsybdb.so')

Output parameters of stored procedures

Also, TMSSQLConnection and TSybaseConnection do not support handling of return status and output parameters of stored procedures. For more informations see: http://www.freetds.org/faq.html#ms.output.parameters As a workaround you can use something like:

with SQLQuery1 do begin
  SQL.Text := 'declare @param int; declare @ret int;' +
              'exec @ret=MyStoredProc 1, @param OUTPUT;' +
              'select @ret as return_status, @param as out_param';
  Open;
end;

Some more tips:

Error messages

Error 20009 : Unable to connect: Adaptive Server is unavailable or does not exist

  • Check if TCP/IP protocol is enabled on SQL Server side using "SQL Server Configuration Manager" - "SQL Server Network Configuration"
  • Check if TCP port 1433 is opened
  • If you connect to specific instance of SQL Server (this is also case of SQL Server Express), check if SQL Server Browser Service is running on server (service listens on UDP port 1434)

Error 20019 : Attempt to initiate a new Adaptive Server operation with results pending

Error 20047 : DBPROCESS is dead or not enabled

  • SQL Server periodically verifies (by default every 30 seconds) if idle TCP/IP connection is still intact by sending a keep alive packet to its peer. If the remote system is still reachable and functioning, a acknowledge packet is sent back. If not local TCP will reset connection.
  • Adjust SQL Server Configuration Manager -> SQL Server Network Configuration -> Protocols -> TCP/IP : "Keep Alive"

EINOutError/Can not load DB-Lib client library "dblib.dll". Check your installation

  • Make sure you have all required dlls installed (e.g. dblib.dll, libiconv2.dll)
  • Make sure you have all required C/C++ runtime libraries installed that dblib.dll depends on
  • Make sure the bitness (32 or 64 bit) of the dblib library and your compiled program match

Examples

Creating a database

The example below creates a database on an MS SQL Server. 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 regular 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:
//FConn.Params.Add('AutoCommit=true');
procedure TForm1.CreateDBButtonClick(Sender: TObject);
var
  CurrentDB: string;
begin
  if DatabaseNameEdit.Text = '' then
  begin
    showmessage('Empty database name. Please specify database name first.');
    exit;
  end;

  CurrentDB := FConn.DatabaseName;
  try
    // For FPC 2.6.1+
    FConn.ExecuteDirect('CREATE DATABASE ' + DatabaseNameEdit.Text);
    {
    // This works with FPC 2.7.1, not on 2.6.1
    FConn.CreateDB;
    }
  except
    on E: Exception do
    begin
      showmessage('Exception running CreateDB: ' + E.Message);
    end;
  end;
end;