Embedded SQL & Stored Procedures

Embedded SQL

SQL statements contained within an application written in a host programming language such as C, C++, Java, or ASP.NET.

Host language

Any programming language that contains embedded SQL.

Steps to build an embedded-SQL program

(1) Programmer writes embedded SQL inside host code, (2) Pre-processor transforms it into DBMS- and language-specific procedure calls, (3) Host compiler compiles the program, (4) Linker produces the executable plus an "access plan" module.

Access plan

The compiled module containing the instructions needed to run embedded SQL code at runtime.

Main weakness of embedded SQL

Executables can be decompiled, exposing table names and dictionary structure; SQL errors are not caught at compile time and may surface at run-time.

Stored procedure

Business logic stored on the database server in the form of SQL code (or a DBMS-specific procedural language) that can be called by applications.

Two main advantages of stored procedures

(1) Reduce network traffic and improve performance (SQL is not transmitted across the network), (2) Reduce code duplication, lowering errors and maintenance cost.

Stored procedure syntax (MySQL)

CREATE PROCEDURE name(parameter_list) BEGIN SQL_statements; END;

IN parameter

A value supplied by the caller into the stored procedure.

OUT parameter

A value returned from the stored procedure to the caller.

How to invoke a stored procedure manually

Use CALL procedure_name(arg1, arg2, …);

Why prefer stored procedures over inline SQL strings in app code

Centralizes business logic, improves security (less SQL injection surface), reduces network traffic, easier to maintain.