A Big cursor problem in SPL. Anybody help? ~ Answered: amber (solid confidence) — SPL doesn't support conditional cursor selection directly; Jonathan Leffler suggests refactoring into shared-logic procedures, and Ravi Krishna gives a clean CASE-WHEN-in-FOREACH-SELECT workaround, then correctly shoots down the asker's own view- and temp-table-based workarounds as unsafe (they force SP recompilation and lock sysprocplan) -- a clear first-answer-wrong-then-corrected pattern.

Advisory only.

I need to open a cursor depending on a condition: if a=1 then open cursor for SELECT1.... if a=2 then open cursor for SELECT2....(same columns but another select) if a=3 then opene ...... ...... now, work with one of those cursors: fetch cursor .... ... a big logic here.. close cursor; It look like Informix does not suport such thing. Or it does somehow?

Paulo Cooker wrote: > I need to open a cursor depending on a condition: > > if a=1 then > open cursor for SELECT1.... > if a=2 then > open cursor for SELECT2....(same columns but another select) > if a=3 then > opene ...... > ...... > > now, work with one of those cursors: > fetch cursor .... > ... > a big logic here.. > close cursor; > > It look like Informix does not suport such thing. Or it does somehow? I think the problem with the syntax as you've got it is that I4GL will not allow you to declare the same cursor name multiple times within a program. What you will want to do is use the PREPARE statement for the version of the SELECT that you need, and DECLARE a cursor for the prepared statement. I don't have an I4GL compiler available to double check the syntax, but you should be able to do something similar to the following: CASE WHEN a = 1 LET prepstring = "SELECT ... FROM..." WHEN a = 2 LET prepstring = "SELECT ... FROM..." ... END CASE PREPARE multi_select FROM prepstring IF (sqlca.sqlcode != 0) THEN .... END IF DECLARE select_cursor CURSOR FOR multi_select OPEN select_cursor FETCH select_cursor... ... CLOSE select_cursor HTH. -- June Hunt

June C. Hunt wrote: > Paulo Cooker wrote: > > [...] > I think the problem with the syntax as you've got it is that I4GL [...] Sorry about that. You were talking about SPL and I was talking about I4GL. (I really should have coffee before I do this.... ) -- June Hunt

