• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1230
  • Last Modified:

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>              ".
0
mersis
Asked:
mersis
  • 4
  • 2
  • 2
  • +1
1 Solution
 
mersisAuthor Commented:
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.
0
 
ghp7000Commented:
some dynamic sql statment canno tbe insert update or delete-is this what you are doing?
0
 
mersisAuthor Commented:
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?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Kent OlsenData Warehouse Architect / DBACommented:
Hi mersis,

I'm not aware that you can use EXECUTE IMMEDIATE in a function.  (If anyone knows differently, please tell me!)  A function is a "compiled" bit of code, and dynamic SQL is evaluated at execution time, not compile time.

EXECUTE may not be any better because it's tied to the PREPARE statement.  (EXECUTE IMMEDIATE is really just PREPARE and EXECUTE run as a single command.)

I believe that CALL statements are fine.


Kent


0
 
ghp7000Commented:
yes, kdo is right, I am getting confused between sp and function :)
0
 
momi_sabagCommented:
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
0
 
mersisAuthor Commented:
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...
0
 
momi_sabagCommented:
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
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now