Stored Procedure question.

AIX 4.2 - IDS 7.22 I have a function in a C library is it possible to call it from a stored procedure? -----Original Message----- From: Art S. Kagel [SMTP:kagel@bloomberg.net] Sent: Tuesday, August 10, 1999 2:25 PM To: informix-list@iiug.org Subject: Re: Posting from the Informix-list Andrew Ford wrote: > > Can anyone help me with a sysmaster tables question? > > Given a table name and a list of column names I would like to know if there is an index on that table with those columns, and if so what is the name of that index? > > I have been working on this and all I can get is a list of indexes on a given table. OK, this is not as hard as it looks but it will wear your fingers down unless the list of columns is short. You can implement it in SPL ONLY if the column list is fixed which is not practical or particularly useful but in 4GL or ESQL/C it is certainly doable: This assumes that you care which order the columns are listed in the index. If not the algorithm is suitably more complex. Steps ----------------- 1) SELECT colno FROM syscolumns c, systables t WHERE c.tabid = t.tabid AND t.tabname = "sometable" AND c.colname IN ("firstcol", "secondcol",...); If the column list is not fixed you can build the IN clause in code and prepare the statement then as you FETCH the column numbers build the statement string below and prepare that: 2) SELECT idxname FROM sysindexes i, systables t WHERE i.tabid = t.tabid AND t.tabname = "sometable" AND abs(i.part1) = col_number[0] AND abs(i.part2) = col_number[1] .... OK now an attempt to use pure SQL because if I don't Jonathan will and I was already shown up once today .... SELECT idxname FROM sysindexes i, systables t WHERE i.tabid = t.tabid AND t.tabname = "sometable" AND abs(i.part1) = ( SELECT colno FROM syscolumns c1 WHERE c1.tabid = t.tabid AND colname = "firstcol" ) AND abs(i.part2) = ( SELECT colno FROM syscolumns c1 WHERE c1.tabid = t.tabid AND colname = "secondcol" ) ... ; You can readily see how messy this will become if you do not care about the order of columns and want to see all indexes that list the columns in any order as you will have to search part1...part16 (in IDS or part8 in SE) for each column. Art S. Kagel

Howard, Paul wrote: > AIX 4.2 - IDS 7.22 > I have a function in a C library is it possible to call it from > a stored procedure? In 7.x or 8.x (or earlier), no. In 9.x or later, possibly, though you have to write the correct interface code. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>