Informix GUID opaque type -9832 error

Hi all!! I'm facing an issue when I'm trying to use a GUID opaque type. I've created the GUID opaque type, as well as the requested casts, as described below: CREATE OPAQUE TYPE guid (INTERNALLENGTH=16, ALIGNMENT=4); 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 (guid2 AS impexpbin WITH guid_expbin); CREATE IMPLICIT CAST (impexpbin AS guid WITH guid_impbin); So I created an example table in order to reproduce the issue and tried to create the index again (AGS Server Studio execution log): 29/05/17 18:19 Executing statement: create table teste ( id_aplicacao GUID not null, ds_aplicacao VARCHAR(50) notnull ) extent size 16 next size 16 lock mode row; Exec Time: 00:00:00,01; Statement executed 29/05/17 18:19 Executing statement: create unique index idx_pk on teste (id_aplicacao) using btree;SQL Error (-9832): Could not find routine (compare) while resolving compare routine. Error Position: Ln: 84 Col: 26 THE ISSUE: I don't have any experience using opaque types that require the "compare" function. I noticed I have several of those "compare" functions implicitly created on the instance, but I believe it requires some specific function. The reason I'm trying to use GUID is I'm trying to move an application that is using PostgreeSQL to Informix. Can anybody help me on that? Regards, Alberto Pessonio.

In order to create an index on a type that type has to support a "compare" function akin to strcmp( char *, char * ) that compares two items of whatever type (so in your case two GUID elements). The definition of this function is described in the Informix User-Defined Routines and Data Types Developer's Guide: If you define a compare() function, you must also define the greaterthan(), lessthan(), equal(), or other functions that use the compare function. For the database server to be able to sort an opaque type, you must define a compare() function that handles the opaque type. The compare() function must follow these rules: 1. The name of the function must be compare(). However, the name is not case sensitive; the compare() function is the same as the Compare() function. 2. The function must accept two arguments, each of the data types to be compared. 3. The function must return an integer value to indicate the result of the comparison, as follows: <0 to indicate that the first argument is less than (<) the second argument 0 to indicate that the two arguments are equal (=) >0 to indicate that the first argument is greater than (>) the second argument SO, you need to build compare(), lessthan(), equal(), and greaterthan() functions for your GUID type. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, May 29, 2017 at 5:24 PM, ALBERTO ROMEU PESSONIO FILHO < albasic@gmail.com> wrote: > Hi all!! > > I'm facing an issue when I'm trying to use a GUID opaque type. > > I've created the GUID opaque type, as well as the requested casts, as > described below: > > CREATE OPAQUE TYPE guid (INTERNALLENGTH=16, ALIGNMENT=4); > > 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 (guid2 AS impexpbin WITH guid_expbin); > CREATE IMPLICIT CAST (impexpbin AS guid WITH guid_impbin); > > So I created an example table in order to reproduce the issue and tried to > create the index again (AGS Server Studio execution log): > > 29/05/17 18:19 Executing statement: > create table teste ( id_aplicacao GUID not null, ds_aplicacao VARCHAR(50) > not> null ) extent size 16 next size 16 lock mode row; > Exec Time: 00:00:00,01; Statement executed > > 29/05/17 18:19 Executing statement: > create unique index idx_pk on teste (id_aplicacao) using btree;> SQL Error (-9832): Could not find routine (compare) while resolving compare > routine. > Error Position: Ln: 84 Col: 26 > > THE ISSUE: I don't have any experience using opaque types that require the > "compare" function. I noticed I have several of those "compare" functions > implicitly created on the instance, but I believe it requires some specific > function. > > The reason I'm trying to use GUID is I'm trying to move an application > that is > using PostgreeSQL to Informix. > > Can anybody help me on that? > > Regards, > Alberto Pessonio. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >