Is Inline SQL hard coding? [closed]_问答_开发者

Closed. This question is opinion-based. It is not currently accepting answers.

Want to improve this 开发者_Python百科question? Update the question so it can be answered with facts and citations by editing this post.

Closed 9 years ago.

Improve this question

I have heard some strong developers say that "inline SQL in and of itself is not evil". I do not understand how inline SQL is acceptable. To me its just like hard coding. Many a developer would scoff in my face for putting a connection string in the code vs a config file. So why is it that "SELECT value1,value2 FROM TABLE" is perfectly acceptable in compiled code?

I think there is a lot of coupling between a database query and the code that surrounds it. For example, a query might fetch first name and last name for a particular user from a database, and then create an XML file containing the first name and last name.

If you put the database query somewhere else (e.g. config file) it might seem like you've increased configurability and flexibility. However, if you want to actually change it, say add a field called "age" to the XML file, you can't just change the query alone, you need to change the code writing the XML file. So in fact you've replaced the problem of having to change one piece of code, with the problem of having to change two things (code and configuration), and the danger that they might not be consistent, and you haven't gained any extra flexibility.

(If it seems like one can just write generic code to take all database fields and put them in the XML file, consider the case where age is stored as the birth year in the database, and you have to do computation to create the age.)

When I see the length programmers go to just to avoid simple queries like:

select * from products where productid in (1,2) and productactive = 1

or:

select * from products where not exists (
    select * from order_lines where products.id = order_lines.productid)

Then I always wonder what their real goal is. Because it can't be simplicity.

SQL is code, it's just not the same programming language as the rest of your code. If you need to change the SQL, you usally always need to change the code dealing with that SQL, data and database schema as well. The SQL shouldn't vary between installation.

If you add a column, changing the SQL isn't enough, you'll need to change code handling that column as well. Pretty much any change to the database schema and SQL, just changing the SQL is not enough.

A connection string on the other hand is configuration, not code.

(And yes, there's exceptions to all of this, if you're creating a general purpose reporting tool, you'll need to engineer that so the SQL can be "configurable")

SQL queries are code, not configuration.

It is useful in an agile environment to use stored procedures on a system like SQL Server where they are dynamically compiled, as you can often fix or workaround an application problem by patching the stored proc.

But it is still code.

Another distinction (aside from code vs. configuration) is that a connection string is probably used all over your codebase whereas a SQL query is more likely to be coupled to a specific class or method. Therefore, if you subscribe to "Don't Repeat Yourself", you'd definitely benefit from centralizing the connection string but would likely gain nothing from moving the SQL string to a different location.

That being said, some of the most elegant solutions involve avoiding SQL strings entirely by using Hibernate or another ORM, but that's a different topic.

"SELECT value1,value2 FROM TABLE" in compiled code was the essence of so called Object-relational impedance mismatch. Since LINQ was released inline SQL is no longer acceptable in vast majority of cases...

I've made systems where all the SQL is in stored procedures and the names of the stored procedures are themselves not even stored in the program, but in the app.config.

This means then not only can stored procedures be patched, but different customers can use different procedures.

The database services interface provided to the application(s) is thus part of the system design.

A drawback of having any SQL in a client application at all (and this includes table-based ORMs and LINQ) is that you have very little explicit security interface. With stored procedures, there is a single GRANT - EXEC. With tables or even views, the ORM will need SELECT, UPDATE, INSERT, DELETE for whatever SQL is generated by the tool or stored in the application. Taking an inventory of the rights needed for the system to properly run and being able to audit is much more difficult than having a good solid layer of stored procedures (and, to some extent, read-only views) which everything goes through.

So I would say that "SELECT value1, value2 FROM TABLE" is not acceptable in client code, because every user who would potentially execute that statement needs to have SELECT on those two column from a base table, and that is hard to manage, even with roles.

One way to look at it is to think of table names and column names as part of the database's "public" interface. That's how databases implement physical data independence. And updatable views implement logical data independence, to some degree.