Tutorial 14 - Updating database tables

Making changes to the database tables is something that is beyond the scope of a report generator. However, Scribe recognizes the need for updating the content of some tables as the reports execute, and provides a set of tools for inserting, updating and deleting rows from the table.

In this Tutorial, we will make use of one such tool: the Insert component.

Looking back at the Tutorial 13, we can see that MailCustomerOrderInfo process lacks the error tracking. That is, if a problem occurs while sending an e-mail (such as a malformed e-mail address), there is no way for us to research and correct it since no trace of the error is left. To keep track of the errors, we will add the following components to the procedure:

1. CurrentDate System Function:

This Function component returns the current date. The "Return value" field is set to a new variable that is later renamed to Date.

2. CurrentUser System Function:

This Function component returns the current user ID which is the user login account in Scribe (do not confuse this with the session login account that one uses to log into Windows or Linux system). The "Return value" field is set to a new variable that is later renamed to User.

3. Database table Insert:

While the Insert component is still selected, set the name of the table by clicking on MailErrorLog entry in the Palette's Source column (alternatively, you can drag it into the Insert component's table name area at the top of the box).

Resize the Insert component by dragging its bottom control point down until there are four field slots, then click on these fields in the Palette's Fields column for table MailErrorLog:

• Email
• ErrorDate
• Report
• UserID

(or, like the table name, you can drag the field names into their slots). Finally, set the Value fields in the Insert component to:

• Customers.Email (run-time button is set to the Run-time mode: )
• Date (run-time button is set to the Run-time mode: )
• CustomerFormatted (run-time button is set to the Hardcoded mode: )
• User (run-time button is set to the Run-time mode: )

This sequence is shown in the figure below:

The Insert component configured in the previous paragraph adds a new row to the MailErrorLog table, setting its fields as follows:

• Email field is set to the value carried by the Email field of the Customers table
• ErrorDate field is set to the value carried by the Date variable (current date)
• Report field is set to the fixed string literal: 'CustomerFormatted'
• UserID field is set to the value carried by the User variable (current user login account)

At the SQL command level, the Insert component is equivalent to:

insert into MailErrorLog(Email, ErrorDate, Report, UserID) values([Customers.Email],[Date],'CustomerFormatted',[User])

where the bracketed values are placeholders for the actual values to be substituted at the run-time.

The modified MailCustomerOrderInfo process is shown below:

Save the Process Procedure as MailCustomerOrderInfoLog.