Difference between revisions of "SQLdb Tutorial1"

From Free Pascal wiki
Jump to navigationJump to search
(→‎Editing data using the grid: Hiding first, autonumber column)
(→‎Introduction: It may seem long, but really, it isn't. Trust me.)
Line 5: Line 5:
 
This tutorial intends to show the usage of the [[SQLdb Package]] on the basis of practical examples. It is primarily targeted at newbies. If somebody is looking for basics about databases and SQL, he should read the corresponding books.  
 
This tutorial intends to show the usage of the [[SQLdb Package]] on the basis of practical examples. It is primarily targeted at newbies. If somebody is looking for basics about databases and SQL, he should read the corresponding books.  
 
For this tutorial I use Firebird with the example database employee.fdb. Other databases can also be used; some adjustments will need to be made which are mentioned in the text.
 
For this tutorial I use Firebird with the example database employee.fdb. Other databases can also be used; some adjustments will need to be made which are mentioned in the text.
 +
 +
While this tutorial may seem long, it mostly is just a lot of text that explains why you should type what you type. As you can see at the end, the amount of actual code you will need for a working application is not that great. More experienced developers will hopefully be able to glance through the instructions and quickly understand what's going on.
  
 
Thanks to [[User:Loesje|Joost]] and Michael. Without their help this tutorial probably never would have come about.
 
Thanks to [[User:Loesje|Joost]] and Michael. Without their help this tutorial probably never would have come about.

Revision as of 13:49, 3 September 2011

Template:SQLdb Tutorial

Introduction

This tutorial intends to show the usage of the SQLdb Package on the basis of practical examples. It is primarily targeted at newbies. If somebody is looking for basics about databases and SQL, he should read the corresponding books. For this tutorial I use Firebird with the example database employee.fdb. Other databases can also be used; some adjustments will need to be made which are mentioned in the text.

While this tutorial may seem long, it mostly is just a lot of text that explains why you should type what you type. As you can see at the end, the amount of actual code you will need for a working application is not that great. More experienced developers will hopefully be able to glance through the instructions and quickly understand what's going on.

Thanks to Joost and Michael. Without their help this tutorial probably never would have come about.

Requirements

If possible you should use a recent Lazarus version (with FPC 2.2.2 or newer) for this tutorial. If the SQLdb package isn't already installed, you should do it now (Package -> Install/Uninstall packages ... -> SQLDBLaz 1.0.1).

Furthermore you need Firebird (if possible version 2.0 or newer). The examples assume that the standard settings (e.g user name SYSDBA and password masterkey) were not changed, and that you have the employee sample database installed.

You can use another database (e.g. MySQL, PostgreSQL, Oracle, SQLite or another database using ODBC): you'd have to have the proper database /table structure (see below), and use the relevant TSQLConnector descendant (also indicated below). If your database has significant differences regarding transactions etc. please make a note in the relevant section.

Firebird database libraries on Windows

Finally, on Windows you will need to have the Firebird client DLLs present, optionally in your system directory, but preferably in your project directory (for design time support in the IDE) and in the output directory where the executable is (to run the compiled program). One easy way to get the client DLLs is: download Firebird Embedded 2.5 from [1] Extract these files to your application directory: <bash> fbembed.dll firebird.msg ib_util.dll icudt30.dll icuin30.dll icuuc30.dll Microsoft.VC80.CRT.manifest msvcp80.dll msvcr80.dll </bash> Rename fbembed.dll to fbclient.dll (the name for a regular, client-server Firebird client). The embedded Firebird DLL can also act as a regular Firebird client.

Finally, compile your project (even if it empty) once to create the output directory, and copy the dlls into that directory.

Firebird database libraries on other systems

On Linux/OSX, you will also need the Firebird client shared libraries. On Linux you can use your distribution's method of getting programs to get the Firebird client packages, e.g. on Debian: <bash> aptitude install libfbclient2 </bash>

No employee.fdb installed?

If you don't have the employee sample database installed or are using a different database, here is a minimal version of the table we'll be using: <sql> CREATE TABLE CUSTOMER (

 CUST_NO CUSTNO NOT NULL,
 CUSTOMER VARCHAR(25) NOT NULL,
 CITY VARCHAR(25),
 COUNTRY VARCHAR(15),
 CONSTRAINT INTEG_60 PRIMARY KEY (CUST_NO)

); </sql> Some data so you can at least show something: <sql> INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2001', 'Michael Design', 'San Diego', 'USA'); INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2002', 'VC Technologies', 'Dallas', 'USA'); INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2003', 'Klämpfl, Van Canneyt and Co.', 'Boston', 'USA'); INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2004', 'Felipe Bank', 'Manchester', 'England'); INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2005', 'Joost Systems, LTD.', 'Central Hong Kong', 'Hong Kong'); INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2006', 'Van der Voort International', 'Ottawa', 'Canada'); INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2007', 'Mrs. Mauvais', 'Pebble Beach', 'USA'); INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2008', 'Asinine Vacation Rentals', 'Lihue', 'USA'); INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2009', 'Fax', 'Turtle Island', 'Fiji'); INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2010', 'FPC Corporation', 'Tokyo', 'Japan'); INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2011', 'Dynamic Intelligence Corp', 'Zurich', 'Switzerland'); INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2012', '3D-Pad Corp.', 'Paris', 'France'); INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2013', 'Swen Export, Ltd.', 'Milan', 'Italy'); INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2014', 'Graeme Consulting', 'Brussels', 'Belgium'); INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2015', 'Klenin Inc.', 'Den Haag', 'Netherlands'); </sql>

