Jim Storey wrote:
> If I have a daily / hourly database task in SQL
> Server I can schedule a stored procedure to run.
> How do I achieve this in MySQL / PHP?
If you are running on a LAMP stack, then you would use a CRON job for this type of task. Currently, there is nothing similar to the MSSQL Job Agent (I think that's what it's called, it's been a while) in the MySQL AB GUI or command line tools. I know that SQLYog (http://webyog.com) has a 3rd party job scheduling GUI tool, but if you're running on LAMP, then simply use a CRON job.
On Windows, you can write a Perl script and schedule this through the task scheduler.
> In SQL Server I have stored procedures for data
> access. This allows optimised queries and also
> security. How is this achieved in MySQL with no
> stored procedures? Are all the db queries
> structured in the PHP?
MySQL has support for stored procedures. If you are coming from a SQL Server background, some of the syntax may be a little difficult to get used to, but I'm sure you can figure it all out. Stored procedures in MySQL have gone (and are continuing to go through) signiificant changes in feature set and support for more advanced error handling. Make sure to get the latest MySQL 5 version and continue to check on the mailling lists for updates.
One thing to note, however, as far as differences between SQL Server stored procs, and MySQL 5 stored procs: SQL Server stored procedures are "compiled" into an execution plan that is stored to avoid re-parsing of the procedure after the first call. This is not currently the case in MySQL procedures, though it may someday be. Currently, MySQL stored procedures are most useful for encapsulating transactions and long SQL scripts into a workable, maintainable block. SQL Server storred procedure security (permissions) are similar to privileges in MySQL.
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com