Lazarus Database Tutorial/nl

From Free Pascal wiki
Revision as of 17:39, 23 March 2005 by Matthijs (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Overview

This tutorial is about getting Lazarus to work with a variety of existing databases.

Please expand on this section.

Lazarus and MySQL

1 - Get MySQL working in Linux or Windows

Follow the instructions in the MySQL User Manual. Make sure that the mysqld daemon runs reliably, and that all potential users (including root, mysql, yourself and anybody else that may need it) have as many privileges as they need, from as many hosts as may be needed (including 'localhost', the local host's name, any other hosts on your network) as far as is consistent with security. It is preferable that all users including root have passwords. Test the action of the database system using the examples given in the manual, and check that all users really do have reliable access.

2 - Get MySQL working for FPC in text mode

There is a directory with an example program in $(FreePascal_directory)/packages/base/mysql/ and this directory also contains the units mysql.pp, mysql_com.pp and mysql_version.pp. Before running the test script, you need to create a database called testdb: do this by logging into the mysql monitor (as root with full privileges) and issuing the following SQL statement

CREATE DATABASE testdb;

then make sure that all relevant users have appropriate access privileges to it

GRANT ALL ON testdb TO johnny-user IDENTIFIED BY 'johnnyspassword'; 

There is a script called mkdb which you should now try to run:

sh ./mkdb

This will probably fail, as the system will not allow an anonymous user to access the database. So change the script using an editor so that the line invoking mysql reads:

mysql -u root -p  ${1-testdb} << EOF >/dev/null

and try running it again, entering your password when prompted. With luck you might have managed to create the test database: test it (while logged in to the mysql monitor) by issuing the mysql statement

select * from FPdev;

You should see a table listing the ID, username and email address of some of the FPC developers.

Now try to run the test program testdb.pp (this may need to be compiled, and will almost certainly fail on the first attempt!!).

I found that the program could not connect to mysql for several reasons:

  • My system (SuSE Linux v9.0) installs mysql v4.0.15, not the version3 for which the package was designed.
  • The program needs to have user names and passwords to get access to the database.
  • The compiler needs to know where to find the mysql libraries (IF YOU HAVEN'T INSTALLED THE MYSQL DEVELOPMENT LIBRARIES, DO SO NOW!)

I created a copy of testdb.pp called trydb.pp, rather than editing the original - this means that the original files still get fixed in subsequent CVS updates. I also copied the files found in the subdirectory mysql/ver40/ into the main mysql/ subdirectory, renaming them mysql_v4.pp, mysql_com_v4.pp and mysql_version_v4.pp, being sure to rename the units within each file correspondingly. I changed the uses statement in trydb.pp to

uses mysql_v4

and the statement in mysql_v4.pp to


uses mysql_com_v4

I added a line to /etc/fpc.cfg to point to my libraries:

-Fl/lib;/usr/lib

and in the /usr/lib directory I had to find the real name of the mysqlclient library. In my case I had to issue the shell command:

ln -s libmysqlclient.so.12.0.0 lmysqlclient

to make a symbolic link allowing FPC to find the library. For good measure I also created the link

ln -s libmysqlclient.so.12.0.0 mysqlclient

and placed similar links in various other directories: not strictly necessary, but just in case ...! Some users might need to add the following link:

ln -s libmysqlclient.so.12.0.0 libmysqlclient.so

I modified trydb.pp to include user details, initially by adding host, user and password as constants:

const
  host : Pchar= 'localhost';
  user : Pchar= 'myusername';
  passwd: Pchar = 'mypassword';

I also found that I couldn't connect to mysql using the mysql_connect() call, but had to use mysql_real_connect() which has many more parameters. To complicate things further, the number of parameters seems to have changed between version3 (where there are seven) and version4 (where there are eight). Before using mysql_real_connect I had to use mysql_init() which is not found in the original mysql.pp but is found in mysql_v4.pp.

So the code for connection to the database is now:

{ a few extra variables}
var
  alloc : PMYSQL;
 
{main program fragment}
 
begin
 if paramcount=1 then
   begin
   Dummy:=Paramstr(1)+#0;
   DataBase:=@Dummy[1];
   end;
 
Writeln ('Allocating Space...');
 alloc := mysql_init(PMYSQL(@qmysql));
 Write ('Connecting to MySQL...');
 sock :=  mysql_real_connect(alloc, host, user, passwd, database, 0, nil, 0);
 if sock=Nil then
   begin
   Writeln (stderr,'Couldnt connect to MySQL.');
   Writeln (stderr, 'Error was: ', mysql_error(@qmysql));
   halt(1);
   end;
 Writeln ('Done.');
 Writeln ('Connection data:');
{$ifdef Unix}
 writeln ('Mysql_port      : ',mysql_port);
 writeln ('Mysql_unix_port : ',mysql_unix_port);
{$endif}
 writeln ('Host info       : ',mysql_get_host_info(sock));
 writeln ('Server info     : ',mysql_stat(sock));
 writeln ('Client info     : ',mysql_get_client_info);
 
 Writeln ('Selecting Database ',DataBase,'...');
 if mysql_select_db(sock,DataBase) < 0 then
   begin
   Writeln (stderr,'Couldnt select database ',Database);
   Writeln (stderr,mysql_error(sock));
   halt (1);
   end;
{... as original contents of testdb.pp}


Now - ready to start compiling trydb.pp?

 fpc trydb

success! Now run it:

 ./trydb

whoopee! I got the listing of the FPC developers!

A few extra refinements: make the entry of user details and the mysql commands interactive, using variables rather than constants, and allow several SQL commands to be entered, until we issue the quit command: see the full program listing, where user details are entered from the console, and the program goes into a loop where SQL commands are entered from the console (without the terminal semicolon) and the responses are printed out, until 'quit' is entered from the keyboard.

See Sample Console Listing.

3 - Connecting to MySQL from a Lazarus Application

This tutorial shows how to connect Lazarus to the MySQL database, and execute simple queries, using only the basic Lazarus components; it uses no Data Aware components, but illustrates the principles of interfacing with the database.

Create a new project in Lazarus:

Project -> New Project -> Application

A new automatically generated Form will appear.

Enlarge the form to fill about half of the screen, then re-name the form and its caption to 'TryMySQL'.

From the Standard Component tab place three Edit Boxes on the upper left side of the Form, and immediately above each box place a label. Change the names and captions to 'Host' (and HostLLabel,HostEdit), 'UserName' (and UserLabel, UserEdit) and 'Password' (with PasswdLabel and PasswdEdit). Alternatively you could use LabelledEdit components from the Additional tab.

Select the Passwd Edit box and find the PasswordChar property: change this to * or some other character, so that when you type in a password the characters do not appear on your screen but are echoed by a series of *s. Make sure that the Text property of each edit box is blank.

Now place another Edit box and label at the top of the right side of your form. Change the label to 'Enter SQL Command' and name it CommandEdit.

Place three Buttons on the form: two on the left under the Edit boxes, and one on the right under the command box.

Label the buttons on the left 'Connect to Database' (ConnectButton)and 'Exit' (ExitButton) and the one on the right 'Send Query' (QueryButton).

Place a large Memo Box labelled and named 'Results' (ResultMemo) on the lower right, to fill most of the available space. Find its ScrollBars property and select ssAutoBoth so that scroll bars appear automatically if text fills the space. Make the WordWrap property True.

Place a Status Bar (from the Common Controls tab) at the bottom of the Form, and make its SimpleText property 'TryMySQL'.

A screenshot of the Form can be seen here: Mysql Example Screenshot

Now we need to write some event handlers.

The three Edit boxes on the left are for entry of hostname, username and password. When these have been entered satisfactorily, the Connect Button is clicked. The OnCLick event handler for this button is based on part of the text-mode FPC program above.

The responses from the database cannot now be written using the Pascal write or writeln statements: rather, the replies have to be converted into strings and displayed in the Memo box. Whereas the Pascal write and writeln statements are capable of performing a lot of type conversion 'on the fly', the use of a memo box for text output necessitates the explicit conversion of data types to the correct form of string, so Pchar variables have to be converted to strings using StrPas, and integers have to be converted with IntToStr.

Strings are displayed in the Memo box using

procedure ShowString (S : string);
(* display a string in a Memo box *)
begin
       trymysqlForm1.ResultsMemo.Lines.Add (S)
end;

The ConnectButton event handler thus becomes:

procedure TtrymysqlForm1.ConnectButtonClick(Sender: TObject);
(* Connect to MySQL using user data from Text entry boxes on Main Form *)
var strg: string;
 
begin
 
 dummy1 :=  trymysqlForm1.HostEdit.text+#0;
 host := @dummy1[1];
 dummy2 := trymysqlForm1.UserEdit.text+#0;
 user := @dummy2[1] ;
 dummy3 := trymysqlForm1.PasswdEdit.text+#0;
 passwd := @dummy3[1] ;
 alloc := mysql_init(PMYSQL(@qmysql));
 sock :=  mysql_real_connect(alloc, host, user, passwd, database, 0, nil, 0);
 if sock=Nil then
   begin
     strg :='Couldnt connect to MySQL.'; showstring (strg);
     Strg :='Error was: '+ StrPas(mysql_error(@qmysql)); showstring (strg);
  end
   else
   begin
     trymysqlForm1.statusBar1.simpletext := 'Connected to MySQL';
     strg := 'Now choosing database : ' + database; showstring (strg);
{$ifdef Unix}
     strg :='Mysql_port      : '+ IntToStr(mysql_port); showstring (strg);
     strg :='Mysql_unix_port : ' + StrPas(mysql_unix_port); showstring (strg);
{$endif}
     Strg :='Host info       : ' + StrPas(mysql_get_host_info(sock));
     showstring (strg);
     Strg :='Server info     : ' + StrPas(mysql_stat(sock)); showstring (strg);
     Strg :='Client info     : ' + Strpas(mysql_get_client_info);  showstring (strg);
 
     trymysqlForm1.statusbar1.simpletext := 'Selecting Database ' + DataBase +'...';
 if mysql_select_db(sock,DataBase) < 0 then
 begin
   strg :='Couldnt select database '+ Database; ShowString (strg);
   Strg := mysql_error(sock); ShowString (strg);
 end
 end;
end;


The Text Box on the right allows entry of a SQL statement, without a terminal semicolon; when you are satisfied with its content or syntax, the SendQuery button is pressed, and the query is processed, with results being written in the ResultsMemo box.

The SendQuery event handler is again based on the FPC text-mode version, except that once again explicit type-conversion has to be done before strings are displayed in the box.

A difference from the text-mode FPC program is that if an error condition is detected, the program does not halt and MySQL is not closed; instead, control is returned to the main form and an opportunity is given to correct the entry before the command is re-submitted. The application finally exits (with closure of MySQL) when the Exit Button is clicked.

The code for SendQuery follows:

procedure TtrymysqlForm1.QueryButtonClick(Sender: TObject);
var
 dumquery, strg: string;
begin
     dumquery := TrymysqlForm1.CommandEdit.text;
     dumquery := dumquery+#0;
     query := @dumquery[1];
     trymysqlForm1.statusbar1.simpletext := 'Executing query : '+ dumQuery +'...';
     strg := 'Executing query : ' + dumQuery; showstring (strg);
     if (mysql_query(sock,Query) < 0) then
     begin
       Strg :='Query failed '+ StrPas(mysql_error(sock)); showstring (strg);
     end
     else
     begin
       recbuf := mysql_store_result(sock);
       if RecBuf=Nil then
       begin
         Strg :='Query returned nil result.'; showstring (strg);
       end
       else
       begin
         strg :='Number of records returned  : ' + IntToStr(mysql_num_rows (recbuf));
         Showstring (strg);
         Strg :='Number of fields per record : ' + IntToStr(mysql_num_fields(recbuf));
         showstring (strg);
         rowbuf := mysql_fetch_row(recbuf);
         while (rowbuf <>nil) do
         begin
              Strg :='(Id: '+ rowbuf[0]+', Name: ' + rowbuf[1]+ ', Email : ' +
               rowbuf[2] +')';
              showstring (strg);
              rowbuf := mysql_fetch_row(recbuf);
         end;
       end;
     end;
end;


Save your Project, and press Run -> Run

Download MYSQL Source Code

A full listing of the program is available here Sample Source Code

Lazarus and Postgresql

Please write me!

Lazarus and SQLite

by Luiz Américo

Introduction

There are two TDataset descendants that access sqlite2.x databases: SQLiteDataset unit/TSqlite class which is outdated and not comes with fpc anymore and sqliteds unit/TSqliteDataset class. The second is newer and has some advantages over the former: its faster, allows inserts, updates and deletes and can apply the updates to the database automatically. So the instructions found here are valid only for the TSqliteDataset class which is found in unit sqliteds.pas. In fact the sqlitedataset.pas file that comes in fpc198 can't be compiled with fpc198.

Requirements

  • fpc 1.9.8
  • sqliteds.pas (get from fpc/fcl/db/sqlite at CVS) [Optional]
  • sqlite runtime library 2.8.15 or above (get from www.sqlite.org)

Before initiating a lazarus projects, ensure that:

  • the sqlite library is on the system PATH
  • under linux, put cmem as the first unit in uses clause of the main program

How To Use (Basic Usage)

The sqlite package that comes with lazarus relies on the outdated sqlitedataset unit. A package that uses the new unit can be found here

At design time set the following properties:

  • FileName: path of the sqlite file [required]
  • TableName: name of the table used in the sql statement [required]
  • Sql: a SQL select statement [optional]

Creating a Table (Dataset)

For now it's not possible to create the dataset at design time, so its necessary to create the dataset at run time if it not already exists, generally at OnCreate event of the main form.

The example bellow shows how it can be done (dsTest is a TSqliteDataSet instance):

 // Create a Table with all field types supported by TSqliteDataset
 with dsTest do
 begin
   if not TableExists then
   begin
     with FieldDefs do
     begin
       Clear;
       Add('Integer',ftInteger);
       Add('AutoInc',ftAutoInc);
       Add('String',ftString);
       Add('Memo',ftMemo);
       Add('Bool',ftBoolean); 
       Add('Float',ftFloat);
       Add('Word',ftWord);
       Add('DateTime',ftDateTime);
       Add('Date',ftDate);
       Add('Time',ftTime);
     end;
     CreateTable; 
   end;
 end;
 

Retrieving the data

After creating the table, open the dataset with Open method. If the SQL property was not set then all records from all fields will be retrieved, the same as

 SQL:='Select * from TABLENAME'; 

But it's possible to retrieve a combination of the fields setting SQL as bellow:

 SQL:='Select Float from TABLENAME';
 SQL:='Select String,Date,Word from TABLENAME';

Remenber that the number of fields and the order of the fields that will be available after opening the dataset are determined by the select statement. In the previous examples the dataset would have 10, 1 and 3 fields respectively.

Applying changes to the underlying datafile

For now, it's necessary that the dataset have an AutoInc field or that FieldIndexName stores the name of a field that acts like a Primary Key.

For example, if the Field 'Integer' stores values that are Unique and not Null, then setting

 FieldIndexName:='Integer';

will allow the data to be stored in the underlying database(file)

After this just call ApplyUpdates method

Remarks

  • Although tested with 10000 records and worked fine, TSqliteDataset keeps all the data in memory, so remenber to retrieve only the necessary data (principally with Memo Fields).
  • The same datafile(filename property) can host several tables/datasets
  • Several datasets (different combinations of fields) can be created using the same table simultaneously
  • It's possible to filter the data using WHERE statements in the sql, closing and reopening the dataset (or calling RefetchData method). But in this case, the order and number of fields must remains the same
  • It's also possible to use complexes SQL statements using aliases or joins in multiples tables (remenber that they must reside in the same datafile), but in this case ApplyUpdates won't work. If someone wants to use complexes queries and to apply the updates to the datafile, mail me and i will give some hints how to do that
  • Setting filename to a sqlite2.x datafile not created by TSqliteDataset and opening it is allowed but some fields won't have the correct field type detected. These will be treated as string fields.

A generic examples can be found at fpc/fcl/sqlite CVS dir

Luiz Américo pascalive(at)bol(dot)com(dot)br

Lazarus and MSSQL

Please write me!


Lazarus and dBase

Tony Maro

You might also want to visit the beginnings of 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 very basic 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.

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, put, 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.

Attempting to open a non-existant 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.

Note: the index example only works with the CVS edition of FPC

Adding indexes is a little different from your typical TTable. It must be done after the database is open. It's also the same method you use to rebuild the indexes. 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.

I will try to add a more detailed example at a later date, but hopefully this will get those old Delphi programmers up and running with databases in Lazarus!

Lazarus and dBase -- How to update your copy

[YoyongHernan|]

How to use TDbf in lazarus (Linux)

1. Update your copy of fcl. FPC verion 1.9.4 is known to work prorperly with this package.

Steps to update: a. Assuming that the cvs files from FPC is located in repo. Then

  cd ~/repo

b. Login to cvs

  cvs -d :pserver:cvs@cvs.freepascal.org:/FPC/CVS login
  password is cvs

c. Now the actual update

  cvs -d :pserver:cvs@cvs.freepascal.org/FPC/CVS -z 3 update fpc/fcl

The above command will udpate your fcl source copy.

d. Delete some files in your fcl directory.

 su
 cd /usr/local/lib/fpc/1.9.4/units/linux/fcl
 rm db*.*

Note: Change to the directory specified in "cd" accordingly. Note: To check the exact location of the units. Assuming of course that you have a working FPC configuration. Type

 fpc -vt bogus

And look for something that reads Using unit path: /usr/local/lib/fpc/1.9.4/* Note: In almost all systems, you need to be the root user to do this.

e. Compile the newest Tdbf sources. Goto to your FPC FCL directory

 cd ~/repo/fpc/fcl/db
 make && make install

The above command will try to compile your sources in the db directory and install the units into /usr/local/bin/fpc/1.9.4/units/linux/fcl Note: Must be root for make install to succeed.

f. Check if the units are there. Now the tricky part. As of this time, FPC compiler will search for your units based on two steps. First it will check if the unit can be found by using the uppercase of your units, i.e.,DB.PPU. If the compiler cannot find this it will try db.ppu. As you can see the files just compiled shows that the units starts with capital D??????. So this will fail your system to find the units for the tdbf. To remedy this just change all Db*.* to lowercase.

If you are lazy like me, you can use the perl script below to change the case to lowercase.

 #!/usr/bin/perl
 use Cwd;
 use File::Copy;

 #this script will rename the files to lower case. useful for FPC programming

 printf "This renames all the files to lower case found in the current directory\n";

 #create a directory containing the lowercase files
 if (length(`ls -d lcase`) > 0 ){
 	print "directory exists\n";
 	print "Deleting files located in lcase directory\n";
 	`rm -d -r lcase`;
 }
 `mkdir lcase`;


 #get the current directory
 $dir = cwd();
 $source = $dir;
 #set to the source directory
 $source .= "/source";

 #open the directory
 opendir(DIR, $source);
 #read the files/direcotry
 @dirlist = readdir(DIR);

 #process the files now
 chdir($source);
 foreach $myfile(@dirlist) {
 	print "copying file $myfile\n";
 	$newfile = $dir;
 	$newfile .= "/lcase/";
 	$newfile .= lc($myfile);
 	copy($myfile,$newfile);
 }


 #close the opened directory
 closedir(DIR);

I. Save the script in /home/yourdirectory/temp/renameme.pl II. Change the mode of the file

   chmod 755 renameme.pl

III. Move all the Db*.* from /usr/local/lib/fpc/1.9.4/units/linux/fcl to /home/yourdirectory/temp/source. IV. Run renameme.pl

   ./renameme.pl

V. Copy the files in /home/yourdirectory/temp/lcase to /usr/local/lib/fpc/1.9.4/units/linux/fcl

2. Run Lazarus.

3. Install the TDbf package Compoenents->Open Package File->Browse to lazarus_directory/components/tdbf/dbflaz.lpk

4. Click on compile.

5. Click on install. Upon lazarus restart the new component can be found in Data Access Page.

Translations of This Page

Spanish by Eduardo Lopez (thanks! :)

Related Links

Creating Databases Applications with Lazarus using the DB-unit

Contributors and Changes

This page has been converted from the epikwiki version.