IDS Forum

RES: RES: RES: Collation Type doubts

Posted By: Alberto Romeu Pessonio Filho
Date: Thursday, 21 July 2011, at 7:02 a.m.

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

Thanks John... It solved my problem!!!

I'm planning the migration to 11.7 for the next months.

Regards,
Alberto.

-----Mensagem original-----
De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de John
Miller iii
Enviada em: quarta-feira, 20 de julho de 2011 16:24
Para: ids@iiug.org
Assunto: Re: RES: RES: Collation Type doubts [24415]

In short you are getting the wrong like operator to execute. My examp=
le
was very precise. If you change the column types and cast operators th=
en
different things will happen. I had the data stored in a column of typ=
e
ichar
and remove the default casts.

Have you removed the default casts?? You will have to play around to
ensure the operator you get is the correct one.

you might try
select * from tbdominiotiss where like( descricao::ichar,
"%procedimento%"::char(20) )

or what ever operators you defined the function with.

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/20/2011 11:06:13 AM:

> From: "Alberto Romeu Pessonio Filho" <arfilho@orizonbrasil.com.br>
> To: ids@iiug.org
> Date: 07/20/2011 11:07 AM
> Subject: RES: RES: Collation Type doubts [24414]
> Sent by: ids-bounces@iiug.org
>
> John:
>
> Column "descri=E7=E3o" is a lvarchar(500).
>
> This statement: select * from tbdominiotiss where descricao::ichar li=

ke
> "%procedimento%=3D "
> returns 113 rows, the same as whe I was using like clause.
>
> []'s
> Alberto.
>
> -----Mensagem original-----
> De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Joh=

n
Miller
> iii
> Enviada em: quarta-feira, 20 de julho de 2011 12:38
> Para: ids@iiug.org
> Assunto: Re: RES: Collation Type doubts [24413]
>
> You are probably executing the wrong function. This is generally due =

t=3D
> o
> the implicit cast of data types. What is the column type of descricao=

?=3D
>
> If it is not ichar then you have a problem. Try the following:
>
> select * from tbdominiotiss where descricao::ichar like "%procediment=

o%=3D
> "
>
> If descricao is already an ichar then make sure you dropped the cast =

wh=3D
> ich
> are automatically created when you create an implicit type.
>
> 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/20/2011 07:46:00 AM:
>
> > From: "Alberto Romeu Pessonio Filho" <arfilho@orizonbrasil.com.br>
> > To: ids@iiug.org
> > Date: 07/20/2011 07:46 AM
> > Subject: RES: Collation Type doubts [24412]
> > Sent by: ids-bounces@iiug.org
> >
> > Hi John!!!
> >
> > Im my case, I've created the function that you sent me and the func=

ti=3D
> on
> is
> > working exactly like the function "LIKE".
> >
> > Look the examples:
> >
> > Table tbdominiotiss:
> >
> > I need to return 174 rows with this words in case sensitive:
> >
> > select * from tbdominiotiss where descricao like '%procedimento%'
> > union all
> > select * from tbdominiotiss where descricao like '%Procedimento%'
> > union all
> > select * from tbdominiotiss where descricao like '%PROCEDIMENTO%';
> >
> > When I execute:
> > select * from tbdominiotiss where descricao like '%procedimento%';
> > returns 113 rows
> >
> > When I execute:
> > select * from tbdominiotiss where descricao like '%Procedimento%';
> > returns 61 rows
> >
> > When I execute:
> > select * from tbdominiotiss where descricao like '%PROCEDIMENTO%';
> > returns 61 rows
> >
> > OK... The function was published succesfully, and look the output o=

f =3D
> the
> > executions using "matches" function:
> >
> > When I execute:
> > select * from tbdominiotiss where descricao matches "*procedimento*=

"
> > Returns 113 rows
> >
> > When I execute:
> > select * from tbdominiotiss where descricao matches "*Procedimento*=

