HP 3000 Manuals HP 3000 Manuals
Looking at an Embedded SQL Source Program
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:
EXEC SQL INCLUDE SQLCA;
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 Define transactions.
8
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.
_____________________________________________________________________
| |
| 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-6. Runtime Dialog of Program cex2
____________________________________________________________________________
| |
| /* 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 */ |
| |
| |
| |
| |
| |
| |
| |
| |
| |
____________________________________________________________________________
Figure 1-7. Program cex2: Using Simple SELECT
______________________________________________________________________________
| |
| 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 */ |
| |
______________________________________________________________________________
Figure 1-7. Program cex2: Using Simple SELECT (page 2 of 5)
__________________________________________________________________________
| |
| 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 */ |
| |
| |
| |
| |
__________________________________________________________________________
Figure 1-7. Program cex2: Using Simple SELECT (page 3 of 5)
_____________________________________________________________________________
| |
| 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 */ |
| |
| |
_____________________________________________________________________________
Figure 1-7. Program cex2: Using Simple SELECT (page 4 of 5)
___________________________________________________________________
| |
| 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 */ |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
___________________________________________________________________
Figure 1-7. Program cex2: Using Simple SELECT (page 5 of 5)