SQL

Embedded SQL

Embedded SQL is a method of combining the computing power of a programming language and the database manipulation capabilities of SQL.

Here’s the high level idea:

Commands

Here are the different command types that you can simply plug into your C++ code

At the top of your main function, you need to Include SQL Communication Area:

EXEC SQL INCLUDE SQLCA;

SQLCA

SQLCA stands for SQL Communications Area. It is a data structure used in programming with Embedded SQL, which is a way of embedding SQL statements within a high-level programming language such as C or COBOL.

EXEC SQL <sql statement>;

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

EXEC SQL BEGIN DECLARE SECTION;
  char db[6] = "cs348";
EXEC SQL END DECLARE SECTION;

You can then use them in EXEC SQL statements. To distinguish them from SQL identifiers, they are prefixed by :. For example,

EXEC SQL CONNECT TO :db;

Exception Handling if a SQL statement fails, you can either check sqlcode != 0, or use exceptional 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>;

• Note that <label> must be in scope.

Full Example sample1.sqc:

#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);
}

More Advanced Example, storing data

Notice a few things:

• use the INTO keyword to store into variable

“Write 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;
 
   EXEC SQL BEGIN DECLARE SECTION;
      char db[6] = "cs348";
      char title[72];
      int pubid;
   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++) {
     pubid = atoi(argv[i]);
     
     EXEC SQL WHENEVER NOT FOUND GO TO nope;
     EXEC SQL SELECT title INTO :title
              FROM   publication
              WHERE  pubid = :pubid;
     
     printf("%4d: %s\n",pubid,title);
     continue;
   nope:
     printf("%4d: *** 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);
}

DANGER!

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

Then, you can run

./sample2 1 7 3 5

It should print out:

Sample C program: SAMPLE2
Connected to DB2
1: Mathematical Logic
7: *** not found ***
3: Principles of DB Systems
5: *** not found ***

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>;

2. 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>;

“Write 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);
}

Execution:

./sample3 "%"
Sample C program: SAMPLE3
Connected to DB2
% -> Sue: Mathematical Logic
% -> Sue: Trans. on Databases
% -> Sue: Query Languages
% -> John: Trans. on Databases
 
./sample3 "%u_"
Sample C program: SAMPLE3
Connected to DB2
%u_ -> Sue: Mathematical Logic
%u_ -> Sue: Trans. on Databases
%u_ -> Sue: Query Languages

Preparing the Application in DB2

How much does the DBMS know about an application? ⇒ precompiling: PREP ⇒ binding: BIND

Steps:

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]

In practice, the above steps is done with a Makefile.

# put the name of your application here:
NAME= sample1
 
###############################################
# don't touch anything below this line
###############################################
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) -w -c util.c $(CFLAGS)