Monthly Archive for March, 2006

I want to outline something I developed something like 5 years ago, and that I was kind of happy with at the time: a way of saving all SQL queries inside the database itself. The reason for writing this is that it would really fit in with all the RubyOnRails and other programming frameworks that are created these days. If someone feels like creating a component/plug-in for it, that would be so nice…

If you have no interest in programming and/or database management, go hug somebody and skip this article.

PRINCIPLE
In the most simple version, you have 1 extra table in your database that looks like this:

query_id   int % unique ID of the query
template   varchar(1000) % or even longer
var_list   varchar(255) % comma separated list of vars

An example:
[ 33 , "select * from users where user_id = %user_id% AND type = '%status%' " , "%user_id%,%status%" ]
Instead of constructing your SQL code in your (PHP) code by making one big string and then executing it, you call a function my_exec_query(query_id,array_params)
This function will

• get the template text
• walk through the comma-separated list var_list and for each variable inside, look the corresponding value up in the array array_params (or this can be a dictionary or a hashtable).
• when all substitutions are finished, execute the SQL code and return the result as (connected/disconnected) recordset or an array.

Continue reading ‘Storing the SQL queries in the database’