IDS Forum

RES: Collation Type doubts

Posted By: Alberto Romeu Pessonio Filho
Date: Tuesday, 19 July 2011, at 9:50 a.m.

In Response To: Re: Collation Type doubts (John Miller iii)

Hi John!

I've found your C sample code. Check if is this code:

mi_integer

jfm_icompare(mi_lvarchar *v1,mi_lvarchar *v2,MI_FPARAM *fparam)
{

mi_string *s1,*s2;

if ( mi_switch_mem_duration(PER_ROUTINE)==MI_ERROR ||

( s1 = mi_lvarchar_to_string(v1)) == NULL ||

( s2 = mi_lvarchar_to_string(v2)) == NULL )

{

mi_fp_setreturnisnull(fparam, 0 , MI_TRUE);

return 0;

}

return strcasecmp(s1,s2);
}

mi_boolean

jfm_iequal(mi_lvarchar *v1,mi_lvarchar *v2, MI_FPARAM *fparam)
{

return (mi_boolean) (jfm_icompare(v1,v2,fparam)==0 ?

MI_TRUE : MI_FALSE);
}
mi_boolean

jfm_inotequal(mi_lvarchar *v1,mi_lvarchar *v2, MI_FPARAM *fparam)
{

return (mi_boolean) (jfm_icompare(v1,v2,fparam)!=0 ?

MI_TRUE : MI_FALSE );
}

Ok... I tested your first example (that seemed more clear to me...
lol...)
But could you give an example of how can I use that function to
transform my search in case insensitive, based on the query that I
listed below?

Thanks in advance,
Alberto Pessonio.

-----Mensagem original-----
De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de John
Miller iii
Enviada em: segunda-feira, 18 de julho de 2011 15:52
Para: ids@iiug.org
Assunto: Re: Collation Type doubts [24400]

while item #4 sounds hard, it is actually pretty ease. I have provided
the code to complete this in presentations at the 2010 IIUG conference.

John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)

ids-bounces@iiug.org wrote on 07/18/2011 10:22:36 AM:

> From: "Art Kagel" <art.kagel@gmail.com>
> To: ids@iiug.org
> Date: 07/18/2011 10:23 AM
> Subject: Re: Collation Type doubts [24399]
> Sent by: ids-bounces@iiug.org
>
> Informix 11.50 does not support case insensitive filters directly. You

have
> several options arranged in increasing utility:
>
> 1. Store a copy of the column mapped to all upper or all lower case
and
>
> filter on that.
>
> 2. Create a function index on the column using a function that returns

>
> the string mapped to all upper or all lower case. Then you can filter
using
>
> that function - but it won't support LIKE comparisons that start with
a
>
> wildcard character (_ or %).
>
> 3. Use the Basic Text Search datablade to index the column and perform

>
> comparisons using the BTS facilities.
>
> 4. Create a new User Defined Type that provides case insensitive
>
> comparison functions and create the table with the columns that need
that

>
> requirement using the new UDT.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own
opinions
and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other
> organization with which I am associated either explicitly, implicitly,
or
by
> inference. Neither do those opinions reflect those of other
individuals
> affiliated with any entity with which I am affiliated nor those of the

