The scheduler in SQL 2 Excel Pro allows you to schedule automatic emails, cache updating and run SQL queries on your database. There are two main steps that you need to do:
- Setting up the Schedule(s) in SQL 2 Excel
- Triggering the schedule handler
Setting up the Schedule(s)
Setting up the schedule tasks in SQL 2 Excel pro administrator interface should be fairly straight forward. You pick a start time for the schedule and what the frequency for the schedule is and what type of action you want to perform.
The "Max Number of Runs" parameter is optional and limits the number of times the schedule is executed. Another way to limit the number of runs is to specify the End Time (Date). The schedule will not run past that time/date.
Triggering the schedule handler
PHP or Joomla does not have any good task scheduler feature unfortunately. You do need to trigger the task scheduler handler somehow.
You can do that in a couple of different ways. Pick one of these two methods:
| # | Method |
Pro's | Con's |
| 1 | Run a cron job (Unix/Linux) or use Task Scheduler on Windows/PC | Most reliable method. Will always be executes as long as the server is up - regardless if you have visitors to youir site or not. Can be configured to run as a specified user. |
Requires some knowledge to setup |
| 2 | Add a few lines of code to your site template which triggers the scheduler | Somewhat easier to setup than the cron job. | Will only be executed whe you have visitors to your site. Slows down page load times on your site. Will be excuted as the user who hits the page, e.g. will be different different times. |
I strongly recommend method #1 if you can. It is the most reliable method and it will fire even if you don't have users visiting your site. And it won't affect the user experience of the site as much. If you have a big report that needs to be generated by a schedule task then it will slow down page loading if you use method #2.
General
You need to execute this PHP script on a regular basis to process the SQL 2 Excel Pro schedules:
{JOOMLA}/administrator/components/com_sql2excel/helpers/schedule_cron.php
How often you need to execute it depends on how fine control over the schedule execution you do need. If you only execute your schedules on a weekly basis then you don't really need to excute more than once per week. If you do need to execute some schedules on an hourly basis an other at a weekly or monthly basis then you will have to execute the schedule handler hourly (at least).
When the schedule handler is triggered as script is executed which will run all schedules where the star time/next time has passed. The start/next time will be moved up with whatever time interval the schedule is defined as. For instance, a weekly schedule is executed. The start/next time will be increased with one week. This schedule will not be executed again until the week has passed.
1) Running a cron job on Linux
You can exceute the cron job as a Public Joomla user, or you can configure it to be run as a specified Joomla user. If all your Workbooks/Worksheets have Public access defined then it's fine to run the scheduler as a Public user. But if any of your Workbook or Worksheets has non-public access specified then you should run the schedule as a specified user to avoid errors.
Execute schedule as a Public user
If you have cPanel acces to your server then you may have the ability to execute cron jobs. Some ISP's may not allow it - others do. Here's some screenshots showing how I set it up on the Joomla-R-Us site using cPanel:
In the example above I decided to execute the schedule handler every hour on minute 35. The schedule handler is 00:35, 01:35, 02:35 and so on..
The command I execute is:
/usr/bin/php /home/xxxx/public_html/joomla/administrator/components/com_sql2excel/helpers/schedule_cron.php >> /dev/null 2>&1
where /home/xxxx/public_html/joomla is the full path to the Joomla installation. You will have to modify that to match your installation.
Also, php is installed as /usr/bin/php on this server. It may be different on your server. You can use this command to find out where php is installed on a Linux server (if you have command line access) : which php
The >> /dev/null 2>&1 part redirects stdout and any errors to the /dev/null "black hole", just in case.
Execute schedule as a SPECIFIED user
To execute the scheduler as a specific Joomla user, please do the following:
- Download schedule_cron_user_v2.zip
- Unzip the file
- Move the schedule_cron_user.php file to somewhere on your server where it is NOT accessible from the web
- Edit the schedule_cron_user.php file and configure, username, password and the location of your Joomla install.
- Change the permissions on the script so that it's only readable by root (or owner) to protect the password from being read by unauthorized persons ( chmod 400 schedule_cron_user.php )
- Setup cron job to run this script, something like this
/usr/bin/php {PATH_TO_SCRIPT}/schedule_cron_user.php >> /dev/null 2>&1
If you have command line access to the server you can just try to execute the script, without the ">> /dev/null 2>&1" at the end, to see that you don't get any errors.
You will see warnings that cookies can't be set - but that's normal since we're executing the PHP script from command line, i.e. we do not have a browser environment where cookies can be set.
DEBUGGING A SCHEDULE
- SQL 2 Excel version 1.8.0 includes an improved Schedule tester. Upgrade to the latest version if you are running an older version.
- Always make sure the schedule test runs ok from the SQL 2 Excel Schedules page. If it doesn't then check:
- That you have specified the schedule FROM/REPLY email address and name
- That you can send emails from your server with Joomla. Try a password recovery etc
- If the schedule test runs ok, but you don't think the schedule runs ok when executed by cron, please check:
- That the Next Date/Time is not in the future. The schedule will not execute until that time has passed.
- That the max number of runs hasn't been exceeded for the schedule
- Does the schedule involve Workbooks which have non-public permissions. Either in the Section/Category or in a Worksheet/Workbook. The cron job is executed as a Public user, unless you set it up specially to run as a specific Joomla user (see above).
- Try executing the schedule_cron.php script from the command line (if you do have command line access to the server). Something like this:
/usr/bin/php {PATH_TO_SCRIPT}/schedule_cron_user.php
There might be errors that can help you figure out the problem - Edit the ./administrator/components/com_sql2excel/helpers/schedule.php script and enable DEBUG Mode.
Change line 17, from
define( 'DEBUG', 0 );
To:
define( 'DEBUG', 1 );
Now you can excute the script from command line (if you have access to command line) or run the Scheduler with cron, like this:
/usr/bin/php {PATH_TO_SCRIPT}/schedule_cron_user.php
The debug and eventual error messages will be sent to cron and you typically get an email with the output.
2) Add code to your site template
A simpler way to trigger the schedule handler is to add these lines of code to your site template :
< ? p h p
require_once (JPATH_SITE.DS.'administrator'.DS.'components'.DS.'com_sql2excel'.DS.'helpers'.DS.'schedule_template.php');
?>
Notes:
- Requires v.2.3.0 or higher.
- Will only trigger the schedule handler when users hit your website. If nobody accesses the site => no schedule emails are sent.
- Use of cron-job to trigger the Schedule Handler is strongly recommended over this method since a cron-job is not dependent on the website traffic
3) Examples
How to run a schedule at 3 different time per day, 10AM, 1PM and 5PM.
- Setup your cron job run run every hour, lets say at **:01 every hour
- Setup your schedule to run "Hourly"
- Define the Run-If rule for the schedule like this
Run-If : SQL query returns one or more rows
Run-If SQL query:
SELECT '{H}'
FROM dual
WHERE '{H}'='10' OR '{H}'='13' OR '{H}'='17'The {H} substitution variable will be replaced with the current hour in 24h format - every time the scheduler is executed (every hour in this case). The Run-If queries will only return a row when the hour matches. The cron job will ping the scheduler every hour, but the schedule will only be executed when the Run-If rule is satisfied, e.g. at the hours we want it to run.
