12
Binding and Defining in Object Applications

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:

Binding
Defining
Binding And Defining Oracle C Datatypes
SQLT_NTY Bind/Define Example

Note: The functionality described in this chapter is only available if you have purchased the Oracle8 Enterprise Edition with the Objects Option.

Binding

This section provides information on binding named datatypes, such as objects and collections, and REFs.

Named Datatype Binds

For a named datatype (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".

For additional important information, see the section "Information for Named Datatype and REF Binds"

For more information about descriptor objects, see "Descriptor Objects".

Binding REFs

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".

For additional important information, see the section "Information for Named Datatype and REF Binds".

Information for Named Datatype and REF Binds

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.

If the datatype being bound is SQLT_NTY, the indicator struct parameter of the OCIBindObject() call (dvoid ** indpp) is used, and the scalar indicator is completely ignored.
If the datatype is SQLT_REF, the scalar indicator is used, and the indicator struct parameter of OCIBindObject() is completely ignored.
The use of indicator structures is optional. The user can pass a NULL pointer in the indpp parameter for the OCIBindObject() call. During the bind, this means that the object is not atomically NULL and none of its attributes are NULL.
The indicator struct size pointer, indsp, and program variable size pointer, pgvsp, in the OCIBindObject() call is optional. Users can pass NULL if these parameters are not needed.

Information Regarding Array Binds

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 Oracle External Datatypes".

Defining

This section provides information on defining named data types (e.g., objects, collections) and REFs.

Defining Named Datatype Output Variables

For a named datatype (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"Information for Named Datatype and REF Defines, and PL/SQL OUT Binds" for more important information about defining named data types.

Defining REF Output Variables

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"Information for Named Datatype and REF Defines, and PL/SQL OUT Binds" for more important information about defining REFs.

Information for Named Datatype and REF Defines, and PL/SQL OUT Binds

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" for more information.

If the datatype being defined is SQLT_NTY, the indicator struct parameter of the OCIDefineObject() call (dvoid ** indpp) is used, and the scalar indicator is completely ignored.
If the datatype is SQLT_REF, the scalar indicator is used, and the indicator struct parameter of OCIDefineObject() is completely ignored.
The use of indicator structures is optional. The user can pass a NULL pointer in the indpp parameter for the OCIDefineObject() call. During a fetch or PL/SQL OUT bind, this means that the user is not interested in any NULLness information.
In a SQL define or PL/SQL OUT bind, if the user passes in preallocated memory for either the output variable or the indicator, then that preallocated memory is used to store result data, and all secondary memory (out-of-line memory), if any, will get deallocated. The pre-allocated memory can either come from the cache (the result of an OCIObjectNew() call), or from the client's private memory space.

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 an object define with type SQLT_NTY, client applications wanting to pre-allocate object memory must use the OCIObjectNew() function. Client applications should not allocate the object in its own private memory space, such as with malloc() or on the stack. The OCIObjectNew() function allocates the object in the object cache. The allocated object can be freed using OCIObjectFree(). Refer to Chapter 16, "OCI Navigational and Type Functions" for details on OCIObjectNew() and OCIObjectFree().

Note: There is no change to the behavior of OCIDefineObject() when the user does not pre-allocate the object memory and instead initializes the output variable to null pointer value. In this case, the object will be implicitly allocated in the object cache by the OCI library.
In a SQL define or PL/SQL OUT bind, if the user passes in a NULL address for the output variable or the indicator, memory for the variable or the indicator will be implicitly allocated by OCI.
If an output object of type SQLT_NTY is atomically NULL (in a SQL define or PL/SQL OUT bind), only the NULL indicator struct will get allocated (implicitly if necessary) and populated accordingly to indicate the atomic NULLness of the object. The top-level object, itself, will not get implicitly allocated.
An application can free indicators by calling OCIObjectFree(). If there is a top-level object (as in the case of a non-atomically NULL object), then the indicator is freed when the top-level object is freed with OCIObjectFree(). If the object is atomically null, then there is no top-level object, so the indicator must be freed separately.
The indicator struct size pointer, indsp, and program variable size pointer, pgvsp, in the OCIDefineObject() call is optional. Users can pass NULL if these parameters are not needed.

Information About Array Defines

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.

Binding And Defining Oracle C Datatypes

Previous chapters of this book have discussed OCI bind and define operations. "Binding" discussed the basics of OCI bind operations, while "Defining" 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 10, "OCI Object-Relational Programming" 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, such as OCINumber or 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.

Table 12-1 Datatype Mappings for Binds and Defines

Datatype	C Mapping	OCI External Datatype and Code
Oracle number	OCINumber	VARNUM (SQLT_VNU)
Oracle date	OCIDate	SQLT_ODT
VARCHAR2	OCIString *	SQLT_VST (see Note 1 below)
RAW	OCIRaw *	SQLT_LVB (see Note 1 below)
CHAR	OCIString *	SQLT_VST
OBJECT	struct *	Named Data Type (SQLT_NTY)
REF	OCIRef *	REF (SQLT_REF)
VARRAY	OCIArray *	Named Data Type (SQLT_NTY)
Nested Table	OCITable *	Named Data Type (SQLT_NTY)

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".

Bind and Define Examples

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". 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.

Salary Update Examples

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

The scenario for these examples is as follows:

An employee named BRUCE exists in the employees database for a hospital. See person type and employees table creation statements in the previous section.
Bruce's current job title is RADIOLOGIST.
Bruce is being promoted to RADIOLOGY_CHIEF, and along with the promotion comes a salary increase.
Hospital salaries are in whole dollar values, are set according to job title, and stored in a table called salaries, defined as follows:
```
CREATE TABLE salaries
(job_title        varchar2(20),
salary              integer));
```
Bruce's salary needs to be updated to reflect his promotion.

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.

Method 1 - fetch, convert, assign

This example uses the following method:

Do a traditional OCI define using an integer variable to retrieve the new salary from the database.
Convert the integer to an OCINumber.

Assign the new salary to Bruce.

#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 */

Method 2 - fetch, assign

This method eliminates one of the steps in Method 1:

Define an output variable of type OCINumber, so that no conversion is necessary after the value is retrieved.

Assign the new salary to Bruce

#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 */

Method 3 - direct fetch

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.

Since Bruce is pinned in the object cache, use the location of his salary attribute as the define variable, and execute/fetch directly into it.

#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 */

Summary and Notes

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.

SQLT_NTY Bind/Define Example

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.

Bind Example

/*
** 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));

Define Example

/* 
** 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));

12 Binding and Defining in Object Applications