The SQL opaque data type

An opaque data type is a user-defined data type that can be used in the same way as the HCL OneDB™ built-in data types. The opaque data type allows you to define new data types for your database applications.

An opaque data type is fully encapsulated; the database server does not know about the internal format of an opaque data type. Therefore, the database server cannot make assumptions about how to access a column having an opaque data type. The database developer defines a data structure that holds the opaque-type information and support functions that tell the database server how to access this data structure.

For more information about how to create an opaque data type, see the description of the CREATE OPAQUE TYPE statement in the HCL OneDB Guide to SQL: Syntax and in HCL OneDB User-Defined Routines and Data Types Developer's Guide.

You can access the value of an opaque data type from the application in one of two ways:
  • In the external format, as a character string

    Transfer of the external format between the client application and database server is supported by the database server through the input and output support functions of the opaque data type.

  • In the internal format, as a data structure in an external programming language (such as C)

    Transfer of the internal format between the client application and database server is supported by the database server through the receive and send support functions of the opaque data type.

The following list shows the data types you can use to access an opaque data type.
HCL OneDB data type
ESQL/C host variable
External format of an opaque data type
lvarchar host variable
Internal format of an opaque data type
fixed binary host variable

var binary host variable

This section uses an opaque data type called circle to demonstrate how lvarchar and fixed binary host variables access an opaque data type. This data type includes an x,y coordinate, to represent the center of the circle, and a radius value. The following figure shows the internal data structures for the circle data type.
Figure 1: Internal data structures for the circle opaque data type
typedef struct 
   {
   double      x;
   double      y;
   } point_t;

typedef struct
   {
   point_t         center;
   double         radius;
   } circle_t;
The following figure shows the SQL statements that register the circle data type and its input, output, send, and receive support functions in the database.
Figure 2: Registering the circle opaque data type
CREATE OPAQUE TYPE circle (INTERNALLENGTH = 24,
   ALIGNMENT = 4);

CREATE FUNCTION circle_in(c_in lvarchar) RETURNS circle
   EXTERNAL NAME '/usr/lib/circle.so(circle_input)'
   LANGUAGE C;
CREATE IMPLICIT CAST (lvarchar AS circle WITH circle_in);

CREATE FUNCTION circle_out(c_out circle) RETURNS lvarchar
   EXTERNAL NAME '/usr/lib/circle.so(circle_output)'
   LANGUAGE C;
CREATE IMPLICIT CAST (circle AS lvarchar WITH circle_out);

CREATE FUNCTION circle_rcv(c_rcv sendrcv) RETURNS circle
   EXTERNAL NAME '/usr/lib/circle.so(circle_receive)'
   LANGUAGE C;
CREATE IMPLICIT CAST (sendrcv AS circle WITH circle_rcv);

CREATE FUNCTION circle_snd(c_snd circle) RETURNS sendrcv
   EXTERNAL NAME '/usr/lib/circle.so(circle_send)'
   LANGUAGE C;
CREATE IMPLICIT CAST (circle AS sendrcv WITH circle_snd);

CREATE FUNCTION radius(circle) RETURNS FLOAT
   EXTERNAL NAME '/usr/lib/circle.so'
   LANGUAGE C;
Suppose the input and output functions of the circle data type define the following external format that the following figure shows.
Figure 3: External Format of the circle Opaque data type


The following figure shows the SQL statements that create and insert several rows into a table called circle_tab, which has a column of type circle.
Figure 4: Creating a column of the circle opaque data type
CREATE TABLE circle_tab (circle_col  circle);
INSERT INTO circle_tab VALUES ('(12.00, 16.00, 13.00)');
INSERT INTO circle_tab VALUES ('(6.5, 8.0, 9.0)');