IDS 11.5

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

O_Caj - 24 Oct 2008 09:11 GMT

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)

Reply to this Message

dcruncher4@aim.com - 24 Oct 2008 10:47 GMT

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.
>
[quoted text clipped - 19 lines]
>Can you please suggest me what I'm doing wron (and please forgive me my
>alfull english)

Reply to this Message

Art Kagel - 24 Oct 2008 15:34 GMT

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

> Hi to all.
>
[quoted text clipped - 23 lines]
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list

Signature

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.

Reply to this Message