Basic example

Project and components

First you should create a new Lazarus project.

To get access to our database we need one TIBConnection, one TSQLTransaction and one TSQLQuery component from the 'SQLdb' tab in the component palette. TIBConnection is an Interbase/Firebird specific connection component. If you are using a different database, substitute the proper component from the 'SQLDB' tab, e.g. a TSQLite3Connection for an SQLite database. Discussion of setting up any database access libraries is out of scope for this tutorial; see e.g. Databases for that.

The other two components, TSQLTransaction and TSQLQuery, can be used for all databases that are supported by SQLdb.

To display the data, we use a TDBGrid component, which can be found on the 'Data Controls' tab. To connect this component to the database components we need a TDatasource component from the 'Data Access' tab. To trigger the actions we use a TButton from the 'Standard' tab.

Now we have all components needed for the first example. You can enlarge the TDBGrid to have enough space to display all data.

Link the components

Next we need to connect our components. A very simple way is to use the object inspector, but you can also do this in your source code. Change the 'Transaction' property of IBConnection1 to 'SQLTransaction1'. This causes the 'Database' property of SQLTransaction1 to automatically change to 'IBConnection1'. In the next step you should change the 'Database' property of SQLQuery1 to 'IBConnection1'. Lazarus automatically adds the value for the 'Transaction' property. Now we change the 'Dataset' property of Datasource1 to 'SQLQuery1'. Finally we change the 'Datasource' property of DBGrid1 to 'Datasource1'.

Connecting to the database

How can we now show the data from our database on the screen? First we need to tell IBConnection1 where the employee.fdb database is located (usually in the .../examples/empbuild/ subdirectory of your Firebird installation). Again you have the choice, if you want to use the object inspector to assign the path or if you want to do it directly in your source code.

We choose to use the object inspector: set the IBConnection1 'HostName' property to the Firebird server name or IP adddress; use localhost if Firebird is running on your development machine. Change the 'DatabaseName' property of IBConnection1 to the path to the employee.fdb file on the database server(e.g. C:\Program Files\Firebird\Firebird_2_0\examples\empbuild\EMPLOYEE.FDB on a Windows machine).

Before the database server grants access to the data, it will check authorisation via username and password. A serious database application will ask the user for both values, when the application is started, and send these to the server when connecting.

However, for now, to simplify matters, we use the object inspector again to hard code these. Change the 'UserName' property to 'SYSDBA' and 'Password' to 'masterkey'.

Now check if all settings so far are correct: set the 'Connected' property to 'True'. If the database path isn't correct or if username or password are wrong, you will get an error message. If the connection was successful, you should cut it now (set it to 'False').

You should now have something like the following screenshot:

Form and components set up

Choosing what data to show

Although the connection was successful, no data was displayed. The reason is simple. We haven't told the database server which data to return: the employee.fdb database contains several tables, and we haven't told Firebird the table we want to see. If you don't know the structure of a database, you can use tools like FlameRobin, to display the contents. Lazarus also provides such a tool - the DataDesktop. You can find it in the /tools/lazdatadesktop/ subdirectory of Lazarus. Open the project lazdatadesktop.lpi and compile it (FPC 2.3.x is currently required) (of course you have saved our example project before).

