One of Bolero's most powerful features is the ability to execute custom SQL scripts at regular intervals. For example, a custom SQL script could be written to:
A custom SQL script called 'Backup BoleroDatabase' has been included for you. This will back up the Bolero database every day at 2:30 am. For more information on backing up the Bolero database, click here.
Any custom SQL scripts you write must be stored as text files in the Bolero Scripts folder, which is in same folder as the Bolero application.
When a custom SQL script is executed by Bolero, the script is performed against the open connection, which will always be the Bolero database. Therefore, you do not need to surround your SQL with an OPEN CLOSE DATABASE.
Note
If your script is executed against a database other than the Bolero database, you must explicitly OPEN...CLOSE that database. Use the following format as an example:
<my script>; /* on BoleroDatabase */ OPEN DATABASE MyDatabase; <my script>; /* on MyDatabase */ COMMIT; CLOSE DATABASE MyDatabase;'
You must always COMMIT your changes.
Debug your script manually against the Bolero database before having Bolero begin regular scheduled execution of the script. Any errors encountered during the execution of a custom SQL script will be recorded in the Bolero Log File..
The Bolero Settings file contains two sections for maintaining custom SQL scripts: Scheduled SQL Scripts and Scheduled SQL Script Details.
Scheduled SQL Scripts is a list of all custom SQL scripts that will be executed by Bolero and must be in the following format:
The custom SQL Scripts settings shown above are in the default Settings file as examples. You can use these script settings if you like, or discard them and create your own. The default Scheduled SQL Scripts settings are intended to show you how to create settings for your own scripts.[Scripts] Backup = Off HourlyReport = Off DailyReport = Off WeeklyReport = Off MonthlyReport = Off AnnualReport = Off
You must list each custom SQL script that will be executed by Bolero, and include whether execution is initially enabled or disabled. The on/off setting allows you to maintain a list of all your scripts in the Bolero Settings file and selectively enable or disable execution of each script, without having to delete and later retype the information.
For each script that is listed under the Scripts subsection, there must be a corresponding entry in the Bolero Settings file. This entry identifies the file name of the script, and provides execution details. This information is shown under the Scheduled SQL Script Details subsection and must be in the following format (this is what is displayed in the default Settings file):
In the example above, a Backup report is listed. We'll use this example, plus five others (for generating hourly, daily, weekly, monthly, and annual reports), to illustrate the information Bolero needs to know before it can execute a script.[Backup] Name = Backup BoleroDatabase Unit = Day Every = 1 Hour = 2 Minute = 30
Detail Definition
Name = The filename of the script stored in the Bolero Scripts folder.
Unit = The unit of time.
Required: Always
Valid Values: Hour, Day, Week, Month or Year
Every = The frequency of 'Unit =', expressed as an integer.
Required: Always
Valid Values: 1 thru 31
DayOfWeek = The day of the week.
Required: When 'Unit = Week'
Valid Values: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
DayOfMonth = The day of the month, expressed as an integer.
Required: When 'Unit =' Month or Year
Valid Values: 1 thru 31
Hour = The hour of the day, expressed as an integer.
Required: When 'Unit =' Day, Week, Month or Year
Valid Values: 0 thru 23
Minute = The minute of the 'Hour =', expressed as an integer.
Required: Always
Valid Values: 0 thru 59
For an Hourly Execution Schedule:
To have the script 'script1' in the Bolero Scripts folder execute every 2 hours at 45 minutes past the hour:
[HourlyReport] Name = script1 Unit = Hour Every = 2 Minute = 45
For a Daily Execution Schedule:
To have the script 'script2' in the Bolero Scripts folder execute every day at 14:15.
[DailyReport] Name = script2 Unit = Day Every = 1 Hour = 14 Minute = 15
For a Weekly Execution Schedule:
To have the script 'script3' in the Bolero Scripts folder execute every 2 weeks on Wednesday at 14:15.
[WeeklyReport] Name = script3 Unit = Week Every = 2 DayOfWeek = Wednesday Hour = 14 Minute = 15
For a Monthly Execution Schedule:
To have the script 'script4' in the Bolero Scripts folder execute on the last day of every month at 09:15.
[MonthlyReport] Name = script4 Unit = Month Every = 1 DayOfMonth = 31 Hour = 9 Minute = 15
For an Annual Execution Schedule:
To have the script 'script5' in the Bolero Scripts folder execute every year, on February 28th at 08:15.
[AnnualReport] Name = script5 Unit = Year Every = 1 Month = 2 DayOfMonth = 28 Hour = 8 Minute = 15
Click here to return to Configuration of the Bolero Settings file.
Click here to return to the Using Bolero page.