473,573 Members | 2,780 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Informix "foreach execute procedure": converting to DB2

Hello,

I'm writing an automatic SQL parser and translator from Informix to
DB2. Now I'm faced with one of the most difficult things to translate,
the "foreach execute procedure" functionality provided by Informix.
This is the problem: the execution of the translated SQL leaves the
rows in the temp table correctly but raises error SQL0480N. It's very
simple to try it:

------------
Original SQL:
------------

create temp table a(a smallint)
;
drop procedure a
;
create procedure a() returning smallint;
return 1 with resume;
return 2 with resume;
end procedure
;
drop procedure b
;
create procedure b()
define b smallint;
foreach execute procedure a() into b
insert into a values(b);
end foreach;
end procedure
;
execute procedure b()
;
select * from a
;
drop table a
;

Results:
-------
1
2
----------------------------
Automatically translated SQL:
----------------------------

DECLARE GLOBAL TEMPORARY TABLE a(a SMALLINT) ON COMMIT PRESERVE ROWS
NOT LOGGED WITH REPLACE
@
CREATE PROCEDURE DROP_IFEXISTS()
BEGIN
DECLARE DROP_SQL VARCHAR(255);
IF EXISTS (SELECT routinename FROM syscat.routines WHERE
routineschema = current_schema AND routinename = 'A' AND routinetype =
'F') THEN
SET DROP_SQL = 'DROP FUNCTION a';
EXECUTE IMMEDIATE DROP_SQL;
ELSEIF EXISTS (SELECT routinename FROM syscat.routines WHERE
routineschema = current_schema AND routinename = 'A' AND routinetype =
'P') THEN
SET DROP_SQL = 'DROP PROCEDURE a';
EXECUTE IMMEDIATE DROP_SQL;
END IF;
END
@
CALL DROP_IFEXISTS
@
DROP PROCEDURE DROP_IFEXISTS
@
CREATE PROCEDURE a() DYNAMIC RESULT SETS 1
BEGIN
DECLARE a_OUTPUT_1_TAB VARCHAR(20);
DECLARE a_OUTPUT_1_SQL VARCHAR(1024);
DECLARE a_OUTPUT_1_CUR CURSOR WITH RETURN TO CALLER FOR
a_OUTPUT_1_SEL;

SET a_OUTPUT_1_TAB = 'a_OUTPUT_1';
SET a_OUTPUT_1_SQL = 'DECLARE GLOBAL TEMPORARY TABLE ' ||
a_OUTPUT_1_TAB || '(a_1 SMALLINT) ON COMMIT PRESERVE ROWS NOT LOGGED
WITH REPLACE';
EXECUTE IMMEDIATE a_OUTPUT_1_SQL;
SET a_OUTPUT_1_SQL = 'INSERT INTO SESSION.' || a_OUTPUT_1_TAB || '
VALUES(1)';
EXECUTE IMMEDIATE a_OUTPUT_1_SQL;
SET a_OUTPUT_1_SQL = 'INSERT INTO SESSION.' || a_OUTPUT_1_TAB || '
VALUES(2)';
EXECUTE IMMEDIATE a_OUTPUT_1_SQL;
SET a_OUTPUT_1_SQL = 'SELECT * FROM SESSION.' || a_OUTPUT_1_TAB;
PREPARE a_OUTPUT_1_SEL FROM a_OUTPUT_1_SQL;
OPEN a_OUTPUT_1_CUR;
END
@
CREATE PROCEDURE DROP_IFEXISTS()
BEGIN
DECLARE DROP_SQL VARCHAR(255);
IF EXISTS (SELECT routinename FROM syscat.routines WHERE
routineschema = current_schema AND routinename = 'B' AND routinetype =
'F') THEN
SET DROP_SQL = 'DROP FUNCTION b';
EXECUTE IMMEDIATE DROP_SQL;
ELSEIF EXISTS (SELECT routinename FROM syscat.routines WHERE
routineschema = current_schema AND routinename = 'B' AND routinetype =
'P') THEN
SET DROP_SQL = 'DROP PROCEDURE b';
EXECUTE IMMEDIATE DROP_SQL;
END IF;
END
@
CALL DROP_IFEXISTS
@
DROP PROCEDURE DROP_IFEXISTS
@
CREATE PROCEDURE b()
BEGIN
DECLARE b SMALLINT;
DECLARE SQLSTATE CHAR(5);
DECLARE a_RESULTS_1 RESULT_SET_LOCA TOR VARYING;

