|
DB2: Embedded SQL for C and C++
|
|
C & C++ Application Programs
| |
This
will walk you through "compiling" an embedded SQL
C program.
The Ramakrishnan text gives examples of writing embedded SQL
C and C++ programs,
and the text is basically correct for DB2's version
of embedded SQL in C
(and C++).
See the directories
- ~db2leduc/sqllib/samples/c/
and
- ~db2leduc/sqllib/samples/cpp/
for examples and compilation instructions for
C and C++,
respectively.
See below for another embedded SQL C program.
|
|
Embedded SQL for C
| |
- Connect.
One needs to first be connected to the database under which the
application program (that is, the embedded SQL C program)
is intended to run for the sake of the next two steps.
So
- % db2 connect to database
- Pre-compile.
One needs to pre-compile the embedded SQL code
to replace the embedded SQL with legitimate C calls.
This is done by
- % db2 precompile program.sqc bindfile
It is convention to name one's embedded SQL C
program with ".sqc" at the end to indicate
that it is an SQL-C program.
The command
- % db2 prep program.sqc bindfile
is identical.
The keywords "prep" and "precompile"
mean the same for DB2.
This pre-compilation step will create a program.c
file which is pure C code.
It has in it function calls that replace the embedded SQL
and will be linked via libraries in the linkage step below.
This step also creates a program.bnd
file, which is used in the bind step discussed next.
- Binding.
The bind step creates a package in DB2.
An application program (APP) must have a package
counterpart within DB2 to function.
The package defines in essence a profile
of the APP for the database.
In "binding" the package,
one can define many aspects of how the APP should be run
(for instance, isolation levels),
and permissions are checked and verified by DB2 at this stage.
Whenever the APP is run later,
it will be run with respect to the database as the DB2 user
who owns the package to which it is bound.
To bind the APP (and hence, to create a package) say
- % db2 bind program.bnd
Various options can be trailed on the command above.
These effect the package created,
which in turn effects how the APP is run with respect to the database.
Read the documentation on "bind" if you are curious.
The package created will have the same name as program.
Packages are like tables in a DB2 database.
They are objects which belong to schemas.
To see one's current packages, type
- % db2 list packages
To drop a package, say
- % db2 drop package
Of course, the associated APP will no longer work.
One would have to bind it again before it will work again.
Also note that there is no "create package ...".
Packages are created via "bind".
- Compile.
Finally, we are at the stage to compile the APP program itself.
- % gcc -I$DB2PATH/include -c program.c
When "~db2leduc/sqllib/prime" was sourced,
it set the environment variable DB2PATH.
All the libraries, includes, and such needed for the compile and linking
are located in the directory it indicates.
Note:
Just because the DB2 pre-compiler and the bind
may have not reported errors,
it does not mean that the C compiler will not report
errors to you here!
This is the first time your program has been parsed and compiled
by the C compiler,
and will be the first time you get a report of any
C-code problems.
- Linking.
In the final step,
one must link with the appropriate libraries to create the
executable APP.
- % gcc -o program program.o -L$DB2PATH/lib -ldb2
(Okay, so steps four and five could be combined.)
If all went well,
you now have a working executable APP called program.
|
|
Embedded SQL for C++
| |
This
is basically the same as for C
as described above.
Just replace the compiler "gcc"
with "g++" for C++.
The convention for naming the DB2 folks use for embedded SQL
C++ programs is to add the suffix
".sqC" to the program code file.
And they use the suffix ".C" for C++ programs.
|
|
Examples of embedded SQL C programs
| |
Here
is an example embedded SQL C program.
It does not do much, but is instructive.
Try pre-compiling / compiling it.
Connect to database c341f02 for this.
The APP queries a table sailor in schema one.
User one has granted select privileges to all
on table sailor, so the bind step will be legal.
Note:
To download an example,
follow the link to the raw text of the program
and save that page.
The title is a link to the raw text.
Do not cut-and-paste the code from this page!
Lines can get broken (that is, extra line-feeds inserted)
during a cut-and-paste,
and these can break the code.
|
sage.sqc
|
/*--------------------------------------------------------------------
EXAMPLE of an embedded SQL C Program for DB2.
Connect to database c3421m for this.
This APP takes one argument on the command line, a sailor's SID. It
then finds the sailor SID's age out of the table ONE.SAILOR (in
database c3421m) and reports it. A dumb and not very interesting
APP, but it shows how things are done.
P. Godfrey NOV 2002
--------------------------------------------------------------------*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlcodes.h>
#include <sys/time.h>
#define EXIT 0
#define NOEXIT 1
/*--------------------------------------------------------------------
Include DB2's SQL error reporting facility.
--------------------------------------------------------------------*/
EXEC SQL INCLUDE SQLCA ;
/*--------------------------------------------------------------------
Declare the SQL interface variables.
--------------------------------------------------------------------*/
EXEC SQL BEGIN DECLARE SECTION ;
short sage;
short sid;
char sname[16];
EXEC SQL END DECLARE SECTION ;
/*--------------------------------------------------------------------
Declare variables to be used in the following C program.
--------------------------------------------------------------------*/
char msg[1025];
int rc;
int errcount;
/*--------------------------------------------------------------------
This macro prints the message in the SQLCA if the return code is 0
and the SQLCODE is not 0.
--------------------------------------------------------------------*/
#define PRINT_MESSAGE() \
{ \
if (rc == 0 && sqlca.sqlcode != 0) \
{ \
sqlaintp(msg, 1024, 0, &sqlca); \
printf("%s\n",msg); \
} \
}
/*--------------------------------------------------------------------
This macro prints out all feilds in the SQLCA.
--------------------------------------------------------------------*/
#define DUMP_SQLCA() \
{ \
printf("********** DUMP OF SQLCA **********************\n"); \
printf("SQLCAID: %s\n", sqlca.sqlcaid); \
printf("SQLCABC: %d\n", sqlca.sqlcabc); \
printf("SQLCODE: %d\n", sqlca.sqlcode); \
printf("SQLERRML: %d\n", sqlca.sqlerrml); \
printf("SQLERRD[0]: %d\n", sqlca.sqlerrd[0]); \
printf("SQLERRD[1]: %d\n", sqlca.sqlerrd[1]); \
printf("SQLERRD[2]: %d\n", sqlca.sqlerrd[2]); \
printf("SQLERRD[3]: %d\n", sqlca.sqlerrd[3]); \
printf("SQLERRD[4]: %d\n", sqlca.sqlerrd[4]); \
printf("SQLERRD[5]: %d\n", sqlca.sqlerrd[5]); \
printf("SQLWARN: %s\n", sqlca.sqlwarn); \
printf("SQLSTATE: %s\n", sqlca.sqlstate); \
printf("********** END OF SQLCA DUMP *******************\n"); \
}
/*--------------------------------------------------------------------
This macro prints the message in the SQLCA if one exists. If the
return code is not 0 or the SQLCODE is not expected, an error occurred
and must be recorded.
--------------------------------------------------------------------*/
#define CHECK_SQL(code,text_string,eExit) \
{ \
PRINT_MESSAGE(); \
if (rc != 0 || sqlca.sqlcode != code) { \
printf("%s\n",text_string); \
printf("Expected code = %d\n",code); \
if (rc == 0) { \
DUMP_SQLCA(); \
} \
else printf("RC: %d\n",rc); \
errcount += 1; \
if (eExit == EXIT) goto errorexit; \
} \
}
/*--------------------------------------------------------------------
The PROGRAM.
--------------------------------------------------------------------*/
main (int argc, char *argv[])
{
/* Grab the first command argument. This is the SID. */
if (argc > 1) {
sid = atoi(argv[1]);
printf("SID requested is %d.\n", sid);
/* If there is no arguement, bail. */
} else {
printf("Which SID?\n");
exit(0);
}
EXEC SQL CONNECT TO C3421M;
CHECK_SQL(0, "Connect failed", EXIT);
/* Find the name and age of sailor SID. */
EXEC SQL SELECT SNAME, AGE into :sname, :sage
FROM ONE.SAILOR
WHERE sid = :sid;
CHECK_SQL(0, "The SELECT query failed.", EXIT);
/* Report the age. */
printf("Sailor %s's age is %d.\n", sname, sage);
printf("Executed Successfuly\n") ;
printf("Bye\n") ;
errorexit:
EXEC SQL CONNECT RESET;
}
|
|
The instance of the table sailor at the time of writing is
SNAME SID RATING AGE
--------------- ----------- ------ ------
yuppy 22 1 20
lubber 31 1 25
guppy 44 2 31
rusty 58 3 47
So once you have a clean compile, if you ask for example
- % sage 44
you should get the response
- SID requested is 44.
- Sailor guppy's age is 31.
- Executed Successfuly
- Bye
Note that the SQL statement in the program after
the EXEC SQL did not have to be a SELECT query.
It could have been any SQL, such as a UPDATE statement.
Do not forget to check out some of the other sample programs found in
- ~db2leduc/sqllib/samples/c/
and
- ~db2leduc/sqllib/samples/cpp/
if you want more examples.
The next example SQC program demonstrates how to open a cursor to walk
through the results of a query.
|
fetcher.sqc
|
/*--------------------------------------------------------------------
EXAMPLE of an embedded SQL C Program for DB2.
Connect to database c3421m for this.
This APP fetches and prints each of the tuples from ONE.SAILOR (in
database c3421m).
P. Godfrey NOV 2002
--------------------------------------------------------------------*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlcodes.h>
#include <sys/time.h>
#define EXIT 0
#define NOEXIT 1
/*--------------------------------------------------------------------
Include DB2's SQL error reporting facility.
--------------------------------------------------------------------*/
EXEC SQL INCLUDE SQLCA ;
/*--------------------------------------------------------------------
Declare the SQL interface variables.
--------------------------------------------------------------------*/
EXEC SQL BEGIN DECLARE SECTION ;
short sid;
short sage;
short rating;
char sname[16];
EXEC SQL END DECLARE SECTION ;
/*--------------------------------------------------------------------
Declare variables to be used in the following C program.
--------------------------------------------------------------------*/
char msg[1025];
int rc;
int errcount;
/*--------------------------------------------------------------------
This macro prints the message in the SQLCA if the return code is 0
and the SQLCODE is not 0.
--------------------------------------------------------------------*/
#define PRINT_MESSAGE() \
{ \
if (rc == 0 && sqlca.sqlcode != 0) \
{ \
sqlaintp(msg, 1024, 0, &sqlca); \
printf("%s\n",msg); \
} \
}
/*--------------------------------------------------------------------
This macro prints out all feilds in the SQLCA.
--------------------------------------------------------------------*/
#define DUMP_SQLCA() \
{ \
printf("********** DUMP OF SQLCA **********************\n"); \
printf("SQLCAID: %s\n", sqlca.sqlcaid); \
printf("SQLCABC: %d\n", sqlca.sqlcabc); \
printf("SQLCODE: %d\n", sqlca.sqlcode); \
printf("SQLERRML: %d\n", sqlca.sqlerrml); \
printf("SQLERRMC: %s\n", sqlca.sqlerrmc); \
printf("SQLERRP: %s\n", sqlca.sqlerrp); \
printf("SQLERRD[0]: %d\n", sqlca.sqlerrd[0]); \
printf("SQLERRD[1]: %d\n", sqlca.sqlerrd[1]); \
printf("SQLERRD[2]: %d\n", sqlca.sqlerrd[2]); \
printf("SQLERRD[3]: %d\n", sqlca.sqlerrd[3]); \
printf("SQLERRD[4]: %d\n", sqlca.sqlerrd[4]); \
printf("SQLERRD[5]: %d\n", sqlca.sqlerrd[5]); \
printf("SQLWARN: %s\n", sqlca.sqlwarn); \
printf("SQLSTATE: %s\n", sqlca.sqlstate); \
printf("********** END OF SQLCA DUMP *******************\n"); \
}
/*--------------------------------------------------------------------
This macro prints the message in the SQLCA if one exists. If the
return code is not 0 or the SQLCODE is not expected, an error occurred
and must be recorded.
--------------------------------------------------------------------*/
#define CHECK_SQL(code,text_string,eExit) \
{ \
PRINT_MESSAGE(); \
if (rc != 0 || sqlca.sqlcode != code) { \
printf("%s\n",text_string); \
printf("Expected code = %d\n",code); \
if (rc == 0) { \
DUMP_SQLCA(); \
} \
else printf("RC: %d\n",rc); \
errcount += 1; \
if (eExit == EXIT) goto errorexit; \
} \
}
/*--------------------------------------------------------------------
The PROGRAM.
--------------------------------------------------------------------*/
main (int argc, char *argv[]) {
EXEC SQL CONNECT TO C3421M;
CHECK_SQL(0, "Connect failed", EXIT);
/* Find the name and age of sailor SID. */
EXEC SQL DECLARE sailorC CURSOR FOR
SELECT SID, SNAME, AGE RATING
FROM ONE.SAILOR;
CHECK_SQL(0, "The SELECT query failed.", EXIT);
EXEC SQL OPEN sailorC;
printf("sid sname age rating\n");
while (1) {
EXEC SQL FETCH sailorC INTO :sid, :sname, :sage, :rating;
if (SQLCODE != 0) break;
printf("%3d %-15s %3d %3d\n", sid, sname, sage, rating);
}
EXEC SQL CLOSE sailorC;
errorexit:
EXEC SQL CONNECT RESET;
}
|
|
|
|
|