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

Gary Smith wrote: > > 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. Cannot do it with a stored procedure. > 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 ?) What si the difference between a shell script (batch file) and a stored procedure? Actually most of us would write quick a 4GL program using R4GL to can such procedures or even an ESQL program since ESQL is now free. However, a shell script with a few good utilities thrown in will do, see below. > Also, how can I check if the procedure has already been defined and thus > drop the old DropAllTables() before defining the new one ? Select 1 from sysprocedures where procname = "DropAllTables"; > Also, are there any sites out there with common SPL examples that I can blag > ? Check out the International Informix User's Group (IIUG) Software Repository at www.iiug.org/software. By the way I would use my dbschema replacement utility, myschema, (which accepts wildcards in tablenames) along with my awk script, mk_drop.awk, to create a set of drop table statements: myschema -h fv -d ext -t 'fw_*' | awk -f mkdrop.awk | dbaccess ext@fv - Myschema is part of the package utils2_ak and my awk scripts are in the package utils4_ak both available from the IIUG Software Repository. Art S. Kagel

Gary Smith wrote: > 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 ?) > [COMMENTS BY AVI] What you are trying to do here is known as "dynamic SQL", and it can't be done in a stored procedure. You have two options: 1. Use ESQL/C 2. As you stated - use a batch file. [END COMMENTS BY AVI] > > Also, how can I check if the procedure has already been defined and thus > drop the old DropAllTables() before defining the new one ? > [COMMENTS BY AVI] You can query the table "sysprocedures", like so SELECT procname FROM sysprocedures WHERE procname = 'DropAllTables' [END COMMENTS BY AVI] > > Also, are there any sites out there with common SPL examples that I can blag > ? > [COMMENTS BY AVI] I'm not sure, but you may find something at http://www.iiug.org or http://www.informix.com [END COMMENTS BY AVI] > > Thanks > > Gary Hope this has been of use to you, Avi. -- /\\ \\ /| Avi Abrami, Analyst/Programmer, Terayon Comms /__\\ \\ / | eMail: avia@terayon.com / \\ \\/ | http://www.terayon.com