CALL a;
ASSOCIATE RESULT SET LOCATORS(a_RESU LTS_1) WITH PROCEDURE a;
ALLOCATE a_OUTPUT_CURSOR _1 CURSOR FOR RESULT SET a_RESULTS_1;
FETCH FROM a_OUTPUT_CURSOR _1 INTO b;
WHILE (SQLSTATE = '00000') DO
INSERT INTO SESSION.a VALUES(b);
FETCH FROM a_OUTPUT_CURSOR _1 INTO b;
END WHILE;
CLOSE a_OUTPUT_CURSOR _1;
END
@
CALL b
@
SELECT * FROM SESSION.a
@
DROP TABLE SESSION.a
@
Results:
-------

DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.

Return state = 0

DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.

Return state = 0

DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.

SQL0480N The procedure "B" has not yet been called. SQLSTATE=51030

Return state = 0

A
------
1
2

DB20000I The SQL command completed successfully.
----------------------------------------------------------------------------

ANY HINT?.......... .. (using DB2 v8.1.0.64, "s040812", "MI00086",
FixPak 7)
Nov 12 '05 #1
5 12400
Gustavo Randich wrote:
Hello,

I'm writing an automatic SQL parser and translator from Informix to
DB2. Now I'm faced with one of the most difficult things to translate,
the "foreach execute procedure" functionality provided by Informix.
This is the problem: the execution of the translated SQL leaves the
rows in the temp table correctly but raises error SQL0480N. It's very
simple to try it:

------------
Original SQL:
------------

create temp table a(a smallint)
;
drop procedure a
;
create procedure a() returning smallint;
return 1 with resume;
return 2 with resume;
end procedure
;
drop procedure b
;
create procedure b()
define b smallint;
foreach execute procedure a() into b
insert into a values(b);
end foreach;
end procedure
;
execute procedure b()
;
select * from a
;
drop table a
;


Not knowing Informix well enough, I'd say that your procedure "A" is just a
table function, i.e. each call made to the function returns the next row.

In DB2 you could achieve something like it in the following way:

CREATE FUNCTION a()
RETURNS TABLE ( col SMALLINT )
LANGUAGE SQL
RETURN SELECT *
FROM TABLE ( VALUES ( 1 ),
( 2 ) ) AS t
@

Now you can use this function in procedure B:

CREATE PROCEDURE b()
LANGUAGE SQL
BEGIN
FOR fetch AS loop CURSOR FOR SELECT * FROM TABLE ( a() ) AS t
DO
INSERT INTO a VALUES (col);
END FOR;
END
@

In this particular case, I would even further simplify the procedure:

CREATE PROCEDURE b()
LANGUAGE SQL
BEGIN
INSERT INTO a SELECT col FROM TABLE ( a() ) AS t;
END
@

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #2
Knut,

Thanks for the reply. The procedure you wrote has exactly the same
behavior as mine. The table is populated, but I'm still getting the
error "SQL0480N The procedure "B" has not yet been called.
SQLSTATE=51030" ; have you tried it?

Knut Stolze wrote:
Not knowing Informix well enough, I'd say that your procedure "A" is just a table function, i.e. each call made to the function returns the next row.
In DB2 you could achieve something like it in the following way:

CREATE FUNCTION a()
RETURNS TABLE ( col SMALLINT )
LANGUAGE SQL
RETURN SELECT *
FROM TABLE ( VALUES ( 1 ),
( 2 ) ) AS t
@

Now you can use this function in procedure B:

CREATE PROCEDURE b()
LANGUAGE SQL
BEGIN
FOR fetch AS loop CURSOR FOR SELECT * FROM TABLE ( a() ) AS t
DO
INSERT INTO a VALUES (col);
END FOR;
END
@

In this particular case, I would even further simplify the procedure:

CREATE PROCEDURE b()
LANGUAGE SQL
BEGIN
INSERT INTO a SELECT col FROM TABLE ( a() ) AS t;
END
@

--
Knut Stolze
Information Integration
IBM Germany / University of Jena


Nov 12 '05 #3
Knut Stolze wrote:
Gustavo Randich wrote:

Hello,

I'm writing an automatic SQL parser and translator from Informix to
DB2. Now I'm faced with one of the most difficult things to translate,
the "foreach execute procedure" functionality provided by Informix.
This is the problem: the execution of the translated SQL leaves the
rows in the temp table correctly but raises error SQL0480N. It's very
simple to try it:

