Release 8.1.5
A68022-01
 Library |
 Product |
 Contents |
 Index |
| Pro*C/C++ Precompiler Programmer's Guide Release 8.1.5 A68022-01 |
|
This chapter describes other kinds of object types, known as collections, and the ways of using them in Pro*C/C++. We present methods to access collections and their elements.
The main sections in this chapter are:
There are two kinds of collection object types: nested tables and VARRAYs.
Collections may occur both in relational columns and also as attributes within an object type. Note that all collections must be named object types in the database. In the case of VARRAYs, you must first create a named type in the database, specifying the desired array element type and maximum array dimension.
A nested table is a collection of rows, called elements, within a column. For each row of the database table there are many such elements. A simple example is the list of tasks that each employee is working on. Thus many-to-one relationships can be stored in one table, without needing to join employee and task tables.
Nested tables differ from C and C++ arrays in these ways:
Use the CREATE TYPE statement to define a table type that can be nested within other object types in one or more columns of a relational table.
For example:, to store several projects in each department of an organization:
CREATE TYPE project_type AS OBJECT ( pno CHAR(5), pname CHAR(20), budget NUMBER(7,2)) ; CREATE TYPE project_table AS TABLE OF project_type ; CREATE TABLE depts ( dno CHAR(5), dname CHAR(20), budgets_limit NUMBER(15,2), projects project_table) NESTED TABLE projects STORE AS depts_projects ;
Unlike nested tables, you must specify the maximum number of elements when you create a VARRAY type. VARRAYs are only dense, unlike nested tables, which can be dense or sparse. The elements of a VARRAY and a nested table are both numbered from 0.
You create a VARRAY with CREATE TYPE statements such as:
CREATE TYPE employee AS OBJECT ( name VARCHAR2(10), salary NUMBER(8,2) ) ; create type employees as varray(15) of employee ; create type department as object ( name VARCHAR2(15), team employees ) ;
Use a VARRAY as a column of a relational table, or as the attribute of an object type. This saves storage space compared to a relational table that has up to 15 records for each team, each containing the team's name.
In a C or C++ program a nested table is read in starting from index value 0 of the collection. When you write the nested table into an array, the elements of the nested table are stored starting at array index 0. When a nested table which is sparse (has gaps in its index) is stored into an array, the gaps are skipped. When reading the array back into the nested table, the gaps are recreated.
In a C or C++ program VARRAYs are written into an array, starting at index 0. When read back into the VARRAY, the elements are restored starting at index 0 in the same order. Thus, arrays are easily used to access collections.
The C type for a nested table is a pointer to OCITable. For VARRAYs, it is a pointer to OCIArray. (Both are subtypes of a pointer to OCIColl). Use the OTT (Object Type Translator) utility to generate typedefs in header files that you then include in your application code. See "The Object Type Translator" .
The host structure for a collection is a descriptor through which the elements in the collection may be accessed. These descriptors do not hold the actual elements of the collection, but instead contain the pointers to them. Memory for both the descriptor and its associated elements come from the object cache.
Following the usual procedure for object types, the OTT-generated typefile must be specified in the INTYPE precompiler option to Pro*C/C++ and the OTT-generated headers included in the Pro*C/C++ program using the #include preprocessor directive. This ensures that the proper type-checking for the collection object type can be performed during precompilation.
Unlike other object types, however, a collection object type does not require a special indicator structure to be generated by OTT; a scalar indicator is used instead. This is because an atomic NULL indicator is sufficient to denote whether a collection as a whole is NULL. The NULL status of each individual element in a collection may (optionally) be represented in separate indicators associated to each element.
As for the other object types, a host variable representing a collection object type must be declared as a pointer to the appropriate OTT-generated typedef.
Unlike other object types, however, the indicator variable for a collection object type as a whole is declared as a scalar signed 2-byte type, OCIInd. The indicator variable is optional, but it is a good programming practice to use one for each host variable declared in Pro*C/C++.
There are two ways to manipulate a collection: the collection is treated as an autonomous entity without access to its elements, or its elements are accessed, appended to, truncated, etc.
Using a C collection descriptor (OCITable or OCIArray) allows only assignment of the collection as a whole. The OBJECT GET embedded SQL statement (see"Converting Object Attributes and C Types" ) binds the collection to a C host variable descriptor. The opposite occurs in an OBJECT SET statement which binds the C host descriptor to the collection.
It is possible to bind more than one collection to a compatible C descriptor in the same statement, or include bindings of other scalars in the same statement where binding of a collection to a C descriptor occurs.
The C collection descriptor is used to access elements of a collection. The descriptor contains the internal attributes of the collection such as its start and end points, and other information.
A slice of elements are bound to a host array that has a compatible data type. A slice of a collection is defined as the contents between a starting index and an ending index. The slice maps to an array, which can have a dimension larger than the number of slice elements.
Binding a scalar is the same as having a host array of dimension one, or having an optional FOR clause evaluated to one.
Note: The FOR clause variable specifies the number of array elements to be processed. Make sure the number does not exceed the smallest array dimension. Internally, the value is treated as an unsigned quantity. An attempt to pass a negative value through the use of a signed host variable will result in unpredictable behavior.
Each access method has its own way of using indicator variables.
The single indicator variable holds the NULL status of the collection as a single entity. This says nothing about the NULL status in the elements.
The indicator variables show whether an element is NULL or not. If a slice of collection data is bound to a host array which has its own indicator array, that indicator array will contain the NULL status of each element in the slice.
When the collection element type is a user-defined object type, the indicator variable associated with the host variable contains the NULL status of the object and of its attributes.
The navigational statements OBJECT SET and OBJECT GET permit you to retrieve and update collection attributes as well as object types defined by you.
For elements that are object types, the OBJECT GET statement retrieves all attributes of the object into the host variables when either the
'*' | {attr [, attr]} FROM
clause is omitted, or 'OBJECT GET * FROM ' is used:
EXEC SQL [AT [:]database] OBJECT GET [ '*' | {attr [,attr]} FROM] :object [[INDICATOR] :object_ind] INTO {:hv [[INDICATOR] :hv_ind] [,:hv [[INDICATOR] :hv_ind]]} ;
The OBJECT SET statement causes all attributes of the object to be updated using the host variables when either the
'*' | {attr, [, attr]} OF
clause is omitted or 'OBJECT SET * OF' is used :
EXEC SQL [AT [:]database] OBJECT SET [ '*' | {attr [, attr]} OF] :object [INDICATOR] :object_ind] TO {:hv [[INDICATOR] :hv_ind] [,:hv [[INDICATOR] :hv_ind]]} ;
For complete details of the OBJECT GET and OBJECT SET statements, see "Converting Object Attributes and C Types".
This table shows how object and collection types are mapped by these two statements:
| Attribute Type | Representation | Host Data Type |
|---|---|---|
|
Object |
OTT-Generated Structure |
Pointer to OTT Structure |
|
Collection |
OCIArray, OCITable (OCIColl) |
OCIArray *, OCITable * (OCIColl *) |
The object or collection must be type compatible with the attribute to which it is bound. Collection attributes are type compatible if and only if they are both either VARRAY or nested table, and their element types are compatible.
This next table shows how type compatibility for the elements of two collection types is obtained.
The object to which the REF refers must be type compatible with the REF to which it is bound.
In both tables OBJECT GET converts database types from the type on the left, using the format specified in the Representation column to an internal datatype using the format in the Host Data Type column. OBJECT SET converts in the opposite direction.
The precompiler does not support explicit type checking on the bindings between collection element datatypes and host variable datatypes. Type-checking is done at runtime.
The COLLECTION GET statement is analogous to the OBJECT GET statement, but is designed for collections. It retrieves the elements of a collection, sets the current slice, and converts elements to C types where appropriate.
EXEC SQL [AT [:]database] [FOR :num] COLLECTION GET :collect [[INDICATOR] :collect_ind] INTO :hv [[INDICATOR] :hv_ind] ;
num (IN)
The number of elements requested. If this clause is omitted, the array size of the host variable (scalar has 1) determines the number of elements retrieved from the collection.
collect (IN)
A host variable C collection descriptor.
collect_ind (IN)
An optional indicator variable returning the NULL status of the collection.
hv (OUT)
The host variable that receives the collection element values.
hv_ind (OUT)
An optional indicator variable returning the NULL status of hv if it is a scalar, or an array holding the status of each element in the slice.
The number of elements actually returned in the last COLLECTION GET is set in sqlca.sqlerrd[2] (not the cumulative total of all GETs). For a discussion of this component of the SQLCA, see (See "sqlerrd").
The number returned can be less than the number requested when one or both of the slice endpoints exceed the collection bounds. This can occur when:
An improperly initialized C collection descriptor results in an error. All other situations in the list will raise an ORA-01403: no data found error condition. In this case, the total number of elements successfully retrieved before the error occurs is still stored in sqlca.sqlerrd[2].
The initial GET or the first GET after a RESET affects the slice as follows:
Subsequent GETs affect the slice indexes as follows:
The COLLECTION SET statement is analogous to the OBJECT SET statement; it is used for updating element values of collections. Elements in the current slice are converted from the native C types to Oracle datatypes.
EXEC SQL [AT [:]database] [FOR :num] COLLECTION SET :collect [[INDICATOR] :collect_ind] TO :hv [[INDICATOR] :hv_ind] ;
num (IN)
This optional scalar value is the maximum number of elements to be updated in the slice. If this clause is omitted, the array size of the host variable (scalar has 1) determines the number of elements updated from the collection.
collect (OUT)
A host variable C collection descriptor.
collect_ind (OUT)
An optional indicator variable which determines the NULL status of the collection.
hv (IN)
A host variable that contains the values to be updated in the collection.
hv_ind (IN)
An associated indicator variable representing the NULL status of the host variable.
The following restrictions apply:
The dimension of the host variable or the value num specified in an optional FOR clause gives the maximum number of elements requested to be updated in the collection.
The variable sqlca.sqlerrd[2] returns the number of elements successfully updated by the previous SET statement (not a cumulative total), which can be less than the number requested to be set (as for the GET), for these cases:
Issuing a COLLECTION SET immediately after a COLLECTION GET or SET will only update the values of the elements in the existing slice. A COLLECTION GET that immediately follows a COLLECTION SET will advance the slice as already described.
Reset the collection slice endpoints back to the beginning of the collection.
EXEC SQL [AT [:]database] COLLECTION RESET :collect [ [INDICATOR] :collect_ind] ;
collect (IN/OUT)
The collection whose endpoints are to be reset.
collect_ind
Optional indicator variable determining NULL status of the collection.
An error occurs if the given collection is NULL, or otherwise invalid.
This statement appends a set of elements (one or more) to the end of a collection, increasing the size of the collection.
EXEC SQL [AT [:]database] [FOR :num] COLLECTION APPEND :src [[INDICATOR] :src_ind] TO :collect [[INDICATOR] :collect_ind] ;
num (IN)
A scalar that contains the number of elements to be appended. If absent, the array size of src is the number of elements to be appended.
src (IN)
Scalar or array of elements to be appended to the collection.
src_ind (IN)
An optional indicator variable (scalar or array) determining the NULL status of the elements appended.
collect (IN OUT)
The collection to which elements are appended.
collect_ind (IN)
An optional indicator variable determining the NULL status of the collection.
Elements are appended one at a time (the collection is increased in size by one, the data is copied to that element, and so on).
The variable sqlca.sqlerrd[2] returns the number of elements successfully appended by the latest APPEND (not a cumulative total). An error results if there is an attempt to add elements beyond the upper bound of the collection, or to append to a NULL collection. Only the elements that fit will be appended.
This statement removes elements from the end of a collection.
EXEC SQL [AT [:]database] COLLECTION TRIM :num FROM :collect [[INDICATOR] :collect_ind] ;
num (IN)
A host scalar variable which is how many elements are to be removed. The maximum permitted value is two Gigabytes.
collect (IN OUT)
The collection to be trimmed.
collect_ind (IN)
An optional indicator variable determining the NULL status of the collection.
Restrictions applied:
num is 2 Gigabytes (the largest number in a 4-byte signed binary variable.
num.
If num is greater than the size of the collection, an error is returned. A warning is returned if a TRIM removes any elements from the current slice.
This statement returns information about a collection.
EXEC SQL [AT [:]database] COLLECTION DESCRIBE :collect [[INDICATOR] :collect_ind] GET attribute1 [{, attributeN}] INTO :hv1 [[INDICATOR] :hv_ind1] [{, hvN [[INDICATOR] :hv_indN]}] ;
where attributeN is:
DATA_SIZE | TYPECODE | DATA_TYPE | NUM_ELEMENTS | PRECISION | SCALE | TYPE_NAME | TYPE_SCHEMA | SIZE | TABLE_SIZE
collect (IN)
The host variable C collection descriptor.
collect_ind (IN)
An optional indicator variable containing the NULL status of the collection.
hv1 .. hvN (OUT)
Output host variables where the information is to be stored.
hv_ind1 .. hv_indN (OUT)
Indicator variables for the output host variables.
These restrictions are in effect:
The following table gives attributes, descriptions, and C types for the attributes retrieved:
Pro*C/C++ only supports the external datatypes CHARZ, STRING, and VARCHAR for the attributes TYPE_NAME and TYPE_SCHEMA.
All the DESCRIBE attributes, except for SIZE and TABLE_SIZE, depend on the element type of the collection and are independent of any one particular instance of the collection. The SIZE and TABLE_SIZE attributes, on the other hand, are attributes whose values strictly depend on a specific instance of the collection. The SIZE or TABLE_SIZE values will change from one collection instance to another in cases where an allocated collection descriptor is being reused to refer to different instances of the same collection. NUM_ELEMENTS is an attribute of the collection type (a VARRAY in this case), not the collection element type, and is independent of any one particular instance of the collection.
Here are examples of SQL and Pro*C/C++ code that illustrates the use of the COLLECTION SQL statements:
Assuming a connection as scott/tiger, we create the following types using SQL:
CREATE TYPE employee AS OBJECT ( name VARCHAR2(10), salary NUMBER(8,2) ) ; CREATE TYPE employees AS VARRAY(15) of employee ; CREATE TYPE department AS OBJECT ( name VARCHAR2(15), team employees ) ;
Now for the header file generated by the Object Type Translator. For a complete discussion of OTT, see "The Object Type Translator". The following intype file (called in.typ) will be used as input to OTT:
case=lower type employee type employees type department
The following command will generate the header file:
ott intype=in.typ outtype=out.typ hfile=example.h user=scott/tiger code=c
This header file, example.h, is produced by OTT:
#ifndef EXAMPLE_ORACLE # define EXAMPLE_ORACLE #ifndef OCI_ORACLE # include <oci.h> #endif typedef OCIRef employee_ref ; typedef OCIArray employees ; typedef OCIRef department_ref ; struct employee { OCIString * name ; OCINumber salary ; } ; typedef struct employee employee ; struct employee_ind { OCIInd _atomic ; OCIInd name ; OCIInd salary ; } ; typedef struct employee_ind employee_ind ; struct department_ind { OCIInd _atomic ; OCIInd name ; OCIInd team ; } ; typedef struct department_ind department_ind ; #endif
Note: oci.h includes orl.h which has a typedef that defines OCIArray. That typedef looks like the following 'typedef OCIColl OCIArray;' where OCIColl is an opaque structure representing a generic collection.
Now create a simple table that has one column as follows:
CREATE TABLE division ( subdivision department ) ;
Now we will insert a few rows into that table:
INSERT INTO division (subdivision) VALUES (department('Accounting', employees(employee('John', 75000), employee('Jane', 75000))) ); INSERT INTO division (subdivision) VALUES (department('Development', employees(employee('Peter', 80000), employee('Paula', 80000))) ) ; INSERT INTO division (subdivision) VALUES (department('Research', employees(employee('Albert', 95000), employee('Alison', 95000))) );
We can now use these type definitions and table information in the examples to follow.
Suppose we want to retrieve the values from the collection attribute of an example object, modify them in some simple way and place them back into the collection.
First, we need to include example.h and declare a variable for the object type:
#include <example.h> department *dept_p ;
Now we will select the 'Development' department from the division table:
EXEC SQL ALLOCATE :dept_p ; EXEC SQL SELECT subdivision INTO :dept_p FROM division WHERE name = 'Development' ;
We also need a variable for the team VARRAY of employee object types and one to represent a single employee object. We will be giving all team members of the 'Development' department a raise so we need a variable for that also:
employees *emp_array ; employee *emp_p ; double salary ;
Now we must ALLOCATE our VARRAY C Collection and employee object descriptors. We will retrieve the actual collection from the object using the navigational interface:
EXEC SQL ALLOCATE :emp_array ; EXEC SQL ALLOCATE :emp_p ; EXEC SQL OBJECT GET team FROM :dept_p INTO :emp_array ;
We will use a loop to iterate through the VARRAY elements, controlling loop termination by using the WHENEVER directive:
EXEC SQL WHENEVER NOT FOUND DO break ; while (TRUE) {
First, retrieve the element from the collection so that we can alter it. The actual element type is an employee object:
EXEC SQL COLLECTION GET :emp_array INTO :emp_p ;
Now that we have the actual object element, we can then proceed to change an attribute's value using the existing navigational interface. In this case, we give a 10% salary increase to everyone:
EXEC SQL OBJECT GET salary FROM :emp_p INTO :salary ; salary += (salary * .10) ; EXEC SQL OBJECT SET salary OF :emp_p TO :salary ;
Once we are done making our changes, we can update the value of the attribute of the object element we are currently at in the collection:
EXEC SQL COLLECTION SET :emp_array TO :emp_p ; }
Once we are done iterating through all of the collection elements, we must then update the column of the table that stores the object that contains the collection we just finished modifying:
EXEC SQL UPDATE division SET subdivision = :dept_p ;
We can then FREE all of our resources and COMMIT our work thus terminating this sequence of operations:
EXEC SQL FREE :emp_array ; EXEC SQL FREE :emp_p ; EXEC SQL FREE :dept_p ; EXEC SQL COMMIT WORK ;
Although this is a fairly small and simple example, it is quite comprehensive. It clearly demonstrates how a collection attribute can be retrieved from an object into a C Collection Descriptor using the semantic extensions to the navigational OBJECT GET statement. Using that C Descriptor we then saw how to use the new COLLECTION GET and SET statements to retrieve and update the actual elements of that collection. We used the navigational interface to modify the attribute values of the collection object element type.
This example illustrates a simple use of the DESCRIBE SQL statement. We want to find out some basic information about a given collection.
First we need our example header file, an object pointer and a SQL Collection Descriptor:
#include <example.h> department *dept_p ;
Now we ALLOCATE the object pointer and retrieve our object from the table as before:
EXEC SQL ALLOCATE :dept_p ; EXEC SQL SELECT subdivision INTO :dept_p FROM division WHERE name = 'Research' ;
Declare Pro*C/C++ variables to store the collection attribute information we want:
int size ; char type_name[9] ; employees *emp_array ;
Allocate the collection descriptor, then use the navigational interface to get the collection attribute from the object:
EXEC SQL ALLOCATE :emp_array ; EXEC SQL OBJECT GET team FROM :dept_p INTO :emp_array ;
Finally, we can use the new COLLECTION DESCRIBE statement to extract the desired collection attribute information:
EXEC SQL COLLECTION DESCRIBE :emp_array GET SIZE, TYPE_NAME INTO :size, :type_name ;
Note: You are permitted to use host variable names that are the same as the desired collection attribute name, as in this example.
Because the type employees is a VARRAY of object employee, we can extract the type name.
After successful completion of the DESCRIBE, the value of size should be 2 (there are 2 elements, Albert and Alison, in this collection instance, Research). The type_name variable should read "EMPLOYEE\0" (it is a CHARZ by default).
Once we are finished with the SQL Descriptor and the object pointer we can FREE their resources:
EXEC SQL FREE :emp_array ; EXEC SQL FREE :dept_p ;
We have just illustrated that the DESCRIBE mechanism is used to extract useful information from a C Collection Descriptor about the underlying collection to which the descriptor refers.
Now suppose that instead of giving just the employees in Development a raise, as in the GET and SET example, we give raises to everybody in the entire division.
We start off as before, including our example header file generated by the Object Type Translator. This time, however, we will be using a cursor to iterate through all departments in the division, one at a time:
#include <example.h> EXEC SQL DECLARE c CURSOR FOR SELECT subdivision FROM division ;
We will need some local variables to manipulate our data:
department *dept_p ; employees *emp_array ; employee *emp_p ; double salary ; int size ;
Before we can use the object and collection variables, we must first initialize them by using this ALLOCATE statement:
EXEC SQL ALLOCATE :emp_array ; EXEC SQL ALLOCATE :emp_p ;
Now we are ready to use our cursor to iterate through all of the departments in the division:
EXEC SQL OPEN c ; EXEC SQL WHENEVER NOT FOUND DO break ; while (TRUE) { EXEC SQL FETCH c INTO :dept_p ;
At this point, we have a department object. We need to use the Navigational Interface to extract the 'team' VARRAY attribute from the department:
EXEC SQL OBJECT GET team FROM :dept_p INTO :emp_array ;
Before we can start referring to the collection, we need to guarantee that the slice endpoints are set to the beginning of the current collection instance (not the end of a previous instance) by use of the RESET statement:
EXEC SQL COLLECTION RESET :emp_array ;
Now we will iterate through all elements of the VARRAY and update the salaries as we did before. Note that the existing WHENEVER directive remains in effect for this loop as well:
while (TRUE) { EXEC SQL COLLECTION GET :emp_array INTO :emp_p ; EXEC SQL OBJECT GET salary FROM :emp_p INTO :salary ; salary += (salary * .05) ; EXEC SQL OBJECT SET salary OF :emp_p TO :salary ;
When we are finished, we'll update the collection attribute:
EXEC SQL COLLECTION SET :emp_array TO :emp_p ; }
As before, we need to update the column of the table that stores the object that contains the collection that we just finished modifying:
EXEC SQL UPDATE division SET subdivision = :dept_p ; }
Loop termination signifies the end of processing. We can now FREE all of our resources and COMMIT our work:
EXEC SQL CLOSE c ; EXEC SQL FREE :emp_p ; EXEC SQL FREE :emp_array ; EXEC SQL FREE :dept_p ; EXEC SQL COMMIT WORK ;
This example demonstrates how it is possible to reuse an ALLOCATEd Collection Descriptor for different instances of the same collection type. The COLLECTION RESET statement ensures that the slice endpoints are set back to the beginning of the current collection instance rather than remain in their existing positions after having been moved during the referencing of a previous collection instance.
By using the COLLECTION RESET statement in this fashion, application developers need not explicitly FREE and reALLOCATE a Collection Descriptor with each new instance of the same collection type.
The following program, coldemo1.pc, is in the demo directory.
This example demonstrates three ways for the Pro*C client to navigate through collection-typed database columns. Although the examples presented use nested tables, they also apply to VARRAYs.
Here is the SQL*Plus file, coldemo1.sql, that sets up the table using the inserts and data contained in calidata.sql:
REM ************************************************************************ REM ** This is a SQL*Plus script to demonstrate collection manipulation REM ** in Pro*C. REM ** Run this script before executing OTT for the coldemo1.pc program REM ************************************************************************ connect scott/tiger; set serveroutput on; REM Make sure database has no old version of the table and types DROP TABLE county_tbl; DROP TYPE citytbl_t; DROP TYPE city_t; REM ABSTRACTION: REM The counties table contains census information about each of the REM counties in a particular U.S. state (California is used here). REM Each county has a name, and a collection of cities. REM Each city has a name and a population. REM IMPLEMENTATION: REM Our implementation follows this abstraction REM Each city is implemented as a "city" OBJECT, and the REM collection of cities in the county is implemented using REM a NESTED TABLE of "city" OBJECTS. CREATE TYPE city_t AS OBJECT (name CHAR(30), population NUMBER); / CREATE TYPE citytbl_t AS TABLE OF city_t; / CREATE TABLE county_tbl (name CHAR(30), cities citytbl_t) NESTED TABLE cities STORE AS citytbl_t_tbl; REM Load the counties table with data. This example uses estimates of REM California demographics from Janurary 1, 1996. @calidata.sql; REM Commit to save COMMIT;
See the comments at the beginning of the following program for explanations of how to set up the table, and then the functionality demonstrated by this program.
/* ***************************************** */ /* Demo program for Collections in Pro*C */ /* ***************************************** */ /***************************************************************************** In SQL*Plus, run the SQL script coldemo1.sql to create: - 2 types: city_t (OBJECT) and citytbl_t (NESTED TABLE) - 1 relational table county_tbl which contains a citytbl_t nested table Next, run the Object Type Translator (OTT) to generate typedefs of C structs corresponding to the city_t and citytbl_t types in the databases: ott int=coldemo1.typ outt=out.typ hfile=coldemo1.h code=c user=scott/tiger Then, run the Pro*C/C++ Precompiler as follows: proc coldemo1 intype=out.typ Finally, link the generated code using the Pro*C Makefile: (Compiling and Linking applications is a platform dependent step). **************************************************************************** Scenario: We consider the example of a database used to store census information for the state of California. The database has a table representing the counties of California. Each county has a name and a collection of cities. Each city has a name and a population. Application Overview: This example demonstrates three ways for the Pro*C client to navigate through collection-typed database columns. Although the examples presented use nested tables, they also apply to VARRAYs. Collections-specific functionality is demonstrated in three different functions, as described below. PrintCounties shows examples of * Declaring collection-typed host variables and arrays * Allocating and freeing collection-typed host variables * Using SQL to load a collection-typed host variable * Using indicators for collection-typed host variables * Using OCI to examine a collection-typed host variables PrintCounty shows examples of * Binding a ref cursor host variable to a nested table column * Allocating and freeing a ref cursor * Using the SQL "CURSOR" clause CountyPopulation shows examples of * Binding a "DECLARED" cursor to a nested table column * Using the SQL "THE" clause ****************************************************************************/ /* Include files */ #include <stdio.h> #include <string.h> #include <stdlib.h> #include <sqlca.h> /* SQL Communications Area */ #include <coldemo1.h> /* OTT-generated header with C typedefs for the */ /* database types city_t and citytbl_t */ #ifndef EXIT_SUCCESS # define EXIT_SUCCESS 0 #endif #ifndef EXIT_FAILURE # define EXIT_FAILURE 1 #endif #define CITY_NAME_LEN 30 #define COUNTY_NAME_LEN 30 #define MAX_COUNTIES 60 /* Function prototypes */ #if defined(__STDC__) void OptionLoop( void ); boolean GetCountyName( char *countyName ); void PrintCounties( void ); long CountyPopulation( CONST char *countyName ); void PrintCounty( CONST char *countyName ); void PrintSQLError( void ); void PrintCountyHeader( CONST char *county ); void PrintCity( city_t *city ); #else void OptionLoop(); boolean GetCountyName(/*_ char *countyName _*/); void PrintCounties(); long CountyPopulation(/*_ CONST char *countyName _*/); void PrintCounty(/*_ CONST char *countyName _*/); void PrintSQLError(/*_ void _*/); void PrintCountyHeader(/*_ CONST char *county _*/); void PrintCity(/*_ city_t *city _*/); #endif /* * NAME * main * COLLECTION FEATURES * none */ int main() { char * uid = "scott/tiger"; EXEC SQL WHENEVER SQLERROR DO PrintSQLError(); printf("\nPro*Census: Release California - Jan 1 1996.\n"); EXEC SQL CONNECT :uid; OptionLoop(); printf("\nGoodbye\n\n"); EXEC SQL ROLLBACK RELEASE; return(EXIT_SUCCESS); } /* * NAME * OptionLoop * DESCRIPTION * A command dispatch routine. * COLLECTION FEATURES * none */ void OptionLoop() { char choice[30]; boolean done = FALSE; char countyName[COUNTY_NAME_LEN + 1]; while (!done) { printf("\nPro*Census options:\n"); printf("\tlist information for (A)ll counties\n"); printf("\tlist information for one (C)ounty\n"); printf("\tlist (P)opulation total for one county\n"); printf("\t(Q)uit\n"); printf("Choice? "); fgets(choice, 30, stdin); switch(toupper(choice[0])) { case 'A': PrintCounties(); break; case 'C': if (GetCountyName(countyName)) PrintCounty(countyName); break; case 'P': if (GetCountyName(countyName)) printf("\nPopulation for %s county: %ld\n", countyName, CountyPopulation(countyName)); break; case 'Q': done = TRUE; break; default: break; } } } /* * NAME * GetCountyName * DESCRIPTION * Fills the passed buffer with a client-supplied county name. * Returns TRUE if the county is in the database, and FALSE otherwise. * COLLECTION FEATURES * none */ boolean GetCountyName(countyName) char *countyName; { int count; int i; printf("County name? "); fgets(countyName, COUNTY_NAME_LEN + 1, stdin); /* Convert the name to uppercase and remove the trailing '\n' */ for (i = 0; countyName[i] != '\0'; i++) { countyName[i] = (char)toupper(countyName[i]); if (countyName[i] == '\n') countyName[i] = '\0'; } EXEC SQL SELECT COUNT(*) INTO :count FROM county_tbl WHERE name = :countyName; if (count != 1) { printf("\nUnable to find %s county.\n", countyName); return FALSE; } else return TRUE; } /* * NAME * PrintCounties * DESCRIPTION * Prints the population and name of each city of every county * in the database. * COLLECTION FEATURES * The following features correspond to the inline commented numbers * 1) Host variables for collection-typed objects are declared using * OTT-generated types. Both array and scalar declarations are allowed. * Scalar declarations must be of type pointer-to-collection-type, and * array declarations must of type array-of-pointer-to-collection-type. * 2) SQL ALLOCATE should be used to allocate space for the collection. * SQL FREE should be used to free the memory once the collection is * no longer needed. The host variable being allocated or free'd * can be either array or scalar. * 3) SQL is used to load into or store from collection-typed host variables * and arrays. No special syntax is needed. * 4) The type of an indicator variable for a collection is OCIInd. * An indicators for a collections is declared and used just like * an indicator for an int or string. * 5) The COLLECTION GET Interface is used to access and manipulate the * contents of collection-typed host variables. Each member of the * collection used here has type city_t, as generated by OTT. */ void PrintCounties() { citytbl_t *cityTable[MAX_COUNTIES]; /* 1 */ OCIInd cityInd[MAX_COUNTIES]; /* 4 */ char county[MAX_COUNTIES][COUNTY_NAME_LEN + 1]; int i, numCounties; city_t *city; EXEC SQL ALLOCATE :cityTable; /* 2 */ EXEC SQL ALLOCATE :city; EXEC SQL SELECT name, cities INTO :county, :cityTable:cityInd FROM county_tbl; /* 3, 4 */ numCounties = sqlca.sqlerrd[2]; for (i = 0; i < numCounties; i++) { if (cityInd[i] == OCI_IND_NULL) /* 4 */ { printf("Unexpected NULL city table for %s county\n", county[i]); } else { /* 5 */ PrintCountyHeader(county[i]); EXEC SQL WHENEVER NOT FOUND DO break; while (TRUE) { EXEC SQL COLLECTION GET :cityTable[i] INTO :city; PrintCity(city); } EXEC SQL WHENEVER NOT FOUND CONTINUE; } } EXEC SQL FREE :city; EXEC SQL FREE :cityTable; /* 2 */ } /* * NAME * PrintCountyHeader * COLLECTION FEATURES * none */ void PrintCountyHeader(county) CONST char *county; { printf("\nCOUNTY: %s\n", county); } /* * NAME * PrintCity * COLLECTION FEATURES * none */ void PrintCity(city) city_t *city; { varchar newCITY[CITY_NAME_LEN]; int newPOP; EXEC SQL OBJECT GET NAME, POPULATION from :city INTO :newCITY, :newPOP; printf("CITY: %.*s POP: %d\n", CITY_NAME_LEN, newCITY.arr, newPOP); } /* * NAME * PrintCounty * DESCRIPTION * Prints the population and name of each city in a particular county. * COLLECTION FEATURES * The following features correspond to the inline commented numbers * 1) A ref cursor host variable may be used to scroll through the * rows of a collection. * 2) Use SQL ALLOCATE/FREE to create and destroy the ref cursor. * 3) The "CURSOR" clause in SQL can be used to load a ref cursor * host variable. In such a case, the SELECT ... INTO does an * implicit "OPEN" of the ref cursor. * IMPLEMENTATION NOTES * In the case of SQL SELECT statements which contain an embedded * CURSOR(...) clause, the Pro*C "select_error" flag must be "no" * to prevent cancellation of the parent cursor. */ void PrintCounty(countyName) CONST char *countyName; { sql_cursor cityCursor; /* 1 */ city_t *city; EXEC SQL ALLOCATE :cityCursor; /* 2 */ EXEC SQL ALLOCATE :city; EXEC ORACLE OPTION(select_error=no); EXEC SQL SELECT CURSOR(SELECT VALUE(c) FROM TABLE(county_tbl.cities) c) INTO :cityCursor FROM county_tbl WHERE county_tbl.name = :countyName; /* 3 */ EXEC ORACLE OPTION(select_error=yes); PrintCountyHeader(countyName); EXEC SQL WHENEVER NOT FOUND DO break; while (TRUE) { EXEC SQL FETCH :cityCursor INTO :city; PrintCity(city); } EXEC SQL WHENEVER NOT FOUND CONTINUE; EXEC SQL CLOSE :cityCursor; EXEC SQL FREE :cityCursor; /* 2 */ EXEC SQL FREE :city; } /* * NAME * CountyPopulation * DESCRIPTION * Returns the number of people living in a particular county. * COLLECTION FEATURES * The following features correspond to the inline commented numbers * 1) A "DECLARED" cursor may be used to scroll through the * rows of a collection. * 2) The "THE" clause in SQL is used to convert a single nested-table * column into a table. */ long CountyPopulation(countyName) CONST char *countyName; { long population; long populationTotal = 0; EXEC SQL DECLARE cityCursor CURSOR FOR SELECT c.population FROM THE(SELECT cities FROM county_tbl WHERE name = :countyName) AS c; /* 1, 2 */ EXEC SQL OPEN cityCursor; EXEC SQL WHENEVER NOT FOUND DO break; while (TRUE) { EXEC SQL FETCH cityCursor INTO :population; populationTotal += population; } EXEC SQL WHENEVER NOT FOUND CONTINUE; EXEC SQL CLOSE cityCursor; return populationTotal; } /* * NAME * PrintSQLError * DESCRIPTION * Prints an error message using info in sqlca and calls exit. * COLLECTION FEATURES * none */ void PrintSQLError() { EXEC SQL WHENEVER SQLERROR CONTINUE; printf("SQL error occurred...\n"); printf("%.*s\n", (int)sqlca.sqlerrm.sqlerrml, (CONST char *)sqlca.sqlerrm.sqlerrmc); EXEC SQL ROLLBACK RELEASE; exit(EXIT_FAILURE); }
