|
an
ugly example (do not write a program
like this...for purposes of argument
ONLY)
/*
-To get right to it, here is an example
program that uses Embedded
SQL. Embedded
SQL allows programmers to connect
to a database and
include SQL
code right in the program, so that
their programs can
use, manipulate,
and process data from a database.
-This example C Program
(using Embedded SQL) will print a
report.
-This program will
have to be precompiled for the SQL
statements,
before regular
compilation.
-The EXEC SQL parts
are the same (standard), but the surrounding
C
code will need
to be changed, including the host
variable
declarations,
if you are using a different language.
-Embedded SQL changes
from system to system, so, once again,
check
local documentation,
especially variable declarations and
logging
in procedures,
in which network, DBMS, and operating
system
considerations
are crucial. */
/************************************************/
/* THIS PROGRAM IS NOT COMPILABLE
OR EXECUTABLE */
/* IT IS FOR EXAMPLE PURPOSES
ONLY
*/
/************************************************/
#include
<stdio.h>
/*
This section declares the host variables;
these will be the
variables your program
uses, but also the variable SQL will
put
values in or take
values out. */
EXEC SQL BEGIN DECLARE SECTION;
int BuyerID;
char FirstName[100], LastName[100],
Item[100];
EXEC SQL END DECLARE SECTION;
/*
This includes the SQLCA variable,
so that some error checking can be
done. */
EXEC SQL INCLUDE SQLCA;
main()
{
/*
This is a possible way to log into
the database */
EXEC SQL CONNECT UserID/Password;
/*
This code either says that you are
connected or checks if an error
code was generated,
meaning log in was incorrect or not
possible. */
if(sqlca.sqlcode) {
printf(Printer,
"Error connecting to database server.\n");
exit();
}
printf("Connected to database
server.\n");
/*
This declares a "Cursor". This is
used when a query returns more
than one row, and
an operation is to be performed on
each row
resulting from the
query. With each row established by
this query,
I'm going to use
it in the report. Later, "Fetch" will
be used to
pick off each row,
one at a time, but for the query to
actually
be executed, the
"Open" statement is used. The "Declare"
just
establishes the query.
*/
EXEC SQL DECLARE ItemCursor CURSOR
FOR
SELECT ITEM, BUYERID
FROM ANTIQUES
ORDER BY ITEM;
EXEC SQL OPEN ItemCursor;
/*
+-- You may wish to put a similar
error checking block here --+ */
/*
Fetch puts the values of the "next"
row of the query in the host
variables, respectively.
However, a "priming fetch" (programming
technique) must first
be done. When the cursor is out of
data, a
sqlcode will be generated
allowing us to leave the loop. Notice
that, for simplicity's
sake, the loop will leave on any sqlcode,
even if it is an
error code. Otherwise, specific code
checking must
be performed. */
EXEC SQL FETCH ItemCursor INTO
:Item, :BuyerID;
while(!sqlca.sqlcode) {
/*
With each row, we will also do a couple
of things. First, bump the
price up by $5 (dealer's
fee) and get the buyer's name to put
in
the report. To do
this, I'll use an Update and a Select,
before
printing the line
on the screen. The update assumes
however, that
a given buyer has
only bought one of any given item,
or else the
price will be increased
too many times. Otherwise, a "RowID"
logic
would have to be
used (see documentation). Also notice
the colon
before host variable
names when used inside of SQL statements.
*/
EXEC
SQL UPDATE ANTIQUES
SET PRICE = PRICE + 5
WHERE ITEM = :Item AND
BUYERID = :BuyerID;
EXEC
SQL SELECT OWNERFIRSTNAME, OWNERLASTNAME
INTO :FirstName, :LastName
FROM ANTIQUEOWNERS
WHERE BUYERID = :BuyerID;
printf("%25s %25s %25s", FirstName,
LastName, Item);
/*
Ugly report--for example purposes
only! Get the next row. */
EXEC SQL FETCH ItemCursor INTO
:Item, :BuyerID;
}
/*
Close the cursor, commit the changes
(see below), and exit the
program. */
EXEC SQL CLOSE ItemCursor;
EXEC SQL COMMIT RELEASE;
exit();
}
|
