asked on

How to create a function that does a dynamic SQL call?

Hi, I am trying to create a function that calls some dynamic SQL and then returns some value, like the example one below. I am using DB2 v9 on Windows.

CREATE FUNCTION halloWorld2()
RETURNS INTEGER
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
READS SQL DATA
BEGIN ATOMIC
EXECUTE IMMEDIATE 'SOME DYNAMIC SQL HERE';
RETURN 1;
END@

However, I am getting some strange error message. Could someone please help me. I have no clue what I am doing wrong here. The error message is:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "EXECUTE IMMEDIATE" was found following "A
BEGIN ATOMIC ". Expected tokens may include: "<compound_return>". LINE
NUMBER=6. SQLSTATE=42601

SQL0104N An unexpected token "EXECUTE IMMEDIATE" was found following "A
BEGIN ATOMIC
". Expected tokens may include: "<compound_return> ".

mersis

ASKER

So it works like that:
CREATE FUNCTION halloWorld2()
RETURNS INTEGER
DETERMINISTIC NO EXTERNAL ACTION
READS SQL DATA
BEGIN ATOMIC
--EXECUTE IMMEDIATE 'SOME DYNAMIC SQL HERE'
RETURN 1;
END@

But as soon as I uncomment the EXECUTE line I start getting the error message above.

ghp7000

some dynamic sql statment canno tbe insert update or delete-is this what you are doing?

mersis

ASKER

no, I am just trying to compile the function first, but DB2 does not allow me to. If I convert it into STORED PROC it works fine, but as a function it refuses to compile the very same code.

Any ideas why? Can I have EXECUTE or CALL statements in an SQL FUNCTION definition?

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ghp7000

yes, kdo is right, I am getting confused between sp and function :)

momi_sabag

hi
it does not say anywhere that one can not use execute immediate in a function,
by looking here it is also implied that execute immediate can be used
http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/r0007249.htm

mersis

ASKER

Hi guys,
thanks for your replies.
after fighting the whole day with DB2 today I come to the following conclusion:
1) PREPARE/EXECUTE is *NOT* allowed inside a FUNCTION body. There is a list of statements that are allowed and PREPARE/EXECUTE are simply not there. So whenever you try using those it causes a syntax error.
2) As always with DB2, there is a complicated way around it (*sigh*, I must say I am disgusted by this DB system). Since PREPARE/EXECUTE is allowed inside a STORED PROCEDURE, and CALL statements are allowed inside FUNCTIONs, you can create a stored proc that issues EXECUTE statements, and then call that stored proc from a function, passing a string that has to be executed to it.
Hope this discovery will eventually help someone...

momi_sabag

can you point me to the manual where you saw the list of statements that are allowed within a function body and execute/prepare was not there ?
i looked for it but couldnt find it

mersis

ASKER

Here ftp://ftp.software.ibm.com/ps/products/db2/info/vr82/pdf/en_US/db2s1e81.pdf
and here ftp://ftp.software.ibm.com/ps/products/db2/info/vr8/pdf/letter/db2s2e80.pdf.
look for Compound SQL (Dynamic).