The DataDesktop in action

Back to our example. We want to display all data from the table 'CUSTOMER'. The SQL instruction for that is:

select * from CUSTOMER

We need to assign this command to the 'SQL' property of SQLQuery1. In the source code of our project this would look like:

SQLQuery1.SQL.Text := 'select * from CUSTOMER';

The SQL instruction must be enclosed by single quotes. You also have the ability to assign the content of another component (e.g. Edit1.Text). This is not always a good idea; see Secure programming (a more advanced text) for details on SQL injection.

When the user clicks on the button on our form, data retrieval should start. Let's start coding this. Double click on Button1. Lazarus then creates the skeleton of the necessary procedure. In our source code we should find the following lines:

<pascal> procedure TForm1.Button1Click(Sender: TObject); begin

end; </pascal>

Between begin and end we must enter the instructions needed to display the data. The 'SQL' property of SQLQuery1 can only be changed, if SQLQuery1 is not active. That's why we close the component first: <pascal> SQLQuery1.Close; </pascal> Then we assign our SQL instruction to the 'SQL' property, overwriting any previous SQL commands: <pascal> SQLQuery1.SQL.Text := 'select * from CUSTOMER'; </pascal> Now we need to establish the connection to the database, activate the transaction and open the query: <pascal> IBConnection1.Connected := True; SQLTransaction1.Active := True; SQLQuery1.Open; </pascal> You can omit the first two instructions, because they are done automatically by the third instruction (but this will not happen in the contrary case, when you close the connection). If you compile the project at this point, you could already see the data from the 'CUSTOMER' table.

However, a serious application must take care that all open database connections are properly closed when not needed anymore. Otherwise the secondary effects would not be foreseeable. So, we use the OnClose event of our form (create it with a double click in the object inspector):

<pascal> procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction); begin

end; </pascal>

To close the connection we use the reverse order compared to our opening code: <pascal> SQLQuery1.Close; SQLTransaction1.Active := False; IBConnection1.Connected := False; </pascal>

Summary

Up to now we have learned, how to connect to a Firebird database using the SQLdb package and how to display the contents of a table on the screen.

If you followed the previous steps, then your code should look like: <delphi> unit Unit1;

{$mode objfpc}{$H+}

interface

uses

 Classes, SysUtils, IBConnection, sqldb, db, FileUtil, Forms, Controls,
 Graphics, Dialogs, DBGrids, StdCtrls;

type

 { TForm1 }
 TForm1 = class(TForm)
   Button1: TButton;
   Datasource1: TDatasource;
   DBGrid1: TDBGrid;
   IBConnection1: TIBConnection;
   SQLQuery1: TSQLQuery;
   SQLTransaction1: TSQLTransaction;
   procedure Button1Click(Sender: TObject);
   procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
 private
   { private declarations }
 public
   { public declarations }
 end; 

var

 Form1: TForm1; 

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject); begin

 SQLQuery1.Close;
 SQLQuery1.SQL.Clear;
 SQLQuery1.SQL.Text:= 'select * from CUSTOMER';
 IBConnection1.Connected:= True;
 SQLTransaction1.Active:= True;
 SQLQuery1.Open;

end;

procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction); begin

 SQLQuery1.Close;
 SQLTransaction1.Active:= False;
 IBConnection1.Connected:= False;

end;

end. </delphi>

Enhancement of the basic example

While the application works, we can add some refinements.

Dynamic database connection

Up to now, we used a fixed database server name, database location, username and password for simplicity. As mentioned, "real" applications often let users specify their own username and password.

Let's change the form so we can specify them: add two TEdits from the standard menu. Set their name properties to Username and Password. Set the Password's PasswordChar property to * (the asterisk) for some security. If you want to make it easier (and less secure, of course) to connect, you can set the UserName Text property to a valid database user, such as SYSDBA. You could even set the Password Text property to masterkey, easy for testing on developer machines if security doesn't matter... Cosmetically, adding some labels so people know what they're supposed to type is useful.

Also, to make it easier to connect to any 'employee.fdb' sample database on any Firebird/Interbase server, we add two textboxes for server name and database path. Add another two TEdits, and name them ServerName and DatabaseName. If you want, you can set the 'Text' property to default sensible values for your situation, e.g. localhost and C:\Program Files\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB Labels to explain what users need to enter would help here, too.

For clarity, we're going to remove the connection info from our designtime components: on the IBConnection1 component, remove all text from the UserName, Password, DatabaseName and HostName properties.

