Re: calling stored procedures
The whole point of putting the original SQL statements in a stored procedure was to eliminate the overhead of preparing them each time they are executed. Using dynamic SQL to call them defeats that purpose. Doesn't Informix have something corresponding to Oracle OCI or Sybase OPEN-Library? Dennis Irwin Goldstein wrote: > > Dennis McCarthy <drm0@gte.com> wrote in article <32AEBD4A.167E@gte.com>... > > I need to write a program which will call a stored procedure > > without compiling the name of the stored procedure into the > > program. The program will get the name of the stored procedure > > at run time. Can this be done with Informix? > > Sure, depending on your language. In 4GL or ESQL/C, just 'prepare' a > dyanmically genereated "execute procedure" statement. If your > procedure does not return any data, simply execute the prepared > statement. In 4GL: > > let scratch = "execute ", proc_name, "(arg1, arg2)" > prepare proc_stmt from scratch > execute proc_stmt > > Of course, you can dynamically generate the arguments as well. > > If your procedure returns data, you must declare it as a cursor > and open, fetch, close or use foreach. Again, in 4GL: > > let scratch = "execute ", proc_name, "(arg1, arg2)" > prepare proc_stmt from scratch > declare proc_cursor cursor for proc_stmt > open proc_cursor > fetch proc_cursor into var1, var2 > close proc_cursor > > -OR- > > let scratch = "execute ", proc_name, "(arg1, arg2)" > prepare proc_stmt from scratch > declare proc_cursor cursor for proc_stmt > foreach proc_cursor into var1, var2 > -some code- > end foreach > > You can also use question marks in the string to prepare > and then substitute parameters with the "using" clause of > the open statement: > > let scratch = "execute ", proc_name, "(?, ?)" > prepare proc_stmt from scratch > declare proc_cursor cursor for proc_stmt > open proc_cursor using arg1, arg2 > fetch proc_cursor into var1, var2 > close proc_cursor > > HTH > -- > Irwin Goldstein > Objective Software Systems, Inc. > http://www.objectsoft.com