Re: Why use USING? (was Re: ORDER BY ?, ?, ? DESC)

>From: rodney@worf.netins.net (Rodney) >Date: 6 Jun 1995 16:14:44 -0500 >X-Informix-List-Id: <news.14457> > >Jonathan Leffler <johnl@informix.com> wrote (edited): >>Someone else wrote (edited heavily): >>} I am trying the following method and it is not working. >>} ............. >>} PREPARE stmt FROM "SELECT * FROM emp_tab ORDER BY ?, ?, ?" >>} >>} PROMPT "enter col1" FOR c1 >>} PROMPT "enter col2" FOR c2 >>} PROMPT "enter col3" FOR c3 >>} >>} DECLARE cur1 SCROLL CURSOR FOR stmt >>} >>} OPEN cur1 USING c1, c2, c3 >> >>You cannot use ? in the ORDER BY clause. >> >>You can, of course, reverse the order of the PROMPT statements and the >>PREPARE, and build the string using the input data from the prompts. The >>cursor would not need a USING clause for the ORDER BY clause. (though it >>might still need it for other reasons). > >At the risk of sounding stupid (what else is new?), does one ever >_have_ to use USING? Is it ever _better_ to use USING? > >Even in a looping situation, I can't imagine that the performance >is that much different (but that may be where I'm way off). One case where you'd benefit from USING is something along the lines of: PREPARE p_select FROM "SELECT * FROM ScannedTable WHERE Pkvalue BETWEEN ? AND ? FOR UPDATE" DECLARE c_select CURSOR FOR p_select DECLARE c_range CURSOR WITH HOLD FOR SELECT Lower, Upper FROM RangeTable ORDER BY Lower FOREACH c_range INTO p_lower, p_upper BEGIN WORK FOREACH c_select USING p_lower, p_upper -- See the notes below INTO r_scantab.* -- Do something useful, like change the DB END FOREACH COMMIT WORK END FOREACH This allows you to prepare and declare the c_select cursor just once, but to re-use it many times. This reduces the amount of time the database spends parsing the SELECT statement. It probably re-optimizes it each time the cursor is opened, but that is less of an overhead than parsing AND optimizing it. The extreme case of the example code is when the the outer cursor does some complex ordering of the data and the inner cursor does a SELECT ... FOR UPDATE of a single row identified by the primary key values identified by the outer cursor, followed by an UPDATE or DELETE operation. (There shouldn't be any rows to skip; those should have been filtered out by the outer cursor without being returned to the application.) The FOREACH...USING...INTO syntax will be available in the next release (4.14, 6.02) of I4GL; at the moment, you would have to program the inner loop as an explicit OPEN, WHILE, FETCH, CLOSE. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>