473,545 Members | 529 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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(pin t 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_da ta_tmp here...>

return select * from session.item_da ta_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 2420
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
12284
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 @patient TABLE
5
3807
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 tables. The function is already implemented in the Client software and as UDF-compliant in MySQL and Oracle. Now there's just MS-SQL left... The...
4
13051
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 column names in a FROM clause. After many hours or reading all manner of manuals I've discovered it appears this is not possible and that in order to...
7
8469
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 DropTable(p_TableName VARCHAR(30) ) RETURNS VARCHAR(50) LANGUAGE SQL BEGIN ATOMIC
3
2806
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 != '2004-10-10') or ( a like 'def%' ) select a,b,c from table where (a = 'zyx' and b = 321 and c != '2004-01-01') or ( a like '%fed' )
5
1803
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 trouble finding anything about return tables. Can such a thing be done in Java? If so, can someone direct me to an example or have an example of...
7
3827
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 reorgchk_ix_stats procedures, so I can easily manipulate the result set using SQL. TIA P. Adhia
12
4115
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 Intel-32. Regards, Serman D.
0
1386
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. http://sourceforge.net/project/showfiles.php?group_id=103064&package_id=110695 The UDFS are
0
7465
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7398
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7805
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7752
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5325
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4944
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3449
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1878
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1013
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.