Check for index existence before creation

Hi all, I'm writing an ESQL/C code to create indexes for tables using the following Create Index command: ... $CREATE UNIQUE INDEX pg_idx ON pg (devicetype,id1,id2,id3,id4); ... But, before creating the index, I would like to check whether the index was created. If the target index was created then skip the index creation. My question is how to check whether the index was created in ESQL/C code? What in my mind now is to ignore the created error if any as below: ... $CREATE UNIQUE INDEX pg_idx ON pg (devicetype,id1,id2,id3,id4); if (sqlca.sqlcode) { cout << "Error creating index on pg: " << " Ignoring. " << sqlca.sqlcode << endl; } Please advise. Thanks. Regards, kitming

Can't you just query the sysindexes table? Low Kit Ming <kmlow72@hotmail.com> wrote in message news:3A03DE47.409D7E47@hotmail.com... Hi all, I'm writing an ESQL/C code to create indexes for tables using the following Create Index command: ... $CREATE UNIQUE INDEX pg_idx ON pg (devicetype,id1,id2,id3,id4); ... But, before creating the index, I would like to check whether the index was created. If the target index was created then skip the index creation. My question is how to check whether the index was created in ESQL/C code? What in my mind now is to ignore the created error if any as below: ... $CREATE UNIQUE INDEX pg_idx ON pg (devicetype,id1,id2,id3,id4); if (sqlca.sqlcode) cout << "Error creating index on pg: " << " Ignoring. " << sqlca.sqlcode << endl; } Please advise. Thanks. Regards, kitming

Low Kit Ming wrote: > I'm writing an ESQL/C code to create indexes for tables using the > following Create Index command: > ... > $CREATE UNIQUE INDEX pg_idx ON pg (devicetype,id1,id2,id3,id4); > ... > > But, before creating the index, I would like to check whether the > index was created. If the target index was created then skip the > index creation. My question is how to check whether the index was > created in ESQL/C code? What in my mind now is to ignore the created > error if any as below: > > ... > $CREATE UNIQUE INDEX pg_idx ON pg (devicetype,id1,id2,id3,id4); > if (sqlca.sqlcode) > { > cout << "Error creating index on pg: " << " Ignoring. " << > sqlca.sqlcode << endl; > } > > Please advise. Thanks. This is the easiest thing to do, but it does not distinguish between errors arising because the index you wanted to create already exists and all the myriad other things that could go wrong (columns don't exist, not enough disk space, no privileges, database was taken offline, ...) If you make the code inside the if statement more discriminating, then it is a very reasonable solution. I note you're using C++ with ESQL/C; how are you orchestrating the compilation and linking? I'm curious, that's all... -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"