Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Is saving SQL statements in a MySQL table for executing later a bad idea? The SQL statements will be ready for execution, i.e. there will be no parameters to swap or anything, for example DELETE FROM users WHERE id=1.

I guess I'm being lazy, but I thought of this idea because I'm working on a project that will require quite a few cron jobs that will execute SQL statements periodically.

share|improve this question
    
You should clarify if if you intend to run these SQL statements once, or if you want to run the same set of statements repeatedly. –  GrandmasterB Feb 6 at 16:34
9  
This whole question seems like one of those situations where the fundamental approach is wrong. But it's hard to tell exactly what because we know so little about the problem space. –  Erick Robertson Feb 6 at 18:39
add comment

7 Answers

up vote 37 down vote accepted

It's safe, if that's what you're asking. As long as you're as careful about your security as you are with your data's security.

But don't reinvent the wheel, Stored Procedures ARE bits of SQL stored in a table. And they support, nay encourage, parameterisation.

Also note, you can make your security simpler AND reduce the number of points of failure AND reduce the network communications by using the MySql Event Scheduler instead of cron.

Other databases have equivalents to these, for good reason. You're not the first to need this functionality.

share|improve this answer
1  
+1 on using the scheduler. Limiting the scheduler's access to just the procs is better than table access. –  JeffO Feb 6 at 14:49
    
+1 complete simple and direct answer. –  SparK Feb 6 at 19:51
add comment

If you want to save SQL statements in a database for later execution, there's a better option than putting them in a table: use the built-in functionality provided for this specific purpose. Put them in stored procedures.

share|improve this answer
2  
So where does the cron job store the list of stored procedures to run? –  JeffO Feb 6 at 12:37
1  
this may be useful stackoverflow.com/questions/6560639/… though it's not using cron –  MetaFight Feb 6 at 12:43
    
Don't know what I was thinking. A list of procs could all be executed from a single proc, so the cron job only needs to execute one. –  JeffO Feb 6 at 19:36
add comment

No, I would say it is not a good idea.

It means every "real" query/update will need 2 hits on the database - one to get the "real" query/update, and one to execute it.

This may not be a huge issue in a small system, but the more users/transactions your system gets, the less it will scale.

I am guessing this comes from looking for an alternative to embedding SQL in your code?

Encapsulating the SQL in a stored procedure as Mason Wheeler suggested, would be one way that be a lot better than this idea.

share|improve this answer
    
+1, this is main reason why @Mason Wheeler's solution is the correct one - he just missed to highlight that. Though IMHO its not the performance issue I see here most important - there is just no need to complicate things by extracting an SQL statement from the DB and send it back for excecution then. –  Doc Brown Feb 6 at 13:32
    
Why does the list of sql statements have to be stored in the same database/server, etc? –  JeffO Feb 6 at 14:39
1  
the OP is the one proposing 2 hits on the database. I say he shouldn't do it. You then ask why it has to be the same database/server. I ask who said it did? you then ask how else do you get 2 hits on the DB. Why don't you state your actual question instead of whatever this is you are doing? –  Ozz Feb 6 at 15:20
1  
@JeffO Even if it is a different database, it's still 2 database queries for what should be 1 query, which is unnecessary slowdown –  Izkata Feb 6 at 19:39
1  
@Ozz: you give too much weight the performance aspect of your answer - the performance is most probably neglectable in most real-world scenarios. But the need for two actions (first one to get the "real" query/update, second one to execute it) makes things just more complicated than necessary. –  Doc Brown Feb 7 at 12:22
show 6 more comments

Sorry, I don't think that is a good idea.

Consider the case where the table in question changes. Say your id column gets renamed to new_id.

As well as the change itself, there is a version of your code written for an old vintage of the database that may no longer run. Sure, you might know to check in the code table but it isn't immediately obvious for someone else.

For a change such as I've described, I'd typically look at standalone SQL files, stored procedures, triggers, in-line SQL in client code (VB, C#, C++ etc) but the last place I'd think to look is in the database tables. Although maybe I will now! :)

share|improve this answer
add comment

The easy way out would be to have an .ini or other configuration flavor file to keep the queries in. This way you can have them in one place, change anything without having to access the database to do so, not hit the database multiple times and also you may want/need at some point to use parameters or add conditions to your queries (augment them with more complex stuff for a particular case in your code).

Of course you can keep them at database level (either in a table or, better yet, as stored procedures), however if you're as lazy as I am ;-) but are also concerned with performance, an .ini file and PHP's beloved parse_ini method to read it, are the way to go (if you're going for another programming language, you're on your own figuring out similar approaches)! Usually I would read this file in the application's bootloader and keep it in a static object (maybe with a cache layer on top) to really speed things up if we're talking about a very large number of distinct queries.

share|improve this answer
add comment

If you only need the SQL statement once, for example, if you are queuing a bunch of operations to perform during off-hours, then yes, putting them into a table will work just fine.

If you need to execute the same SQL statement at specified intervals, then the stored procedure route others have mentioned is likely a better choice for you.

That said, what you are asking about is fairly unusual, and may be an indication of some other problem. For example, if you are waiting to delete a user from a database, it may be better to call a scheduled script that does the operation through your application code, rather than recording the actual SQL statements. This way the SQL and code are never out of sync.

share|improve this answer
add comment

There are valid reasons to store SQL in a table. Software I work with at work now includes a system for generating documents and the documents need to include fairly complex calculations using data in a database. The documents are updated frequently, are often significantly different in terms of the data needed, and the calculations that need to be performed. SQL is being used, basically, as a scripting language for performing these calculations and that SQL is stored in tables that also store other info for these documents. The people that maintain those documents are not programmers or DBAs, but they're familiar with the database schema and they're proficient with SQL. It would be significant overhead for them to maintain that SQL code in the form of stored procedures.

For what you described – "... a project that will require quite a few cron jobs that will execute SQL statements periodically" – the other answers are probably correct in suggesting that you used store procedures, or the equivalent, for the DBMS you're using.

A good criteria for deciding whether it's sensible to store SQL in a table versus in a stored procedure is determining who will be maintaining that SQL. If users maintain that SQL, i.e. they write it and change it whenever they want, then it may be perfectly fine, and indeed best, to store that SQL in a table. If developers will be maintaining that SQL, then it should be stored in a form that can be updated by your (hopefully automated) build and deployment procedures, e.g. stored as an object like a stored procedure in the database itself.

share|improve this answer
1  
Thank you for the answer. I ended up using MySQL events to schedule the queries to run. Quite a few people thought "the fundamental approach is wrong" :) when all I needed was to run a few SQL statements after 15 minutes from a specific user action. –  Amgad Suliman Feb 9 at 6:01
2  
@AmgadSuliman – it's important to be charitable to everyone, especially on the SE sites. It's good to have strong opinions, but it's too easy to over-match for patterns against which we enjoy inveighing. But part of being charitable to others on these sites is providing enough context; too much can obscure the real question, but generally that's fairly easy to fix by subsequent editing. –  Kenny Evitt Feb 9 at 20:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.