> entities themselves.
>
> On Mon, Jul 18, 2011 at 10:53 AM, ALBERTO ROMEU PESSONIO FILHO <
> arfilho@orizonbrasil.com.br> wrote:
>
> > Hi folks!
> >
> > IDS 11.50 FC7W1GE
> > Linux RedHat 64 x86_64 platform
> >
> > I'm having an issue when I'm trying to select data using like
clause.
> > ---------------------------------------------------------------
> > Scenario:
> >
> > - Statement 1:
> >
> > select * from tbdominiotiss
> > where descricao like '%procedimento%';
> >
> > Returns 113 rows. - OK
> >
> > - Statement 2:
> >
> > select * from tbdominiotiss
> > where descricao like '%Procedimento%';
> >
> > Returns 61 rows - OK
> >
> > -----------------------------------------------------------
> >
> > The Collate of Database is: en_us.819
> > The Collate of client is en_us.CP1252
> >
> > This is our Environment Variables:
> >
> > [informix@valencia ~]$ onstat -g env
> >
> > IBM Informix Dynamic Server Version 11.50.FC7W1GE -- On-Line -- Up
34
days
> > 02:37:52 -- 14779012 Kbytes
> >
> > Server start-up environment:
> >
> > Variable Value [values-list]
> > DBDATE DMY4/
> > DBDELIMITER |
> > DBPATH .
> > DBPRINT lp -s
> > DBTEMP /tmp
> > DBUPSPACE 100000:50
> > INFORMIXDIR /opt/IBM/informix
> >
> > [/opt/IBM/informix]
> >
> > [/usr/informix]
> > INFORMIXSERVER ifxorizon1
> > INFORMIXSQLHOSTS /opt/IBM/informix/etc/sqlhosts
> > INFORMIXTERM terminfo
> > LANG en_US.UTF-8
> > LC_COLLATE en_US.UTF-8
> > LC_CTYPE en_US.UTF-8
> > LC_MONETARY en_US.UTF-8
> > LC_NUMERIC en_US.UTF-8
> > LC_TIME en_US.UTF-8
> > LKNOTIFY yes
> > LOCKDOWN no
> > NODEFDAC no
> > ONCONFIG onconfig.ifxorizon1
> > PATH /opt/IBM/informix/bin:/home/informix/bin:/usr/kerberos/bin:
> >
> > /usr/local/bin:/bin:/usr/bin:/sbin:/usr/kerberos/sbin:/usr
> >
> > /kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/u
> >
> > sr/sbin:/usr/bin:/root/bin:/opt/IBM/informix/bin:/home/inf
> >
> > ormix/util:/opt/IBM/csdk/bin
> > SERVER_LOCALE en_US.819
> > SHELL /bin/bash
> > TERM xterm
> >
> > [xterm]
> >
> > [dumb]
> > TERMCAP /etc/termcap.
> >
> > I need to transform any key word of my query in case insensitive, to

bring
> > 113+61 = 174 rows, instead of IDS bring me rows using case sensitive

> > filter.
> >
> > Can anybody help me?
> >
> > Regards,
> > Alberto.
> >
> >
> >
> >
>

************************************************************************
*******

> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --20cf307d00f0a2444a04a85b3ecd
>
>
>

************************************************************************
*******

> Forum Note: Use "Reply" to post a response in the discussion forum.
>

************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• Collation Type doubts
  ALBERTO ROMEU PESSONIO FILHO -- Monday, 18 July 2011, at 10:53 a.m.
  • Re: Collation Type doubts
    Art Kagel -- Monday, 18 July 2011, at 1:22 p.m.
    • Re: Collation Type doubts
      John Miller iii -- Monday, 18 July 2011, at 2:52 p.m.
      • Re: Collation Type doubts
        Art Kagel -- Monday, 18 July 2011, at 3:11 p.m.
        • Re: Collation Type doubts
          ALBERTO ROMEU PESSONIO FILHO -- Tuesday, 19 July 2011, at 8:38 a.m.
          • Re: Collation Type doubts
            John Miller iii -- Tuesday, 19 July 2011, at 8:10 p.m.
            • RES: Collation Type doubts
              Alberto Romeu Pessonio Filho -- Wednesday, 20 July 2011, at 10:45 a.m.
              • Re: RES: Collation Type doubts
                John Miller iii -- Wednesday, 20 July 2011, at 11:38 a.m.
                • RES: RES: Collation Type doubts
                  Alberto Romeu Pessonio Filho -- Wednesday, 20 July 2011, at 2:06 p.m.
                  • Re: RES: RES: Collation Type doubts
                    John Miller iii -- Wednesday, 20 July 2011, at 3:23 p.m.
                    • RES: RES: RES: Collation Type doubts
                      Alberto Romeu Pessonio Filho -- Thursday, 21 July 2011, at 7:02 a.m.
      • RES: Collation Type doubts
        Alberto Romeu Pessonio Filho -- Tuesday, 19 July 2011, at 9:50 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.