Code legibility/maintenance: Where to put SQL statements?

• In the language file itself?
• In a language file with all SQL statements?
• In different .sql files?
• Another 开发者_JS百科way?

Share your code style. :)

---

Even if you don't use a framework, MVC helps keep you sane by separating your data access, logic and presentation into separate language files.

---

I guess you will get as many answers as answers. Anyway let's ask why one could be preferrable over the other:

In the language file itself (I don't know what yoyu mean with the language file) but let me assume you meant the programming langauge itself. Well this approach was taken by Microsoft with Linq . It was taken e.g in Gemstone where the "query" langauge is Smalltalk (but not SQL)

If you put it in some .sql file then there must be a way to adress the code. I think this is what is done with stored procedures. Examples for that can e.g be found in the Postgres Database software.

If you put it in one of many files is probably open. E.g it could be that you have one query one file. Is that better or worse than having a hash table with diverse SQL statements identified by some key.

I see the following approaches every day in Access software 1) embedded in VBA as "just strings" 2) put into the queries section of access 3) I even read about putting this SQL statements in an extra SQL Statement Table.

Regards Friedrich

---

It all depends - e.g:

• in small DB maintenance scripts with a simple sequential control flow it's nice to see the statements where they are used
• programs with loops/callbacks should prepare the statements early; then a list of all stements near a init/prepare function makes sense
• a special case: I use a set of tool scripts written in different languages that do 'the same thing'; they all get their statements from .txt files containig SQL statements tagged by name
• 'big' applications (should) use stored procedures - then the problem vanishes

---

In Cobol, I put the SQL in the language file, but in separate procedures. That way, I separated the business logic from the data base logic.

In Python, I put the SQL in its own .py module. That way, I separated the business logic from the data base logic.

In Java, I put the SQL in a separate package. That way, I separated the business logic from the data base logic.

I've not used other languages, but I'd probably separate the business logic from the data base logic.