Difference between revisions of "Lazarus Database Overview"

From Lazarus wiki
(Lazarus and MSSQL/Sybase: Clarified no need to recompile FPC to get FreeTDS support going)
Line 42: Line 42:
 
Please see the FPC and Lazarus help files for details on using SQLDB with Sybase ASE and MSSQL Server.
 
Please see the FPC and Lazarus help files for details on using SQLDB with Sybase ASE and MSSQL Server.
  
Note1: In any of the above forms, you can connect using TCP/IP, e.g. on port 1433.  
+
{{Note| In any of the above forms, you can connect using TCP/IP, e.g. on port 1433. }}
  
 
If you use native Microsoft Libraries (over Windows) it could be possible to connect to remote server with Named Pipes. FreeTDS libraries do not support Named Pipes.
 
If you use native Microsoft Libraries (over Windows) it could be possible to connect to remote server with Named Pipes. FreeTDS libraries do not support Named Pipes.
  
Note2: Lazarus 1.0 was released with FPC 2.6.0 and does not include TMSSQLConnection or TSybaseConnection. If you want these, it might be easiest to download a snapshot of the latest Lazarus built with FPC 2.6.1, optinally installing that in a directory different from your Lazarus 1.0 version. You can use --primary-config-path to set up a separate environment for the Lazarus snapshot (see [[Multiple_Lazarus]])
+
{{Note| Lazarus 1.0 was released with FPC 2.6.0 and does not include TMSSQLConnection or TSybaseConnection. If you want these, it might be easiest to download a snapshot of the latest Lazarus built with FPC 2.6.1, optinally installing that in a directory different from your Lazarus 1.0 version. You can use --primary-config-path to set up a separate environment for the Lazarus snapshot (see [[Multiple Lazarus]]) }}
  
 
== Lazarus and ODBC ==
 
== Lazarus and ODBC ==
Line 85: Line 85:
  
 
Real functionality requires a bit more effort, however.  If a table doesn't already exist, you'll need to create it programmatically, unless there's a compatible table designer I'm not familiar with.
 
Real functionality requires a bit more effort, however.  If a table doesn't already exist, you'll need to create it programmatically, unless there's a compatible table designer I'm not familiar with.
Note: Current version of OpenOffice/LibreOffice (3.x) contains OpenOffice Base, which can create dbf files in a somewhat user-friendly way.
+
{{Note| Current version of OpenOffice/LibreOffice (3.x) contains OpenOffice Base, which can create dbf files in a somewhat user-friendly way.}}
  
 
Attempting to open a nonexistent table will generate an error.  Tables can be created programmatically through the component after the runtime path and table name are set.
 
Attempting to open a nonexistent table will generate an error.  Tables can be created programmatically through the component after the runtime path and table name are set.
Line 139: Line 139:
 
Dbf1.Close;</syntaxhighlight>   
 
Dbf1.Close;</syntaxhighlight>   
  
Note that you can use Ddf1.findfirst to get the first record in the filtered set, then use  
+
Note that you can use Ddf1.findfirst to get the first record in the filtered set, then use  
 
Dbf1.next to move though the data. I found that using Dbf1.Findnext just causes the program to hang.   
 
Dbf1.next to move though the data. I found that using Dbf1.Findnext just causes the program to hang.   
  

Revision as of 12:27, 22 October 2012

Template:Translate

Overview

This article is an overview of the which databases can work with Lazarus.

Lazarus supports several databases out of the box (using e.g. the SQLDB framework), however the developer must install the required packages (client libraries) for each one.

You can access the database through code or by dropping components on a form. The data-aware components represent fields and are connected by setting the DataSource property to point to a TDataSource. The Datasource represents a table and is connected to the database components (examples: TPSQLDatabase, TSQLiteDataSet) by setting the DataSet property. The data-aware components are located on the "Data Controls" tab. The Datasource and the database controls are located on the "Data Access" tab.

Lazarus and Interbase / Firebird

  1. Built-in SQLDB support
  2. FBLib
  3. IBX
  4. PDO
  5. Zeos

For a tutorial that leads you through creating a Lazarus GUI for a Firebird database, see:


