<img src="02cr_in7.gif" height="49" width="100"> CREATE OPAQUE TYPE

Element	Purpose	Restrictions	Syntax
length	Number of bytes needed by the database server to store a value of this data type	The number must match the positive integer reported when the C language sizeof() directive is applied to the type structure.	Literal Number, p. 4-237
type	Name of the new opaque data type	The name you specify must follow the conventions of SQL identifiers. The type must be unique within the database. In an ANSI-compliant database, the combination owner.type must be unique within the database.	Identifier, p. 4-205 Data Type, p. 4-53

The CREATE OPAQUE TYPE statement registers a new opaque type in the database. Dynamic Server stores information on extended data types, including opaque types, in the sysxtdtypes system catalog table.

To create an opaque type, you must have the Resource privilege on the database. When you create the opaque type, only you, the owner, have the Usage privilege on this type. Use the GRANT or REVOKE statements to grant or revoke the Usage privilege to other database users.

To find out what privileges exist on a particular type, check the sysxtdtypes system catalog table for the owner name and the sysxtdtypeauth system catalog table for additional type privileges that might have been granted. For more information on system catalog tables, see the Informix Guide to SQL: Reference.

The actual name of an opaque type is an SQL identifier. When you create an opaque type, the name must be unique within a database.

When you create an opaque type in an ANSI-compliant database, owner.type_name must be unique within the database.

The owner name is case sensitive. If you do not put quotes around the owner name, the name of the opaque-type owner is stored in uppercase letters.

The INTERNALLENGTH modifier specifies the size of an opaque type. The way you specify the internal length defines whether the opaque type is fixed length or varying length.

A fixed-length opaque type has an internal structure that has a fixed size. To create a fixed-length opaque type, specify the size of the internal structure, in bytes, for the INTERNALLENGTH modifier. The following statement creates a fixed-length opaque type called fixlen_typ. The database server allocates 8 bytes for this type.

A varying-length opaque type has an internal structure whose size might vary from one instance of the opaque type to another. For example, the internal structure of an opaque type might hold the actual value of a string up to a certain size but beyond this size it might use an LO-pointer to a CLOB to hold the value.

To create a varying-length opaque type, use the VARIABLE keyword for the INTERNALLENGTH modifier. The following statement creates a variable-length opaque type called varlen_typ:

To define the opaque type to the database server, you must provide the following information in the C language:

Element	Purpose	Restrictions	Syntax
align_value	Byte boundary on which the database server aligns the opaque type when passing it to a user-defined routine	The alignment must be 1, 2, 4, or 8, depending on the C definition of the opaque type and the hardware and compiler used to build the object file for the type. If alignment is not specified, the system default is 4 bytes.	Literal Number, p. 4-237
length	Maximum length in bytes to allocate for instances of the type in varying-length opaque types If maximum length is not specified for a variable-length type, the default is 2 kilobytes.	The length must be a positive integer less than or equal to 32 kilobytes. Do not specify for fixed-length types. Values that exceed this length return errors.	Literal Number, p. 4-237


Function	Purpose	Invoked
input	Converts the opaque type from its external LVARCHAR representation to its internal representation	When a client application sends a character representation of the opaque type in an INSERT, UPDATE, or LOAD statement
output	Converts the opaque type from its internal representation to its external LVARCHAR representation	When the database server sends a character representation of the opaque type as a result of a SELECT or FETCH statement
receive	Converts the opaque type from its internal representation on the client computer to its internal representation on the server computer Provides platform-independent results regardless of differences between client and server computer types.	When a client application sends an internal representation of the opaque type in an INSERT, UPDATE, or LOAD statement
send	Converts the opaque type from its internal representation on the server computer to its internal representation on the client computer Provides platform-independent results regardless of differences between client and database server computer types.	When the database server sends an internal representation of the opaque type as a result of a SELECT or FETCH statement
import	Performs any tasks need to convert from the external (character) representation of an opaque type to the internal representation for a bulk copy	When DB-Access (LOAD) or the High Performance Loader initiates a bulk copy from a text file to a database
export	Performs any tasks need to convert from the internal representation of an opaque type to the external (character) representation for a bulk copy	When DB-Access (UNLOAD) or the High Performance Loader initiates a bulk copy from a database to a text file
importbinary	Performs any tasks need to convert from the internal representation of an opaque type on the client computer to the internal representation on the server computer for a bulk copy	When DB-Access (LOAD) or the High Performance Loader initiates a bulk copy from a binary file to a database
exportbinary	Performs any tasks need to convert from the internal representation of an opaque type on the server computer to the internal representation on the client computer for a bulk copy	When DB-Access (UNLOAD) or the High Performance Loader initiates a bulk copy from a database to a binary file
assign()	Performs any processing required before storing the opaque type to disk This support function must be named assign().	When the database server executes an INSERT, UPDATE, and LOAD statement, before it stores the opaque type to disk
destroy()	Performs any processing necessary before removing a row that contains the opaque type This support function must be named destroy().	When the database server executes the DELETE and DROP TABLE statements, before it removes the opaque type from disk
lohandles()	Returns a list of the LO-pointer structures (pointers to smart large objects) in an opaque type	Whenever the database server must search opaque types for references to smart large objects: when the oncheck utility runs, when an archive is performed
compare()	Compares two values of the opaque type and returns an integer value to indicate whether the first value is less than, equal to, or greater than the second value	When the database server encounters an ORDER BY, UNIQUE, DISTINCT, or UNION clause in a SELECT statement, or when it executes the CREATE INDEX statement to create a B-tree index

Once you write the necessary support functions for the opaque type, use the CREATE FUNCTION statement to register these support functions in the same database as the opaque type. Certain support functions convert other data types to or from the new opaque type. After you create and register these support functions, use the CREATE CAST statement to associate each function with a particular cast. The cast must be registered in the same database as the support function.

When you have written the necessary source code to define the opaque type, you then use the CREATE OPAQUE TYPE statement to register the opaque type in the database.

Informix Guide to SQL: Syntax
SQL Statements

CREATE OPAQUE TYPE

Usage

Privileges on Opaque Types

Naming an Opaque Type

INTERNALLENGTH Modifier

Fixed-Length Opaque Types

Varying-Length Opaque Types

Opaque-Type Modifier

Defining an Opaque Type

Related Information

Informix Guide to SQL: SyntaxSQL Statements