Ingres Q & A
=========================================================================
An Introduction/Guide on How to Program in C with Embedded SQL Statements
=========================================================================
(The follow sample program is stored as sample.sc. You can type 'make'
to compile it. This should be a good starting point for you to write
your embedded sql codes.)
Step 1: Writing embedded SQL code in C programs
To write C-code which manipulates the database, there are a few special
steps that are needed to be taken to ensure your program communicating with
INGRES System smoothly. The following program will serve as an example:
Sample program:
#include <stdio.h>
(1) EXEC SQL BEGIN DECLARE SECTION;
int status;
char s_num[6];
char sname[20];
EXEC SQL END DECLARE SECTION;
(2) EXEC SQL INCLUDE SQLCA;
(3) EXEC SQL WHENEVER SQLERROR GOTO Stmt_Err;
(4)
/*
** Procedure : Print_Error
** Purpose : SQLCA error detected. Retrieve the error message and
** print it.
** Parameters: None
**
*/
#define ERROR_BUFFER 150
void
Print_Error()
{
EXEC SQL BEGIN DECLARE SECTION;
char error_buf[ERROR_BUFFER]; /* For error text retrieval */
EXEC SQL END DECLARE SECTION;
EXEC SQL INQUIRE_SQL (:error_buf = ERRORTEXT);
printf("\nSQL Error:\n %s\n",error_buf);
} /*Print_Error */
main()
{
(5) EXEC SQL CONNECT testdb;
scanf("%d", &status);
(6) EXEC SQL UPDATE S
SET sname = 'wan'
WHERE status = :status;
(7) EXEC SQL DECLARE C1 CURSOR FOR
SELECT SNAME, STATUS
FROM S
WHERE s# > '50544';
(8) EXEC SQL OPEN C1;
(9) EXEC SQL WHENEVER NOT FOUND GOTO Label;
for (;;)
{
printf("Fetching : ");
(10) EXEC SQL FETCH C1 INTO :sname, :i_status;
printf("name : %s\n", sname);
}
Label:
(11) EXEC SQL CLOSE C1;
(12) EXEC SQL DISCONNECT ;
exit(0);
Stmt_Err:
EXEC SQL WHENEVER SQLERROR CONTINUE;
Print_Error();
}
The basic structure of the program is the same as a standard C program,
but in order to communicate with INGRES a few extra commands need to be
introduced:
(1) Declaration section
Declaring all variables that will be used to communicate with the
SQL statements, e.g. to hold the tuple values from a selection; to
specify parameter for a query/update; to store values to be inserted
etc.
Each type in INGRES relation's attribute should correspond nicely to
a specific data type of C, except date, which should probably be
converted into character strings.
The syntax of the declaration section should be:
EXEC SQL BEGIN DECLARE SECTION;
<declarations>
EXEC SQL END DECLARE SECTION;
(2) EXEC SQL INCLUDE SQLCA;
This statement tells the program to include SQLCA, the structure
that hold the error and status information returned by INGRES. This
statement should always be included.
(3) EXEC SQL WHENEVER SQLERROR <action>;
This statement tell the program what to do when an error occurs
while trying to execute a SQL command, where <action> can be
stop -- to stop execution of program.
GOTO <label> -- will call a branch to the <label> where execution of
the program will continue.
(4) A standard SQL Error Detection Procedure.
This procedure is called when a SQL error occurs. It prints out
the appropriate informations about the error so that you can modify your
program easily.
(5) EXEC SQL CONNECT <database name>;
This command connects the program to the database you just created.
Note the database name is case sensitive.
(6) UPDATE, INSERT or DELETE
These commands can be written simply as their SQL syntaxs, with EXEC
SQL added in the front:
EXEC SQL UPDATE S
SET sname = 'WAN'
WHERE status = :status;
If program variables need to be used in the SQL statements, they
must be preceded by a colon (e.g. :status), and of course they must have
been declared in the declaration section.
(7) - (11) Execute a database query
There are a few steps needed to execute a database query:
Firstly, a CURSOR needed to be set up. A cursor is an area reserved
in memory where the tuples retrieved from a query are stored. The tuples
selected will be stored in the cursor and then fetched to the program
one tuple at a time.
To declare a cursor, use the DECLARE statement:
EXEC SQL DECLARE <cursor name> CURSOR FOR <select_statement> ;
Once again, if program variables need to be used, they must be
preceded by a colon (eg :status).
Next the cursor needs to be opened by the OPEN statement,
EXEC SQL OPEN <cursor name>;
Note that there may be a upper limit to the number of cursor allowed
to be opened. So after finished using a cursor, it is always good
practice to close it:
EXEC SQL CLOSE <cursor name>;
Now, the cursor has been opened, the next thing you need to do is to
tell the program what to do when all the matched tuples has been read:
EXEC SQL WHENEVER NOT FOUND GOTO <label>;
This statement tells the program what to do when there is no more
tuples to fetch.
After all the above declaration, it is now ready to fetch the
tuples. The command for fetching a tuple is:
EXEC SQL FETCH <cursor name> INTO <program variable list>;
The list of the program variables must match the number of
attributes being selected, as well as the types. Once again, a colon is
needed to be attached to the beginning of each variable.
Normally the user wants to execute a select command, and
process each tuple in some manner. A for-loop, such as one above, keep
fetching the tuples until all have been fetched, and then branched to
the label 'label' (thus exiting the loop), is a good idea. However, be
careful: the loop will terminate only at the FETCH statement when there is
no more tuples to fetch. Thus in the example, if 5 tuples is fetched by
the select statement, "Fetching : " will be printed 6 (not 5) times.
(12) Closing a database
EXEC SQL DISCONNECT ;
This statement will disconnect(close) the database.
------------
Step 2: Compiling and running the C program
a) : All files with the programs should be with suffix .sc
b) : After a program have been written, issue the command
> esqlc <filename>.sc
A <filename>.c file will appear.
"esqlc" is the command of the Embedded SQL Preprocessor.
c) : Compile the .c file with this command
>cc -o <output filename> <filename>.c /usr/kits/sql/ingres/lib/libsql.a -lm
The extra stuff at the end is to ensure all the relevant
procedures are linked to the program.
Also you may experience in some cases that the compilation will fail
because of "file size exceeded". You can change the file size limit by
>limit filesize 1000 (or some other number, 1000 should be OK)
If you know how to use the makefile, you can write down your own
makefile following the example:
# INGRES.PLUS makefile
CC = cc
SCC = esqlc
LIBS = $(II_SYSTEM)/sql/lib/libsql.a \
/usr/lib/libm.a \
/usr/lib/libc.a
OBJ= test.o
test: $(OBJ)
$(CC) -o test $(OBJ) -ll $(LIBS)
test.o: test.c
$(CC) -c -g test.c
test.c: test.sc
$(SCC) test.sc
d) :After preprocessing and compiling your C program with Embedded SQL
Statements, you may run your program now.
-------------
Ingres Q & A
To William's Home Page
© William Yuan 2000
Email William