RE: Basic SPL Questions

What you are trying is dynamic SQL in SPL, this isn't allowed. But you can get a Dynamic SQL blade from www.iiug.org if you are using the later engines. But this won't help 'cos you can do drops using the blade. What you can do is get your SPL to write another SPL and then run that one Paul Watson # WF Software Ltd # You are only young once Tel: +44 1436 674729 # but you can be immature Fax: +44 1436 678693 # forever www.wfsoftware.com # > -----Original Message----- > From: Gary Smith [mailto:g.s.smith@marconi.com] > Sent: Thursday, June 15, 2000 11:57 AM > To: informix-list@iiug.org > Subject: Basic SPL Questions > > > Hi folks > > I am fairly new to Informix and I'm struggling to find how to > get things > done in Informix that I have been used to doing in other RDBMS's. > > So, consider the following SPL: > > CONNECT TO 'ext@fv'; > --DROP PROCEDURE DropAllTables; > > CREATE PROCEDURE DropAllTables() > DEFINE TableName LIKE systables.tabname;> > FOREACH SELECT tabname INTO TableName FROM systables WHERE > tabname LIKE > 'fw_%' OR tabname LIKE 'FW_%' > DROP TABLE TableName;> END FOREACH > END PROCEDURE; > > > The problem with the above is that it literally tries to drop > the table > 'TableName' whereas I obviously want some macro var > substitution (e.g. DROP > TABLE &&TableName) to substitute the value of TableName prior > to execution. > Is such a thing possible in Informix ? Should I be > approaching this another > way around ? (surely I don't have to write batch files for such an > elementary operation ?) > > Also, how can I check if the procedure has already been > defined and thus > drop the old DropAllTables() before defining the new one ? > > Also, are there any sites out there with common SPL examples > that I can blag > ? > > > Thanks > > Gary > > The contents of this e-mail are confidential to the ordinary user(s) of the mail address(es) to which it was sent and may be legally privileged. If you are not the intended recipient, any disclosure, copying, distribution or use of it, or any part of it, in any form whatsoever, and any actions taken or omitted to be taken in reliance on it, is prohibited and may be unlawful.