Jaume,
you have me very curious as to why you would be trying this method of using
dynamic SQL. I'm not sure where to start...
AFAIK, there is no such datatype as "STATEMENT", so there's little wonder
why it objects to this. The prepare instruction is something a client needs
(sometimes) to do, I have never used it in a stored procedure but maybe this
is possible.

What I really wonder about is why you are going to so much trouble building
the statement client side, then saving it into a table column, then
committing it then trying to execute it inside a SP... If you are building
it client side anyway, why wouldn't you just execute it from the client?

A mystery inside an enigma perhaps - I'm sure you will enlighten us...

Alan
-----Original Message-----
From: Jaume Andreu Sabater Malondra [mailto:jsabater@...]
Sent: Sunday, 8 September 2002 4:27
To: ib-support@yahoogroups.com
Subject: [ib-support] Executing DSQL inside a stored procedure


Hi, people.

I have got a problem with DSQL. I have dynamically built an UPDATE
statement,
which is located inside a varchar variable. But I can't find the way to
make
Firebird execute. I am inside a stored procedure. Here is the code:

[..]

DECLARE VARIABLE SQL_UPDATE VARCHAR(255);
DECLARE EXEC_UPDATE STATEMENT;

[..]

/* Now SQL_UPDATE contains something like

UPDATE GROUPS SET
GRP_GRPNAM = :P_GRPNAM,
GRP_PARCOD = :P_PARCOD,
GRP_PASSWD = :P_PASSWD
WHERE GRP_GRPCOD = :P_GRPCOD;

where :P_GRPNAM, :P_PARCOD, :P_PASSWD and :P_GRPCOD are the input stored
procedure parameters. */

PREPARE EXEC_UPDATE FROM :SQL_UPDATE;
EXECUTE :EXEC_UPDATE;

And here is the error:

ISC ERROR CODE: 335544569
ISC ERROR MESSAGE
Dynamic SQL Error
SQL error code = -104
Token Unknown - line 16, char 9
EXEC_UPDATE

Line 16 is the second declaration at top, the on with "DECLARE EXEC_UPDATE
STATEMENT" is.

If I take that line out, then the error is

ISC ERROR CODE: 335544569
ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Token unknown - line 103, char 15
EXEC_UPDATE

Line 103 is the one with the PREPARE statement.

This is the first time I try this (using DSQL inside a stored procedure),
so I
really don't know what to do.

I hope any of you may help me. Meanwhile I'll read the language reference
manual again and see if I forgot something. Thanks in advance anyway.

P.S.: I also tried to use EXECUTE IMMEDIATE instead of PREPARE and
EXECUTE,
since the language reference says it, but I got a "TOKEN UNKNOWN
'IMMEDIATE'"
error, which stopped me.

--
Jaume Andreu Sabater Malondra
jsabater@...
Registered linux user #209072

"Ubi sapientas ibi libertas"


Yahoo! Groups Sponsor
ADVERTISEMENT



To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]