Skip to content

Module 5: Applications Programming and SQL

Embedded SQL (in C)

SQL Statements are embedded in a host language such as C, C++, FORTRAN, and so on.

Development Process for Embedded SQL Applications

image.png

Warning: Not all RDBMSs communicate with the server to compile at this time.

Note

❗

Concept Explanation from ChatGPT:

Components:

  1. <app>.sqc (C+EmbeddedSQL):
    • This file contains your application code written in C (or another host language), with SQL queries embedded within the code. The .sqc extension typically stands for SQL/C and indicates that this is source code mixing SQL with C.
  2. Preprocessor:
    • The preprocessor is a tool that processes your source code file before it is compiled by the standard C compiler. The role of the preprocessor is to parse the embedded SQL statements and convert them into standard C calls that can interact with the database. This is necessary because a standard C compiler does not understand SQL syntax.
  3. <app>.c (generated C):
    • This is the output from the preprocessor. It is a pure C program that has been generated from the .sqc file. All SQL commands have been replaced by calls to the database interface functions, typically provided by a database connectivity library like ODBC (Open Database Connectivity) or JDBC (for Java).
  4. RDBMS (Relational Database Management System):
    • This is the database system that manages the data. Your application communicates with the RDBMS via API calls generated by the preprocessor. These calls are formulated to query or manipulate the database as originally specified by the SQL embedded in your C code.

Process Flow:

  • Step 1: You write your application logic in a file that mixes C code with SQL queries (<app>.sqc).
  • Step 2: The preprocessor reads this file and translates all SQL queries into appropriate function calls that the C compiler can understand. This translation involves converting SQL commands into calls to a database API.
  • Step 3: The output of the preprocessor is a standard C program (<app>.c) that includes database API calls but no direct SQL code.
  • Step 4: This C program is then compiled into an executable that, when run, interacts with the RDBMS to perform database operations.
  • some products simply generate a CLI interface, e.g., ODBC

image.png

Reminder: For DB2, will be bound to a particular server engine.

How much does the DBMS know about an application?

  • precompiling: PREP
  • binding: BIND

Application Structure

image.png

Note

❗

Concept Explanation from ChatGPT:

SQLCA (SQL Communication Area)

  1. Purpose:
    • The SQLCA is used to store status information that helps the application determine the outcome of the most recently executed SQL statement. This includes error codes, warning flags, and diagnostic messages.
  2. Key Fields:
    • sqlcode: A status code that indicates the success or type of error that occurred (e.g., 0 for success, negative for errors, positive for warnings).
    • sqlerrm: Contains an error message related to the last SQL operation.
    • sqlerrd: An array of integers that provides additional information about the execution of the SQL statement, such as the number of rows processed.
    • sqlwarn: An array of characters that serves as warning flags for various conditions in the SQL environment.
  3. Usage:
    • After each SQL statement, the SQLCA is automatically updated by the DBMS. The application can then inspect the SQLCA to determine the next steps, such as error handling or logging.

SQLDA (SQL Descriptor Area)

  1. Purpose:
    • The SQLDA is used for dynamic SQL operations where the number of columns, their data types, and sizes may not be known until runtime. It describes the columns of a result set or the parameters for a SQL statement, enabling the application to allocate appropriate memory and handle data types dynamically.
  2. Key Components:
    • sqln: Indicates the number of variable descriptors that the SQLDA can currently hold.
    • sqld: Represents the actual number of variables (columns or parameters) that are being described.
    • sqlvar: An array of descriptors, each of which contains information about a column in a result set or a parameter to a SQL statement. This includes data type, data length, buffer pointers, and indicators for null values.
  3. Usage:
    • In dynamic SQL, the application initially allocates an SQLDA with an estimated number of descriptors. After preparing a statement, the application can adjust the SQLDA based on the actual number of columns or parameters reported by the DBMS. The SQLDA can then be used to bind application variables to SQL statement parameters or to fetch column values into program variables.

Declarations

Include SQL communication area: EXEC SQL INCLUDE SQLCA;

Defines:

  • return code of SQL statements (sqlcode)
  • error messages (if any)
  • required

An SQL statement can be inserted by embedding the following where C statements can occur: EXEC SQL <sql statement>;

Host Variables

Host Variables

SQL statements can have parameters that are local or global variables in the embedding language.

  • Host variables communicate single scaler values between an SQL a statement and the embedding language.
  • Must be declared within an SQL declare section:
EXEC SQL BEGIN DECLARE SECTION;
*(only declarations of host variables in host language go here)*
EXEC SQL END DECLARE SECTION;
  • Can then be used in EXEC SQL statements.
  • To distinguish them from SQL identifiers, they are prefixed by “:”, the colon character.

Exception Handling

