Databases in Lazarus
This page aims to provide a general overview of using databases in Lazaraus, and to provide a home from which other database pages can be linked
Database use in Lazarus (or FreePascal) is fundamentally based on the TDataset class. This represents a table or query to your application. However, like many other such fundamental classes, you don't use the TDataset class it self, you use a descendant of it. There are many of these. They provide access to different kinds of databases, such as local dbase or text files, or back-end databases such as PostgreSQL, Firebird, MySQl and so forth. Some dataset descendants link directly to database tables, while others use additional components or libraries to perform the link.
The Databases page documents such descendants.
Dataset descendants, being non-visual components are (usually) part of the Free Component Library (FCL) rather than the Lazarus Component Library (LCL).
Datasets can be used both programmatically and with visual controls. A typical Lazarus database application will often use both methods. In either case, the first step is to create the TDataset descendant, initialise it to connect to the table or query you want, and open it. This can be done either in code at run time or by putting a component on your form and setting it's properties at design time. The details of this vary considerably with different TDataset descendants, so see the various guides under Databases for what has to be done for your database.
When the dataset is opened, a set of field components are created, one for each field or column of the table or query you opened. Each field component is a descendant of TField, appropriate to the particular data type of the field, eg, TStringField.
Using datasets from code
Programmatic access will be explained in more detail in Using Dataset and Field components, but as a very simple overview:
- Use the TDataset descendant to open the table or query, filter the rows you want to see, and to move from row to row.
- Use the TField descendants to:
- Access general information about fields
- Access the specific data values for the current row. (use the As... properties, such as AsString, AsInteger, etc.)
- Access the fields of a TDataset descendant by using either:
- The fields property, eg Fields is the first field,
- The FieldByName method, eg FieldByName('AGE') returns the field associated with the database field called 'AGE'
Using the visual (data-aware) controls
To use databases in a simple, "RAD" style Lazarus application, you usually configure the dataset descendant at design time and the use the data-aware controls. To do this:
- Add the dataset descendant for the database of your choice, together with any supporting components, to your form, and open it (Set the 'Active' property to true)
- Add a TDatasource component (from the "Data Access" tab) to the form, and "link" it to the dataset (set the DataSet property)
- Add data-aware controls from the "Data Controls" tab to the form, and link each one to the DataSource (not dataset) component
- Most controls link to a single field, so you also need to set the Field for each tab.
See below for more details on the controls
Datasets can be in a number of states. While there are quite a few (look up TDataSetState in the source), the main ones to be aware of initally are
|dsInactive||The dataset is closed|
|dsBrowse||The user can browse through the dataset, looking at values|
|dsEdit||The user can edit values on the current row. Values are not saved until a post is performed.|
|dsInsert||A new row has been added, and the user can set the values. The record is not saved until a post is performed|
The other states are fairly transitory, and are usually handled "automatically". They are used internally and in more complicated code. If your database only views data, and you open the dataset at design time, you can largely ignore the state, as it will mostly be dsBrowse. However, most applications will want to change the data at some stage. If you are using data-aware controls, they will handle a lot of this automatically. If you change the text in a DBEdit control, for example, it will put the dataset into dsEdit state - unless you are already in dsEdit or dsInsert. If you "scroll" to a different record while the current state is dsEdit or dsInsert, that record will be "posted" and the dataset revert to dsBrowse. However, if you are accessing the dataset from code, you will often have to change the state in code as well. The dbNavigator control (see below) allows the user to change the state explicitly.
Post and Cancel
If you have edited or inserted a record, the new values are held in a buffer.
- Calling the dataset cancel method removes the new record (insert) or reverts the values to their previous values (edit).
- Calling the database post method saves the values (edit) or record (insert). In some dataset descendants, they will be written to the database immediately, while in others they will be stored in a list of updates until a further call is made to save all changes to the database. Finally, even when they are written to the database, you may still have to call a "commit" method to make the database write them permanently. All of this also varies considerably with the dataset descendant, so look up the details for the one you are using.