Lazarus and MySQL

  • Please see mysql for details on various access methods, which include:
  1. Built-in SQLDB support
  2. PDO
  3. Zeos

Lazarus and MSSQL/Sybase

You can connect to Microsoft SQL Server databases using

  1. The built-in SQLdb (recent Lazarus/FPC 2.6.1+): TMSSQLConnection (MS SQL) and TSybaseConnection (Sybase ASE)
    1. On Windows, you can download a recent 32 or 64 bit version of the FreeTDS library dblib.dll here: [1]
    2. Not necessary normally: 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).
    3. On Linux by default it uses the FreeTDS library libsybdb.so (for example, in Debian is in package libsybd5).
  2. Zeos component TZConnection (latest CVS, see links to Zeos elsewhere on this page)
    1. On Windows you can choose between native library ntwdblib.dll (protocol mssql) or FreeTDS libraries (protocol FreeTDS_MsSQL-nnnn) where nnnn is one of four variants depending on the server version. For Delphi (not Lazarus) there is also another Zeos protocol ado for MSSQL 2005 or later. Using protocols mssql or ado generates code not platform independient.
    2. On Linux the only way is with FreeTDS protocols and libraries (you should use libsybdb.so).
  3. ODBC (MSSQL and Sybase ASE) with SQLdb TODBCConnection (see also [2])
    1. On Windows it uses native ODBC Microsoft libraries (like sqlsrv32.dll for MSSQL 2000)
    2. On Linux it uses unixODBC + FreeTDS (packages unixodbc or iodbc, and tdsodbc). Since 2012 there is also a Microsoft SQL Server ODBC Driver 1.0 for Linux which is a binary product (no open source) and provides native connectivity, but was released only for 64 bits and only for RedHat.

Please see the FPC and Lazarus help files for details on using SQLDB with Sybase ASE and MSSQL Server.

Note-icon.png

Note: In any of the above forms, you can connect using TCP/IP, e.g. on port 1433.

If you use native Microsoft Libraries (over Windows) it could be possible to connect to remote server with Named Pipes. FreeTDS libraries do not support Named Pipes.

Note-icon.png

Note: Lazarus 1.0 was released with FPC 2.6.0 and does not include TMSSQLConnection or TSybaseConnection. If you want these, it might be easiest to download a snapshot of the latest Lazarus built with FPC 2.6.1, optinally installing that in a directory different from your Lazarus 1.0 version. You can use --primary-config-path to set up a separate environment for the Lazarus snapshot (see Multiple Lazarus)

Lazarus and ODBC

ODBC is a general database connection standard which is available on Linux, Windows and OSX. You will need an ODBC driver from your database vendor and set up an ODBC "data source" (also known as DSN). You can use the SQLDB components (TODBCConnection) to connect to an ODBC data soruce. See ODBCConn for more details and examples

Lazarus and Oracle

  • Acces methods include:
  1. Built-in SQLDB support
  2. Zeos

Lazarus and PostgreSQL

  • Please see postgres for details on various access methods, which include:
  1. Built-in SQLdb support. Use component TPQConnection from palette SQLdb
  2. Zeos. Use component TZConnection with protocol 'postgresql' from palette Zeos Access

Lazarus and SQLite

SQLite is an embedded database; the database code can be distributed as a library (.dll/.so/.dylib) with your application to make it self-contained (comparable to Firebird embedded). SQLite is quite popular due to its relative simplicity, speed, small size and cross-platform support.

Please see the SQLite page for details on various access methods, which include:

  1. Built-in SQLDb support
  2. Zeos
  3. SQLitePass
  4. TSQLite3Dataset

Lazarus and dBase

Tony Maro

You might also want to visit the TDbf Tutorial page

FPC includes a simple database component that is similar in function to the Delphi TTable component called "TDbf" (TDbf Website) that supports a subset of features for dBase files. It is not installed by default, so you will first need to install the Lazarus package from the "lazarus/components/tdbf" directory and rebuild your Lazarus IDE. It will then appear next to the TDatasource in your component palette (Data Access tab).

The TDbf component has an advantage over other database components in that it doesn't require any sort of runtime database engine. However it's not the best option for large database applications.

