If exists then do this and that....

[AoN]

Hi,

In my cross-platform development with ADO I have constructed the following kind of statement for SYBASE and MSSQL:

IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.table_name') AND name='index_name')
BEGIN
DROP INDEX table_name.index_name

...

END

How would I go about to do something similar in Informix (preferably as a single command as above)?


Thanks in advance,
Anders

 

[olded]

AoN:

Can't help you with the ADO, but Informix has a sysindexes table which works similar. Part of the primary key into sysindexes is a tabid column from the systables table.

You'll have to do something like this:

SELECT * FROM sysindexes WHERE sysindexes.idxname = <your_idx_name> and
sysindexes.tabid = (SELECT tabid FROM systables WHERE tabname = <your_tab_name>)

Regards,


Ed

 

[AoN]

I was vague in my problem specification, sorry.

How do I get a query execute something, say a drop index, IF the index exists in a 'simple' query?

Is it possible, or do I need to work it through a cursor?


Thanks,

Anders

 

[olded]

AoN:

I assumed you were using ADO with Informix. Basically, you just want to delete an index from a table if the index exists. If you have isql/dbaccess and you want to write a shell script you can do something like this:

#!/bin/ksh
export DBCOMMAND=&quot;dbaccess testdb&quot;

$DBCOMMAND <<MSG
output to temp.txt without headings
SELECT sysindexes.idxname FROM sysindexes WHERE sysindexes.idxname = &quot;ix384_1&quot;
and sysindexes.tabid = (SELECT tabid FROM systables WHERE tabname = &quot;edstemp&quot
MSG
indexname=`sed /^$/d | sed &quot;s/ //g&quot; temp.txt`
rm temp.txt

if [ -z $indexname ]
then
: # index does NOT exist
else
$DBCOMMAND << MSG
DROP INDEX $indexname;
MSG
fi

Using the Unix here document facility, I use dbaccess to execute the find index select and send it to a file. If the index is in the file, I call dbaccess again to drop it.

Another way to do it, is to create a Stored Procedure to perform the exact thing. You could pass the stored procedure the table name and index name and if the index exists, drop it.

I don't mind submitting a S.P. solution if you want one.

Regards,


Ed

 

[olded]

AoN:

I better backtrack on that SP. The Informix SPL is rather brain dead. The ability of creating cursors in SPL is limited. I need to think about a SP solution.


Ed

 

[AoN]

olded:

I just want to be able to write a statement so I can execuse it and it drops the index if it exists. Wether I do it via dbaccess, ADO, embeddedsql or what not doesn't matter.

Now, I realise it might not be possible to do it the way I want it, and then I'll settle to use a recordset to see if it gets populated and if it is, I drop the index. I just think a single statement is more tidy so I hope it is possible.

//
AoN