Mssqlconn

This is an old revision of Mssqlconn, edited by Lacak at . The current revision is 89937.

Overview

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.

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

As the documentation specifies:

Also, TMSSQLConnection and TSybaseConnection do not support handling of return status and output parameters of stored procedures. 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===

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

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;

Return To: LCL Components  — Previous: TSQLConnector Next: TPQConnection
LCL Components
Component Tab Components
Standard TMainMenu • TPopupMenu • TButton • TLabel • TEdit • TMemo • TToggleBox • TCheckBox • TRadioButton • TListBox • TComboBox • TScrollBar • TGroupBox • TRadioGroup • TCheckGroup • TPanel • TFrame • TActionList
Additional TBitBtn • TSpeedButton • TStaticText • TImage • TShape • TBevel • TPaintBox • TNotebook • TLabeledEdit • TSplitter • TTrayIcon • TControlBar • TFlowPanel • TMaskEdit • TCheckListBox • TScrollBox • TApplicationProperties • TStringGrid • TDrawGrid • TPairSplitter • TColorBox • TColorListBox • TValueListEditor
Common Controls TTrackBar • TProgressBar • TTreeView • TListView • TStatusBar • TToolBar • TCoolBar • TUpDown • TPageControl • TTabControl • THeaderControl • TImageList • TPopupNotifier • TDateTimePicker
Dialogs TOpenDialog • TSaveDialog • TSelectDirectoryDialog • TColorDialog • TFontDialog • TFindDialog • TReplaceDialog • TTaskDialog • TOpenPictureDialog • TSavePictureDialog • TCalendarDialog • TCalculatorDialog • TPrinterSetupDialog • TPrintDialog • TPageSetupDialog
Data Controls TDBNavigator • TDBText • TDBEdit • TDBMemo • TDBImage • TDBListBox • TDBLookupListBox • TDBComboBox • TDBLookupComboBox • TDBCheckBox • TDBRadioGroup • TDBCalendar • TDBGroupBox • TDBGrid • TDBDateTimePicker
Data Access TDataSource • TCSVDataSet • TSdfDataSet • TBufDataset • TFixedFormatDataSet • TDbf • TMemDataset
System TTimer • TIdleTimer • TLazComponentQueue • THTMLHelpDatabase • THTMLBrowserHelpViewer • TAsyncProcess • TProcessUTF8 • TProcess • TSimpleIPCClient • TSimpleIPCServer • TXMLConfig • TEventLog • TServiceManager • TCHMHelpDatabase • TLHelpConnector
Misc TColorButton • TSpinEdit • TFloatSpinEdit • TArrow • TCalendar • TEditButton • TFileNameEdit • TDirectoryEdit • TDateEdit • TTimeEdit • TCalcEdit • TFileListBox • TFilterComboBox • TComboBoxEx • TCheckComboBox • TButtonPanel • TShellTreeView • TShellListView • TXMLPropStorage • TINIPropStorage • TJSONPropStorage • TIDEDialogLayoutStorage • TMRUManager • TStrHolder
LazControls TCheckBoxThemed • TDividerBevel • TExtendedNotebook • TListFilterEdit • TListViewFilterEdit • TLvlGraphControl • TShortPathEdit • TSpinEditEx • TFloatSpinEditEx • TTreeFilterEdit • TExtendedTabControl •
RTTI TTIEdit • TTIComboBox • TTIButton • TTICheckBox • TTILabel • TTIGroupBox • TTIRadioGroup • TTICheckGroup • TTICheckListBox • TTIListBox • TTIMemo • TTICalendar • TTIImage • TTIFloatSpinEdit • TTISpinEdit • TTITrackBar • TTIProgressBar • TTIMaskEdit • TTIColorButton • TMultiPropertyLink • TTIPropertyGrid • TTIGrid
SQLdb TSQLQuery • TSQLTransaction • TSQLScript • TSQLConnector • TMSSQLConnection • TSybaseConnection • TPQConnection • TPQTEventMonitor • TOracleConnection • TODBCConnection • TMySQL40Connection • TMySQL41Connection • TMySQL50Connection • TMySQL51Connection • TMySQL55Connection • TMySQL56Connection • TMySQL57Connection • TSQLite3Connection • TIBConnection • TFBAdmin • TFBEventMonitor • TSQLDBLibraryLoader
Pascal Script TPSScript • TPSScriptDebugger • TPSDllPlugin • TPSImport_Classes • TPSImport_DateUtils • TPSImport_ComObj • TPSImport_DB • TPSImport_Forms • TPSImport_Controls • TPSImport_StdCtrls • TPSCustomPlugin
SynEdit TSynEdit • TSynCompletion • TSynAutoComplete • TSynMacroRecorder • TSynExporterHTML • TSynPluginSyncroEdit • TSynPasSyn • TSynFreePascalSyn • TSynCppSyn • TSynJavaSyn • TSynPerlSyn • TSynHTMLSyn • TSynXMLSyn • TSynLFMSyn • TSynDiffSyn • TSynUNIXShellScriptSyn • TSynCssSyn • TSynPHPSyn • TSynTeXSyn • TSynSQLSyn • TSynPythonSyn • TSynVBSyn • TSynAnySyn • TSynMultiSyn • TSynBatSyn • TSynIniSyn • TSynPoSyn
Chart TChart • TListChartSource • TRandomChartSource • TUserDefinedChartSource • TCalculatedChartSource • TDbChartSource • TChartToolset • TChartAxisTransformations • TChartStyles • TChartLegendPanel • TChartNavScrollBar • TChartNavPanel • TIntervalChartSource • TDateTimeIntervalChartSource • TChartListBox • TChartExtentLink • TChartImageList
IPro TIpFileDataProvider • TIpHtmlDataProvider • TIpHttpDataProvider • TIpHtmlPanel
Virtual Controls TVirtualDrawTree • TVirtualStringTree • TVTHeaderPopupMenu