It's very easy to use. Simply drop a TDbf on your form, set the runtime path to the directory that your database files will be in, set the table name, and link it to your TDatasource component.

Real functionality requires a bit more effort, however. If a table doesn't already exist, you'll need to create it programmatically, unless there's a compatible table designer I'm not familiar with.

Note-icon.png

Note: Current version of OpenOffice/LibreOffice (3.x) contains OpenOffice Base, which can create dbf files in a somewhat user-friendly way.

Attempting to open a nonexistent table will generate an error. Tables can be created programmatically through the component after the runtime path and table name are set.

For instance, to create a table called "dvds" to store your DVD collection you would drop it on your form, set the runtime path, and set the table name to "dvds". The resulting file will be called "dvds.dbf".

In your code, insert the following:

Dbf1.FilePathFull := '/path/to/my/database';
Dbf1.TableName := 'dvds';
With Dbf1.FieldDefs do begin
  Add('Name', ftString, 80, True);
  Add('Description', ftMemo, 0, False);
  Add('Rating', ftString, 5, False);
end;
Dbf1.CreateTable;

When this code is run, your DVD collection table will be created. After that, all data aware components linked through the TDatasource to this component will allow easy access to the data.

Adding an index is a little different from your typical TTable. It must be done after the database is open. You use the same method also to rebuild the indices. For instance:

Dbf1.Exclusive := True;
Dbf1.Open;
Dbf1.AddIndex('dvdsname','Name',[ixPrimary, ixUnique, ixCaseInsensitive]);
Dbf1.AddIndex('rating.ndx', 'Rating', [ixCaseInsensitive]);
Dbf1.Close;

The first (primary) index will be a file called "dvdsname.mdx" and the second will be a file named "rating.ndx" so in a multiple table database you must be careful not to use the same file name again.

Please see TDbf Tutorial page for more details, but hopefully this will get those old Delphi programmers up and running with databases in Lazarus!

Searching and Displaying a data set

Simon Batty

In this example I wanted to search a database of books for all the titles an author has listed and then display the list in a memo box

Dbf1.FilePathFull := '/home/somelocatio/database_location/'; // path to the database directory
Dbf1.TableName := 'books.dbase';                             // database file (including extension)
DbF1.Open;
memo1.Clear;                                                 // clear the memo box
 
Dbf1.FilterOptions := [foCaseInsensitive];
Df1.Filter := 'AU=' + QuotedStr('anauthor');         // AU is the field name containing the authors
 
Dbf1.Filtered := true;       // This selects the filtered set
Dbf1.First;                  // moves the the first filtered data
while not dbf1.EOF do        // prints the titles that match the author to the memo box
begin
  memo1.Append(Dbf1.FieldByName('TI').AsString); // TI is the field name for titles
  dbf1.next;                                     // use .next here NOT .findnext!
end;
Dbf1.Close;

Note that you can use Ddf1.findfirst to get the first record in the filtered set, then use Dbf1.next to move though the data. I found that using Dbf1.Findnext just causes the program to hang.

This database was generated using TurboBD that came with the Kylix 1. I cannot get TurboBD tables to work with Lazarus, however you can download a command line tool from TurboDB's website that allows you to convert TurboDB table to other formats.

Using TSdfDataset and TFixedDataset

TSdfDataset and TFixedDataset are two simple datasets which offer a very simple textual storage format. These datasets are very convenient for small databases, because they are fully implemented as an object pascal unit, and thus require no external libraries, and because their textual format allows them to be easely edited with a text editor.

To start with this formats, a initial database file should be created. The format is very simple, so use a text editor to do this.

Bellow is a sample database for TSdfDataset. Note that the first line has the names of the fields and that we are using commas as separators:

ID,NAMEEN,NAMEPT,HEIGHT,WIDTH,PINS,DRAWINGCODE
1,resistor,resistor,1,1,1,LINE
2,capacitor,capacitor,1,1,1,LINE
3,transistor npn,transistor npn

And here is an example database for using with TFixedDataset. Each record occupies a fixed amount of space, and if the field is smaller then it, spaces should be used to fill the remaining size.