------------
Original SQL:
------------

create temp table a(a smallint)
;
drop procedure a
;
create procedure a() returning smallint;
return 1 with resume;
return 2 with resume;
end procedure
;
drop procedure b
;
create procedure b()
define b smallint;
foreach execute procedure a() into b
insert into a values(b);
end foreach;
end procedure
;
execute procedure b()
;
select * from a
;
drop table a
;

Not knowing Informix well enough, I'd say that your procedure "A" is just a
table function, i.e. each call made to the function returns the next row.

In DB2 you could achieve something like it in the following way:

CREATE FUNCTION a()
RETURNS TABLE ( col SMALLINT )
LANGUAGE SQL
RETURN SELECT *
FROM TABLE ( VALUES ( 1 ),
( 2 ) ) AS t
@

Now you can use this function in procedure B:

CREATE PROCEDURE b()
LANGUAGE SQL
BEGIN
FOR fetch AS loop CURSOR FOR SELECT * FROM TABLE ( a() ) AS t
DO
INSERT INTO a VALUES (col);
END FOR;
END
@

In this particular case, I would even further simplify the procedure:

CREATE PROCEDURE b()
LANGUAGE SQL
BEGIN
INSERT INTO a SELECT col FROM TABLE ( a() ) AS t;
END
@

Alternatively (a bit more dumbed down and less performant) you could
do a CALL. Go through the allocate/associate locator logic
and then fetch from the result set of the procedure in a WHILE loop.

Of cousre the fastest would be:
INSERT INTO T SELECT * FROM TABLE(foo(a)) AS F

Cheers
Serge

Cheers
Serge
Nov 12 '05 #4
gu************* @yahoo.com.ar wrote:
Knut,

Thanks for the reply. The procedure you wrote has exactly the same
behavior as mine. The table is populated, but I'm still getting the
error "SQL0480N The procedure "B" has not yet been called.
SQLSTATE=51030" ; have you tried it?


Yes, I have tried it and it did work quite fine. In particular, I didn't
see the SQL0480 that you got.

You might want to set the DIAGLEVEL to 4, run your statements again and see
if there is anything interesting in the db2diag.log.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #5
Knut Stolze <st****@de.ibm. com> wrote in message news:<co******* ***@fsuj29.rz.u ni-jena.de>...
gu************* @yahoo.com.ar wrote:
Knut,

Thanks for the reply. The procedure you wrote has exactly the same
behavior as mine. The table is populated, but I'm still getting the
error "SQL0480N The procedure "B" has not yet been called.
SQLSTATE=51030" ; have you tried it?


Yes, I have tried it and it did work quite fine. In particular, I didn't
see the SQL0480 that you got.

You might want to set the DIAGLEVEL to 4, run your statements again and see
if there is anything interesting in the db2diag.log.


Strange thing... no info about this ins db2diag.log; fortunately, the
applications I'm working on execute this SQL via ADO, which doesn't
intercept's the error. Thanks again.
Nov 12 '05 #6

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

Similar topics

0
1382
by: Luis | last post by:
I'm using a SQL Server 2000 stored procedure similar to the one below to upload data to a database. This data is collected from the user on a number of asp pages and stored in session variables (that's the way I inherited the system...). When the user has captured all the info and clicks the submit button the stored procedure is called and it...
32
4113
by: James Curran | last post by:
I'd like to make the following proposal for a new feature for the C# language. I have no connection with the C# team at Microsoft. I'm posting it here to gather input to refine it, in an "open Source" manner, and in an attempt to build a ground-swell of support to convince the folks at Microsoft to add it. Proposal: "first:" "last:"...
4
4909
by: dstorms | last post by:
Hi, I'm trying to create a form with a list box with 2 columns and a button that opens the selected record in the appropriate form. In the sample below, the first column lists the type of equipment (labeled Category) and the second column lists the equipment's serial number (SerialNo). The sub is supposed to use the Category data (say, Cameras)...
1
4220
by: indikamaligaspe | last post by:
Hi all, I am trying to get a PL/TCL trigger to update an audit table working. The problem is when I create the trigger on an exiting table, I get the following error "ERROR: can't read "tgname": no such variable CONTEXT: can't read "tgname": no such variable while executing ....." If I drop the table and then recreate the table and...
0
7753
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
7676
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
8187
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...
1
7751
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6376
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5275
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
3714
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...
0
3718
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2178
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

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.