Re: Whatcha' wanta have?????
A wish-list discussion about getting case-insensitive searching in Informix without rewriting application code. Mark Stock suggested functional indexes as an alternative; Andrew Hamm objected that they require an upgrade to 9.x and that queries would have to be rewritten to use UPSHIFT() for the optimiser to pick the index — impractical when WHERE clauses are generated by hundreds of 4GL CONSTRUCT statements. His preferred idea was converting columns to NCHAR with a case-insensitive GLS collation locale. Jonathan Leffler confirmed 7.31 and earlier 9.x would get no new features (only 9.40/9.50), and Neil Truby noted upgrade uplift charges. No actual solution was reached.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Andrew Hamm wrote: > "Andrew Hamm" <ahamm@mail.com> wrote ... > >>Mark D. Stock wrote: >> >>>Can't this be achieved using functional indexes? >> >>ummmmm, we'd have to deliver 9.X to all customers.... Well I don't think you are going to get any new features "grafted" to your current version. So you are going to have to deliver SOMETHING. ;-) > and we'd have to change ALLLLLLLL the code to have something like > > and upshift(cats_name) = upshift(?) > > instead of > > and cats_name = ? > > wouldn't we? I've not used functional indexes before (note to self...) but > my understanding is that the engine needs to recognise an expression that is > solvable with the index as it was defined. They allow you to store converted data in the index rather than in a separate column. > Also, can a functional index solve this? > > and cats_name matches "tidd*l*s" I haven't tried. > See, we want case to be ignored. SOMETIMES. When we ask for it ONLY. Without > having to rewrite lots of code. So you will need a code change to indicate when you are asking? Use of functional indexes is determined by the optimiser, so if you ask case insensitivity, then it will oblige if it can. I'm not saying functional indexes are your answer, I was just asking if they would do the same job. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+ sending to informix-list
Mark D. Stock wrote: > > Well I don't think you are going to get any new features "grafted" to > your current version. So you are going to have to deliver SOMETHING. > ;-) The 7's are still getting all the new goods grafted in, so long as they are relevant to the traditional data types only. That is, unless I've missed some news lately. The cheaper licence fee for 7 is a strong persuader. > So you will need a code change to indicate when you are asking? Use of > functional indexes is determined by the optimiser, so if you ask case > insensitivity, then it will oblige if it can. > > I'm not saying functional indexes are your answer, I was just asking > if they would do the same job. Then we are both asking :-) It's like this - imagine you have a form with someones name on it. Imagine you have hundreds of forms like this. Now they use a CONSTRUCT to generate the WHERE clause. This is grafted into a select: ' where customer.fname matches "john*" and customer.state = "CA" and orders.order_no > 2000' It will be a major task to attack EVERY construct and mess with the string to plug in UPSHIFT on the customer.fname If my understanding of functional indexes is correct, unless the query contains UPSHIFT(fname) then this index will not be used. If, however, we did the simple job of converting the fname field from CHAR(20) to NCHAR(20) and applied a GLS locale with a case-insensitive collection order to the database, then by 2 simple strokes of the magic wand (OTC, silence) we have allowed case-insensitive searches to the entire application without changing one line of code.
Andrew Hamm wrote: > Mark D. Stock wrote: >>Well I don't think you are going to get any new features "grafted" to >>your current version. So you are going to have to deliver SOMETHING. >>;-) > > The 7's are still getting all the new goods grafted in, so long as they are > relevant to the traditional data types only. That is, unless I've missed > some news lately. The cheaper licence fee for 7 is a strong persuader. You can safely assume that IDS 7.31 won't be getting any new features in the future - nor will IDS 9.30 or anything earlier. New features will only appear in IDS 9.50 (and some might possibly be retrofitted into 9.40). -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
Jonathan Leffler wrote: > > You can safely assume that IDS 7.31 won't be getting any new features > in the future - nor will IDS 9.30 or anything earlier. New features > will only appear in IDS 9.50 (and some might possibly be retrofitted > into 9.40). So that's the end of the 7.XX line? No 7.40 coming out? I /have/ missed some news then!
"Andrew Hamm" <ahamm@mail.com> wrote in message news:c0pp53$19los9$1@ID-79573.news.uni-berlin.de... > Jonathan Leffler wrote: > > > > You can safely assume that IDS 7.31 won't be getting any new features > > in the future - nor will IDS 9.30 or anything earlier. New features > > will only appear in IDS 9.50 (and some might possibly be retrofitted > > into 9.40). > > So that's the end of the 7.XX line? No 7.40 coming out? I /have/ missed some > news then! Yes, you have. Also, even customers who have a meticulously paid-up support contract are required to pay an uplift charge to move to IDS v9, which has caused a fair deal of resentment amongst users, and which may well do so again when more people realise. Recognising this, IBM introduced a rebate scheme which would allow a discount on this upgrade under certain circumstances, but for bureaucratic reasons this offer was confined to N America only.
Andrew Hamm wrote: > then by 2 simple strokes of the magic > wand (OTC, silence) we have allowed case-insensitive searches to the > entire application without changing one line of code. I'm not even going to stroke your magic wand once, mate. -- "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche