3.9.3. Configuring the Module

Figure 7. The Invoice form tab

Figure 8. The Invoice data module tab

Since the latest invoices are the most requested ones, it makes sense to sort them by date in reverse order. The query will look like this in the SQL property of the qryInvoice dataset:

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

To open this dataset, it will be necessary to initialise the query parameters:

qryInvoice.ParamByName('date_begin').AsSqlTimeStamp := dmMain.BeginDateSt;
qryInvoice.ParamByName('date_end').AsSqlTimeStamp := dmMain.EndDateSt;
qryInvoice.Open;

For the purpose of illustration, we will use stored procedures to perform all operations on an invoice. Regular INSERT/UPDATE/DELETE queries can be used when operations are simple and involve writing to only one table in the database. We will execute each stored procedure as a separate query in TFDCommand objects. This component is not descended from TFDRdbmsDataSet, does not buffer data and returns not more than one result row. We are using it because it consumes fewer resources for queries that do not return data.

Since our stored procedures modify data, it is necessary to point the Transaction property of each TFDCommand object to the trWrite transaction.