Re: Dynamic SQL with cursors

asberry@acm.org wrote: > I'm trying to do something that I'm sure I've seen done before, > but I must be losing my mind because what I'm reading in the > documentation seems to indicate it can't be done. Which language are you using? ESQL/C or I4GL or something else? > Basically, I have an existing application with a function that has a > parameter x. I have several cursors that I am opening, and inside my > function I want to fetch from one of these cursors depending on the > value of x (basically I want to alternate between cursors depending > on x). OK. As Sunil pointed out, these cursors will presumably all have the same select list. I also assume that there is no commonality between the criteria -- that is, you cannot rework the code so that the queries simply contain one or more question-mark place holders, and you derive from x that actual values to be passed to the query when you open the cursor. > The brute force method is to set up my cursor references within a > switch/case construct (there are 5 cursors for each of the 16 > possible values of x). 80 alternatives -- revisit the use of placeholders. > It just seems like there should be a cleaner method to do this than > to cut and paste all this code with slight modifications in all > these switch/case statements. Most probably, you should be building the query string dynamically based on the value of x, and then preparing the string and declaring a cursor for the string. Use a single cursor name. I'm not even sure whether you can free statements in 2.x. > It seems like I should be able to use dynamic SQL to do this, > building the name of the cursor to be used, etc with sprintfs, > but my documentation says that you can't use prepare/execute for > declare, execute, fetch and open statements (which just happen to > be the ones I need!). I tried to do it anyway just for giggles but > I end up getting a syntax error. Unless you want any combination of the 80 cursors accessible all at once, you simply use a single prepared statement and a single cursor, and you write your code in terms of these. You generate the string which is prepared according to whatever complex rules are necessary. You might even need to have 80 cases somewhere (though I get the impression that x is not the only factor which determines which qquery you need to execute). > This may be different in newer versions of Informix, I have a REALLY > old version of (like 2.x, and no way around that). Or maybe I'm just > forgetting something ... it's been awhile since I've done much heavy > duty esql/c. Oh, it is ESQL/C you are using... > Any suggestions other than "upgrade" would be appreciated. :) Why is "upgrade' not an option? As Sunil said, there are string-named cursors in version 5.00 and above ESQL/C, and using these would deal with any residual problems. The alternatives involve going behind the scenes and fiddling with the structures in the generated C code, are highly non-portable, non-supportable (not that 2.x is supported anyway), and highly susceptible to unexpected breakages after code modification. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>