Release 8.0
A58234-01
 Library |
 Product |
 Contents |
 Index |
| Oracle Call Interface Programmer's Guide, Volumes 1 & 2 Release 8.0 A58234-01 |
|
The concepts of binding and defining were introduced and discussed in Chapter 2, "OCI Programming Basics" and in Chapter 5, "Binding and Defining". This chapter provides additional information necessary for users who are developing object applications. This includes information about binding and defining object datatypes, as well as additional datatypes which have been introduced to support objects.
This chapter assumes that readers are familiar with the basics of binding and defining described in the earlier chapters.
This chapter includes the following sections:
Note: The functionality described in this chapter is only available if you have purchased the Oracle8 Enterprise Edition with the Objects Option.
This section provides information on binding named data types (e.g., objects, collections) and REFs.
For a named data type (object type or collection) bind, a second bind call is necessary (following OCIBindByName() or OCIBindByPos() ). The OCI Bind Object Type call, OCIBindObject(), sets up additional attributes specific to the object type bind. An OCI application uses this call when fetching data from a table which has a column with an object datatype.
The OCIBindObject() call takes, among other parameters, a Type Descriptor Object (TDO) for the named data type. The TDO, of datatype OCIType is created and stored in the database when a named data type is created. It contains information about the type and its attributes. An application can obtain a TDO by calling OCITypeByName().
The OCIBindObject() call also sets up the indicator variable or structure for the named data type bind.
When binding a named data type, use the SQLT_NTY datatype constant to indicate the datatype of program variable being bound. SQLT_NTY indicates that a C struct representing the named data type is being bound. A pointer to this structure is passed to the bind call.
It is possible that working with named data types may require the use of three bind calls in some circumstances. For example, to bind a static array of named data types to a PL/SQL table, three calls must be invoked: OCIBindByName(), OCIBindArrayOfStruct(), and OCIBindObject().
See Also: For information about using these data types to fetch an embedded object from the database, refer to the section "Fetching Embedded Objects" on page 8-15.
For additional important information, see the section "Additional Information for Named Data Type and REF Binds" on page 10-3
For more information about descriptor objects, see "Descriptor Objects" on page 9-23.
As with named data types, binding REFs is a two-step process. First, call OCIBindByName() or OCIBindByPos(), and then call OCIBindObject().
REFs are bound using the SQLT_REF datatype. When SQLT_REF is used, then the program variable being bound must be of type OCIRef *.
See Also: For information about binding and pinning REFs to objects, see "Retrieving an Object Reference from the Server" on page 8-11.
For additional important information, see the section "Additional Information for Named Data Type and REF Binds" on page 10-3.
This section presents some additional important information to keep in mind when working with named data type and REF defines. It includes pointers about memory allocation and indicator variable usage.
For doing array binds of named data types or REFs, for array inserts or fetches, the user needs to pass in an array of pointers to buffers (pre-allocated or otherwise) of the appropriate type. Similarly, an array of scalar indicators (for SQLT_REF types) or an array of pointers to indicator structs (for SQLT_NTY types) needs to be passed.
See Also: For more information about SQLT_NTY, see the section "New OCI 8.0 External Datatypes" on page 3-18.
This section provides information on defining named data types (e.g., objects, collections) and REFs.
For a named data type (object type, nested table, varray) define, two define calls are necessary. The application should first call OCIDefineByPos(), specifying SQLT_NTY in the dty parameter. Following OCIDefineByPos(), the application must call OCIDefineObject(). In this case, the data buffer pointer in OCIDefineByPos() is ignored and additional attributes pertaining to a named data type define are set up using the OCI Define Object attributes call, OCIDefineObject().
There SQLT_NTY datatype constant is specified for a named datatype define. In this case, the application fetches the result data into a host-language representation of the named data type. In most cases, this will be a C struct generated by the Object Type Translator.
When making an OCIDefineObject() call, a pointer to the address of the C struct (preallocated or otherwise) must be provided. The object may have been created with OCIObjectNew(), allocated in the cache, or with user-allocated memory.
Note: Please refer to the section"Additional Information for Named Data Type and REF Defines, and PL/SQL OUT Binds" on page 10-5 for more important information about defining named data types.
As with named data types, defining for a REF output variable is a two-step process. The first step is a call to OCIDefineByPos(), and the second is a call to OCIDefineObject(). Also as with named data types, the SQLT_REF datatype constant is passed to the dty parameter of OCIDefineByPos().
SQLT_REF indicates that the application will be fetching the result data into a variable of type OCIRef *. This REF can then be used as part of object pinning and navigation, as described in Chapter 6.
Note: Please refer to the section"Additional Information for Named Data Type and REF Defines, and PL/SQL OUT Binds" on page 10-5 for more important information about defining REFs.
This section presents some additional important information to keep in mind when working with named data type and REF defines. It includes pointers about memory allocation and indicator variable usage.
A PL/SQL OUT bind refers to binding a placeholder to an output variable in a PL/SQL block. Unlike a SQL statement, where output buffers are set up with define calls, in a PL/SQL block, output buffers are set up with bind calls. Refer to the section "Binding Placeholders in PL/SQL" on page 5-5 for more information.
Note: If a client application wants to allocate memory from its own private memory space, instead of the cache, it must insure that there is no secondary out-of-line memory in the object.
For doing array defines of named data types or REFs, the user needs to pass in an array of pointers to buffers (pre-allocated or otherwise) of the appropriate type. Similarly, an array of scalar indicators (for SQLT_REF types) or an array of pointers to indicator structs (for SQLT_NTY types) needs to be passed.
Previous chapters of this book have discussed OCI bind and define operations. "Binding" on page 4-5 discussed the basics of OCI bind operations, while "Defining" on page 4-11 discusses the basics of OCI define operations. Information specific to binding and defining named data types and REFs is found in Chapter 5, "Binding and Defining".
The sections covering basic bind and define functionality showed how an application could use a scalar variable or array of scalars as an input (bind) value in a SQL statement, or as an output (define) buffer for a query.
The sections covering named data types and REFs showed how to bind or define an object or reference. Chapter 8 expanded on this to talk about pinning object references, object navigation, and fetching embedded instances.
The purpose of this section is to cover binding and defining of individual attribute values, using the datatype mappings explained in this chapter.
Variables of one of the types defined in this chapter (e.g., OCINumber, OCIString) can typically be declared in an application and used directly in an OCI bind or define operation as long as the appropriate datatype code is specified. The following table lists the datatypes that can be used for binds and defines, along with their C mapping, and the OCI external datatype which must be specified in the dty (datatype code) parameter of the bind or define call.
Note 1: Before fetching data into a define variable of type OCIString *, the size of the string must first be set using the OCIStringResize() routine. This may require a describe operation to obtain the length of the select-list data. Similarly, an OCIRaw * must be first sized with OCIRawResize().
The following section presents examples of how to use C-mapped datatypes in an OCI application.
See Also: For a discussion of OCI external datatypes, and a list of datatype codes, refer to Chapter 3, "Datatypes".
The examples in this section demonstrate how variables of type OCINumber can be used in OCI bind and define operations.
Note: The examples in this section are intended to demonstrate the flow of calls used to perform certain OCI tasks. An expanded pseudocode is used for the examples in this section. Actual function names are used, but for the sake of simplicity not all parameters and typecasts are filled in. Additionally, other necessary OCI calls, like handle allocations, have been omitted.
Assume, for this example, that the following person object type was created:
CREATE TYPE person AS OBJECT (name varchar2(30), salary number);
This type is then used to create an employees table which has a column of type person.
CREATE TABLE employees (emp_id number, job_title varchar2(30), emp person);
OTT generates the following C struct and null indicator struct for person:
struct person { OCIString * name; OCINumber salary;}; typedef struct person person; struct person_ind { OCIInd _atomic; OCIInd name; OCIInd salary;} typedef struct person_ind person_ind;
Assume that the employees table has been populated with values, and an OCI application has declared a person variable:
person *my_person;
and fetched an object into that variable through a SELECT statement, like
text *mystmt = (text *) "SELECT person FROM employees WHERE emp.name='ANDREA'";
This would require defining my_person to be the output variable for this statement, using appropriate OCI define calls for named datatypes, as described in the section "Advanced Define Operations" on page 5-16. Executing the statement would retrieve the person object named `ANDREA' into the my_person variable.
Once the object is retrieved into my_person, the OCI application now has access to the attributes of my_person, including the name and the salary.
The application could go on to update another employee's salary to be the same as Andrea's, as in
text *updstmt = (text *) "UPDATE employees SET emp.salary = :newsal WHERE emp.name = `MONGO'"
Andrea's salary (stored in my_person->salary) would be bound to the placeholder :newsal, specifying an external datatype of VARNUM (datatype code=6) in the bind operation:
OCIBindByName(...,":newsal",...,&my_person->salary,...,6,...); OCIStmtExecute(...,updstmt,...)
Executing the statement updates Mongo's salary in the database to be equal to Andrea's, as stored in my_person.
Conversely, the application could update Andrea's salary to be the same as Mongo's, by querying the database for Mongo's salary, and then making the necessary salary assignment:
text *selstmt = (text *) "SELECT emp.salary FROM employees WHERE emp.name = `MONGO'" OCINumber mongo_sal; ... OCIDefineByPos(...,1,...,&mongo_sal,...,6,...); OCIStmtExecute(...,selstmt,...); OCINumberAssign(...,&mongo_sal, &my_person->salary);
In this case, the application declares an output variable of type OCINumber and uses it in the define step. In this case we define an output variable for position 1, and use the appropriate datatype code (6 for VARNUM).
The salary value is fetched into the mongo_sal OCINumber, and the appropriate OCI function, OCINumberAssign(), is used to assign the new salary to the copy of the Andrea object currently in the cache. To modify the data in the database, the change must be flushed to the server.
The examples in the previous section should give some idea of the flexibility which the new Oracle8 datatypes provide for bind and define operations. The goal of this section is to show how the same operation can be performed in several different ways. The goal is to give you some idea of the variety of ways in which these datatypes can be used in OCI applications.
The examples in this section are intended to demonstrate the flow of calls used to perform certain OCI tasks. An expanded pseudocode is used for the examples in this section. Actual function names are used, but for the sake of simplicity not all parameters and typecasts are filled in. Additionally, other necessary OCI calls, like handle allocations, have been omitted.
The scenario for these examples is as follows:
employees database for a hospital (see person type and employees table creation statements in previous section).
salaries, defined as follows:
CREATE TABLE salaries (job_title varchar2(20), salary integer));
Accomplishing the above task requires that the application retrieve the salary corresponding to `RADIOLOGY_CHIEF' from the salaries table, and update Bruce's salary. A separate step would write his new title and the modified object back to the database.
Assuming that a variable of type person has been declared
person * my_person;
and the object corresponding to Bruce has been fetched into it, the following sections present three different ways in which the salary update could be performed.
This example uses the following method:
#define INT_TYPE 3 /* datatype code for sword integer define */ text *getsal = (text *) "SELECT salary FROM salaries WHERE job_title='RADIOLOGY_CHIEF' sword new_sal; OCINumber orl_new_sal; ... OCIDefineByPos(...,1,...,new_sal,...,INT_TYPE,...); /* define int output */ OCIStmtExecute(...,getsal,...); /* get new salary as int */ OCINumberFromInt(...,new_sal,...,&orl_new_sal); /* convert salary to OCINumber */ OCINumberAssign(...,&orl_new_sal, &my_person->salary); /* assign new salary */
This method eliminates one of the steps in Method 1:
#define VARNUM_TYPE 6 /* datatype code for defining VARNUM */ text *getsal = (text *) "SELECT salary FROM salaries WHERE job_title='RADIOLOGY_CHIEF' OCINumber orl_new_sal; ... OCIDefineByPos(...,1,...,orl_new_sal,...,VARNUM_TYPE,...); /* define OCINumber output */ OCIStmtExecute(...,getsal,...); /* get new salary as OCINumber */ OCINumberAssign(...,&orl_new_sal, &my_person->salary); /* assign new salary */
This method accomplishes the entire operation with a single define and fetch. No intervening output variable is used, and the value retrieved from the database is fetched directly into the salary attribute of the object stored in the cache.
#define VARNUM_TYPE 6 /* datatype code for defining VARNUM */ text *getsal = (text *) "SELECT salary FROM salaries WHERE job_title='RADIOLOGY_CHIEF' ... OCIDefineByPos(...,1,...,&my_person->salary,...,VARNUM_TYPE,...); /* define bruce's salary in cache as output variable */ OCIStmtExecute(...,getsal,...);
/* execute and fetch directly */
As the previous three examples show, the Oracle8 C datatypes provide flexibility for binding and defining. In these examples an integer can be fetched, and then converted to an OCINumber for manipulation; an OCINumber could be used as intermediate variable to store the results of a query; or data can be fetched directly into a desired OCINumber attribute of an object.
Note: In all of these examples it is important to keep in mind that in the Oracle8 OCI, if an output variable is defined before the execution of a query, the resulting data will be prefetched directly into the output buffer.
In the above examples, extra steps would be necessary to insure that changes are written to the database permanently. This may involve SQL UPDATE calls and OCI transaction commit calls.
These examples all dealt with define operations, but a similar situation applies for binding.
Similarly, although these examples dealt exclusively with the OCINumber type, a similar variety of operations are possible for the other Oracle8 C types described in the remainder of this chapter.
The following code fragments demonstrate the use of SQLT_NTY bind and define calls, including OCIBindObject() and OCIDefineObject(). In each example, a previously defined SQL statement is being processed.
/* ** This example performs a SQL insert statement */ STATICF void insert(envhp, svchp, stmthp, errhp, insstmt, nrows) OCIEnv *envhp; OCISvcCtx *svchp; OCIStmt *stmthp; OCIError *errhp; text *insstmt; ub2 nrows; { orttdo *addr_tdo = NULLP(orttdo); address addrs; null_address naddrs; address *addr = &addrs; null_address *naddr = &naddrs; sword custno =300; OCIBind *bnd1p, *bnd2p; ub2 i; /* define the application request */ checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) insstmt, (ub4) strlen((char *)insstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); /* bind the input variable */ checkerr(errhp, OCIBindByName(stmthp, &bnd1p, errhp, (text *) ":custno", (sb4) -1, (dvoid *) &custno, (sb4) sizeof(sword), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIBindByName(stmthp, &bnd2p, errhp, (text *) ":addr", (sb4) -1, (dvoid *) 0, (sb4) 0, SQLT_NTY, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)); checkerr(errhp, OCITypeByName(envhpx, errhp, svchpx, (const text *) SCHEMA, (ub4) strlen((char *)SCHEMA), (const text *) "ADDRESS_VALUE", (ub4) strlen((char *)"ADDRESS_VALUE"), OCI_DURATION_SESSION, &addr_tdo)); if(!addr_tdo) { DISCARD printf("Null tdo returned\n"); goto done_insert; } checkerr(errhp, OCIBindObject(bnd2p, errhp, addr_tdo, (dvoid **) &addr, (ub4 *) 0, (dvoid **) &naddr, (ub4 *) 0));
/* ** This example executes a SELECT statement from a table which includes ** an object. */ STATICF void selectval(envhp, svchp, stmthp, errhp) OCIEnv *envhp; OCISvcCtx *svchp; OCIStmt *stmthp; OCIError *errhp; { orttdo *addr_tdo = NULLP(orttdo); OCIDefine *defn1p, *defn2p; address *addr = (address *)NULL; sword custno =0; sb4 status; /* define the application request */ checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) selvalstmt, (ub4) strlen((char *)selvalstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); /* define the output variable */ checkerr(errhp, OCIDefineByPos(stmthp, &defn1p, errhp, (ub4) 1, (dvoid *) &custno, (sb4) sizeof(sword), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) OCI_DEFAULT));
checkerr(errhp, OCIDefineByPos(stmthp, &defn2p, errhp, (ub4) 2, (dvoid *) 0, (sb4) 0, SQLT_NTY, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) OCI_DEFAULT)); checkerr(errhp, OCITypeByName(envhpx, errhp, svchpx, (const text *) SCHEMA, (ub4) strlen((char *)SCHEMA), (const text *) "ADDRESS_VALUE", (ub4) strlen((char *)"ADDRESS_VALUE"),OROODTSES, &addr_tdo)); if(!addr_tdo) { printf("NULL tdo returned\n"); goto done_selectval; } checkerr(errhp, OCIDefineObject(defn2p, errhp, addr_tdo, (dvoid **) &addr, (ub4 *) 0, (dvoid **) 0, (ub4 *) 0)); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT));