Now, finally, we need to tell our database connection component how to connect. This should normally only be necessary only at the beginning of an application run. In our case the existing 'Button1' code is a good way to set up the connection: Add code until you get: <delphi> procedure TForm1.Button1Click(Sender: TObject); begin

 SQLQuery1.Close;
 //Connection settings for Firebird/Interbase database
 //only needed when we have not yet connected:
 if IBConnection1.Connected = false then
 begin
   IBConnection1.HostName := ServerName.Text;
   IBConnection1.DatabaseName := DatabaseName.Text;
   IBConnection1.Username := UserName.Text;
   IBConnection1.Password := Password.Text;
   // Now we've set up our connection, visually show that
   // changes are not possibly any more
   ServerName.ReadOnly:=true;
   DatabaseName.ReadOnly:=true;
   UserName.ReadOnly:=true;
   Password.ReadOnly:=true;
 end;  
 SQLQuery1.SQL.Text:= 'select * from CUSTOMER';
 IBConnection1.Connected:= True;
 SQLTransaction1.Active:= True;
 SQLQuery1.Open;

end; </delphi> Now run and test if you can connect.

  • Other dabases: obviously, where it says IBConnection1, use your specific connection component. Also, you might need to set other properties on your components, so you might need more textboxes, or different contents.

Filtering data

Often, tables contain a huge amount of data that the user doesn't want to see (and that might take a long time to query from the database and travel over the network). Let's assume, that only the customers from the USA should be displayed. Therefore the SQL instruction in 'SQLQuery1' would look like: <sql> select * from CUSTOMER where COUNTRY = 'USA' </sql> ... which would translate to something like: <delphi> SQLQuery1.SQL.Text := 'select * from CUSTOMER where COUNTRY = 'USA; </delphi> There are two reasons why we will not use this instruction for our example application:

First there is a problem with the usage of the single quote. The compiler would interpret the quote before USA as a closing quote (the first quote is before the select from...) and so the SQL instruction would become invalid. Solution: double the inside quotes: <delphi>SQLQuery1.SQL.Text := 'select * from CUSTOMER where COUNTRY = USA';</delphi>

The second, more important reason is the fact, that we probably don't know what constraints the user will want to filter on. We don't want to limit the flexibility of the user.

To get this flexibility, first we change our SQL query statement and replace 'USA' by a placeholder (a parameter in SQL speak): change the Button1click procedure and replace <delphi> SQLQuery1.SQL.Text := 'select * from CUSTOMER'; </delphi> with <delphi>

 SQLQuery1.SQL.Text:= 'select * from CUSTOMER where COUNTRY = :COUNTRY';

</delphi>

The SQL parameter is marked by the leading colon. To allow the user to enter a value for the filter, we place a TEdit component ('Standard' tab in the component palette) on our form. Delete the value of its 'Text' property. We can now fill the SQL parameter with the text, which was entered in TEdit by using the 'Params' property of TSQLQuery. Add this below the previous statement: <delphi> SQLQuery1.Params.ParamByName('COUNTRY').AsString := Edit1.Text; </delphi> The parameter can be specified by its position or name. Using the name should improve the readability of the source code, and obviously helps if you insert more parameters in the middle of existing parameters.

Overall the procedure should now look like: <delphi> procedure TForm1.Button1Click(Sender: TObject); begin

 SQLQuery1.Close;
 //Connection settings for Firebird/Interbase database
 //only needed when we have not yet connected:
 if IBConnection1.Connected = false then
 begin
   IBConnection1.HostName := ServerName.Text;
   IBConnection1.DatabaseName := DatabaseName.Text;
   IBConnection1.Username := UserName.Text;
   IBConnection1.Password := Password.Text;
   // Now we've set up our connection, visually show that
   // changes are not possibly any more
   ServerName.ReadOnly:=true;
   DatabaseName.ReadOnly:=true;
   UserName.ReadOnly:=true;
   Password.ReadOnly:=true;
 end; 
 SQLQuery1.SQL.Text:= 'select * from CUSTOMER where COUNTRY = :COUNTRY';
 SQLQuery1.Params.ParamByName('COUNTRY').AsString := Edit1.Text;
 IBConnection1.Connected:= True;
 SQLTransaction1.Active:= True;
 SQLQuery1.Open;

end; </delphi> Now you can play around a bit with filtering using Edit1. If you enter a country that's not present in the database, an empty grid is shown.