If a SQL statement fails:

  • Check sqlcode != 0, or
  • Use exception handling SQL commands:
 EXEC SQL WHENEVER SQLERROR GO TO <label>;
 EXEC SQL WHENEVER SQLWARNING GO TO <label>;
 EXEC SQL WHENEVER NOT FOUND GO TO <label>;

<label> must be in scope

Sample 1 - Connecting to a Database

#include <stdio.h>
#include <stdlib.h>
#include "util.h"
EXEC SQL INCLUDE SQLCA;
int main(int argc, char *argv[]) {
   char * getpass();
   EXEC SQL BEGIN DECLARE SECTION;
      char db[6] = "cs348";
   EXEC SQL END DECLARE SECTION;
   printf("Sample C program: CONNECT\n" );
   EXEC SQL WHENEVER SQLERROR  GO TO error;
   EXEC SQL CONNECT TO :db;
   printf("Connected to DB2\n");
   EXEC SQL COMMIT;
   EXEC SQL CONNECT reset;
   exit(0);
error:
   check_error("My error",&sqlca);
   EXEC SQL WHENEVER SQLERROR CONTINUE;
   EXEC SQL ROLLBACK;
   EXEC SQL CONNECT reset;
   exit(1);
}

Preparing your Application in DB2

  1. Write the application in a file called <name>.sqc
  2. Preprocess the application: db2 prep <name>.sqc
  3. Compile the application: cc -c -O <name>.c
  4. Link with DB2 libraries: cc -o <name> <name.o> -L... -l...
  5. Run it: ./<name> [arguments]

Note: there is a Makefile to do this. make all NAME=sample1

DB2PATH = /home/db2inst2/sqllib
# The following compile and link options are for the gcc
CC=gcc

CFLAGS=-I$(DB2PATH)/include
#LIBS=-L$(DB2PATH)/lib -R$(DB2PATH)/lib -ldb2
LIBS=-L$(DB2PATH)/lib -ldb2

all: $(NAME)

$(NAME): $(NAME).sqc util.o
    db2 connect to cs348
    db2 prep $(NAME).sqc bindfile
    db2 bind $(NAME).bnd
    db2 connect reset
    $(CC) $(CFLAGS) -c $(NAME).c
    $(CC) $(CFLAGS) -o $(NAME) $(NAME).o util.o $(LIBS)

clean:
    rm -f $(NAME) $(NAME).c $(NAME).o $(NAME).bnd

util.o : util.c
    $(CC) -c util.c $(CFLAGS)

Sample 2 - Executing Simple Statements

// A program that prints out the title of the publication for each publication identifier supplied as an argument.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "util.h"
EXEC SQL INCLUDE SQLCA;
int main(int argc, char *argv[]) {
   int i;
   char pubidstr[10];
   EXEC SQL BEGIN DECLARE SECTION;
      char db[6] = "cs348";
      int pubid;
      char title[30];
   EXEC SQL END DECLARE SECTION;
   printf("Sample C program: SAMPLE2\n" );
   EXEC SQL CONNECT TO :db;
   printf("Connected to DB2\n");
   EXEC SQL WHENEVER SQLERROR  GO TO error;
   for (i=1; i<argc; i++) {
     strncpy(pubidstr,argv[i],8);
     pubid = atoi(pubidstr);
     EXEC SQL WHENEVER NOT FOUND GO TO nope;
     EXEC SQL SELECT title INTO :title
              FROM   publication
              WHERE  pubid = :pubid;
     printf("%d: %s\n",pubid,title);
     continue;
   nope:
     printf("%d: *** not found *** \n",pubid);
   };
   EXEC SQL COMMIT;
   EXEC SQL CONNECT reset;
   exit(0);
error:
   check_error("My error",&sqlca);
   EXEC SQL WHENEVER SQLERROR CONTINUE;
   EXEC SQL ROLLBACK;
   EXEC SQL CONNECT reset;
   exit(1);
}

It is important that at most one title is returned for each publication identifier.

NULLs and Indicator Variables

What if a result value is NULL?

  • not a valid value in the datatype

Embedded SQL uses an extra indicator variable, e.g.:

short ind; // (within a BEGIN/END DECLARE SECTION)
...
EXEC SQL SELECT firstname INTO :firstname INDICATOR :ind
FROM ... ;
  • If ind < 0 then firstname is NULL.

If the indicator variable is not provided and the result is a null we get an run-time error.

The same rules apply for host variables in updates.

Impedance Mismatch

Impedance mismatch is a term used in computer science to describe the problem that arises when two systems or components that are supposed to work together have different data models, structures, or interfaces that make communication difficult or inefficient.

In the context of databases, impedance mismatch refers to the discrepancy between the object-oriented programming (OOP) model used in application code and the relational model used in database management systems (DBMS). While OOP models are designed to represent data as objects with properties and methods, relational models represent data as tables with columns and rows.

