Creating applications for Firebird with the use of various components and drivers: FireDac
This article will describe the process of creating applications for the Firebird DBMS using the FireDac access components and the Delphi XE5 environment. FireDac is a standard set of components for accessing various databases starting with Delphi XE3.
Our application will work with the database model, which is shown in the figure below.
')
At the end of this article there are links to other articles that describe the process of creating a database with this model and a link to the script for creating a database.
Create a new project File-> New-> VCL Forms Application - Delphi. In the new project, add the new date module File-> New-> Other, in the wizard that appears, select Delphi Projects-> Delphi Files-> Data Module. This date module will be the main one in our project. It will contain some instances of global access components that should be accessible to all forms that need to work with the data. For example, such a component is a TFDConnection.
The TFDConnection component provides connectivity to various types of databases. We will specify an instance of this component in the Connection properties of the remaining FireDac components. What type of database will be connected to depends on the value of the DriverName property. To access Firebird, we need to set this property to FB. In order for the connection to know exactly which access library to work with, we will place the TFDPhysFBDriverLink component in the main data module. Its property VendorLib allows you to specify the path to the client library. If it is not specified, the connection to Firebird will be made through libraries registered in the system, for example in system32, which in some cases may be undesirable.
We will place the necessary access library in the fbclient folder, which is located in the application folder. To do this, in the code for the OnCreate event, the date of the module is the following code.
If you need your application to work without the Firebird server installed, i.e. in Embedded mode, then for Firebird 2.5 you need to replace fbclient.dll with fbembed.dll. If desired, the library name can be placed in the configuration file of your application. For Firebird 3.0, nothing needs to be changed (the mode of operation depends on the connection string and the value of the Providers parameter in the firebird.conf / databases.conf file).
The TFDConnection component of the database connection settings are contained in the Params property (user name, password, connection character set, etc.). If you use the property editor TFDConnection (double click on the component), then the mentioned properties will be filled automatically. The set of these properties depends on the type of database.
Additional properties:
Connected - control connection to the database, or check the status of the connection. This property must be set to True for the wizards of other FireDac components to work. If your application should request data for authorization, it is important not to forget to reset this property to False before compiling your application.
LoginPrompt - whether to ask for a username and password when trying to connect.
Transaction is a TFDTransaction component that will be used as the default for performing various TFDConnection operations. If this property is not explicitly assigned, TFDConnection will create an instance of TFDTransaction on its own, its parameters can be specified in the TxOptions property.
UpdateTransaction is a component of TFDTransaction, which will be used as default for properties of the same name of TFDQuery components. If this property is not assigned explicitly, the value from the Transaction property will be used.
Since the connection settings, with the exception of the username and password, usually do not change during the operation of the application, we will read them from the configuration file.
The config.ini file contains approximately the following lines:
The contents of the connection section can be obtained by copying the contents of the Params property of the TFDConnection component after the wizard has been run.
To connect to the database, you must change the Connected TFDConnection property to True or call the Open method. In the latter method, you can pass the username and password as parameters. In our application, we will replace the standard database connection dialog. We give the opportunity to make a mistake when entering registration information no more than three times, after which the application will be closed. To do this, write the following code in the OnCreate event handler of the main datamodule.
The TFDTransaction component is intended for explicit work with transactions.
The client side of Firebird allows execution of any actions only in the context of a transaction. Therefore, if you were able to access the data without an explicit call to TFDTransaction.StartTransaction, then somewhere in the depths of FireDac this call occurred automatically. This behavior is not recommended to use. For correct operation of applications with the database, it is desirable to manage transactions manually, that is, explicitly call the StartTransaction, Commit and Rollback methods of the TFDTransaction component.
Unlike other DBMS, in Firebird it is allowed to use any number of TFDTransaction components bound to one connection. In our application, we will use one common read transaction for all reference books and operational journals, and one writing transaction for each reference book / journal.
In our application, we will not rely on the automatic start and completion of transactions, and therefore in all transactions, Options.AutoCommit = False, Options.AutoStart = False and Options.AutoStop = False.
Since the reading transaction is common for all directories and journals, it is convenient to place it in the main data module. For normal operation (showing data in a grid, etc.), the isolation mode is READ COMMITED (Options.Isolation = xiReadCommited), since it allows the transaction to see other people committed to database changes simply by re-performing queries (re-reading the data). Since this transaction is used only for reading, we set the Options.ReadOnly property to True. Thus, our transaction will have parameters read read_commited rec_version. A transaction with such parameters in Firebird can be open for an arbitrarily long time (days, weeks, months), without blocking other transactions or affecting the accumulation of garbage in the database (because in fact, on a server, such a transaction starts as committed).
We start the read transaction immediately after successfully establishing the connection with the database, calling trRead.StartTransaction in the OnCreate event of the main datamodule, and terminating before closing the connection by calling tRead.Commit in the OnDestroy event of the main datamodule. The value of the Options.DisconnectAction property is xdCommit by default, suitable for a read-only transaction.
The writing transaction will be separate for each directory / journal. We will post it on a form that relates directly to the desired journal. A writing transaction should be as short as possible in order not to hold Oldest Active Transaction, which does not allow garbage collection, which in turn leads to performance degradation. Since the writing transaction is very short, we can use the SNAPSHOT isolation level. Thus, our writing transaction will have Options.ReadOnly = False and Options.Isolation = xiSnapshot parameters. For writing transactions, the value of the Options.DisconnectAction property is not suitable by default, it must be set to xdRollback.
You can work with data in FireDac using the FDQuery, FDTable, FDStoredProc, FDCommand components, but FDCommand is not a dataset.
TFDQuery, TFDTable, and TFDStoredProc are inherited from TFDRdbmsDataSet. In addition to the data sets to work directly with the database, in FireDac there is also a component TFDMemTable, which is designed to work with the data set in memory, which is analogous to TClientDataSet.
The main component for working with datasets is TFDQuery. The capabilities of this component is enough for almost any purpose. The TFDTable and TFDStoredProc components are just modifications, either slightly extended or truncated. We will not consider and apply them in our application. If you wish, you can read them in the FireDac documentation.
The purpose of the component is to buffer the records selected by the SELECT statement to represent this data in the Grid, as well as to ensure the "editable" record (the current one in the buffer (grid)). Unlike the IBX.IBDataSet component, the FDQuery component does not contain the RefreshSQL, InsertSQL, UpdateSQL and DeleteSQL properties. Instead, “editableness” is provided by the FDUpdateSQL component, which is set in the UpdateObject property.
TFDUpdateSQL SQL , . TFDQuery, TFDTable TFDStoredProc. TFDUpdateSQL TFDQuery TFDTable, . TFDUpdateSQL TFDStoredProc. , , .
SQL , TFDUpdateSQL , .
On the Generate tab, you can simplify the task of writing Insert / Update / Delete / Refresh queries. To do this, select the table for the update, its key fields, the fields for the update, and the fields that will be re-read after the update, and click on the “Generate SQL” button.
After that, the requests will be generated automatically, and you will be taken to the “SQL Commands” tab, where you can correct each of the requests.
The Options tab contains some properties that can affect query generation. These properties are not related to the TFDUpdateSQL component itself, but are references to the UpdateOptions properties of a dataset that has the current TFDUpdateSQL in the UpdateObject property. This is done solely for convenience.
, TFDUpdateSQL Transaction. , , , TFDRdbmsDataSet.
TFDCommand SQL . TDataSet, SQL , .
: . TDBGrid, TDataSource, TFDQuery, TFDTransaction.
.
TFDQuery qryCustomers. DataSet DataSource. Transaction ReadOnly trRead, . UpdateTransaction trWrite, Connection — . SQL :
trWrite , SNAPSHOT. , . , :
Options.AutoStart = False
Options.AutoCommit = False
Options.AutoStop = False
Options.DisconnectAction = xdRollback
Options.Isolations = xiSnapshot
Options.ReadOnly = False
SNAPSHOT INSERT/UPDATE/DELETE. , INSERT/UPDATE/DELETE , SNAPSHOT.
, READ COMMITED (statement read consistency). , SELECT , . SNAPSHOT , .
To be able to edit a dataset, you must fill in the InsertSQL, ModifySQL, DeleteSQL and FetchRowSQL properties. These properties can be generated by the wizard, but after that some editing may be required. For example, you can add a RETURNING clause, delete a modification of some columns, or even completely replace the automatically generated request to call a stored procedure.
InsertSQL:
ModifySQL:
DeleteSQL:
FetchRowSQL:
. TFDQuery UpdateOptions.GeneratorName = GEN_CUSTOMER_ID UpdateOptions.AutoIncFields = CUSTOMER_ID. , ( ) INSERT RETURNING. .
, mrOK . DBAware , Insert/Edit, .. Post. Insert/Edit . , - , , , . , , . :
. .
, CachedUpdates, .
From the code it is clear that until the OK button is pressed, the writing transaction does not start at all. Thus, the writing transaction is active only while data is being transferred from the dataset buffer to the database. Since we are saving no more than one record in the buffer, the transaction will be active for a very short time, as required.
The reference book of goods is done similarly to the reference book of customers. However, we will demonstrate another method for obtaining auto-increment values ​​in it.
The main query will look like this:
The property of the TFDUpdateSQL.InsertSQL component will contain the following query:
RETURNING, PRODUCT_ID BEFORE INSERT . UpdateOptions.GeneratorName. , PRODUCT_ID Required = False ReadOnly = True, . .
«-». .
- – , (, , …), - , , .. : , — . , TDBGrid, TDataSource, TFDQuery. qryInvoice, qryInvoiceLine.
In the Transaction property of both datasets, we specify the ReadOnly transaction trRead, which was created in the main datamodule of the project. In the UpdateTransaction property, we specify the transaction trWrite, in the Connection property, the connection located in the main datamodule.
. , . – , . , , , dmMain, , , , . ( ). .
, . , SQL qryInvoice :
:
- , INSERT/UPDATE/DELETE.
TFDCommand. TFDRdbmsDataSet, , , . , Transaction TFDCommand trWrite.
- : , , «». - , , , . . .
qryAddInvoice.CommandText:
qryEditInvoice.CommandText:
qryDeleteInvoice.CommandText:
qryPayForInvoice.CommandText:
TFDUpdateSQL, qryInvoice.Refresh .
, , :
, . DBAware . — TButtonedEdit. , . , - TDBLookupCombobox, , - , - .
, . TButtonedEdit :
DBAware , .
- , , . CachedUpdates, , DBAware .
We now turn to the positions of the invoice. Set the qryInvoiceLine data set the property MasterSource = MasterSource, which is bound to qryInvoice, and the property MasterFields = INVOICE_ID. In the SQL property, we write the following query:
All modifications, as in the case of the invoice header, will be carried out using stored procedures. We give the text of the request to call the stored procedures.
qryAddInvoiceLine:
qryEditInvoiceLine:
qryDeleteInvoiceLine:
The form for adding a new record and editing an existing one, as in the case with the header, will not use DBAware. To select a product, we will use the TButtonedEdit component. The button handler code in the TButtonedEdit component will look like this:
DBAware , , .
.
, . , Delphi FireDac Firebird.
.
, , .
Our application will work with the database model, which is shown in the figure below.
')
At the end of this article there are links to other articles that describe the process of creating a database with this model and a link to the script for creating a database.
| Attention! This model is just an example. Your subject area may be more complicated, or completely different. The model used in this article is simplified as much as possible in order not to clutter up the description of working with components with the description of creating and modifying the data model. |
Create a new project File-> New-> VCL Forms Application - Delphi. In the new project, add the new date module File-> New-> Other, in the wizard that appears, select Delphi Projects-> Delphi Files-> Data Module. This date module will be the main one in our project. It will contain some instances of global access components that should be accessible to all forms that need to work with the data. For example, such a component is a TFDConnection.
TFDConnection Component
The TFDConnection component provides connectivity to various types of databases. We will specify an instance of this component in the Connection properties of the remaining FireDac components. What type of database will be connected to depends on the value of the DriverName property. To access Firebird, we need to set this property to FB. In order for the connection to know exactly which access library to work with, we will place the TFDPhysFBDriverLink component in the main data module. Its property VendorLib allows you to specify the path to the client library. If it is not specified, the connection to Firebird will be made through libraries registered in the system, for example in system32, which in some cases may be undesirable.
Client Library Path
We will place the necessary access library in the fbclient folder, which is located in the application folder. To do this, in the code for the OnCreate event, the date of the module is the following code.
// xAppPath := ExtractFileDir(Application.ExeName) + PathDelim; FDPhysFBDriverLink.VendorLib := xAppPath + 'fbclient' + PathDelim + 'fbclient.dll'; | Important! If you are compiling a 32 bit application, then you must use the 32 bit fbclient.dll library. For 64 bit - 64 bit. In addition to the fbclient.dll file, it is advisable to place the msvcp80.dll and msvcr80.dll libraries (for Firebird 2.5), and msvcp100.dll and msvcr100.dll (for Firebird 3.0) in the same folder. These libraries can be found either in the bin subfolder (Firebird 2.5) or in the root folder of the server (Firebird 3.0). In order for the application to correctly display its own firebird errors, you must also copy the file firebird.msg. For Firebird 2.5 and earlier, it should be one level higher than the client library catalog, i.e. in our case in the application directory. For Firebird 3, it should be located in the client library directory, i.e. in the fbclient directory. |
If you need your application to work without the Firebird server installed, i.e. in Embedded mode, then for Firebird 2.5 you need to replace fbclient.dll with fbembed.dll. If desired, the library name can be placed in the configuration file of your application. For Firebird 3.0, nothing needs to be changed (the mode of operation depends on the connection string and the value of the Providers parameter in the firebird.conf / databases.conf file).
| The board Even if your application will work with Firebird in Embedded mode, it is more convenient to conduct development under a full-fledged server. The fact is that in Embedded mode, Firebird works in the same address space with your application, which can lead to undesirable consequences if errors occur in your application. In addition, at the time of development, the Delphi environment and your application are separate applications using Embedded. Until version 2.5, they cannot work with one base at the same time. |
Connection settings
The TFDConnection component of the database connection settings are contained in the Params property (user name, password, connection character set, etc.). If you use the property editor TFDConnection (double click on the component), then the mentioned properties will be filled automatically. The set of these properties depends on the type of database.
| Parameter | Purpose |
|---|---|
| Pooled | Whether the connection pool is used. |
| Database | The path to the database or its alias, as defined in the aliases.conf configuration file (or databases.conf) of the Firebird server. |
| User_Name | Username. |
| Password | Password. |
| OSAuthent | Whether authentication is used by the operating system. |
| Protocol | Connection protocol The following values ​​are allowed:
|
| Server | Server name or IP address. If the server is running on a non-standard port, then you must also specify the port through a slash, for example, localhost / 3051. |
| SQLDialect | Dialect. Must match the database dialect. |
| RoleName | Role name |
| CharacterSet | The name of the connection character set. |
Additional properties:
Connected - control connection to the database, or check the status of the connection. This property must be set to True for the wizards of other FireDac components to work. If your application should request data for authorization, it is important not to forget to reset this property to False before compiling your application.
LoginPrompt - whether to ask for a username and password when trying to connect.
Transaction is a TFDTransaction component that will be used as the default for performing various TFDConnection operations. If this property is not explicitly assigned, TFDConnection will create an instance of TFDTransaction on its own, its parameters can be specified in the TxOptions property.
UpdateTransaction is a component of TFDTransaction, which will be used as default for properties of the same name of TFDQuery components. If this property is not assigned explicitly, the value from the Transaction property will be used.
Since the connection settings, with the exception of the username and password, usually do not change during the operation of the application, we will read them from the configuration file.
// xIniFile := TIniFile.Create(xAppPath + 'config.ini'); try xIniFile.ReadSectionValues('connection', FDConnection.Params); finally xIniFile.Free; end; The config.ini file contains approximately the following lines:
[connection] DriverID=FB Protocol=TCPIP Server=localhost/3051 Database=examples OSAuthent=No RoleName= CharacterSet=UTF8 The contents of the connection section can be obtained by copying the contents of the Params property of the TFDConnection component after the wizard has been run.
| Comment In fact, the general settings are usually found in% AppData% \ Manufacture \ AppName and saved there by the installer of the application. However, during development it is convenient for the settings file to be placed somewhere closer, for example, in the folder with the application. Note that if your application is installed in the Program Files folder and the configuration file is in the same place, then this file will be virtualized into Program Data, or there will be problems with its modification and subsequent reading of the new settings. |
Database connection
To connect to the database, you must change the Connected TFDConnection property to True or call the Open method. In the latter method, you can pass the username and password as parameters. In our application, we will replace the standard database connection dialog. We give the opportunity to make a mistake when entering registration information no more than three times, after which the application will be closed. To do this, write the following code in the OnCreate event handler of the main datamodule.
// 3 , xLoginCount := 0; xLoginPromptDlg := TLoginPromptForm.Create(Self); while (xLoginCount < MAX_LOGIN_COUNT) and (not FDConnection.Connected) do begin try if xLoginPromptDlg.ShowModal = mrOK then FDConnection.Open( xLoginPromptDlg.UserName, xLoginPromptDlg.Password) else xLoginCount := MAX_LOGIN_COUNT; except on E: Exception do begin Inc(xLoginCount); Application.ShowException(E); end end; end; xLoginPromptDlg.Free; if not FDConnection.Connected then Halt; Component TFDTransaction
The TFDTransaction component is intended for explicit work with transactions.
The client side of Firebird allows execution of any actions only in the context of a transaction. Therefore, if you were able to access the data without an explicit call to TFDTransaction.StartTransaction, then somewhere in the depths of FireDac this call occurred automatically. This behavior is not recommended to use. For correct operation of applications with the database, it is desirable to manage transactions manually, that is, explicitly call the StartTransaction, Commit and Rollback methods of the TFDTransaction component.
| Parameter | Purpose |
|---|---|
| Connection | Communication with the FDConnection component. |
| Options.AutoCommit | Manages the automatic start and completion of the transaction. The default is True. If the value of this property is set to True, then FireDAC does the following:
In Firebird, automatic transaction management is emulated by the FireDAC components themselves. |
| Options.AutoStart | Manages the automatic start of a transaction. Default is True. |
| Options.AutoStop | Manages the automatic completion of a transaction. Default is True. |
| Options.DisconnectAction | The action that will be performed when the connection is closed, if the transaction is active. The default is xdCommit. The following options are possible:
In other access components, the default value for the similar xdRollback property. Therefore, it is necessary to set this property manually to the value that is really required. |
| Options.EnableNested | Manages nested transactions. The default is True. When a transaction is active, the next StartTransaction call will create a nested transaction. FireDAC emulates nested transactions using savepoints if the DBMS does not explicitly support nested transactions. To disable nested transactions, set EnableNested to False and the next call to StartTransaction will raise an exception. Firebird does not explicitly support nested transactions. |
| Options.Isolation | Specifies the isolation level of the transaction. This is the most important property of a transaction. The default is xiReadCommited. The following options are possible:
|
| Options.Params | DBMS specific transaction parameters. Currently used only for Firebird and Interbase. Possible values:
|
| Options.ReadOnly | Indicates whether the transaction is read only. Default is False. If set to True, then no changes within the current transaction are possible, in Firebird in this case there is no read value in the transaction parameters. Setting this property to True allows the DBMS to optimize the use of resources. |
Unlike other DBMS, in Firebird it is allowed to use any number of TFDTransaction components bound to one connection. In our application, we will use one common read transaction for all reference books and operational journals, and one writing transaction for each reference book / journal.
In our application, we will not rely on the automatic start and completion of transactions, and therefore in all transactions, Options.AutoCommit = False, Options.AutoStart = False and Options.AutoStop = False.
Since the reading transaction is common for all directories and journals, it is convenient to place it in the main data module. For normal operation (showing data in a grid, etc.), the isolation mode is READ COMMITED (Options.Isolation = xiReadCommited), since it allows the transaction to see other people committed to database changes simply by re-performing queries (re-reading the data). Since this transaction is used only for reading, we set the Options.ReadOnly property to True. Thus, our transaction will have parameters read read_commited rec_version. A transaction with such parameters in Firebird can be open for an arbitrarily long time (days, weeks, months), without blocking other transactions or affecting the accumulation of garbage in the database (because in fact, on a server, such a transaction starts as committed).
| Comment Such a transaction cannot be used for reports (especially if they use several consecutive requests), because a transaction with the READ COMMITED isolation mode during re-reading of data will see all new committed changes. For reports, it is recommended to use a read-only short transaction with SNAPSHOT isolation mode (Options.Isolation = xiSnapshot and Options.ReadOnly = True). In this example, working with reports is not considered. |
We start the read transaction immediately after successfully establishing the connection with the database, calling trRead.StartTransaction in the OnCreate event of the main datamodule, and terminating before closing the connection by calling tRead.Commit in the OnDestroy event of the main datamodule. The value of the Options.DisconnectAction property is xdCommit by default, suitable for a read-only transaction.
The writing transaction will be separate for each directory / journal. We will post it on a form that relates directly to the desired journal. A writing transaction should be as short as possible in order not to hold Oldest Active Transaction, which does not allow garbage collection, which in turn leads to performance degradation. Since the writing transaction is very short, we can use the SNAPSHOT isolation level. Thus, our writing transaction will have Options.ReadOnly = False and Options.Isolation = xiSnapshot parameters. For writing transactions, the value of the Options.DisconnectAction property is not suitable by default, it must be set to xdRollback.
Datasets
You can work with data in FireDac using the FDQuery, FDTable, FDStoredProc, FDCommand components, but FDCommand is not a dataset.
TFDQuery, TFDTable, and TFDStoredProc are inherited from TFDRdbmsDataSet. In addition to the data sets to work directly with the database, in FireDac there is also a component TFDMemTable, which is designed to work with the data set in memory, which is analogous to TClientDataSet.
The main component for working with datasets is TFDQuery. The capabilities of this component is enough for almost any purpose. The TFDTable and TFDStoredProc components are just modifications, either slightly extended or truncated. We will not consider and apply them in our application. If you wish, you can read them in the FireDac documentation.
The purpose of the component is to buffer the records selected by the SELECT statement to represent this data in the Grid, as well as to ensure the "editable" record (the current one in the buffer (grid)). Unlike the IBX.IBDataSet component, the FDQuery component does not contain the RefreshSQL, InsertSQL, UpdateSQL and DeleteSQL properties. Instead, “editableness” is provided by the FDUpdateSQL component, which is set in the UpdateObject property.
| Comment In some cases, you can make the FDQuery component editable without setting the UpdateObject property and prescribing Insert / Update / Delete queries by simply setting the UpdateOptions.RequestLive = True property, while modifying queries will be generated automatically. However, this approach has many limitations on the basic SELECT query, so do not rely on it. |
| Parameter | Purpose |
|---|---|
| Connection | Communication with the FDConnection component. |
| MasterSource | Reference to the Master Data Source (TDataSource) for FDQuery, used as Detail. |
| Transaction | The transaction within which the query specified in the SQL property will be executed. If the property is not specified, the default transaction will be used to connect. |
| UpdateObject | Communication with the FDUpdateSQL component, which provides the “editable” data set when a SELECT query does not meet the requirements for automatically generating modifying queries when UpdateOptions.RequestLive = True is set. |
| UpdateTransaction | The transaction within which the modifying queries will be executed. If the property is not specified, a transaction from the Transaction property will be used. |
| UpdateOptions.CheckRequired | If the CheckRequired property is set to True, then FireDac controls the Required property of the corresponding fields, i.e. fields with NOT NULL constraint. The default is set to true. If CheckRequired = True and no value is assigned to the field with the property Required = True, then an exception is raised when the Post method is called. This may be undesirable if the value of this field can be assigned later in the BEFORE triggers. |
| UpdateOptions.EnableDelete | Determines whether deleting a record from the dataset is allowed. If EnableDelete = False, an exception will be raised when the Delete method is called. |
| UpdateOptions.EnableInsert | Determines whether insertion of a record into a dataset is allowed. If EnableInsert = False, then an exception will be raised when calling the Insert / Append method. |
| UpdateOptions.EnableUpdate | Determines whether a record is allowed to be changed in the dataset. If EnableUpdate = False, an exception will be raised when the Edit method is called. |
| UpdateOptions.FetchGeneratorsPoint | Controls the moment when the next value of the generator specified in the UpdateOptions.GeneratorName property or the GeneratorName property of the auto-increment field AutoGenerateValue = arAutoInc is received. It has the following options:
The default is gpDeffered. |
| UpdateOptions.GeneratorName | The name of the generator to extract the next value of the auto-increment field. |
| UpdateOptions.ReadOnly | Indicates whether the dataset is read only. Default is False. If the value of this property is set to True, then the values ​​of the properties EnableDelete, EnableInsert and EnableUpdate will be automatically set to False. |
| UpdateOptions.RequestLive | Setting RequestLive to True makes the request “live”, i.e. editable if possible. In this case, Insert / Update / Delete requests will be generated automatically. This option imposes many restrictions on the SELECT query, introduced for backward compatibility with the BDE and is not recommended. |
| UpdateOptions.UpdateMode | Responsible for checking the modification record. This property made it possible to control the possible “overlapping” of updates for cases when a user performs editing a “long” record, and another user can manage to edit the same record and save it earlier. That is, the first user at the editing stage will not even know that the record has already changed, perhaps more than once, and will be able to “wipe” these updates with his:
The last check corresponds to the request automatically generated for UpdateSQL. , where . , , upWhereChanged update table set… — , set , . , UpdateSQL. , ProviderFlags . |
| CachedUpdates | , . True, (Insert/Post, Update/Post, Delete) , . , ApplyUpdates. . False. |
| SQL | SQL . SELECT , Open. Execute ExecSQL. |
TFDUpdateSQL
TFDUpdateSQL SQL , . TFDQuery, TFDTable TFDStoredProc. TFDUpdateSQL TFDQuery TFDTable, . TFDUpdateSQL TFDStoredProc. , , .
SQL , TFDUpdateSQL , .
| Comment FireDac , (TFDConnection.Connected = True) (TFDTransaction.Options.AutoStart = True). . , , TFDConnection SYSDBA. TFDConnection.Connected . , . |
On the Generate tab, you can simplify the task of writing Insert / Update / Delete / Refresh queries. To do this, select the table for the update, its key fields, the fields for the update, and the fields that will be re-read after the update, and click on the “Generate SQL” button.
After that, the requests will be generated automatically, and you will be taken to the “SQL Commands” tab, where you can correct each of the requests.
| Comment Because product_id is not included in Updating Fields, it is not in the generated insert query. It is assumed that this column is filled automatically with a trigger (with a generator), or this is an IDENTITY column (starting with Firebird 3.0). When getting the generator value for this column from the server, it is recommended to manually add the PRODUCT_ID column to the RETURNING clause of the INSERT statement. |
The Options tab contains some properties that can affect query generation. These properties are not related to the TFDUpdateSQL component itself, but are references to the UpdateOptions properties of a dataset that has the current TFDUpdateSQL in the UpdateObject property. This is done solely for convenience.
| Parameter | Purpose |
|---|---|
| Connection | Communication with the FDConnection component. |
| DeleteSQL | SQL query to delete the record. |
| FetchrowSQL | SQL query to return one current (updated, inserted) record. |
| InsertSQL | SQL query to insert record. |
| LockSQL | SQL query to lock one current record. (FOR UPDATE WITH LOCK). |
| ModifySQL | SQL query to modify the record. |
| UnlockSQL | SQL query to unlock the current record. In Firebird does not apply. |
, TFDUpdateSQL Transaction. , , , TFDRdbmsDataSet.
TFDCommand
TFDCommand SQL . TDataSet, SQL , .
| Parameter | |
|---|---|
| Connection | FDConnection. |
| Transaction | , SQL . |
| CommandKind | .
SQL . |
| CommandText | SQL . |
: . TDBGrid, TDataSource, TFDQuery, TFDTransaction.
| Comment trRead , , dmMain. |
.
TFDQuery qryCustomers. DataSet DataSource. Transaction ReadOnly trRead, . UpdateTransaction trWrite, Connection — . SQL :
SELECT customer_id, name, address, zipcode, phone FROM customer ORDER BY name trWrite , SNAPSHOT. , . , :
Options.AutoStart = False
Options.AutoCommit = False
Options.AutoStop = False
Options.DisconnectAction = xdRollback
Options.Isolations = xiSnapshot
Options.ReadOnly = False
SNAPSHOT INSERT/UPDATE/DELETE. , INSERT/UPDATE/DELETE , SNAPSHOT.
, READ COMMITED (statement read consistency). , SELECT , . SNAPSHOT , .
To be able to edit a dataset, you must fill in the InsertSQL, ModifySQL, DeleteSQL and FetchRowSQL properties. These properties can be generated by the wizard, but after that some editing may be required. For example, you can add a RETURNING clause, delete a modification of some columns, or even completely replace the automatically generated request to call a stored procedure.
InsertSQL:
INSERT INTO customer (customer_id, name, address, zipcode, phone) VALUES (:new_customer_id, :new_name, :new_address, :new_zipcode, :new_phone) ModifySQL:
UPDATE customer SET name = :new_name, address = :new_address, zipcode = :new_zipcode, phone = :new_phone WHERE (customer_id = :old_customer_id) DeleteSQL:
DELETE FROM customer WHERE (customer_id = :old_customer_id) FetchRowSQL:
SELECT customer_id, name, address, zipcode, phone FROM customer WHERE customer_id = :old_customer_id . TFDQuery UpdateOptions.GeneratorName = GEN_CUSTOMER_ID UpdateOptions.AutoIncFields = CUSTOMER_ID. , ( ) INSERT RETURNING. .
, mrOK . DBAware , Insert/Edit, .. Post. Insert/Edit . , - , , , . , , . :
- CachedUpdates, , .
- DBAware . .
. .
procedure TCustomerForm.actEditRecordExecute(Sender: TObject); var xEditor: TEditCustomerForm; begin xEditor := TEditCustomerForm.Create(Self); try xEditor.OnClose := CustomerEditorClose; xEditorForm.DataSource := DataSource; xEditor.Caption := 'Edit customer'; qryCustomer.CachedUpdates := True; qryCustomer.Edit; xEditor.ShowModal; finally xEditor.Free; end; end; , CachedUpdates, .
procedure TCustomerForm.CustomerEditorClose (Sender: TObject; var Action: TCloseAction); begin if TForm(Sender).ModalResult <> mrOK then begin // qryCustomer.Cancel; qryCustomer.CancelUpdates; // qryCustomer.CachedUpdates := False; // Action := caFree; Exit; end; try // qryCustomer.Post; // trWrite.StartTransaction; // if (qryCustomer.ApplyUpdates = 0) then begin // qryCustomer.CommitUpdates; // trWrite.Commit; end else begin raise Exception.Create(qryCustomer.RowError.Message); end; qryCustomer.CachedUpdates := False; Action := caFree; except on E: Exception do begin // if trWrite.Active then trWrite.Rollback; Application.ShowException(E); // , Action := caNone; end; end; end; From the code it is clear that until the OK button is pressed, the writing transaction does not start at all. Thus, the writing transaction is active only while data is being transferred from the dataset buffer to the database. Since we are saving no more than one record in the buffer, the transaction will be active for a very short time, as required.
The reference book of goods is done similarly to the reference book of customers. However, we will demonstrate another method for obtaining auto-increment values ​​in it.
The main query will look like this:
SELECT product_id, name, price, description FROM product ORDER BY name The property of the TFDUpdateSQL.InsertSQL component will contain the following query:
INSERT INTO PRODUCT (NAME, PRICE, DESCRIPTION) VALUES (:NEW_NAME, :NEW_PRICE, :NEW_DESCRIPTION) RETURNING PRODUCT_ID RETURNING, PRODUCT_ID BEFORE INSERT . UpdateOptions.GeneratorName. , PRODUCT_ID Required = False ReadOnly = True, . .
«-». .
- – , (, , …), - , , .. : , — . , TDBGrid, TDataSource, TFDQuery. qryInvoice, qryInvoiceLine.
In the Transaction property of both datasets, we specify the ReadOnly transaction trRead, which was created in the main datamodule of the project. In the UpdateTransaction property, we specify the transaction trWrite, in the Connection property, the connection located in the main datamodule.
. , . – , . , , , dmMain, , , , . ( ). .
, . , SQL qryInvoice :
SELECT invoice.invoice_id AS invoice_id, invoice.customer_id AS customer_id, customer.NAME AS customer_name, invoice.invoice_date AS invoice_date, invoice.total_sale AS total_sale, IIF(invoice.payed=1, 'Yes', 'No') AS payed FROM invoice JOIN customer ON customer.customer_id = invoice.customer_id WHERE invoice.invoice_date BETWEEN :date_begin AND :date_end ORDER BY invoice.invoice_date DESC :
qryInvoice.ParamByName('date_begin').AsSqlTimeStamp := dmMain.BeginDateSt; qryInvoice.ParamByName('date_end').AsSqlTimeStamp := dmMain.EndDateSt; qryInvoice.Open; - , INSERT/UPDATE/DELETE.
TFDCommand. TFDRdbmsDataSet, , , . , Transaction TFDCommand trWrite.
| Comment , TFDUpdateSQL. |
- : , , «». - , , , . . .
qryAddInvoice.CommandText:
EXECUTE PROCEDURE sp_add_invoice( NEXT VALUE FOR gen_invoice_id, :CUSTOMER_ID, :INVOICE_DATE ) qryEditInvoice.CommandText:
EXECUTE PROCEDURE sp_edit_invoice( :INVOICE_ID, :CUSTOMER_ID, :INVOICE_DATE ) qryDeleteInvoice.CommandText:
EXECUTE PROCEDURE sp_delete_invoice(:INVOICE_ID) qryPayForInvoice.CommandText:
EXECUTE PROCEDURE sp_pay_for_inovice(:invoice_id) TFDUpdateSQL, qryInvoice.Refresh .
, , :
if MessageDlg(' ?', mtConfirmation, [mbYes, mbNo], 0) = mrYes then begin // trWrite.StartTransaction; try qryDeleteInvoice.ParamByName('INVOICE_ID').AsInteger := qryInvoice.FieldByName('INVOICE_ID').AsInteger; // qryDeleteInvoice.Execute; // trWrite.Commit; // qryInvoice.Refresh; except on E: Exception do begin if trWrite.Active then trWrite.Rollback; Application.ShowException(E); end; end; end; , . DBAware . — TButtonedEdit. , . , - TDBLookupCombobox, , - , - .
, . TButtonedEdit :
procedure TEditInvoiceForm.edtCustomerRightButtonClick(Sender: TObject); var xSelectForm: TCustomerForm; begin xSelectForm := TCustomerForm.Create(Self); try xSelectForm.Visible := False; if xSelectForm.ShowModal = mrOK then begin FCustomerId := xSelectForm.qryCustomer.FieldByName('CUSTOMER_ID') .AsInteger; edtCustomer.Text := xSelectForm.qryCustomer.FieldByName('NAME').AsString; end; finally xSelectForm.Free; end; end; DBAware , .
procedure TInvoiceForm.actEditInvoiceExecute(Sender: TObject); var xEditorForm: TEditInvoiceForm; begin xEditorForm:= TEditInvoiceForm.Create(Self); try xEditorForm.OnClose := EditInvoiceEditorClose; xEditor.Caption := ' -'; xEditorForm.InvoiceId := qryInvoice.FieldByName('INVOICE_ID').AsInteger; xEditorForm.SetCustomer(qryInvoice.FieldByName('CUSTOMER_ID').AsInteger, qryInvoice.FieldByName('CUSTOMER_NAME').AsString); xEditorForm.InvoiceDate := qryInvoice.FieldByName('INVOICE_DATE').AsDateTime; xEditorForm.ShowModal; finally xEditorForm.Free; end; end; procedure TEditInvoiceForm.SetCustomer(ACustomerId: Integer; const ACustomerName: string); begin FCustomerId := ACustomerId; edtCustomer.Text := ACustomerName; end; - , , . CachedUpdates, , DBAware .
procedure TInvoiceForm.EditInvoiceEditorClose(Sender: TObject; var Action: TCloseAction); var xEditorForm: TEditInvoiceForm; begin xEditorForm := TEditInvoiceForm(Sender); // OK, // . . if xEditorForm.ModalResult <> mrOK then begin Action := caFree; Exit; end; // trWrite.StartTransaction; try qryEditInvoice.ParamByName('INVOICE_ID').AsInteger := xEditorForm.InvoiceId; qryEditInvoice.ParamByName('CUSTOMER_ID').AsInteger := xEditorForm.CustomerId; qryEditInvoice.ParamByName('INVOICE_DATE').AsSqlTimeStamp := DateTimeToSQLTimeStamp(xEditorForm.InvoiceDate); qryEditInvoice.Execute(); trWrite.Commit; qryInvoice.Refresh; Action := caFree; except on E: Exception do begin if trWrite.Active then trWrite.Rollback; Application.ShowException(E); // , Action := caNone; end; end; end; We now turn to the positions of the invoice. Set the qryInvoiceLine data set the property MasterSource = MasterSource, which is bound to qryInvoice, and the property MasterFields = INVOICE_ID. In the SQL property, we write the following query:
SELECT invoice_line.invoice_line_id AS invoice_line_id, invoice_line.invoice_id AS invoice_id, invoice_line.product_id AS product_id, product.name AS productname, invoice_line.quantity AS quantity, invoice_line.sale_price AS sale_price, invoice_line.quantity * invoice_line.sale_price AS total FROM invoice_line JOIN product ON product.product_id = invoice_line.product_id WHERE invoice_line.invoice_id = :invoice_id All modifications, as in the case of the invoice header, will be carried out using stored procedures. We give the text of the request to call the stored procedures.
qryAddInvoiceLine:
EXECUTE PROCEDURE sp_add_invoice_line( :invoice_id, :product_id, :quantity ) qryEditInvoiceLine:
EXECUTE PROCEDURE sp_edit_invoice_line( :invoice_line_id, :quantity ) qryDeleteInvoiceLine:
EXECUTE PROCEDURE sp_delete_invoice_line( :invoice_line_id ) The form for adding a new record and editing an existing one, as in the case with the header, will not use DBAware. To select a product, we will use the TButtonedEdit component. The button handler code in the TButtonedEdit component will look like this:
procedure TEditInvoiceLineForm.edtProductRightButtonClick(Sender: TObject); var xSelectForm: TGoodsForm; begin // // if FEditMode = emInvoiceLineEdit then Exit; xSelectForm := TGoodsForm.Create(Self); try xSelectForm.Visible := False; if xSelectForm.ShowModal = mrOK then begin FProductId := xSelectForm.qryGoods.FieldByName('PRODUCT_ID') .AsInteger; edtProduct.Text := xSelectForm.qryGoods.FieldByName('NAME').AsString; // edtPrice.Text := xSelectForm.qryGoods.FieldByName('PRICE').AsString; end; finally xSelectForm.Free; end; end; DBAware , , .
procedure TInvoiceForm.actEditInvoiceLineExecute(Sender: TObject); var xEditorForm: TEditInvoiceLineForm; begin xEditorForm:= TEditInvoiceLineForm.Create(Self); try xEditorForm.OnClose := EditInvoiceLineEditorClose; xEditorForm.EditMode := emInvoiceLineEdit; xEditorForm.Caption := ' '; xEditorForm.InvoiceLineId := qryInvoiceLine.FieldByName('INVOICE_LINE_ID').AsInteger; xEditorForm.SetProduct(qryInvoiceLine.FieldByName('PRODUCT_ID').AsInteger, qryInvoiceLine.FieldByName('PRODUCTNAME').AsString, qryInvoiceLine.FieldByName('SALE_PRICE').AsCurrency); xEditorForm.Quantity := qryInvoiceLine.FieldByName('QUANTITY').AsInteger; xEditorForm.ShowModal; finally xEditorForm.Free; end; end; procedure TEditInvoiceLineForm.SetProduct(AProductId: Integer; AProductName: string; APrice: Currency); begin FProductId := AProductId; edtProduct.Text := AProductName; edtPrice.Text := CurrToStr(APrice); end; .
procedure TInvoiceForm.EditInvoiceLineEditorClose(Sender: TObject; var Action: TCloseAction); var xCustomerId: Integer; xEditorForm: TEditInvoiceLineForm; begin xEditorForm := TEditInvoiceLineForm(Sender); // OK, // . . if xEditorForm.ModalResult <> mrOK then begin Action := caFree; Exit; end; // trWrite.StartTransaction; try qryEditInvoiceLine.ParamByName('INVOICE_LINE_ID').AsInteger := xEditorForm.InvoiceLineId; qryEditInvoiceLine.ParamByName('QUANTITY').AsInteger := xEditorForm.Quantity; qryEditInvoiceLine.Execute(); trWrite.Commit; qryInvoice.Refresh; qryInvoiceLine.Refresh; Action := caFree; except on E: Exception do begin if trWrite.Active then trWrite.Rollback; Application.ShowException(E); // . Action := caNone; end; end; end; , . , Delphi FireDac Firebird.
.
, , .
Links
Source: https://habr.com/ru/post/273549/
All Articles