Paulo Cooker wrote: > I need to open a cursor depending on a condition: > > if a=1 then > open cursor for SELECT1.... > if a=2 then > open cursor for SELECT2....(same columns but another select) > if a=3 then > opene ...... > ...... > > now, work with one of those cursors: > fetch cursor .... > ... > a big logic here.. > close cursor; > > It look like Informix does not suport such thing. Or it does somehow? If you have a 9.x engine, you can use the Dynamic SPL Bladelet which you can download from the IBM IDN website. If you have an earlier version, you're cattle trucked. -- "C'est pas parce qu'on n'a rien ''' dire qu'il faut fermer sa gueule" - Coluche

Paulo Cooker wrote: > I need to open a cursor depending on a condition: > > if a=1 then > open cursor for SELECT1.... > if a=2 then > open cursor for SELECT2....(same columns but another select) > if a=3 then > opene ...... > ...... > > now, work with one of those cursors: > fetch cursor .... > ... > a big logic here.. > close cursor; > > It look like Informix does not suport such thing. Or it does somehow? Cursors are not directly supported in SPL. What I think you need is one - or several - procedures for the different select statements, all of which call onto a common procedure that encapsulates the "a big logic here" part of your code. The tricky part is how much context you need to keep from row to row. If none, then plain old-fashioned boring software engineering (move common code into a function) does the job. If you need some humungous quantity of state between rows, you will have to get cleverer - global variables, or passing state back and forth, or something. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

"Paulo Cooker" <larini@email.com> wrote in message news:8efe8056.0312130043.63a45ca0@posting.google.com... > I need to open a cursor depending on a condition: > > if a=1 then > open cursor for SELECT1.... > if a=2 then > open cursor for SELECT2....(same columns but another select) > if a=3 then > opene ...... > ...... > > now, work with one of those cursors: > fetch cursor .... > ... > a big logic here.. > close cursor; > > It look like Informix does not suport such thing. Or it does somehow? Please mention what version of Informix. I am using 9.21.UC4 and I have achieved somewhat similar using one SQL inside SPL. u can achieve what u want, with a little bit of creativity. I am simplifying my example, which is more complex than what I am reproducing here. I have written a SP which expects a parameter. Depending on the parameter, the sort condition and the columns fetched vary. Informix SPL does not support ORDER BY as a variable. it has to be hard coded. The business rule was:- 1. WHEN 1 is passed as parameter, sort the result by airlines. 2. WHEN 2 is passed as parameter, sort the result by price, followed by number of availabile contracts. 3. WHEN 3 is passed as parameter, sort the result by available contracts, and price. This is how I approached it:- FOREACH SELECT WHEN W_PARAM = 1 THEN airlines -- airlines is a char(2) field ELSE 'AA' END SORT1, WHEN W_PARAM = 1 THEN 99999 WHEN W_PARAM = 2 THEN price*100::int WHEN W_PARAM = 3 THEN avail_contracts -- avail_contracts is an int field END SORT2 WHEN W_PARAM =1 THEN 99999, WHEN W_PARAM =2 THEN avail_contracts WHEN W_PARAM =3 THEN price*100::int END SORT3 .... (rest of the select) ORDER BY 1,2,3 END FOREACH Most of it is self explanatory except the type casting of price as an int. THat is done to ensure that the column type in all the 3 case remain same. If we don't do that, then depending on the parameter, second column will be either a money field or an int. Informix does not like that. Note: My actual SP is far more complicated bcos of 6 different types of parameter, two of which requires column from other tables. I had to use outerjoin with nvl() setting to a fixed constant to achieve it. Since the above mentioned columns are strictly for sorting purposes only, I ignore them. The columns I pass back to client are mentioned separately. The above technique works like a charm and saved me the pain of duplicating the code. To be frank I was pleasantly surprised by Informix SPL's capability in this regard. I always considered Informix SPL to be the most limited when compared to other SPLs like Oracle PL/SQL, SQLServer's or even DB2s. But this one exceeded my expectations. If u require any help please contact me in the email I am posting. Ravi

Where can I find this patch? What is the site, more specific? Thanks Obnoxio The Clown <obnoxio@hotmail.com> wrote in message news:<brf1j8$2j940$1@ID-64669.news.uni-berlin.de>... > Paulo Cooker wrote: > > > I need to open a cursor depending on a condition: > > > > if a=1 then > > open cursor for SELECT1.... > > if a=2 then > > open cursor for SELECT2....(same columns but another select) > > if a=3 then > > opene ...... > > ...... > > > > now, work with one of those cursors: > > fetch cursor .... > > ... > > a big logic here.. > > close cursor; > > > > It look like Informix does not suport such thing. Or it does somehow? > > If you have a 9.x engine, you can use the Dynamic SPL Bladelet which you can > download from the IBM IDN website. If you have an earlier version, you're > cattle trucked.

I will tell you a solution that I found: if a=1 then CREATE VIEW MYVIEW AS SELECT1; if a=2 then CREATE VIEW MYVIEW AS SELECT2; if a=3 then CREATE VIEW MYVIEW AS SELECT3; ...... FOREACH MYCURSOR FOR SELECT * FROM MYVIEW .... END FOREACH; DROP MYVIEW; What you thing about this solution?

Paulo Cooker wrote: > Where can I find this patch? What is the site, more specific? http://www-106.ibm.com/dmdd/zones/informix/library/samples/db_downloads.html#everyone It's the exec bladelet. The words "lazy" and "bugger" come to mind, although in no particular order. > Obnoxio The Clown <obnoxio@hotmail.com> wrote in message > news:<brf1j8$2j940$1@ID-64669.news.uni-berlin.de>... >> Paulo Cooker wrote: >> >> > I need to open a cursor depending on a condition: >> > >> > if a=1 then >> > open cursor for SELECT1.... >> > if a=2 then >> > open cursor for SELECT2....(same columns but another select) >> > if a=3 then >> > opene ...... >> > ...... >> > >> > now, work with one of those cursors: >> > fetch cursor .... >> > ... >> > a big logic here.. >> > close cursor; >> > >> > It look like Informix does not suport such thing. Or it does somehow? >> >> If you have a 9.x engine, you can use the Dynamic SPL Bladelet which you >> can download from the IBM IDN website. If you have an earlier version, >> you're cattle trucked. -- "C'est pas parce qu'on n'a rien ''' dire qu'il faut fermer sa gueule" - Coluche

