Where SQL statements are executed from a program, what is the best practice to store the statement text?
Most often it is in a read only string, in the source file it is executed in.
I think a better approach would be to have a separate text file containing all SQL statements, that are then read in to a dictionary object where the key is the purpose of that statement. Statements can then be executed by using their key, decoupling database implementation from the program and allowing reuse of statements between program objects.
Is this efficient? Is there a better way? Thank you!
10
The only reason I can think of to treat SQL as data instead of code is if you are dynamically generating SQL statements, and you don’t need to store those.
There are actually advantages to going in the other direction; by hard-coding your SQL statements into Stored Procedures, you benefit from certain security and performance optimizations that the RDBMS can provide.
SQL is already naturally “parameterized;” if you need additional flexibility beyond that, you should carefully balance your application requirements against the additional security challenges that will accrue from treating your SQL statements as data. SQL injection is one example of a potential security leak that can occur.
If you need generalized CRUD (create, read, update, delete) statements for each of your tables, you should use an Object-Relational Mapper to generate them.
3
I’d say the best way is to use Stored Procedures, if the DBMS supports it, for the following reasons:
- They centralize the logics so you don’t need to keep multiple copies in sync, which seems to be what you cares most.
- They centralize the permission control (authorization) to the DBMS.
- They are pre-compiled so they run faster.
- (minor) The string size sent over the network is smaller (code and DBMS usually run on different servers).
1