SQL can be used interactively within a database product, such as Oracle, to create and manipulate a database. It can also be used within SQL-based programming environments, such as the procedural language PL/SQL. However, another way in which SQL can be used is embedded within another programming language, such as C, FORTRAN, APL, PL/I, or COBOL (among others). When used this way, it is referred to as embedded SQL.
In the embedded approach, SQL statements are placed directly into the host-language source code, and intermixed with the other programming language statements. Special embedded SQL statements are used to retrieve data into the program. A language-specific precompiler accepts the combined code and, along with other programming tools, converts it into an executable program. This type of blending of languages is an effective way to program; the host-language provides flow of control, variables, block structure, and I/O functions while SQL handles database access.
The remainder of this section will discuss how to interact with the Oracle database product by using embedded SQL with C as the host-language.
An embedded SQL program contains both SQL and regular programming statements. Because of this it can not be directly submitted to the compiler. Instead, there is a multi-step development process that goes through the following steps:
1. The embedded SQL program is submitted to the precompiler. The precompiler finds the embedded SQL statements and processes them by converting them to the desired target language. A different precompiler is required for each target language.
2. The file generated by the precompiler is submitted to the regular compiler for the host programming language. At this stage the compiler is usually made to generate an object file, not an executable program.
3. Any number of object files can be linked together to create a single executable program. At this stage all the user-created object files and libraries for the database product are linked together to create the final program.
Important Note:
This is the process required for the Oracle implementation on Zeus. Other database installations might require different procedures for the implementation of embedded SQL programs.
It is important to keep in mind that all embedded SQL statements begin with the key words:
exec sql
The precompiler searches for these keywords and converts the included SQL into the appropriate C code. The exec statement is still contained in the output host language file as a comment.
Important Note:
Recall that this document will focus on using embedded SQL with C as the host-language.
Embedded SQL is not case-sensitive. That is, you can use upper or lower case in order to make your embedded statements more readable. Most programmers tend to use upper case for all embedded SQL statements.
The SQL Communications Area, or SQLCA, is a very complex data structure that makes embedded SQL possible. Essentially, the SQLCA structure contains error variables and status indicators. By examining the SQLCA, the application program can determine the success or failure of the embedded SQL commands and act accordingly. To include an SQLCA in your program, you must issue the following statement:
exec sql include sqlca;
This includes the SQLCA definition into your program, defined accordingly for your host programming language. The SQLCA structure may differ from one database product to another -- but this does not matter since, as a programmer, you will interact with the SQLCA through much higher-level embedded SQL statements. The precompiler will process the embedded SQL commands and interact with the SQLCA accordingly. Because of this, embedded SQL programs are highly portable from one DBMS to another.
SQLCODE and SQLSTATE are variables defined within the SQLCA data structure. As a programmer, you should not need to interact directly with these variables. However, after each SQL statement is processed, the DBMS sets the values of these variables. To see how they are used you should read section 7.16.7.4 Coping With Errors, or take a look at the host language output file produced by your precompiler.
To simplify error handling we can use the WHENEVER statement. This is a directive to the SQL precompiler to automatically generate error handling code following every executable embedded SQL statement. It also specifies what the generated code should do.
There are 3 different exception conditions that can occur:
whenever sqlerror Tells the precompiler to generate code to handle
errors (a negative SQLCODE).
whenever sqlwarning Handle warnings.
whenever not found Handle the condition resulted when an attempt to
retrieve information fails.
Exception
conditions for embedded SQL.
The WHENEVER conditions can be set up to call an appropriate function or to execute a particular statement. Following are a few examples of various WHENEVER statements.
This WHENEVER statement issues a C command when the NOT FOUND condition occurs:
EXEC SQL WHENEVER NOT FOUND DO break;
The following statement jumps to a label named "NotFound" when the NOT FOUND condition is encountered:
exec sql whenever Not Found goto NotFound;
The following WHENEVER statement executes the function "sql_error()" with its specified parameters whenever the SQLERROR condition is met:
EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle Error");
Programs can be written that do not require the creation of any variables in the host programming language. However, these are limited to programs that do not retrieve and present any information (ie. only programs that create or delete data). To retrieve and present information you will need to create variables in the host language to correspond to the values in your tables.
The following SQL datatypes can be coded into the following C datatypes:
SQL Type C Type
INTEGER int
NUMBER(p,s) double
VARCHAR(n) struct { short len; char x[n+1]; }
DATE char x[12]; /* dd-mmm-yyyy */
Comparable SQL and C datatype types.
In some implementations you can use the regular SQL datatypes within the embedded SQL program and the precompiler will convert them to the regular host language types. However, this is not guaranteed to work from one DBMS to another.
When declaring your variables you must place them in a special SQL data declaration section. The following example defines and declares 3 SQL variables. They correspond to the CAR_VINFO table defined earlier. Notice how the SQL variables are located within a begin and end directives:
main()
{
/* Declare regular C variables ... */
int i, j;
char name1[11];
/* Declare embedded SQL variables ... */
exec sql begin declare section;
struct {
short len;
char x[31];
} vn;
struct {
short len;
char x[16];
} vt;
struct {
short len;
char x[16];
} vv;
exec sql end declare section;
...
Using C-style datatypes to represent SQL datatypes.
As mentioned above, the 3 structures could have been coded in the following manner:
main()
{
/* Declare regular C variables ... */
int i, j;
char name1[11];
/* Declare embedded SQL variables ... */
exec sql begin declare section;
varchar vn[31];
varchar vt[16];
varchar vv[16];
exec sql end declare section;
...
Using SQL datatypes within a C program.
and the precompiler would have converted them to the appropriate C types. In this case the VARCHAR types would have been converted to C data structures with a short component called "len" and a character component called "arr". However, this is not guaranteed behaviour for all precompilers and database products, so it is best to code for the host language.
Important Note:
The implementation of Oracle on Zeus supports the use of SQL types in the precompiler code.
To use a variable declared in the host language in an SQL statement, you must prefix that variable with a ":" character.
Even though you are coding SQL statements into a stand-alone executable program, you must still connect to a database in order to manipulate the data. This is accomplished through the following process:
1. Variables must be defined to hold your Oracle username and password:
varchar username[UNAME_LEN];
varchar password[PWD_LEN];
2. You must place your username into the username array and your password into the password array:
strncpy((char *) username.arr, "USER1", UNAME_LEN);
username.len = strlen((char *) username.arr);
strncpy((char *) password.arr, "PASS1", PWD_LEN);
password.len = strlen((char *) password.arr);
For the sake of security you may be prohibited from hard-coding the database password directly into the program. In this case you will have to prompt for a password or acquire it in some other fashion.
3. You must issue the SQL command that will connect you to the database:
exec sql CONNECT :username identified by :password;
printf("\nConnected to ORACLE as user: %s\n", username.arr);
Notice that the variables you have defined in your host language can be used within SQL statements as long as you prefix them with the ":" character.
The connection to the database may not have succeeded. In that case none of your SQL queries or commands will work properly since the database can not be accessed. If an error occurs, you should quit processing immediately.
Using embedded SQL you can create tables, insert and delete data from the tables, delete tables, issue queries, and so on. The syntax for the commands is very similar and in many cases identical to the syntax for the interactive SQL commands.
For example, to delete information from a table named CLERKS, you might use the following command:
exec sql delete from clerks where profit < 2500.00;
To insert information into a table named CITIES, you might do the following:
exec sql insert into cities
values('New York', 1, 20.2, 'east');
exec sql insert into cities
values('Toronto', 4, 13.1, 'central);
Notice that the embedded SQL command can exist on multiple lines. This is absolutely fine, as long as the command is terminated with a semicolon.
Important Note:
If you want any changes to the database to be permanent, you must make sure to COMMIT the data. This is done with the following command:
exec sql commit;
The same rules for ROLLBACK and COMMIT apply to embedded SQL as for non-embedded SQL (see section 7.16.3.8. Rollback and Commit).
To retrieve information you issue a SELECT statement. However, if you wish to display the information somehow, you must assign the returned values to variables declared in the host language. You can do this by specifying the variables in the SELECT statement after an INTO clause. The variables must be listed in the correct order and they must be prefixed by the colon character. For example:
exec sql select car_name, car_type, car into :vn.x, :vt.x, :vv.x
from car_vinfo
where car_type = 'sport';
At this point you can use the host-language variables for output. You do not need to prefix the variables with a colon when using them in the host-language. For example:
printf("Vehicle Name: %s\n", vn.x);
printf("Vehicle Type: %s\n", vt.x);
printf(" Vehicle: %s\n", vv.x);
As you can see, the embedded SQL statements are very similar to regular SQL.
If you want to retrieve more than one row of information from your database, you will need to define a cursor. A cursor in embedded SQL works exactly as a regular SQL cursor. To define a cursor, you must issue a DECLARE command:
exec sql declare repcurs cursor for
select car_name, car_type, car
from car_vinfo
where car_type = 'sport';
This declares a cursor named REPCURS that will SELECT the specified information from the named table, CAR_VINFO. The cursor can be used to retrieve multiple rows of information from the database by using the FETCH command:
exec sql fetch repcurs into :vn.x, :vt.x, :vv.x;
The information returned from the query is placed into host-language variables. From this point on, a report can be generated or any type of computation required can be performed. This is the benefit of using embedded SQL -- the power of SQL's data retrieval capabilities plus the capabilities of the host language to perform some types of compute-intensive tasks.
Just as in PL/SQL, you are required to OPEN and CLOSE a cursor before and after using it. This is done in exactly the same manner and is shown in the sample program in the next section.
The following program is a complete, working example. Following the listing is a description of what is going on and why:
1 #include <stdio.h>
2 #include <stdlib.h>
3
4 /* Define constants for USERNAME and PASSWORD lengths ... */
5 #define UNAME_LEN 20
6 #define PWD_LEN 40
7
8 void main()
9 {
10 exec sql include sqlca;
11
12 exec sql begin declare section;
13 struct {
14 short len;
15 char x[31];
16 } vn;
17 struct {
18 short len;
19 char x[16];
20 } vt;
21 struct {
22 short len;
23 char x[16];
24 } vv;
25
26 /* Reserve space for username and password to log into database. */
27 varchar username[UNAME_LEN];
28 varchar password[PWD_LEN];
29
30 exec sql end declare section;
31
32 /* Set up SQL interrupts ... */
33 exec sql whenever sqlerror goto error;
34 exec sql whenever not found goto done;
35
36 /* Connect to the database ... */
37 strncpy((char *) username.arr, "USER1", UNAME_LEN);
38 username.len = strlen((char *) username.arr);
39
40 strncpy((char *) password.arr, "PASS1", PWD_LEN);
41 password.len = strlen((char *) password.arr);
42
43 exec sql CONNECT :username identified by :password;
44
45 printf("\nConnected to ORACLE as user: %s\n", username.arr);
46
47
48 /* Declare a cursor for multiple-retrieve ... */
49 exec sql declare repcurs cursor for
50 select car_name, car_type, car
51 from car_vinfo
52 where car_type = 'sport';
53
54 /* Open the cursor ... */
55 exec sql open repcurs;
56
57 for (;;)
58 {
59 exec sql fetch repcurs into :vn.x, :vt.x, :vv.x;
60
61 printf("Display all Sports Cars ...\n");
62 printf("Vehicle Name: %s\n", vn.x);
63 printf("Vehicle Type: %s\n", vt.x);
64 printf(" Vehicle: %s\n", vv.x);
65 }
66
67
68 done:
69 /* All done ... no more data to retrieve so close the cursor and
70 * exit out of the program.
71 */
72 exec sql close repcurs;
73 printf("\n -- Finished Query -- \n");
74 exit(0);
75
76 error:
77 /* Some type of error occured so print out the error message and
78 * exit out of the program.
79 */
80 printf("\n -- SQL error %ld --\n",sqlca.sqlcode);
81 exit(0);
82 }
A complete, working embedded SQL program.
Following is a description of what is happening at specific points within the program listed above.
Line # Meaning
1 - 2 Include files required by the host language.
5 - 6 Define some constants -- not required, but it makes reading the
program a bit easier.
10 Include the SQLCA (communications area) definition.
12 Declare SQL variable declaration section.
13 - 24 Create 3 structures which correspond to the 3 VARCHAR types that
will be used in our SELECT statement. NOTE: Since these are
regular C-type struct statements, they do not really need to be in
this section. Alternatively, they could have been declared as
SQL-type VARCHAR statements.
27 - 28 Declare the username and password variables. NOTE: Since these
variables are declared using the SQL type VARCHAR, they need to be
declared within this section. Alternatively, they could have been
declared as regular C-type struct statements.
The 3 structures declared in lines 13 - 24 could have been
declared as VARCHAR. The precompiler would have translated them to
the appropriate C-type data structures. This is what occurred with
the 2 VARCHAR types declared in lines 27 and 28. Those 3
structures (lines 13 - 24) did not need to be declared within the
SQL declare section because they are completely declared in the
host-language. However, had they been declared as VARCHAR, it
would be required for them to be declared within the SQL declare
section.
30 End the SQL variable declaration section.
33 Set up the WHENEVER SQLERROR condition to jump to the label "error"
whenever an error occurs.
34 Set up the WHENEVER NOT FOUND condition to jump to the label "done"
if no more data can be retrieved.
Using GOTO statements is evil and should not be done in a
production environment -- it is used here to simplify the example.
In a real program it would be better to set up the WHENEVER
condition to execute a function instead of just jumping to a label.
37 - 41 place the username and password into the appropriate variables
In some working environments it may be prohibited to hard-code
usernames and/or passwords. In such a circumstance you may need to
use special coding routines, prompting from the user, or some other
technique to provide the username and password to the DBMS.
43 issue the SQL CONNECT command to connect to the DBMS. Notice that
the username and password variables are prefixed by a ":" because
they are host-language variables being used in an SQL statement.
45 If the CONNECT was successful, inform the user.
If the CONNECT or any other embedded SQL command was not
successful, an error message should be displayed and some kind of
code to handle the error should be executed. In this sample
program the WHENEVER condition defined in line 33 will execute the
code at the "error" label whenever any embedded SQL statement
generates an error.
49 - 52 Declare a cursor called REPCURS for the specified set of SQL
commands.
55 Open the cursor.
57 - 65 Start up an infinite loop. This loop should continue until all the
data is retrieved. When there is no more data we will exit the
loop via whatever was specified in the WHENEVER NOT FOUND condition.
59 Execute the cursor with the FETCH command. The values that are
returned should be placed into the host-language variables
specified after the INTO clause.
61 - 64 Display the values returned by the query. These are just simple
display statements, but any kind of computation required can be
done at this point.
65 End of the infinite loop.
68 The "done" label to be jumped to in the case that there is no more
data to be retrieved.
72 Close the cursor.
73 Display some kind of "program finished" statement.
74 Exit out of the program.
76 The "error" label to be jumped to in the case that some kind of SQL
ERROR occurs.
80 Print out the error code provided from the SQLCA data structure.
81 Exit out of the program.
Before you can run an embedded SQL program, you must precompile your program. A different precompiler is required for each different target or host-language. The precompiler required for translating embedded SQL and C programs into regular C/C++ code is "proc".
To precompile an embedded SQL and C program, do the following:
proc filename.pc
Notice the file extension of ".pc". This is the file extension required by the proc precompiler.
The proc program has a number of command line options that are beyond the scope of this section. However, if you would like to study these options, simply issue the proc command with no parameters:
proc
Important Note:
The FORTRAN precompiler is also installed on Zeus. To access it you must use the profor command and your source code must use the extension ".pfo".
The precompiler should generate a source code file in the appropriate host language; in this case, C. You must then compile the host language files into object files that can all be linked together to create the executable file. For example, consider the following example:
cc -c filename.c
The previous proc command translated the embedded SQL source code file into a regular C source code file -- all the special SQL constructs and high-level SQL commands were translated into their corresponding C code. The system standard C compiler should then be used to compile the file into an object file. The output of the above compilation is a file named "filename.o".
Depending on the type of development being done, you may have numerous object files created from a number of different source files that must be linked together, or you may just have one single source code file. In either case, it is recommended that you always create object files instead of executable files. Once your object files are created you can use the linker to link all the object files together along with Oracle libraries.
Oracle provides a large number of function calls, data structures, and so forth that are all used by embedded SQL programs. For your program to function, you must link in all these libraries. Unfortunately, the libraries on Zeus are not dynamically linkable, which means your final executable size is very large (usually larger than 2 megabytes). To link all the appropriate Oracle and C libraries to your object files, issue a command similar to the following:
cc -L/usr2/oracle/product/7.2.3/lib -o filename filename.o -lsql /usr2/oracle/product/7.2.3/lib/osntab.o -lsqlnet -lora -lsqlnet -lpls -lora -lnlsrtl3 -lc3v6 -lcore3 -lnlsrtl3 -lcore3 -lc -lsocket -lnsl -lm -lc -lsocket -lnsl -lm
Linking object files and Oracle libraries. It is easier to use the Makefile described below.
The first part of the command (in boldface) is the C compiler command being issued with the "-L" parameter which specifies the path to the Oracle libraries. The next (underlined) part specifies the output file name with the "-o" parameter. Following that, all the object files are specified (files with a ".o" extension). In this case, there only happens to be one. The remainder of the command lists all the libraries that are to be linked with the object files. The result should be a file with the name specified after the "-o" parameter.
An easier way to link is to use the special Oracle supplied Makefile. A Makefile is used in program development to make compilation and linking of programs much easier and faster. The Oracle supplied Makefile allows you, the programmer, to specify your object files and output file name and then it proceeds to build your executable program. This Makefile can be copied from the following directory:
/usr2/oracle/product/7.2.3/proc/demo/proc.mk
The Makefile is called "proc.mk" and is a fairly standard Makefile. To use it, issue the following command:
make -f proc.mk EXE=filename OBJS=filename.o
This is a simple Unix command -- the make program is called and made to execute the Makefile titled "proc.mk". Two parameters are passed to the Makefile; the name of the executable and the names of all the object files. If you had more than one object file, you would issue the command in the following manner:
make -f proc.mk EXE=filename OBJS="file-1.o file-2.o ... file-n.o"
Notice that the multiple object files are separated by spaces and enclosed within double-quote delimiters.
Important Note:
This is the process required for the Oracle implementation on Zeus. Other database installations might require different procedures for the implementation of embedded SQL programs.