"Paulo Cooker" <larini@email.com> wrote in message news:8efe8056.0312140043.4c6a2209@posting.google.com... > I will tell you a solution that I found: > > if a=1 then > CREATE VIEW MYVIEW AS SELECT1;> if a=2 then > CREATE VIEW MYVIEW AS SELECT2;> if a=3 then > CREATE VIEW MYVIEW AS SELECT3;> ...... > > FOREACH MYCURSOR FOR SELECT * FROM MYVIEW > .... > END FOREACH; > DROP MYVIEW; > > What you thing about this solution? this will work but this is a very bad solution. creating a view inside a SP means an object is created inside a SP. Depending on how your SP is called, this can lead to problem. If your SP is called inside transaction, Informix will recompile this SP and all dependent SPs, leading to a lock on sysprocplan. That will reduce concurrency drastically.

Ravi Krishna wrote: > "Paulo Cooker" <larini@email.com> wrote in message news:8efe8056.0312140043.4c6a2209@posting.google.com... > >>I will tell you a solution that I found: >> >> if a=1 then >> CREATE VIEW MYVIEW AS SELECT1;>> if a=2 then >> CREATE VIEW MYVIEW AS SELECT2;>> if a=3 then >> CREATE VIEW MYVIEW AS SELECT3;>> ...... >> >> FOREACH MYCURSOR FOR SELECT * FROM MYVIEW >> .... >> END FOREACH; >> DROP MYVIEW; >> >> What you thing about this solution? > > > this will work but this is a very bad solution. creating a view inside a SP means an > object is created inside a SP. Depending on how your SP is called, this can lead to > problem. If your SP is called inside transaction, Informix will recompile this SP and > all dependent SPs, leading to a lock on sysprocplan. That will reduce concurrency > drastically. > > > What about selecting into a temp table ... if a=1 then select col1, col2 from tab1 into temp t1 with no log; "SELECT1" if a=2 then select col1, col2 from tab1 into temp t1 with no log; "SELECT2" if a=3 then select col1, col2 from tab1 into temp t1 with no log; "SELECT3" ...... FOREACH MYCURSOR FOR SELECT * FROM t1 .... END FOREACH; DROP t1;

"TBP" <TBP@Nospam.Nothere.Co.Uk> wrote in message news:ka6Db.736$S63.521@newsfep3-gui.server.ntli.net... > > this will work but this is a very bad solution. creating a view inside a SP means an > > object is created inside a SP. Depending on how your SP is called, this can lead to > > problem. If your SP is called inside transaction, Informix will recompile this SP and > > all dependent SPs, leading to a lock on sysprocplan. That will reduce concurrency > > drastically. > > > > > > > What about selecting into a temp table ... > > if a=1 then > select col1, col2 from tab1 into temp t1 with no log; "SELECT1" > if a=2 then > select col1, col2 from tab1 into temp t1 with no log; "SELECT2" > if a=3 then > select col1, col2 from tab1 into temp t1 with no log; "SELECT3" > ......> > FOREACH MYCURSOR FOR SELECT * FROM t1 .... > END FOREACH; > DROP t1; same issue with creating temp tables too. Temp table is also an object. What happens is that the moment any object referenced by the SP changes (which includes creating and destroying it), the SP is recompiled. That's the way Informix is designed. My suggestion: Unless the SP is guaranteed to run in single user mode(as in reports), avoid creating/modifying any object inside it.