Introduction

The Informix® Dynamic Server 9.x (IDS 9.x) supports multiple methods to create unique identifiers. The original method is to use the SERIAL type with a unique constraint. IDS 9.x adds the SERIAL8 type that is an 8-byte integer value that virtually gives an unlimited number of identifiers. With the use of a unique constraint, the SERIAL8 can virtually provide unlimited number of unique identifiers. Both SERIAL and SERIAL8 are specific to a given table.

As of IDS 9.4, we can also use a SEQUENCE object to generate an identifier that can then be used in any table in the database. This virtually insures a unique identifier for any table within a database as long as the generation is done through one specific sequence.

With the raising popularity of distributed systems and collaboration between corporations, there is a need for the generation and manipulation of identifiers that are guaranteed to be unique worldwide. This can be done with the help of the operating systems capabilities to generate a Globally Unique Identifier (GUID) and the use of an opaque type that can manipulate the GUID.

This article describes the implementation of a new data type that represents a GUID. This includes a User-Define Routine (UDR) that generates a GUID with the help of the operating system.

Back to top

GUID

This type of identifier is also known as UUID: Universally Unique Identifier. It is represented by a series of hexadecimal digits such as:

2ac07282-184e-4103-ad2a-ad8abdab8f0b

This is a 36 character string. Its internal representation is a 16-byte binary string. If we were to use its character representation, we would use more than double the required space. What we need is a new data type that can accommodate both the internal representation and the external representation.

Back to top

Opaque types

IDS 9.4 allows you to create your own types. One of them is called an opaque type where you define its internal structure:

CREATE OPAQUE TYPE Guid
(INTERNALLENGTH=16, ALIGNMENT=4);

In our case, it is a fixed-length opaque type. IDS 9.x also supports variable-size opaque types. Since it is opaque, you must provide additional function to the database server to allow it to manipulate it.

The required functions are as follows:

• Input/Output: Convert from the external (character) to the internal (binary) representation and vice versa.
• Import/Export: These functions are similar to the Input/Output functions but are used for bulk loading and unloading.
• Send/Receive: Provides binary conversions for communication between client and server. This conversion is required to convert between the byte ordering of the server and client.
• ImportBinary/ExportBinary: Similar to Import/Export for binary data.
• Comparison functions: Needed for comparison in WHERE clauses and for indexing.

For our purpose, we need to implement the Input/Output, Send/Receive, and comparison functions. The first four functions are mapped to the opaque type with cast definitions. Since their functionalities also provide the Import/Export and ImportBinary/ExportBinary functions, we can use their implementations and create additional casts that take the appropriate type of arguments. The cast statements are as follows:

CREATE IMPLICIT CAST (lvarchar AS GUID WITH guid_in);
CREATE CAST (guid AS lvarchar WITH guid_out);
CREATE IMPLICIT CAST (impexp AS GUID WITH guid_imp);
CREATE CAST (guid AS impexp WITH guid_exp);
CREATE CAST (guid AS sendrecv WITH guid_send);
CREATE IMPLICIT CAST (sendrecv AS guid WITH guid_recv);
CREATE CAST (guid AS impexpbin WITH guid_expbin);
CREATE IMPLICIT CAST (impexpbin AS guid
                      WITH guid_impbin);

The first two statements provide the conversions between the external representation (character) and the internal representation (binary). The following two provide the functionality for load and unload in bulk from the character representation like in the case of the dbaccess functions load and unload. It is followed by two casts that indicate how to transfer the binary representation between a client and the database server. The last two provide the mapping for bulk load and unload in binary format.

For more information on opaque types, consult the manuals listed in the reference section at the end of this article.

Back to top

Generating a GUID

In addition to supporting the new GUID type, it is useful to be able to generate a globally unique identifier. We can create a function called makeguid(). That can be used in several ways:

EXECUTE FUNCTION makeguid();
INSERT INTO tab(guidcol) VALUES(makeguid());

The first method allows you to retrieve a new GUID and use it. The second method generates a GUIDand inserts it into a table with a column of type GUID.

Back to top

Using the GUID data type

Once registered with a database, the GUID type is a fully supported type just like any other such as DECIMAL or INTEGER. It can be used as a primary key or as part of an index. You can insert values into that column using either its internal format or its character representation, or use it in the WHERE clause:

CREATE TABLE tab( col1 guid);
INSERT INTO tab VALUES("2ac07282-184e-4103-ad2a-ad8abdab8f0b");
SELECT col1 FROM tab WHERE col1 > "0ac07282-184e-4103-ad2a-ad8abdab8f0b";

In the last example, we see that the column col1 of type GUID is compared to the character representation of a GUID. This comparison is allowed because the implementation defines an implicit cast between a character string and a GUID. Before the comparison is made, the character string is converted to a GUID's internal representation.

Back to top

Accessing a GUID from a program

Most programming interfaces provide two ways to retrieve the result of an SQL statement. It either retrieves it in character format or in binary format. If you don't want to have to manipulate the internal representation (binary), you can convert the value of the column to its character format explicitly. For example:

