IDS 11.5

Hi to all. I'm have a problem with creating stored procedure that use Dinamy SQL. Example, when I try to create a procedur: CREATE PROCEDURE "informix".test( ) DEFINE iGod SMALLINT; LET iGod = 2003; PREPARE stmt FROM 'INSERT INTO godine(iGodina) VALUES (?)'; EXECUTE stmt USING :iGod; return; END PROCEDURE I'm get a syntax error but when I comment EXECUTE part then everithing goes OK. It's also OK when I use EXECUTE IMMEDIATE statement but then I cant use params. Can you please suggest me what I'm doing wron (and please forgive me my alfull english)

Dynamic SQL in SPL follow the same coding pattern like esqlc or 4gl. prepare declare cursor open or execute fetch close free >Hi to all. > >I'm have a problem with creating stored procedure that use Dinamy SQL. >Example, when I try to create a procedur: > > >CREATE PROCEDURE "informix".test( ) > > DEFINE iGod SMALLINT; > > LET iGod = 2003; > > PREPARE stmt FROM 'INSERT INTO godine(iGodina) VALUES (?)'; > EXECUTE stmt USING :iGod; > > return; > >END PROCEDURE > >I'm get a syntax error but when I comment EXECUTE part then everithing >goes OK. It's also OK when I use EXECUTE IMMEDIATE statement but then I >cant use params. >Can you please suggest me what I'm doing wron (and please forgive me my >alfull english)

VERSION AND PLATFORM INFORMATION ARE CRITICAL IF YOU EXPECT A USEFUL ANSWER! If you are using IDS 11.50 which DOES support dynamic SQL in SPL stored procedures, the problem is the colon (:) preceding the function variable iGod. Colon is ONLY used to identify host variables in ESQL/C in SPL local variables are recognized by syntax. As an asside, this is why it is generally a bad idea to name SPL variables the same as table columns as there are situations where it is not clear to the parser whether you mean a column or the local var and the parser will invariable not devine you true intentions. I use the convention "l_<columname>" when I want a local var to be named like the table column whose value it will receive/supply. Art On Fri, Oct 24, 2008 at 4:11 AM, O_Caj <just_send@spam.com> wrote: > Hi to all. > > I'm have a problem with creating stored procedure that use Dinamy SQL. > Example, when I try to create a procedur: > > > CREATE PROCEDURE "informix".test( ) > > DEFINE iGod SMALLINT; > > LET iGod = 2003; > > PREPARE stmt FROM 'INSERT INTO godine(iGodina) VALUES (?)'; > EXECUTE stmt USING :iGod; > > return; > > END PROCEDURE > > I'm get a syntax error but when I comment EXECUTE part then everithing > goes OK. It's also OK when I use EXECUTE IMMEDIATE statement but then I > cant use params. > Can you please suggest me what I'm doing wron (and please forgive me my > alfull english) > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.