"
> > Returns 61 rows
> >
> > The expected result must be 174 rows for any query using matches, i=

ns=3D
> 't
> it?
> >
> > Tks,
> > Alberto.
> >
> > -----Mensagem original-----
> > De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de J=

oh=3D
> n
> Miller
> > iii
> > Enviada em: ter=3DE7a-feira, 19 de julho de 2011 21:11
> > Para: ids@iiug.org
> > Assunto: Re: Collation Type doubts [24409]
> >
> > Here is what you are looking to do. Please check the code for your =

se=3D
> lf
> as
> > I
> > just threw this together. I did make this a little more complicated=

a=3D
> s I
> > used
> > the UDR fast path execute model to exeute the matches code faster. =

Th=3D
> is
> > saves the overhead of the SQL code for simple udr executtion. You c=

an=3D
>
> > make this run even faster by saving the handle to the udr in sessio=

n
> memory
> > the first time and not retrieving it on every evaluation, but that =

is=3D
>
> > another
> > exercise.
> >
> > Of course you can just move to version 11.70 and all this is alread=

y
> there
> > -:)
> >
> > drop database if exists d1;
> >
> > create database d1 with log;
> > {*
> > **********************************
> > **** TIME ZONE ****
> > ***********************************
> > *}
> >
> > CREATE DISTINCT TYPE ichar AS lvarchar;
> >
> > DROP CAST( lvarchar as ichar );
> > DROP CAST( ichar as lvarchar );
> >
> > CREATE FUNCTION matches(ichar, char(20))
> >
> > RETURNS BOOLEAN
> >
> > WITH (NOT VARIANT)
> >
> > EXTERNAL NAME '$INFORMIXDIR/lib/c_udr_lib.so(jfm_imatch)'
> >
> > LANGUAGE C;
> >
> > create table t1
> >
> > (
> >
> > c1 serial,
> >
> > c2 ichar,
> >
> > c3 lvarchar
> >
> > );
> >
> > insert into t1 values (0,"JOHN","John");
> > insert into t1 values (0,"MILLER","miller");
> > insert into t1 values (0,"JOHN","miller");
> >
> > select * from t1 where c2 matches "mill*";
> >
> > {*
> > *************************
> > **** OUTPUT ******
> > *************************
> > c1 2
> > c2 MILLER
> > c3 miller
> >
> > 1 row(s) retrieved.
> >
> > *}
> >
> > mi_boolean
> > jfm_imatch(mi_lvarchar *v1,mi_lvarchar *v2, MI_FPARAM *fparam)
> > {
> >
> > mi_string *s1,*s2;
> >
> > MI_FUNC_DESC *funcdesc=3D3DNULL;
> >
> > mi_string funcsig[2*IDENTSIZE+20];
> >
> > char msg[3*IDENTSIZE+20];
> >
> > MI_CONNECTION *conn =3D3D NULL;
> >
> > MI_DATUM funcresult;
> >
> > int funcerror;
> >
> > mi_string *str1,*str2;
> >
> > conn =3D3D mi_open(NULL, NULL, NULL);
> >
> > if (conn =3D3D=3D3D NULL)
> >
> > mi_db_error_raise(NULL, MI_EXCEPTION, "Bad connection");
> >
> > if ( mi_switch_mem_duration(PER_ROUTINE) =3D3D=3D3D MI_ERROR ||
> >
> > mi_fp_argisnull(fparam,0) =3D3D=3D3D MI_TRUE ||
> >
> > mi_fp_argisnull(fparam,1) =3D3D=3D3D MI_TRUE ||
> >
> > ( s1 =3D3D mi_lvarchar_to_string(v1)) =3D3D=3D3D NULL ||
> >
> > ( s2 =3D3D mi_lvarchar_to_string(v2)) =3D3D=3D3D NULL )
> >
> > {
> >
> > mi_fp_setreturnisnull(fparam, 0 , MI_TRUE);
> >
> > return 0;
> >
> > }
> >
> > rdownshift(s1);
> >
> > rdownshift(s2);
> >
> > str1 =3D3D mi_string_to_lvarchar(s1);
> >
> > str2 =3D3D mi_string_to_lvarchar(s2);
> >
> > if ( str1=3D3D=3D3DNULL || str2 =3D3D=3D3D NULL )
> >
> > mi_db_error_raise(NULL, MI_EXCEPTION, "Out of memory.");
> >
> > sprintf(funcsig, "matches(lvarchar,lvarchar)");
> >
> > if((funcdesc=3D3Dmi_routine_get(conn, 0, funcsig)) =3D3D=3D3D (MI_F=

UNC_DESC=3D
> *)NULL)
> >
> > {
> >
> > snprintf(msg, sizeof(msg),"Unable to find routine [%s].",funcsig);
> >
> > mi_db_error_raise(NULL, MI_EXCEPTION, msg);
> >
> > }
> >
> > /* =3D3D=3D3D=3D3D=3D3D=3D3D 3. Execute the function */
> >
> > funcresult =3D3D mi_routine_exec(conn, funcdesc, &funcerror, str1, =

str2=3D
> );
> >
> > if (funcerror =3D3D=3D3D MI_ERROR)
> >
> > {
> >
> > if (funcdesc) mi_routine_end(conn, funcdesc);
> >
> > snprintf(msg, sizeof(msg), "Unable to execute function [ %s ] ",
> > funcsig);
> >
> > mi_db_error_raise(NULL, MI_EXCEPTION, msg);
> >
> > }
> >
> > if (funcdesc) mi_routine_end(conn, funcdesc);
> >
> > return (mi_boolean)funcresult;
> > }
> >
> > 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/19/2011 05:38:32 AM:
> >
> > > From: "ALBERTO ROMEU PESSONIO FILHO" <arfilho@orizonbrasil.com.br=