Error handling

The application should run, but sometimes, serious problems can occur. Because client and server are usually physically separated, it's often not clear at first sight why a problem occurred. Was the server shut down or has somebody unplugged a network connection? Databases, even embedded databases can crash (e.g. when the disk is full, or just due to a bug), leaving the application hanging.

Access to a database should therefore always be integrated in a try ... except and/or try ... finally loop. Only that way is it ensured, that database errors can be handled and the user isn't left out in the rain. A rudimental routine for our example application could look as follows: <delphi> begin

 try
   SQLQuery1.Close;
   ...
   SQLQuery1.Open;
 except
   //EDatabaseError is a general error, but we're dealing with Firebird/Interbase, so:
   on E: EIBDatabaseError do
   begin
     MessageDlg('Error','A database error has occurred. Technical error message: ' + E.Message,mtError,[mbOK],0);
     Edit1.Text:=;
   end;
 end;

end; </delphi>

  • Other database users: if you don't use Firebird/Interbase, you can either use the more generic EDatabaseError, or your own specialized databaseerror, if you need more details.

Editing data using the grid

Editing

Up to now, if you tried to edit data in the grid, the changes would not be saved. This is because the SQLQuery1 is not instructed to send the changes to the database transaction at the right moment. We need to fix this, and then commit the transaction in the database, so all changes get written. For this, you would use code like this: <delphi> SQLQuery1.ApplyUpdates; //Pass user-generated changes back to database... SQLTransaction1.Commit; //... and commit them using the transaction. //SQLTransaction1.Active now is false </delphi> We want to make sure any edits (inserts, updates, deletes) are written to the database:

  • when the users changes the filtering criteria and presses the button to query the database
  • when the form is closed

It makes sense to make a separate procedure for this that is called in those two instances. Go to the code, and add an empty line here: <delphi>

 TForm1 = class(TForm)
   Button1: TButton;
   Datasource1: TDatasource;
   DBGrid1: TDBGrid;
   Edit1: TEdit;
   IBConnection1: TIBConnection;
   SQLQuery1: TSQLQuery;
   SQLTransaction1: TSQLTransaction;
          • insert the empty line here****
   procedure Button1click(Sender: TObject);
   procedure Formclose(Sender: TObject; var Closeaction: Tcloseaction);
 private

</delphi> then type <delphi>

   procedure SaveChanges;

</delphi> press shift-ctrl-c (default combination) to let code completion automatically create the corresponding procedure body.

We need to add error handling and check that the transaction is active - remember, this code also gets called when pressing the button the first time, when the transaction is not active yet. We get: <delphi> procedure Tform1.Savechanges; // Saves edits done by user, if any. begin

 try
   if SQLTransaction1.Active=true then
   // Only if we are within a started transaction
   // otherwise you get "Operation cannot be performed on an inactive dataset"
   begin
     SQLQuery1.ApplyUpdates; //Pass user-generated changes back to database...
     SQLTransaction1.Commit; //... and commit them using the transaction.
     //SQLTransaction1.Active now is false
   end;
 except
 on E: EIBDatabaseError do
   begin
     MessageDlg('Error', 'A database error has occurred. Technical error message: ' +
       E.Message, mtError, [mbOK], 0);
     Edit1.Text := ;
   end;
 end;

end; </delphi>

Now we need to call this procedure at the appropriate moments: <delphi> procedure Tform1.Button1click(Sender: TObject); begin

 SaveChanges; //Saves changes and commits transaction
 try
   SQLQuery1.Close;

.... </delphi> and <delphi> procedure Tform1.Formclose(Sender: TObject; var Closeaction: Tcloseaction); begin

 SaveChanges; //Saves changes and commits transaction
 SQLQuery1.Close;

.... </delphi>

Now test and see if edits made in the dbgrid are saved to the database.

Hiding primary key column

Often, you don't want your users to see autonumber/generated primary keys as they are only meant to maintain referential integrity. If users do see them, they might get upset that the numbers change, there are gaps in the numbers, etc.

In our example, CUST_NO is the primary key, with content auto-generated by Firebird using triggers and a sequence/generator. This means that you can insert a new record without specifying the CUST_NO; Firebird will create one automatically.

We could simply change our SQLQuery1.SQL.Text property to not include CUST_NO, but this would lead to problems when editing data - a primary key is handy in those circumstances.

