The first technique of database programming is embedding SQL commands into code written in any programming language. In this technique programmer write code in with some commonly agreed prefix “EXEC SQL” is one of the examples used by programs to identify embedded SQL code, which is then extracted by preprocessor to send it to database server for execution.
I used this technique in my development career and it is probably the worst technique to use in terms of performance, security and maintainability. Since SQL commands are sent from application sever to database server in every call to database this makes it slower in terms of bandwidth consumption over wire, pulse these SQL statements needs to be parsed on database server before execution which is another performance overhead.
Security is another challenge in this technique, as if you don’t parameterize SQL within these embedded command, it can easily lead to SQL injection attacks in which attacker injects malicious SQL command in place of variable used in SQL query.
Maintainability of embedded code becomes challenging when code-base grows bigger pulse it is harder to debug the code to find any issues.
Library of functions or classes
This is another technique to database programming in which certain database functions are exposed as Application Programming Interface (API), through which database operations are performed.
The database commands are sent to database server through function call along with the parameters and then the actual SQL code is executed on database server.
Java’s JDBC class library and Microsoft ADO.NET class library are the two prime examples of this paradigm. These libraries have classes for managing database connections, retrieving query results etc.
There are few caveats while programming with these interface database interactions such as database connection opening are un-managed operations which means if you open connection in your code you need to make sure its closed after you done with query results, otherwise this would lead to memory leak in your application.
Memory leaks are one type of security flaws in application which can lead to sensitive data leakage.
Object Relational Mapper (ORM) is another popular technique in this paradigm, in which database tables/entities are mapped to high level language objects. You can write query based on objects which is converted by ORM to database SQL. Entity Framework and Hibernate are two example of popular ORMs.
SQL programming language
Another and by far the most popular technique is SQL programming language. The is redesign of traditional SQL language wither some additions such as loops and conditionals etc. to make it full programming language. One example of SQL as programming language is Oracle’s PL/SQL, which used to write store procedures.
A Store Procedure is special database object which has name and set of SQL statements and accepts zero or more arguments. The stored procedures are called by name from application programmed to get data from database.
Store Procedures have significant advantages over traditional programming technique for database; as it sits in database in pre-compiled form which make Store Procedures fast as compared to their other counter parts.