Cursor is the solution to Impedance Mismatch!

SQL CURSOR

For SQL queries possibly returning more than one answer, an SQL CURSOR is defined and used in an iterator protocol.

  1. Declaring a cursor:
EXEC SQL DECLARE <name> CURSOR FOR <query>;
  1. An iterator protocol using a cursor:
EXEC SQL OPEN <name>;
EXEC SQL WHENEVER NOT FOUND GO TO end;
for (;;) {
    <set up host parameters>
    EXEC SQL FETCH <name> INTO <host variables>;
    <process the fetched tuple>
};
end:
EXEC SQL CLOSE <name>;

or

EXEC SQL DECLARE <id> CURSOR FOR <qry>;
EXEC SQL OPEN <id>;
do {
    EXEC SQL FETCH <id> INTO <vars>;
} while (SQLCODE == 0);
EXEC SQL CLOSE <id>;

Sample 3 - Executing Queries with Many Answers

// A program that lists all author names and publication titles with author name matching a pattern given as an argument.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "util.h"
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
   char db[6] = "cs348";
   char title[72], name[20], apat[10];
   short aid;
EXEC SQL END DECLARE SECTION;
int main(int argc, char *argv[]) {
   if (argc!=2) {
      printf("Usage: sample3 <pattern>\n");
      exit(1);
   };
   printf("Sample C program: SAMPLE3\n" );
   EXEC SQL WHENEVER SQLERROR  GO TO error;
   EXEC SQL CONNECT TO :db;
   printf("Connected to DB2\n");
   strncpy(apat,argv[1],8);
   EXEC SQL DECLARE author CURSOR FOR
             SELECT name, title
             FROM author , wrote, publication
             WHERE name LIKE :apat
               AND aid=author
               AND pubid=publication;
   EXEC SQL OPEN author;
   EXEC SQL WHENEVER NOT FOUND GO TO end;
   for (;;) {
     EXEC SQL FETCH author INTO :name, :title;
     printf("%10s -> %20s: %s\n",apat,name,title);
     };
end:
   EXEC SQL CLOSE author;
   EXEC SQL COMMIT;
   EXEC SQL CONNECT RESET;
   exit(0);
error:
   check_error("My error",&sqlca);
   EXEC SQL WHENEVER SQLERROR CONTINUE;
   EXEC SQL ROLLBACK;
   EXEC SQL CONNECT reset;
   exit(1);
}

Note

❗

ChatGPT said (Why we need EXEC SQL WHENEVER SQLERROR CONTINUE in the error label?):

In the provided code, the line:

EXEC SQL WHENEVER SQLERROR CONTINUE;

ensures that cleanup operations (like ROLLBACK and CONNECT reset) in the error: block execute even if SQL errors occur during cleanup. Without it, the program might re-trigger the error: block on errors during these commands, causing infinite loops or incomplete cleanup. It guarantees a proper and graceful exit.

Stored Procedures

Note

A stored procedure executes application logic directly inside the RBDMS server.

Reasons for stored procedures:

  • Reduces volume of data transfer between client and server;
  • Centralizes application code at the server; and
  • Conceptual schema enhancement.

Examples

Atomic-Valued Function

  • A function that returns a single, indivisible value
CREATE FUNCTION sumSalaries(dept CHAR(3))
    RETURNS DECIMAL(9,2)
LANGUAGE SQL
RETURN
    SELECT sum(salary)
    FROM employee
    WHERE workdept = dept

We can execute this function like this:

db2 => SELECT deptno, sumSalaries(deptno) AS sal \
        => FROM department

Table-Valued Function

CREATE FUNCTION deptSalariesF(dept CHAR(3))
    RETURNS TABLE(salary DECIMAL(9,2))
    LANGUAGE SQL
RETURN
    SELECT salary
    FROM employee
    WHERE workdept = dept

We can execute this function like this:

db2 => SELECT * FROM TABLE \
        => (deptSalariesF(CAST(’A00’ AS CHAR(3)))) AS s

Branching

CREATE PROCEDURE UPDATE_SALARY_IF
        (IN employee_number CHAR(6), INOUT rating SMALLINT)
    LANGUAGE SQL
BEGIN
    DECLARE not_found CONDITION FOR SQLSTATE ’02000’;
    DECLARE EXIT HANDLER FOR not_found
        SET rating = -1;
    IF rating = 1 THEN
        UPDATE employee
        SET salary = salary * 1.10, bonus = 1000
        WHERE empno = employee_number;
    ELSEIF rating = 2 THEN
        UPDATE employee
        SET salary = salary * 1.05, bonus = 500
        WHERE empno = employee_number;
    ELSE
        UPDATE employee
        SET salary = salary * 1.03, bonus = 0
        WHERE empno = employee_number;
    END IF;
END