SELECT col1::lvarchar FROM tab;

This expression cast col1 as an lvarchar which is a form of variable-size character string. This, in fact, converts the internal representation to the external representation that is a 36-byte character string.

Back to top

Accessing a GUID column from a java program

To manipulate the new data type from a Java program, you must first create a Java class that represents it. This new class must implement the SQLData interface. This interface defines three methods:

• getSQLTypeName: This method returns the name of the SQL type.
• readSQL: This method receives an data stream (SQLInput) and uses it to initialize the content of the object.
• writeSQL: This method receives an output stream (SQLOutput) and writes the content of the object to it.

In the case of the Guid type, the readSQL and writeSQL methods are quite simple since they simply read or write 16 bytes to or from the stream.

Before you can use the new class, you must register it in the type map. Here is a code fragment that registers the Guid class to manipulate this opaque type:

java.util.Map map = conn.getTypeMap();
map.put("Guid", Class.forName("Guid"));
conn.setTypeMap(map);

There are basically two operations you want to perform with a Guid object as it relates to SQL statements: pass a Guid as argument and receive a Guid from a ResultSet.

To pass a Guid as argument to an SQL statement, you use the setObject method of a PreparedStatement object:

pstmt = conn.prepareStatement(sqlstmt);
Guid myGuid =
  new Guid("0ac07282-184e-4103-ad2a-ad8abdab8f0b");
pstmt.setObject(1, myGuid);

You could also pass the argument as a character string using the setString() method. To retrieve a Guid columns from a ResultSet with the getObject() method:

Guid col1Guid = (Guid)rs.getObject(1);

You then manipulate the result as a Guid object and follow the standard Java processing.

Back to top

Accessing a GUID column with a ESQL/C program

As for the Java program, you can pass the value of a Guid as a character string to the prepared statement when opening a cursor. As for receiving a Guid in binary format, you can declare a structure and use it in a fixed binary declaration:

typedef struct Guid {
  unsigned char values[16];
} GUID;
EXEC SQL BEGIN DECLARE SECTION;
fixed binary 'guid' GUID id;
EXEC SQL END DECLARE SECTION;

With these declarations, you can then fetch into the id variable and use it as it is declared: an array of 16 unsigned characters.

Back to top

Example implementation

This article comes with an example implementation that implements all the functions discussed above. It also includes the client implementation for Java and ESQL/C.

Please consult the README file for details on how to compile and use it.

Back to top

Limitations

The example implementation has been tested on Linux and Windows 2000. The Windows 2000 version does not implement the makeguid() function so its execution returns an the error "makeguid() Not implemented". The makeguid() function depends on the uuid_generate() function call provided by the operating system platform. This Linux function is conforms to the OSF DCE 1.1 specification for universally unique identifiers.

If you want to use this implementation on any Unix platform, you simply have to insure that the uuid_generate() function or an equivalent is available on your platform of choice. Otherwise, you can simply not use the makeguid() function and still use the Guid type.

If you are using AIX, look at the definition of uuid_t under dce/uuid.h and at the function uuid_create().

This implementation may generate exceptions when an input string does not follow the proper format or if you are calling makeguid() if it is not implemented. These errors come directly from the code. You can create your own exceptions in the system catalog that will allow you to internationalize them.

The guid_in() input function assumes that the characters coming in are in the ASCII format. You will need to re-write it if your environment is different.

Back to top

Conclusion

IDS 9.x provides the ability to integrate new data types into the database. This gives you the flexibility to better integrate the database server with the design of your business solution. The GUID type is an example of this capability. It is treated as a first class type of object within the database.

The integration of new types in the database allows you to take advantage of the strengths of the relational model, such as searching, sorting, and grouping, instead of having to reproduce these capabilities in client code. This approach can simplify your solution and result in a business advantage over your competitors.

Back to top

Download

Name	Size	Download method
uuid.zip	18KB	FTP | HTTP

Information about download methods

Resources

• Informix Dynamic Server.2000: Server-Side Programming in C, Informix Press, ISBN 0-13-013709-X, Jacques Roy
  
  
• IBM Informix DataBlade API Programmer’s Guide, Version 9.4, (G251-1258-00)
  
  
• IBM Informix DataBlade API Function Reference, Version 9.4, (G251-1257-00)
  
  

About the author

Jacques Roy is a member of IBM's worldwide sales support organization. He has over 20 years of industry experience and over 5 years of experience with database extensibility. He is the author of Informix Dynamic Server.2000: Server-Side Programming in C and co-author of Open-Source Components for the Informix Dynamic Server 9.x.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• GUID
• Opaque types
• Generating a GUID
• Using the GUID data type
• Accessing a GUID from a program
• Accessing a GUID column from a java program
• Accessing a GUID column with a ESQL/C program
• Example implementation
• Limitations
• Conclusion
• Download
• Resources
• About the author
• Comments

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers