Contents
Embedded SQL is a method to combine the computing power a high level language like C/C++ and the database manipulation capabilities of SQL. It allows you to execute any SQL statement from an application program.
The embedded SQL programs are compiled in two steps
All the SQL statements need to start with EXEC SQL and end with a semicolon. You can place the SQL statements anywhere in the code, with the restriction that the declarative statements do not come after the executable statements (a C syntax violation)
The SQL statements allowed by ProC are (in alphabetic order)
All SQL stetements are embedded as
EXEC SQL .... ;
int a;
. . .
EXEC SQL SELECT salary INTO :a
FROM Employee
WHERE SSN=876543210
;
printf("The salary is %d\n", a);
. . .
The preprocessor directives that can be used with ProC are
You cannot use macros with ProC The following code is invalid
#define THE_SSN 876543210
EXEC SQL SELECT salary INTO :a
FROM Employee
WHERE SSN = THE_SSN
; /* invalid usage */
You can connect C labels with SQL as in
EXEC SQL WHENEVER SQLERROR GOTO error_in_SQL;
...
error_in_SQL:
/* Do something */
We will come to what WHENEVER means a little later.
Host variables are the key to communication between the host program and Oracle. A host variable can be any arbitrary C expression that resolves to an lvalue.
You declare host variables according to the rules of C, as you declare the regular C variables. The C datatypes that can be used with Oracle are
You cannot use register storage-class specifier for the host variables.
Referencing host variables in SQL
A host variable must be prefixed with a colon (:) in SQL statements but must not be prefixed with a colon in C statements. The example above illustrated the use of host variables.
Restriction : A host variable needs to resolve to an address, so function calls and numeric expressions cannot be used for host variables. The following code is invalid:
int get_dept();
...
main()
{
int x;
...
EXEC SQL INSERT INTO emp (empno, ename,
deptno)
VALUES
(:x * x, 'BIG SHOT', :get_dept());
/* All 3 values are invalid
as host variables,
the first two
are expressions without l-values and
the third is
a function call */
...
}
You define pointer variables following the normal C practice, and in SQL statements prefix them with a colon as in
int *x;
...
EXEC SQL SELECT xyz INTO :x FROM .....
You can associate every host variable with an optional indicator variable. An indicator variable must be defined as a 2-byte int (using the type short) and, in SQL statements, must be prefixed by a colon and must immediately follow its host variable. Or you may use the keyword INDICATOR in between the host variable and indicator variable. Examples are
Using indicator variables
You use indicator variables in the VALUES and SET clause of INSERT or UPDATE statements to assign nulls to input host variables and in the INTO clause of SELECT statements to detect nulls or truncated values in output host variables.
Structures can be used as host variables as in the following example
typedef struct {
char name[21]; /* one greater than the column length
*/
int SSN;
} Emp;
...
Emp bigshot;
EXEC SQL INSERT INTO emp (ename, eSSN)
VALUES (:bigshot)
;
Arrays of host variables can be used as in the following
int emp_number[50];
char name[50][11];
...
EXEC SQL INSERT INTO emp(emp_number, name)
VALUES (:emp_number,
:emp_name) ;
which will insert all the 50 tuples in one go.
Arrays can only be single dimensional. The example char name[50][11] would seem to contradict that rule. However ProC considers name a one dimensional array of strings rather than a two dimensional array of characters. Also you can have an array of structs.
When using arrays to store the results of a query, in case the size of the array is smaller than the number of tuples outputted by the query, the first N tuples will be entered in the host array.
At precompile time, a default Oracle external datatype is assigned to each host variable. Datatype equivalencing allows you to override this default equivalencing and lets you control the way Oracle interprets the inputs and formats the output data.The equivalencing can be done on a variable-by-variable basis.
EXEC SQL VAR host_variable IS type_name [ (length)
] ;
EXEC SQL TYPE user_type ID type_name [ (length) ] [REFERENCE]
;
SQLCA - SQL Communications Area is used to detect errors and status changes in your program. This structure contains components that are filled in at runtime after the SQL statement is processed by Oracle.
To use it you need to include the file sqlca.h , using the #include command. In case you need to include the sqlca at many places you need to undefine the macro SQLCA by using #undef SQLCA
Oracle updates the sqlca after every executable SQL statement. By checking the return codes stored in the sqlca your program can find the status in two ways
Header file
The relevant chunk of the header file sqlca.h follows:
#ifndef SQLCA
#define SQLCA 1
struct sqlca {
/* ub1 */ char sqlcaid[8];
/* b4 */ long sqlabc;
/* b4 */ long sqlcode;
struct {
/* ub2 */ unsigned short sqlerrml;
/* ub1 */ char sqlerrmc[70];
sqlerrm;
/* ub1 */ char sqlerrp[8];
/* b4 */ long sqlerrd[6];
/* ub1 */ char sqlwarn[8];
/* ub1 */ char sqlext[8];
};
...
The fields in sqlca have the following meaning
| sqlcaid | This string component is initialized to "SQLCA" to identify the comm area | ||||||||||||||||
| sqlcabc | This holds the length in bytes of the sqlca structure | ||||||||||||||||
| sqlcode | Status code of the most recently executed SQL statement
|
||||||||||||||||
| sqlerrm |
|
||||||||||||||||
| sqlerrp | Future use | ||||||||||||||||
| sqlerrd | Array of binary integers has 6 elements
|
||||||||||||||||
| sqlwarn | This array fo single characters has eight elements used as warning
flags. Oracle sets a flag by assigning 'W' to these.
|
||||||||||||||||
| sqlext | Reserved for future use |
Text of error messages
The sqlca can accomodate error messages upto 70 characters long. To get the full text of longer (or nested) error messages, you need the sqlglm function.
void sqlglm(char *message, size_t *buffer_size, size_t *message_length)
where:
| message_buffer | Is the text buffer in which you want Oracle to store the text. |
| buffer_size | Maximum size of buffer in bytes |
| message_length | The actual length of the error-message, returned by Oracle. |
The maximum length of an Oracle error message is 512 bytes including the error code, nested messages, and message inserts as table and column names.
This statement allows you to do automatic checking and error handling. The syntax is
EXEC SQL WHENEVER <condition> <action> ;
Conditions
Actions
Examples
Use example
/* Code to find student name given his id */
int id;
...
for (;;){
printf("Give student id number : ");
scanf("%d", &id);
EXEC SQL WHENEVER NOT FOUND GOTO
notfound;
EXEC SQL SELECT studentname INTO :st_name
FROM
studentrec
WHERE
studentid = :id ;
printf("Name of student is %s\n", st_name);
continue;
notfound:
printf("No record exists for id %d\n", id);
}
To get the precompiler to generate appropriate C++ code you need to be aware of the following considerations
So C++ users must specify PARSE=NONE or PARTIAL.
They therefore lose the freedom
to declare host variables anywhere in the code.
Rather, the host variables must be encapsulated
in Declare sections.
Thus, C++ users will declare their
host and indicator variables as:
EXEC SQL BEGIN DECLARE SECTION;
// All the declarations
...
EXEC SQL END DECLARE SECTION;
You need to follow this routine for declaring the host and indicator variables
at all the places you do so.
The demo programs are available in the directory /usr/class/cs145/code/proc named sample*.pc (for C users) and cppdemo*.pc (for C++ users). pc is an extension for pro*C code. Please don't copy the files manually , since there are a couple of customizations to do. So, to download the sample programs and customize them, just:
source cora.env load_samples <db_username> <db_passwd>
<sample_dir>,
(e.g., load_samples scott@cs tiger cs145_samples) cd <sample_dir> make samples (or make cppsamples
for C++) to compile all the
samples. Step (1) will create a new directory as specified in <sample_dir> and copy the sample files to the directory. It will also change the user name and passwd in the samples to be yours, so that you don't have to type in the oracle username and password everytime when running a sample. However, sample1 and cppdemo1 do provide an interface for user to input the username and password, in case you'd like to learn how to do it.
If you happen to make any mistake with the entered username or passwd in
Step (1), just run clean_samples <db_username> <db_passwd>
<sample_dir>
to uninstall the sample files and redo Step (1).
For step (4), you can also compile each sample seperately. For example,
make sample1 compiles sample1.pc seperately. What it does is to
generate executables for sample1 using the Makefile.
The compilation proceeds in 2 phases:
To compile your own files, just change a few variables in Makefile --
add your program name foo to variable SAMPLES and the source code
name foo.pc to variable SAMPLE_SRC. Then, do make foo after
foo.pc is ready. foo.pc will be precompiled to
foo.c and then compiled to foo, the executable.
C++ users will need to add their program name to CPPSAMPLES instead of
SAMPLES, and source filename to CPPSAMPLE_SRC instead of SAMPLE_SRC.
The sample programs operate on the following database:
CREATE TABLE DEPT
(DEPTNO NUMBER(2) NOT
NULL,
DNAME VARCHAR2(14),
LOC VARCHAR2(13) )
CREATE TABLE EMP
(EMPNO NUMBER(4)
NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,
2),
COMM NUMBER(7,
2),
DEPTNO NUMBER(2)
)
These tables are created automatically after you run load_samples
in Step (1). A few tuples are inserted. You may like to browse the tables
before running the samples on them. You can also play with them as you like
(e.g., adding or changes tuples).
clean_samples.
You should take a look at a sample's source code before running it. The comments at the top of the source code tell what the sample program does. For example, what sample1 does is to take an employees EMPNO and get the name, salary and commision for that employee from the table EMP.
You are supposed to look at the source code of the samples and learn the following.
Then, you can use these techniques to code your own PDA interface program. and have fun!