GUID/UUID Columns
The ODBC Driver supports SQL_C_GUID and SQL_GUID data types for the following DBMS data types:
• UUID
• character data types char, varchar, nchar, and nvarchar
• binary and varbinary
The ODBC data type called GUID for "Globally Unique Identifier" is mapped to the DBMS data type of UUID for "Universally Unique Identifier". They are roughly equivalent. They have the same display format and the same internal binary length.
The display format is 36 characters long:
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
where x's are alphanumeric (0-9, a-f) hex representations of the internal fields within the GUID.
The internal format is 16 bytes of binary values, consisting of a few integer type fields followed by a binary array of 8 bytes. The ODBC SQLGUID structure defines the integer and binary fields within this structure.
There is a subtle difference in the internal format for ODBC GUID versus DBMS UUID. The ODBC GUID (structure SQLGUID) contains 3 integer fields at the beginning which are in platform-endianness format, whereas the DBMS UUID internal format exposed to applications is simply a 16-byte binary object and the 3 integer fields are always in big-endian format regardless of the platform.
For ODBC applications that wish to access the internal format, it is recommended to use the ODBC SQLGUID structure and map the C type to SQL_C_GUID. This will provide consistent results across different DBMS's. This pertains to both sending parameters for a query as well as selecting results. The SQL (DBMS) data type in general would be SQL_GUID, which is an Ingres or Vector column defined as UUID.
The GUID / UUID data type can also be handled in the display format. A conversion is done between the internal and display formats when the C and the SQL data types are different; specifically, if one is a character data type and the other is the GUID or UUID data type.
Example:
If a DBMS character column contains a valid UUID display format string, it can be selected into a SQL_C_GUID data type in the application. Conversely, an application with a SQL_C_CHAR data type containing a GUID display format string can be sent to the DBMS to update a UUID column, by binding the SQL type to SQL_GUID.
The default C type for a SQL type of SQL_GUID (that is, UUID in the database) is SQL_C_GUID.
In the rare case that the raw binary value of the DBMS internal format of a UUID column is desired, then the column (or parameter) should be bound to a C type of SQL_C_BINARY. This will copy the internal DBMS value, without accounting for platform endianness.
Note: JDBC does not have a GUID or UUID data type, so it is handled as a single 16-byte binary object by the Actian JDBC driver; the value is the DBMS format (big endian for the 1st 3 fields in the structure). Hence the Actian JDBC driver returns the same as an ODBC application that uses the BINARY binding, but is different from the ODBC SQL_C_GUID binding.
Conversions to or from GUID/UUID and other data types besides the above are not supported and will result in a conversion error. For example, a DBMS UUID column cannot be selected into a C date or integer data type.
Last modified date: 08/14/2024