473,322 Members | 1,398 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Dynamic SQL in table SQL UDFs

In a DB2 V8.1 FP4 database I am trying to create a table SQL UDF that
is to return a contents of a temporary table with in this UDF:
create function getitemdata(pint int)
returns table
(
hostid smallint,
owid bigint
)
specific getitemdata
MODIFIES SQL DATA
NO EXTERNAL ACTION
begin atomic
declare v_temp_sql varchar(256);

set v_temp_sql =
'declare global temporary table '||
'item_data_tmp '||
'( '||
' hostid smallint, '||
' owid bigint '||
') '||
'on commit preserve rows not logged with replace' ;

execute immediate v_temp_sql;

<...inserting into session.item_data_tmp here...>

return select * from session.item_data_tmp;
end
@

It fails returning:

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 v_temp_sql;
<...inse" was
found following "mpuser_8k_01' ; ". Expected tokens may include:
"<space>". LINE NUMBER=25. SQLSTATE=42601

Are dinamic SQL statements (EXECUTE/PREPARE) not allowed in table SQL
UDFs (FP4)? Any workaround?

Thanks,
-Eugene
Nov 12 '05 #1
1 2413
No can do. Only the statements listed in "Compound Statement (Dynamic)"
are allowed in inline SQL PL. The idea is to keep it small and slick.
I would propose to use a persistent table and some unique "token" to
privatize access to the table by session.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Chris Geihsler | last post by:
I have a set of udf's dealing that return a one column table of values parsed from a comma delimeted string. For example: CREATE FUNCTION . ( @patient_list varchar(2000) ) RETURNS...
5
by: Ralph | last post by:
Hi all, I'm a newbie to MS-SQL UDFs and seem to have a real big problem. I need to implement a logic to receive an adress build out of various user definable fields from various user defined...
4
by: Tim.D | last post by:
People, I've ventured into the wonderful world of Stored Procedures. My first experience has been relatively successful however I am stuck on using host variables to specifiy actualy table or...
7
by: Yaro | last post by:
Hello Is it possible using dynamic SQL in UDF? In Sybase below example work but in DB2 UDB 8.1.3 I get error: "SQL0104N Unexpected element "EXECUTE IMMEDIATE" found....." CREATE FUNCTION...
3
by: Alexandre H. Guerra | last post by:
Hello I need to process a SQL monitoring log stored in a table to group the statements that change just the constants in it. Ex: select a,b,c from table where (a = 'xyz' and b = 123 and c !=...
5
by: jcccs05 | last post by:
Hi all I've been searching the Internet for a while and I've been trying to find any resources on return tables using UDF's with Java. I've been sucessful in returning integers, etc, but I have...
7
by: P. Adhia | last post by:
Hi, I don't have much experience writting UDFs, so I don't know if this is possible (and simple). I am basically looking to write a wrapper table UDF that encapsulates reorgchk_tb_stats and...
12
by: Serman D. | last post by:
Hi all, How do I describe non-table objects, like UDF functions and procedures, using db2's normal command line interface? Database server = DB2/LINUX 9.1.0 Express-C running on Linux 2.6...
0
by: Helmut Tessarek | last post by:
Hi everybody, I've written some UDFs to generate passwords within DB2. They are compatible to the functions that are used in Apache's htpasswd utility. Maybe someone can use them. ...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.