Is embedded SQL hard coding?

Question

I heard some strong developers say that "embedded SQL is not evil in itself." I do not understand how valid is embedded SQL. For me it is just like hard coding. Many developers would scoff at my head to insert a connection string into the code and configuration file. So why is "SELECT value1, value2 FROM TABLE" quite acceptable in compiled code?

Answer 1

I think there is a connection between the database query and the code that surrounds it . For example, a query can retrieve the name and surname of a specific user from a database, and then create an XML file containing the first and last name.

If you put the database query in another place (for example, a configuration file), it might seem that you have increased usability and flexibility. However, if you want to change it, say, add the "age" field to the XML file, you cannot just change the request yourself, you need to change the code that writes the XML file. Thus, in fact, you replaced the problem of changing one part of the code, having the problem of changing two things (code and configuration) and the danger that they can be consistent, and you did not get any additional flexibility.

(If it seems that you can simply write general code to take all the fields of the database and put them in an XML file, consider the case where the age is stored as the year of birth in the database and you need to make calculations to create the age.)

Answer 2

When I see programmers in length, just avoid simple queries, for example:

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 purpose is. Because it cannot be simplicity.

Answer 3

SQL is code, it’s just not the same programming language as the rest of your code. If you need to modify SQL, you usually need to change the code associated with this SQL schema, data, and database. SQL should not change between installations.

If you add a column, changing SQL is not enough, you will also need to change the processing of the code in that column. Virtually any change to the database schema and SQL, just changing SQL is not enough.

On the other hand, the connection string is a configuration, not a code.

(And yes, there are exceptions to all of this, if you are creating a general-purpose reporting tool, you need to predict that SQL may be "custom")

Answer 4

SQL queries are code, not configuration.

In a flexible environment, it is useful to use stored procedures on a system such as SQL Server, where they are dynamically compiled, since you can often fix or work around an application problem by correcting the stored procedure.

But this is still code.

Answer 5

Another difference (besides code and configuration) is that the connection string is likely to be used throughout your code base, while the SQL query is more likely to be associated with a particular class or method. Therefore, if you subscribe to "Do not Repeat Yourself", you will definitely be able to centralize the connection string, but most likely you will not benefit from moving the SQL string to another location.

At the same time, some of the most elegant solutions include the complete exclusion of SQL strings using Hibernate or another ORM, but this is another topic.

Answer 6

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

Answer 7

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

This means that not only stored procedures can be fixed, but different clients can use different procedures.

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

The disadvantage of having any SQL in the client application in general (and this includes ORM and LINQ) is table-based, because you have very little explicit security interface. With stored procedures, there is one GRANT - EXEC. With tables or even ORM views, you need SELECT, UPDATE, INSERT, DELETE for any SQL generated by the tool or stored in the application. Carrying out an inventory of the rights necessary for the proper operation of the system and the possibility of auditing is much more complicated than having a good solid level of stored procedures (and, to some extent, read-only) that everything goes through.

Therefore, I would say that “SELECT value1, value2 FROM TABLE” is unacceptable for client code, because every user who would potentially execute this statement must have SELECT in these two columns from the base table, and this is difficult to manage, even with roles .

Answer 8

One way to look at this is to think about table names and column names within the "public" database interface. The way databases implement physical data independence. And updatable representations to some extent realize logical independence of the data.