Using dynamic SQL within routines

Hello, help needed. Does anybody know if it is possible to create a dynamic statement in a UDR like following example. Thanks for your advice! peter Example: CREATE FUNCTION neighbors( db CHAR(30), pred VARCHAR) RETURNING SET(INTEGER NOT NULL)SPECIFIC neighbors6; DEFINE neighbors_set SET(INTEGER NOT NULL); DEFINE db_table CHAR(30); DEFINE where_clause VARCHAR; INSERT INTO tabel(neighbors_set) SELECT oid_1 FROM db_table = db WHERE where_clause = pred; RETURN neighbors_set; END FUNCTION

Peter Hamm wrote: > help needed. Does anybody know if it is possible to create a dynamic > statement in a UDR like following example. No. Your example will compile, but it won't do what you want it to do. It will compare the uninitialized value of 'where_clause' with the parameter 'pred' and the two will not be equal (probably the result will be UNKNOWN), so no data will be selected. Dynamic SQL is not allowed in stored procedures. A UDR is more likely to get over this than an SPL procedure, but I think the constraint still holds. > Example: > > CREATE FUNCTION neighbors( db CHAR(30), pred VARCHAR) > RETURNING SET(INTEGER NOT NULL)> SPECIFIC neighbors6; > > DEFINE neighbors_set SET(INTEGER NOT NULL); > DEFINE db_table CHAR(30); > DEFINE where_clause VARCHAR; > > INSERT INTO tabel(neighbors_set) > SELECT oid_1 > FROM db_table = db > WHERE where_clause = pred;> > RETURN neighbors_set; > > END FUNCTION -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN #include <disclaimer.h>

Peter Hamm wrote: > Hello, > > help needed. Does anybody know if it is possible to create a dynamic > statement in a UDR like following example. > Thanks for your advice! > > peter > > Example: > > CREATE FUNCTION neighbors( db CHAR(30), pred VARCHAR) > RETURNING SET(INTEGER NOT NULL)> SPECIFIC neighbors6; > > DEFINE neighbors_set SET(INTEGER NOT NULL); > DEFINE db_table CHAR(30); > DEFINE where_clause VARCHAR; > > INSERT INTO tabel(neighbors_set) > SELECT oid_1 > FROM db_table = db > WHERE where_clause = pred;> > RETURN neighbors_set; > > END FUNCTION > > ------------------------------------------------------------------------ > > Peter Hamm <hamm@dbs.informatik.uni-muenchen.de> > University of Munich > Computer Science > > Peter Hamm > University of Munich <hamm@dbs.informatik.uni-muenchen.de> > Computer Science HTML Mail > Comeniusstrasse 3 / Rgb Cellular: +49 171 / 8 98 48 00 > Munich Fax: +49 89 / 48 00 24 21 > Bavaria Home: +49 89 / 48 00 24 20 > 81667 Netscape Conference Address > Germany > Additional Information: > Last Name Hamm > First Name Peter > Version 2.1 Peter, As far as I know, you can only do this with ESQL/C. Check out the "Dynamic SQL" section of the ESQL/C manual. This is available online at Informix's website: http://www.informix.com HTH, Avi. -- /\\ \\ /| Avi Abrami, Analyst/Programmer, Telegate Ltd. /__\\ \\ / | 7 Haplada Street, Or-Yehuda, ISRAEL / \\ \\/ | Phone:+972-3-5384717 Fax:+972-3-5335877 eMail:avia@telegate.co.il