Name = 15 chars; Surname = 15 chars; Tell = 10 chars; e_mail = 20 chars;
Piet           Pompies                  piet@pompies.net

Using the datasets directly

Sometimes it is useful to create the dataset and work with it completely in code, and the following code will do exactly this. Note some peculiarities of TSdfDataset/TFixedDataset:

  • The lines in the database can have a maximum size of about 300. A fix is being researched.
  • It is necessary to add the field definitions. Some datasets are able to fill this information alone from the database file
  • One should set FirstLineAsSchema to true, to indicate that the first line includes the field names and positions
  • The Delimiter property holds the separator for the fields. It will not be possible to use this char in strings in the database. Similarly it will not be possible to have lineendings in the database because they mark the change between records. It's possible to overcome this by substituting the needed comma or line ending with another not often used char, like # for example. So that when showing the data on screen all # chars could be converted to line endings and the inverse when storing data back to the database. The ReplaceString routine is useful here.
uses sdfdata, db;

constructor TComponentsDatabase.Create;
var
  FDataset: TSdfDataset;
begin
  inherited Create;

  FDataset := TSdfDataset.Create(nil);
  FDataset.FileName := vConfigurations.ComponentsDBFile;

  // Not necessary with TSdfDataset
//  FDataset.TableName := STR_DB_COMPONENTS_TABLE;
//  FDataset.PrimaryKey := STR_DB_COMPONENTS_ID;

  // Adds field definitions
  FDataset.FieldDefs.Add('ID', ftString);
  FDataset.FieldDefs.Add('NAMEEN', ftString);
  FDataset.FieldDefs.Add('NAMEPT', ftString);
  FDataset.FieldDefs.Add('HEIGHT', ftString);
  FDataset.FieldDefs.Add('WIDTH', ftString);
  FDataset.FieldDefs.Add('PINS', ftString);
  FDataset.FieldDefs.Add('DRAWINGCODE', ftString);

  // Necessary for TSdfDataset
  FDataset.Delimiter := ',';
  FDataset.FirstLineAsSchema := True;

  FDataset.Active := True;

  // Sets the initial record
  CurrentRecNo := 1;
  FDataset.First;
end;

When using TSdfDataset directly be aware that RecNo, although it is implemented, does not work as a way to move through the dataset whether reading or writing records. The standard navigation routines like First, Next, Prior and Last work as expected, so you need to use them rather than RecNo. If you are used to using absolute record numbers to navigate around a database you can implement your own version of RecNo. Declare a global longint variable called CurrentRecNo which will hold the current RecNo value. Remember that this variable will have the same convention as RecNo, so the first record has number 1 (it is not zero-based). After activating the database initialize the database to the first record with TSdfDataset.First and set CurrentRecNo := 1

{@@
  Moves to the desired record using TDataset.Next and TDataset.Prior
  This avoids using TDataset.RecNo which doesn't navigate reliably in any dataset.

  @param AID Indicates the record number. The first record has number 1
}
procedure TComponentsDatabase.GoToRec(AID: Integer);
begin
  // We are before the desired record, move forward
  if CurrentRecNo < AID then
  begin
    while (not FDataset.EOF) and (CurrentRecNo < AID) do
    begin
      FDataset.Next;
      FDataset.CursorPosChanged;
      Inc(CurrentRecNo);
    end;
  end
  // We are after the desired record, move back
  else if CurrentRecNo > AID  then
  begin
    while (CurrentRecNo >= 1) and (CurrentRecNo > AID) do
    begin
      FDataset.Prior;
      FDataset.CursorPosChanged;
      Dec(CurrentRecNo);
    end;
  end;
end;

Using with data-aware controls

Lazarus and Advantage Database Server

See also

External links

  • Pascal Data Objects - a database API that worked for both FPC and Delphi and utilises native MySQL libraries for version 4.1 and 5.0 and Firebird SQL 1.5, and 2.0. It's inspired by PHP's PDO class.
  • Zeos+SQLite Tutorial - Good tutorial using screenshots and screencasts it explain how to use SQLite and Zeos, spanish (google translate does a good work in translating it to english)