Programmer's Guide to the Pro*C/C++ Precompiler | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
After covering the key concepts of embedded SQL programming and the steps you take in developing an application, this chapter uses a simple program to illustrate the main points.
Figure 2 - 1 shows all the interactive SQL statements your application program can execute.
Figure 2 - 1. SQL Allowed in a Program
For example, to manipulate and query Oracle data, you use the INSERT, UPDATE, DELETE, and SELECT statements. INSERT adds rows of data to database tables, UPDATE modifies rows, DELETE removes unwanted rows, and SELECT retrieves rows that meet your search condition.
The Pro*C/C++ Precompiler supports all the Oracle7 SQL statements. For example, the powerful SET ROLE statement lets you dynamically manage database privileges. A role is a named group of related system and/or object privileges granted to users or other roles. Role definitions are stored in the Oracle data dictionary. Your applications can use the SET ROLE statement to enable and disable roles as needed.
Only SQL statements--not SQL*Plus statements--are valid in an application program. (SQL*Plus has additional statements for setting environment parameters, editing, and report formatting.)
Declarative statements, on the other hand, do not result in calls to SQLLIB and do not operate on Oracle data. You use them to declare Oracle objects, communications areas, and SQL variables. They can be placed wherever C variable declarations can be placed. You treat the ALLOCATE statement, however, as an executable, not a declarative, statement.
Table 2 - 1 groups the various embedded SQL statements.
Many embedded SQL statements differ from their interactive counterparts only through the addition of a new clause or the use of program variables. The following example compares interactive and embedded ROLLBACK statements:
ROLLBACK WORK; -- interactive
EXEC SQL ROLLBACK WORK; -- embedded
These statements have the same effect, but you would use the first in an interactive SQL environment (such as when running SQL*Plus), and the second in a Pro*C/C++ program.
However, some applications might be required to accept and process any valid SQL statement at runtime. So, you might not know until runtime all the SQL commands, database tables, and columns involved.
Dynamic SQL is an advanced programming technique that lets your program accept or build SQL statements at run time and take explicit control over datatype conversion.
From embedded PL/SQL blocks, you can manipulate Oracle data flexibly and safely because PL/SQL supports all SQL data manipulation and transaction processing commands. For more information about PL/SQL, see Chapter 5, ``Using Embedded PL/SQL''.
Your program uses input host variables to pass data to Oracle. Oracle uses output host variables to pass data and status information to your program. The program assigns values to input host variables; Oracle assigns values to output host variables.
Host variables can be used anywhere a SQL expression can be used. In SQL statements, host variables must be prefixed with a colon (:) to set them apart from Oracle objects.
You can also use a C struct to contain a number of host variables. When you name the structure in an embedded SQL statement, prefixed with a colon, Oracle uses each of the components of the struct as a host variable.
You can associate any host variable with an optional indicator variable. An indicator variable is a short integer variable that "indicates" the value or condition of its host variable. You use indicator variables to assign nulls to input host variables and to detect nulls or truncated values in output host variables. A null is a missing, unknown, or inapplicable value.
In SQL statements, an indicator variable must be prefixed with a colon and immediately follow its associated host variable. The keyword INDICATOR can be placed between the host variable and its indicator for additional clarity.
If the host variables are packaged in a struct, and you want to use indicator variables, you simply create a struct that has an indicator variable for each host variable in the host structure, and name the indicator struct in the SQL statement, immediately following the host variable struct, and prefixed with a colon. You can also use the INDICATOR keyword to separate a host structure and its associated indicator structure.
Oracle recognizes two kinds of datatypes: internal and external. Internal datatypes specify how Oracle stores data in database columns. Oracle also uses internal datatypes to represent database pseudocolumns, which return specific data items but are not actual columns in a table.
External datatypes specify how data is stored in host variables. When your host program inputs data to Oracle, if necessary, Oracle converts between the external datatype of the input host variable and the internal datatype of the target database column. When Oracle outputs data to your host program, if necessary, Oracle converts between the internal datatype of the source database column and the external datatype of the output host variable.
On a variable-by-variable basis, you can equivalence supported C datatypes to the Oracle external datatypes. You can also equivalence user-defined datatypes to Oracle external datatypes.
For static SQL statements, there are two types of cursors: implicit and explicit. Oracle implicitly declares a cursor for all data definition and data manipulation statements, including SELECT statements (queries) that return only one row. However, for queries that return more than one row, to process beyond the first row, you must explicitly declare a cursor (or use host arrays).
The set of rows returned is called the active set; its size depends on how many rows meet the query search condition. You use an explicit cursor to identify the row currently being processed, called the current row.
Imagine the set of rows being returned to a terminal screen. A screen cursor can point to the first row to be processed, then the next row, and so on. In the same way, an explicit cursor "points" to the current row in the active set. This allows your program to process the rows one at a time.
All the data manipulation statements executed since the last data definition, COMMIT, or ROLLBACK statement was executed make up the current transaction.
To help ensure the consistency of your database, Pro*C/C++ lets you define transactions using the COMMIT, ROLLBACK, and SAVEPOINT statements.
COMMIT makes permanent any changes made during the current transaction. ROLLBACK ends the current transaction and undoes any changes made since the transaction began. SAVEPOINT marks the current point in the processing of a transaction; used with ROLLBACK, it undoes part of a transaction.
The SQLCA is a data structure that you include (or hardcode) in your host program. It defines program variables used by Oracle to pass runtime status information to the program. With the SQLCA, you can take different actions based on feedback from Oracle about work just attempted. For example, you can check to see if a DELETE statement succeeded and, if so, how many rows were deleted.
With the WHENEVER statement, you can specify actions to be taken automatically when Oracle detects an error or warning condition. These actions include continuing with the next statement, calling a function, branching to a labeled statement, or even stopping.
Figure 2 - 2. Embedded SQL Application Development Process
As you can see, precompiling results in a modified source file that can be compiled normally. Though precompiling adds a step to the traditional development process, that step is well worth taking because it lets you write very flexible applications.
CREATE TABLE DEPT (DEPTNO NUMBER(2) NOT NULL, DNAME VARCHAR2(14), LOC VARCHAR2(13)) CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2))
DEPTNO DNAME LOC ------- ---------- --------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------- --------- ------ --------- ------ ------ ------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10
The program connects to Oracle, then loops, prompting the user for an employee number. It queries the database for the employee's name, salary, and commission, displays the information, and then continues the loop. The information is returned to a host structure. There is also a parallel indicator structure to signal whether any of the output values SELECTed might be null.
You should precompile sample programs using the precompiler option MODE=ORACLE.
/* * sample1.pc * * Prompts the user for an employee number, * then queries the emp table for the employee's * name, salary and commission. Uses indicator * variables (in an indicator struct) to determine * if the commission is NULL. * */ #include <stdio.h> #include <string.h> /* Define constants for VARCHAR lengths. */ #define UNAME_LEN 20 #define PWD_LEN 40 /* Declare variables. No declare section is needed if MODE=ORACLE. */ VARCHAR username[UNAME_LEN]; /* VARCHAR is an Oracle-supplied struct */ varchar password[PWD_LEN]; /* varchar can be in lower case also. */
/* Define a host structure for the output values of a SELECT statement. */ struct { VARCHAR emp_name[UNAME_LEN]; float salary; float commission; } emprec; /* Define an indicator struct to correspond to the host output struct. */ struct { short emp_name_ind; short sal_ind; short comm_ind; } emprec_ind; /* Input host variable. */ int emp_number; int total_queried; /* Include the SQL Communications Area. You can use #include or EXEC SQL INCLUDE. */ #include <sqlca.h> /* Declare error handling function. */ void sql_error(); main() { char temp_char[32]; /* Connect to ORACLE-- * Copy the username into the VARCHAR. */ strncpy((char *) username.arr, "SCOTT", UNAME_LEN); /* Set the length component of the VARCHAR. */ username.len = strlen((char *) username.arr); /* Copy the password. */ strncpy((char *) password.arr, "TIGER", PWD_LEN); password.len = strlen((char *) password.arr);
/* Register sql_error() as the error handler. */ EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n"); /* Connect to ORACLE. Program will call sql_error() * if an error occurs when connecting to the default database. */ EXEC SQL CONNECT :username IDENTIFIED BY :password; printf("\nConnected to ORACLE as user: %s\n", username.arr); /* Loop, selecting individual employee's results */ total_queried = 0; for (;;) { /* Break out of the inner loop when a * 1403 ("No data found") condition occurs. */ EXEC SQL WHENEVER NOT FOUND DO break; for (;;) { emp_number = 0; printf("\nEnter employee number (0 to quit): "); gets(temp_char); emp_number = atoi(temp_char); if (emp_number == 0) break; EXEC SQL SELECT ename, sal, comm INTO :emprec INDICATOR :emprec_ind FROM EMP WHERE EMPNO = :emp_number; /* Print data. */ printf("\n\nEmployee\tSalary\t\tCommission\n"); printf("--------\t------\t\t----------\n");
/* Null-terminate the output string data. */ emprec.emp_name.arr[emprec.emp_name.len] = '\0'; printf("%-8s\t%6.2f\t\t", emprec.emp_name.arr, emprec.salary); if (emprec_ind.comm_ind == -1) printf("NULL\n"); else printf("%6.2f\n", emprec.commission); total_queried++; } /* end inner for (;;) */ if (emp_number == 0) break; printf("\nNot a valid employee number - try again.\n"); } /* end outer for(;;) */ printf("\n\nTotal rows returned was %d.\n", total_queried); printf("\nG'day.\n\n\n"); /* Disconnect from ORACLE. */ EXEC SQL COMMIT WORK RELEASE; exit(0); } void sql_error(msg) char *msg; { char err_msg[128]; int buf_len, msg_len; EXEC SQL WHENEVER SQLERROR CONTINUE; printf("\n%s\n", msg); buf_len = sizeof (err_msg); sqlglm(err_msg, &buf_len, &msg_len); printf("%.*s\n", msg_len, err_msg); EXEC SQL ROLLBACK RELEASE; exit(1); }
![]() ![]() Prev Next |
![]() Copyright © 1997 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |