DROP TABLE Problem 
Author Message
 DROP TABLE Problem

I'm running Informix SE 7.22 on SCO OpenServer 5.0.4. I want to remove
a set of table from a database and I want to use a stored procedure.
All the tables have names starting with "r_". I wrote the following
procedure:

create procedure rm_rel()

define qqqq char(255);

trace on;

foreach select tabname into qqqq from systables where tabname like "r_%"

    trace qqqq;
    drop table qqqq;
end foreach

end procedure;

but I received the following report:

trace on

start select cursor.
select tabname
  from systables
  where (like tabname, "r_%")
select cursor iteration.
select cursor returns r_121_76_01
trace expression :r_121_76_01

drop table qqqq;
exception : looking for handler
SQL error = -206 ISAM error = -111  error string =  = "qqqq"
exception : no appropriate handler

Am I missing something? I looked into the manuals (RTFM) but I found no
(significative) help.

Any idea?

TIA

Paolo Agati

-----------------------------------------------------------
Paolo Agati                        CO.R.EL. Italiana S.r.l.
                                   Via Tavagnacco, 91




Wed, 18 Jun 1902 08:00:00 GMT
 DROP TABLE Problem

Quote:

> I'm running Informix SE 7.22 on SCO OpenServer 5.0.4. I want to remove
> a set of table from a database and I want to use a stored procedure.

This cannot be done in a stored procedure as a general purpose function.
You cannot use the contents of a host variable in place of a tablename.
You also cannot build a DELETE statement in a string an prepare it in
a stored procedure.  The only chance might be to build the string into
a shell command to pipe the command back into another dbaccess session.
Never tried it but the idea has promise.

Art S. Kagel



Wed, 18 Jun 1902 08:00:00 GMT
 DROP TABLE Problem

Quote:

> I'm running Informix SE 7.22 on SCO OpenServer 5.0.4. I want to remove
> a set of table from a database and I want to use a stored procedure.
> All the tables have names starting with "r_". I wrote the following
> procedure:

> create procedure rm_rel()
> define qqqq char(255);
> foreach select tabname into qqqq from systables where tabname like "r_%"
>     drop table qqqq;
> end foreach
> end procedure;

> but I received the following report:
> SQL error = -206 ISAM error = -111  error string =  = "qqqq"

-206: no such table or words to that effect

Quote:
> Am I missing something?

Yup -- what you're trying to do is called Dynamic SQL,
and you cannot do Dynamic SQL in SPL (Stored Procedure Language).

The table names in SPL are fixed when the SP is created.

--

Guardian of DBD::Informix -- see http://www.perl.com/CPAN
#include <disclaimer.h>



Wed, 18 Jun 1902 08:00:00 GMT
 DROP TABLE Problem

Quote:

> I'm running Informix SE 7.22 on SCO OpenServer 5.0.4. I want to remove
> a set of table from a database and I want to use a stored procedure.
> All the tables have names starting with "r_". I wrote the following
> procedure:

> create procedure rm_rel()

> define qqqq char(255);

> trace on;

> foreach select tabname into qqqq from systables where tabname like "r_%"

>     trace qqqq;
>     drop table qqqq;
> end foreach

> end procedure;

> but I received the following report:

> trace on

> start select cursor.
> select tabname
>   from systables
>   where (like tabname, "r_%")
> select cursor iteration.
> select cursor returns r_121_76_01
> trace expression :r_121_76_01

> drop table qqqq;
> exception : looking for handler
> SQL error = -206 ISAM error = -111  error string =  = "qqqq"
> exception : no appropriate handler

> Am I missing something? I looked into the manuals (RTFM) but I found no
> (significative) help.

> Any idea?

> TIA

> Paolo Agati

> -----------------------------------------------------------
> Paolo Agati                        CO.R.EL. Italiana S.r.l.
>                                    Via Tavagnacco, 91



 You can't do this in SPL.  Convert your procedure to a script and call the
script via the SPL.

--
Paul Watson                    #
WF Software Ltd                # I don't suffer from stress -
Tel: +44 1436 674729           # I'm a carrier.
Fax: +44 1436 678693           #
www.wfsoftware.co.uk           #



Wed, 18 Jun 1902 08:00:00 GMT
 DROP TABLE Problem

Thanks a lot for the answers. A viable solution has been shown by
Art Kagel:

     This cannot be done in a stored procedure as a general purpose
     function.
     You cannot use the contents of a host variable in place of a tablename.

     You also cannot build a DELETE statement in a string an prepare it in
     a stored procedure.  The only chance might be to build the string into
     a shell command to pipe the command back into another dbaccess session.

     Never tried it but the idea has promise.

     Art S. Kagel

Paolo



Wed, 18 Jun 1902 08:00:00 GMT
 DROP TABLE Problem

Yes! you can't use vars for this sort of statements, and of course, neither
can you prepare them. I'm afraid your only option is to use SYSTEM to
fire dbaccess, passing it a "DROP TABLE "|| qqqq (actual details left as an
exercise for the reader :-)

Ciao,
Marco
______________________________________________________________________________

Informix faq                     http://www.iiug.org/techinfo/faq/informix.htm
4glworks                                        http://www.ctonline.it/~marcog
Informix on Linux                 http://www.ctonline.it/~marcog/ifmxlinux.htm

Quote:

> I'm running Informix SE 7.22 on SCO OpenServer 5.0.4. I want to remove
> a set of table from a database and I want to use a stored procedure.
> All the tables have names starting with "r_". I wrote the following
> procedure:

> create procedure rm_rel()

> define qqqq char(255);

> trace on;

> foreach select tabname into qqqq from systables where tabname like "r_%"

>     trace qqqq;
>     drop table qqqq;
> end foreach

> end procedure;

> but I received the following report:

> trace on

> start select cursor.
> select tabname
>   from systables
>   where (like tabname, "r_%")
> select cursor iteration.
> select cursor returns r_121_76_01
> trace expression :r_121_76_01

> drop table qqqq;
> exception : looking for handler
> SQL error = -206 ISAM error = -111  error string =  = "qqqq"
> exception : no appropriate handler

> Am I missing something? I looked into the manuals (RTFM) but I found no
> (significative) help.

> Any idea?

> TIA

> Paolo Agati

> -----------------------------------------------------------
> Paolo Agati                        CO.R.EL. Italiana S.r.l.
>                                    Via Tavagnacco, 91





Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. DROP Table Problem - MySQL

2. drop table problems

3. drop table problem

4. ODBC drop table problem

5. Can't Drop Table Problem

6. drop table problem (11.9.2)

7. Drop table problem???

8. Problems dropping indexes when dropping tables

9. Problem with Drop Table and truncate table

10. HELP! drop table and create table problems

11. Drop table versus truncate and drop index


 
Powered by phpBB® Forum Software