Therefore, let's use a trick to query for all columns/fields in the table, but keep from showing the first field, CUST_NO, in the grid: in the Button1Click procedure, add code so it looks like: <delphi> procedure Tform1.Button1click(Sender: TObject); begin ...

   SQLQuery1.Open;
   // Hide the primary key column which is the first column in our queries.
   // We can only do this once the DBGrid has created the columns
   DBGrid1.Columns[0].Visible:=false;

</delphi> Recompile, and check to see if the primary key column is really hidden.

  • Other databases: a lot of other databases use an 'autonumber' or 'autoinc' type of field to provide auto-generated field content. Try changing your table definition and see if it works.

to do: mention error SQLQuery1: Field CUST_NO is required, but not supplied. when inserting a new record without a primary key, find out what the fix is and describe it here.

Summary

If you followed along up to now, you can retrieve data from the database, filter it, and edit data in the grid. Your code should look something like this: <delphi> unit sqldbtutorial1unit;

{$mode objfpc}{$H+}

interface

uses

 Classes, SysUtils, IBConnection, sqldb, DB, FileUtil, Forms, Controls,
 Graphics, Dialogs, DBGrids, StdCtrls;

type

 { TForm1 }
 TForm1 = class(TForm)
   Button1: TButton;
   DatabaseName: TEdit;
   Datasource1: TDatasource;
   DBGrid1: TDBGrid;
   Edit1: TEdit;
   Password: TEdit;
   UserName: TEdit;
   ServerName: TEdit;
   IBConnection1: TIBConnection;
   Label1: TLabel;
   SQLQuery1: TSQLQuery;
   SQLTransaction1: TSQLTransaction;
   procedure SaveChanges;
   procedure Button1click(Sender: TObject);
   procedure Formclose(Sender: TObject; var Closeaction: Tcloseaction);
 private
   { private declarations }
 public
   { public declarations }
 end;

var

 Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure Tform1.Savechanges; // Saves edits done by user, if any. begin

 try
   if SQLTransaction1.Active=true then
   // Only if we are within a started transaction
   // otherwise you get "Operation cannot be performed on an inactive dataset"
   begin
     SQLQuery1.ApplyUpdates; //Pass user-generated changes back to database...
     SQLTransaction1.Commit; //... and commit them using the transaction.
     //SQLTransaction1.Active now is false
   end;
 except
 on E: EIBDatabaseError do
   begin
     MessageDlg('Error', 'A database error has occurred. Technical error message: ' +
       E.Message, mtError, [mbOK], 0);
     Edit1.Text := ;
   end;
 end;

end;


procedure Tform1.Button1click(Sender: TObject); begin

 SaveChanges; //Saves changes and commits transaction
 try
   SQLQuery1.Close;
   //Connection settings for Firebird/Interbase database
   //only needed when we have not yet connected:
   if IBConnection1.Connected = false then
   begin
     IBConnection1.HostName := ServerName.Text;
     IBConnection1.DatabaseName := DatabaseName.Text;
     IBConnection1.Username := UserName.Text;
     IBConnection1.Password := Password.Text;
     // Now we've set up our connection, visually show that
     // changes are not possibly any more
     ServerName.ReadOnly:=true;
     DatabaseName.ReadOnly:=true;
     UserName.ReadOnly:=true;
     Password.ReadOnly:=true;
   end;
   SQLQuery1.SQL.Text := 'select * from CUSTOMER where COUNTRY = :COUNTRY';
   SQLQuery1.Params.ParamByName('COUNTRY').AsString := Edit1.Text;
   IBConnection1.Connected := True;
   SQLTransaction1.Active := True; //Starts a new transaction
   SQLQuery1.Open;
   // Hide the primary key column which is the first column in our queries.
   // We can only do this once the DBGrid has created the columns
   DBGrid1.Columns[0].Visible:=false;
 except
   //EDatabaseError is a general error, but we're dealing with Firebird/Interbase, so:
   on E: EIBDatabaseError do
   begin
     MessageDlg('Error', 'A database error has occurred. Technical error message: ' +
       E.Message, mtError, [mbOK], 0);
     Edit1.Text := ;
   end;
 end;

end;

procedure Tform1.Formclose(Sender: TObject; var Closeaction: Tcloseaction); begin

 SaveChanges; //Saves changes and commits transaction
 SQLQuery1.Close;
 SQLTransaction1.Active := False;
 IBConnection1.Connected := False;

end;

end. </delphi>

See also