 |
» |
|
|
|
In every ALLBASE/SQL C program, you embed SQL commands
in the declaration part and the procedure part of your program to carry
out specific tasks. The program listing shown in Figure 1-7
illustrates where in a program
you can embed SQL commands to accomplish these tasks: - 1
Declare the SQL Communications Area (sqlca). The sqlca is an ALLBASE/SQL data structure that contains current information
about a program's DBE session. Every ALLBASE/SQL C program must contain an
sqlca declaration in the global declaration section. Use the following
command: - 2
Declare host variables. All host variables used in a program must be declared in a declaration
part. You can put more than one such declaration section in a program,
but all host variables must be declared between the BEGIN and END
DECLARE SECTION commands. - 3
Display error and warning messages
from the ALLBASE/SQL message catalog. You can display messages for any errors encountered
in execution as shown in the SQLStatusCheck function. Complete details
about error and message handling are presented in Chapter 4. - 4
Start a DBE session. In most application programs, you embed the CONNECT command to start a
DBE session. This command must be executed before you can access the
DBEnvironment - 5
Check the status of SQL command execution. Your program should check for the success or failure of execution of
each SQL command, as shown in the example program. - 6
Terminate the DBE session. You use the RELEASE command or the RELEASE option of the COMMIT WORK
command to end a DBE session. - 7 and 8
Define transactions. You define transactions in a program to control concurrency and
consistency in your database access. The transaction is bounded by the
BEGIN WORK and COMMIT WORK or ROLLBACK WORK commands. When a COMMIT
WORK is successfully executed, all operations performed by the
transaction it ends are permanently committed to the DBEnvironment - 9
Define or manipulate data in the DBEnvironment. Nearly all programs access data in one or more databases.
The SELECT command shown in the example program retrieves the row from
PurchDB.Parts that contains a part number matching the value in the host
variable named in the WHERE clause. Note, indicator variables such
as SalesPriceInd are discussed in Chapter 3, and data manipulation
is presented fully in Chapters 5 through 8.
Figure 1-6 Runtime Dialog of Program cex2
Program to SELECT specified rows from the Parts Table - cex2
Event List:
CONNECT to PartsDBE
BEGIN WORK
SELECT specified row from Parts Table
until user enters a '/'
COMMIT WORK
RELEASE from PartsDBE
Connect to PartsDBE
Enter Part Number within Parts Table or '/' to STOP> 1243-P-01
Begin Work
SELECT PartNumber, PartName, SalesPrice
Row not found!
Commit Work
Enter Part Number within Parts Table or "/" to STOP> 1323-D-01
Begin Work
SELECT PartNumber, PartName, SalesPrice
Part Number: 1323-D-01
Part Name: Floppy Diskette Drive
Sales Price: 200.00
Commit Work
Enter Part Number within Parts Table or "/" to STOP> /
Release PartsDBE
|
Figure 1-7 Program cex2: Using Simple SELECT
/* Program cex2 */
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
/* This program illustrates the use of SQL's SELECT command to */
/* retrieve one row or tuple of data at a time. */
/* BEGIN WORK is executed before the SELECT and a COMMIT WORK */
/* is executed after the SELECT. An indicator variable is also */
/* used for SalesPrice. */
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
typedef int boolean;
char response[2];
boolean Abort;
#include <stdio.h>
#define OK 0
#define NotFound 100
#define MultipleRows -10002
#define DeadLock -14024
#define FALSE 0
#define TRUE 1
EXEC SQL INCLUDE SQLCA; /* SQL Communication Area */ 1
/* Begin Host Variable Declarations */
EXEC SQL BEGIN DECLARE SECTION; 2
char PartNumber[17];
char PartName[31];
double SalesPrice;
sqlind SalesPriceInd;
char SQLMessage[133];
EXEC SQL END DECLARE SECTION; 2
/* End Host Variable Declarations */
int SQLStatusCheck() /* Function to Display Error Messages */
{
Abort = FALSE;
if (sqlca.sqlcode < DeadLock)
Abort = TRUE;
do {
EXEC SQL SQLEXPLAIN :SQLMessage; 3
printf("\n");
printf("%s\n",SQLMessage);
} while (sqlca.sqlcode != 0);
if (Abort) {
EndTransaction();
ReleaseDBE();
}
} /* End SQLStatusCheck Function */
boolean ConnectDBE() /* Function to Connect to PartsDBE */
{
boolean ConnectDBE;
ConnectDBE = TRUE;
printf("\n connect to PartsDBE");
EXEC SQL CONNECT TO 'PartsDBE');
if (sqlca.sqlcode != OK) {
ConnectDBE = FALSE;
SQLStatusCheck(); 5
} /* End if */
return (ConnectDBE);
} /* End of ConnectDBE Function */
int ReleaseDBE() /* Function to Release PartsDBE */
{
printf("\n");
printf("\n Release PartsDBE");
printf ("\n);
EXEC SQL RELEASE; 6
if (sqlca.sqlcode != OK) SQLStatusCheck();
} /* End ReleaseDBE Function */
boolean BeginTransaction() /* Function to Begin Work */
{
boolean BeginTransaction;
BeginTransaction = TRUE;
printf("\n");
printf("\n Begin Work");
EXEC SQL BEGIN WORK; 7
if (sqlca.sqlcode != OK) {
BeginTransaction = FALSE;
SQLStatusCheck(); 5
ReleaseDBE();
} /* End if */
return (BeginTransaction);
} /* End BeginTransaction Function */
int EndTransaction() /* Function to Commit Work */
{
printf("\n");
printf("\n Commit Work");
EXEC SQL COMMIT WORK; 8
if (sqlca.sqlcode != OK) SQLStatusCheck(); 5
} /* End EndTransaction Function */
int DisplayRow() /* Function to Display Parts Table Rows */
{
printf("\n");
printf("\n Part Number: %s\n", PartNumber);
printf(" Part Name: %s\n", PartName);
if (SalesPriceInd < 0) {
printf(" Sales Price: is NULL \n");
}
else
printf(" Sales Price: %10.2f\n", SalesPrice);
} /* End of DisplayRow */
int Select() /* Function to Query Parts Table */
{
do {
printf("\n");
printf("\n Enter Part Number within Parts Table or '/' to STOP > ");
scanf("%s",PartNumber);
if (PartNumber[0] != '/') {
BeginTransaction();
printf("\n SELECT PartNumber, PartName, SalesPrice");
EXEC SQL SELECT PartNumber, PartName, SalesPrice 9
INTO :PartNumber,
:PartName,
:SalesPrice :SalesPriceInd
FROM PurchDB.Parts
WHERE PartNumber = :PartNumber;
if ((sqlca.sqlwarn[0] == 'W') || (sqlca.sqlwarn[0] == 'w')) {
printf("\n SQL WARNING has occurred. The following row");
printf("\n of data may not be valid!");
}
if (sqlca.sqlcode == OK) {
DisplayRow();
}
else if (sqlca.sqlcode == NotFound) {
printf("\n Row not found!");
}
else if (sqlca.sqlcode == MultipleRows) {
printf("\n WARNING: More than one row qualifies!");
}
else {
SQLStatusCheck(); 5
}
EndTransaction();
}
} /* End do */
while (PartNumber[0] != '/');
}/* End of Select Function */
main() /* Beginning of program */
{
printf("\n Program to SELECT specified rows from");
printf("\n the Parts Table - cex2");
printf("\n");
printf("\n Event List:");
printf("\n CONNECT TO PartsDBE");
printf("\n BEGIN WORK");
printf("\n SELECT the specified row from the Parts Table");
printf("\n until the user enters a '/'");
printf("\n COMMIT WORK");
printf("\n RELEASE from PartsDBE");
printf("\n");
if (ConnectDBE()) {
Select();
ReleaseDBE();
}
else
printf("\n Error: Cannot Connect to PartsDBE!\n");
} /* End of Program */
|
|