>
> > > To: ids@iiug.org
> > > Date: 07/19/2011 05:39 AM
> > > Subject: Re: Collation Type doubts [24405]
> > > Sent by: ids-bounces@iiug.org
> > >
> > > 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)=3D3D=3D3DMI_ERROR ||
> > >
> > > ( s1 =3D3D mi_lvarchar_to_string(v1)) =3D3D=3D3D NULL ||
> > >
> > > ( s2 =3D3D mi_lvarchar_to_string(v2)) =3D3D=3D3D 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)=3D3D=3D3D0 ?
> > >
> > > MI_TRUE : MI_FALSE);
> > > }
> > > mi_boolean
> > >
> > > jfm_inotequal(mi_lvarchar *v1,mi_lvarchar *v2, MI_FPARAM *fparam)=

{=3D
>
> > >
> > > return (mi_boolean) (jfm_icompare(v1,v2,fparam)!=3D3D0 ?
> > >
> > > MI_TRUE : MI_FALSE );
> > > }
> > >
> > > Ok... I tested your first example described on the presentation(t=

ha=3D
> t
> > seemed
> > > more clear to me... lol...) But could you give me an example of h=

ow=3D
> can
> I
> > use
> > > that function to transform the string that I gave as an argument =

in=3D
>
> case
> > > insensitive, based on the query that I listed below?
> > >
> > > Select * from tbdominiotiss where descricao like '%Procedimento%'=

> > >
> > > I wonder how this is pretty easy for you, but this is my first ti=

me=3D
>
> using
> > UDR
> > > routines. So please, show me how to use.
> > >
> > > Thanks in advance,
> > > Alberto Pessonio.
> > >
> > >
> > >
> >
> >
> >
> *********************************************************************=

**=3D
> ********
>
> >
> > > Forum Note: Use "Reply" to post a response in the discussion foru=

m.=3D
>
> > >
> >
> >
> >
> *********************************************************************=

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

> >
> >
> >
> *********************************************************************=

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

=3D
>
> >=3D
>
>
>
***********************************************************************=

********

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

********

> 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.