substitution variable

In a simple sql query or in a stored procedure language,if I want to use variables in the where condition which should be prompted for the values when I execute it (for eg:select * from emp where empno=&a (in Oracle) When I execute it,it should prompt for a& substitute it and give the o/p).How can I do it in informix?

On Fri, 03 Mar 2000 12:13:36, "Usha M Rao" <usha_mr@trigent.com> wrote: > >In a simple sql query or in a stored procedure language,if I want to use >variables in the where condition which should be prompted for the values >when I execute it >(for eg:select * from emp where empno=&a (in Oracle) When I execute it,it >should >prompt for a& substitute it and give the o/p).How can I do it in informix? In UNIX: echo "Enter string " read a dbaccess database_name - << EOF select column_list from table_name where col_name="$a";EOF

Usha M Rao wrote: > > In a simple sql query or in a stored procedure language,if I want to use > variables in the where condition which should be prompted for the values > when I execute it > (for eg:select * from emp where empno=&a (in Oracle) When I execute it,it > should > prompt for a& substitute it and give the o/p).How can I do it in informix? For simple queries use a shell script to prompt for the value and construct the SQL using the value and pass it to dbaccess, or better to Jonathan Leffler's sqlcmd in server mode, using redirection or a 'here script'. In SPL you CANNOT do this. The reason is really simple. Early on Informix had excellent development tools in ISQL/I4GL/R4GL/ESQL-C and super DBA tools in ISQL/DBACCESS/ONMONITOR etc. so never felt the need to convert SPL into a programming language but kept it as a simple way to can complex SQL. Informix expects that you can easily do what you want using shell, ISQL, 4GL or C so why muck up the engine? Also Informix has always been client/server and SPL runs in the engine in Informix NOT in the front-end tools. How would the server prompt a user on a separate client machine anyway? Oracle which did not have as broad or powerful a range of user and DBA tools early on decided to place the power into its dialect of SQL and subsequently into its version of SPL. Similarly Oracle was originally developed on monolithic IBM mainframes and only came to client server later, its philosophy assumes local users. -- Art S. Kagel & Family kagel@erols.com

On Wed, 29 Mar 2000 15:59:34 -0500, "Art S. Kagel & Family" <kagel@erols.com> wrote: >Usha M Rao wrote: >> >> In a simple sql query or in a stored procedure language,if I want to use >> variables in the where condition which should be prompted for the values >> when I execute it >> (for eg:select * from emp where empno=&a (in Oracle) When I execute it,it >> should >> prompt for a& substitute it and give the o/p).How can I do it in informix? > >For simple queries use a shell script to prompt for the value and >construct the SQL using the value and pass it to dbaccess, or better to >Jonathan Leffler's sqlcmd in server mode, using redirection or a 'here >script'. > >In SPL you CANNOT do this. The reason is really simple. Early on <snip> The only way is use this variable as parameters and then use shell script for read values and then call procedure in this script with readed values. (warning! my native language is not english) -- Jiří Lisický ČD DATIS Olomouc e-mail: lisicky@datis.cdrail.cz Nerudova 1 phone: +420-068-472-5496 Olomouc, Czech Republic >>> čeština ISO-8859-2 Compatible <<<

Jiri Lisicky wrote: > On Wed, 29 Mar 2000 15:59:34 -0500, "Art S. Kagel & Family" > <kagel@erols.com> wrote: > > >Usha M Rao wrote: > >> > >> In a simple sql query or in a stored procedure language,if I want to use > >> variables in the where condition which should be prompted for the values > >> when I execute it > >> (for eg:select * from emp where empno=&a (in Oracle) When I execute it,it > >> should > >> prompt for a& substitute it and give the o/p).How can I do it in informix? > > > >For simple queries use a shell script to prompt for the value and > >construct the SQL using the value and pass it to dbaccess, or better to > >Jonathan Leffler's sqlcmd in server mode, using redirection or a 'here > >script'. > > > >In SPL you CANNOT do this. The reason is really simple. Early on > <snip> > The only way is use this variable as parameters and then use shell > script for read values and then call procedure in this script with > readed values. > Another similar trick is to call an external app with parameters such that it can create a new SP and then just call that one. > > (warning! my native language is not english) You did fine. Art S. Kagel